…) mysql> select * from tst for update; +—+ | i | +—+ | 1 | +—+ 1 row in set (0.00 sec) #Standard SELECT does not see rows while SELECT for UPDATE and … Do not assume you can simply add SELECT FOR UPDATE to your select and reduce deadlocks if you’re updating selected rows. As query results may …

… with mysql 5.0.21 on freebsd6 and linux. I also thought that update should succeed, but it does not! It waits for some table level locks which were made by second SELECT FOR UPDATE which in turn waits for X lock. That is why undetectable deadlock… any SELECT FOR UPDATE for the SAME ROW in both transactions. Second transaction will wait for the lock, then from the first transaction try to UPDATE…

Updating data in the same order is good strategy anyway. Yes you’re right SELECT FOR UPDATE should not deadlock with other instances if MySQL executes them same way (which it might not).

… waiting for an X lock on assets since it came in between the SELECT and UPDATE statements in Transaction-1 and hence the deadlock condition. The MySQL 5.0 Reference Guide has… a single TinyInt), then as the first statement for every transaction there is an UPDATE semaphore SET lock=1; Since this is…