CODEWITHSUNDEEP
mysqldatabaseconcurrencytransactionsspring-transactions
Wrapper
Wrapper

Reputation: 932

Understanding transaction in Spring and on a database level?

Trying to understand how database transactions are implemented in Spring and in databases. I have been writing a lot of queries and used @Transactional, but never actually tried to think about what is happening on a database level.

So, I started browsing here, here and here.

Let's say that we have this piece of code:

@Service
public class ReservationService {
    private static final int SEAT_LIMIT = 50;
    private SeatsRepository seatsRepository;

    @Transactional
    public String reservate(User user){

        int currentSeatsReservations = seatsRepository.getCurrentSeatsReservations();
        if (SEAT_LIMIT - currentSeatsReservations >= 1){
            //make new seat reservation
            seatsRepository.addNewSeatReservation(System.currentTimeMillis(), user.getId());
            return "Congrats! You just made your reservation";
        }
        return "All seats are already booked";

    }
    @Repository
    interface SeatsRepository{
        @Query(value = "SELECT COUNT(id) as CURRENT_TOTAL_SEATS_TAKEN FROM seats;", nativeQuery = true)
        int getCurrentSeatsReservations();
        @Query(value = "INSERT INTO seats(timestamp, user_id) VALUES (:timestamp, :userId);", nativeQuery = true)
        void addNewSeatReservation(long timestamp, int userId);
    }

}

I do get that MySQL default isolation is REPEATABLE_READ. But how will that play along with following situation like on the image?

enter image description here

Lets imagine that we have a simple and single flight to fill up the seats for. Let's say 50 seats. Reservation is a first come, first serve basis. Reservation is made by inserting a new row in a simple seats table.

Table can be seen on the image.

Is this situation possible? I know everything happens fast on database level, but let's say transaction on the left (T1) is for some reason slower then right transaction (T2). Since we are on the REPEATABLE_READ isolation level, DIRTY_READ, NON_REPEATABLE_READS and PHATOM_READS (as concurrency problems) cannot happen.

P.S. any recommendation on a good book or Udemy course on the topic would be appreciated. I'm a beginner though.

Upvotes: 2

Views: 426

Answers (2)

Wrapper
Wrapper

Reputation: 932

Based on Rick James answer. That pointed me if I'm not mistaken to 2PL.

My diagram looks like this. I tested it as well from two command lines, and that is behavior I need.

enter image description here

Upvotes: 1

Rick James
Rick James

Reputation: 142298

START TRANSACTION;
SELECT ... FOR UPDATE;
...
UPDATE ...;
COMMIT;

Note the FOR UPDATE. It adds extra locking to prevent problems such as the one you describe.

(I do not think transaction_isolation_mode is important for your example.)

Upvotes: 1

Related Questions