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

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.

CodePudding user response：

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.)

CodePudding user response：

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