Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Does a transaction stop all race condition problems in MySQL?

Consider this situation:

  1. Begin transaction
  2. Insert 20 records into a table with an auto_increment key
  3. Get the first insert id (let's say it's 153)
  4. Update all records in that table where id >= 153
  5. Commit

Is step 4 safe?

That is, if another request comes in almost precisely at the same time, and inserts another 20 records after step 2 above, but before step 4, will there be a race condition?

like image 931
nickf Avatar asked May 11 '10 07:05

nickf


1 Answers

That is, if another request comes in almost precisely at the same time, and inserts another 20 records after step 2 above, but before step 4, will there be a race condition?

Yes, it will.

Records 21 to 40 will be locked by the transaction 2.

Transaction 1 will be blocked and wait until transaction 2 commits or rolls back.

If transaction 2 commits, then transaction 1 will update 40 records (including those inserted by transaction 2)

like image 173
Quassnoi Avatar answered Sep 28 '22 03:09

Quassnoi