MySQL ‘select for update’ behaviour

by

Let me go through your cases and explain how these locks work:

1 case

T1 wants to update some rows in your test table. This transaction puts IX lock on all table and X lock on the first 5 rows.

T2 wants to update some rows in your test table. This transaction puts IX (because IX compatible with IX) lock on all table and tries to first 5 rows but it can’t do it because X is not compatible with X

So we are fine.

2.1 case

T1 wants to update some rows in your test table. This transaction put IX lock on all table and X lock on the first 5 rows.

T2 wants to select some rows from your test table. And it does not place any locks (because InnoDB provides non-locking reads)

2.1 case

T1 wants to update some rows in your test table. This transaction put IX lock on all table and X lock on the first 5 rows.

T2 wants to update (select for update)some rows from your test table. Place IS on the whole table and tries to get S lock on the row and fails because X and S are uncompatible.

Also always be aware of isolation level: different level cause different mechanism to free/acquire locks

Hope it helps

More Related Contents:

  1. Any way to select without causing locking in MySQL?
  2. How to debug Lock wait timeout exceeded on MySQL?
  3. MySQL AUTO_INCREMENT does not ROLLBACK
  4. Are nested transactions allowed in MySQL?
  5. mysql transaction – roll back on any exception
  6. MySQL: Transactions vs Locking Tables
  7. MySQL : transaction within a stored procedure
  8. When to use SELECT … FOR UPDATE?
  9. How to lock a single row
  10. How to enable INNODB in mysql
  11. Detecting locked tables (locked by LOCK TABLE)
  12. How to display open transactions in MySQL
  13. How can I use transactions in my MySQL stored procedure?
  14. Fixing “Lock wait timeout exceeded; try restarting transaction” for a ‘stuck” Mysql table?
  15. Difference between SET autocommit=1 and START TRANSACTION in mysql (Have I missed something?)
  16. How do I lock on an InnoDB row that doesn’t exist yet?
  17. Automatic Rollback if COMMIT TRANSACTION is not reached
  18. Optimistic locking in MySQL
  19. Order by With Union SQL
  20. How to fix: “No suitable driver found for jdbc:mysql://localhost/dbname” error when using pools? [duplicate]
  21. MySQL join with where clause
  22. Error 1022 – Can’t write; duplicate key in table
  23. How to export SQL Server database to MySQL? [duplicate]
  24. SQL Query to get column values that correspond with MAX value of another column?
  25. MySQL – How to search for exact word match using LIKE?
  26. Implementing a hierarchical data structure in a database
  27. MySQL date format
  28. MySQL Group By Hours
  29. RMySQL dbWriteTable with field.types
  30. Equivalent of MSSQL IDENTITY Column in MySQL

Leave a Comment