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 preci开发者_如何学Csely at the same time, and inserts another 20 records after step 2 above, but before step 4, will there be a race condition?

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)

I don't think this can be catalogued as a race condition but rather as a DMBS specific behavior. Basically if the DBMS locks the newly inserted records, then the first transaction will not see the records from the second one until the second transaction is committed.

And of course there is the matter of locking the table, if the first transaction write-locks the table then the second one will be blocked on writes until the first one completes. Not sure though if the standard mysql offers this kind of feature. I know MSSQL server it does.