// book(seat_id, user_id) PostgreSQL Begin Transaction(read_commited) seat = select * from seats where id = seat_id for update if(seat == null seat.status == "booked") rollback return error("Seat isnt available") insert into bookings (user_id, seat_id, status, created_at) values (used_id, ...) update seats set status = 'booked' where id = seat_id commit return success()

This code demonstrates transactional locking and seat reservation in PostgreSQL with the read_committed isolation level.

Main idea:

• A transaction begins.
• A seat is selected with a FOR UPDATE lock to prevent concurrent booking of the same seat.
• If the seat is not found or already booked, the transaction is rolled back and an error is returned.
• If the seat is available, a booking record is created and the seat status is updated to "booked".
• The transaction is committed.

This approach prevents race conditions during parallel attempts to book the same seat.

Example in Kotlin using JDBC:

fun bookSeat(seatId: Int, userId: Int): Boolean {
    val connection = dataSource.connection
    try {
        connection.autoCommit = false
        connection.transactionIsolation = Connection.TRANSACTION_READ_COMMITTED

        val seatStmt = connection.prepareStatement("SELECT status FROM seats WHERE id = ? FOR UPDATE")
        seatStmt.setInt(1, seatId)
        val rs = seatStmt.executeQuery()

        if (!rs.next() || rs.getString("status") == "booked") {
            connection.rollback()
            return false // Seat is unavailable
        }

        val insertBooking = connection.prepareStatement(
            "INSERT INTO bookings (user_id, seat_id, status, created_at) VALUES (?, ?, 'booked', NOW())"
        )
        insertBooking.setInt(1, userId)
        insertBooking.setInt(2, seatId)
        insertBooking.executeUpdate()

        val updateSeat = connection.prepareStatement("UPDATE seats SET status = 'booked' WHERE id = ?")
        updateSeat.setInt(1, seatId)
        updateSeat.executeUpdate()

        connection.commit()
        return true
    } catch (e: SQLException) {
        connection.rollback()
        throw e
    } finally {
        connection.close()
    }
}

This code guarantees that two users cannot simultaneously book the same seat.