MySQL supports the keyword "for updating". This is how I tested that it works as expected. I opened two browser tabs and executed the following commands in one window.
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from myxml where id = 2 for update; .... mysql> update myxml set id = 3 where id = 2 limit 1; Query OK, 1 row affected, 1 warning (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> commit; Query OK, 0 rows affected (0.08 sec)
In another window, I started a transaction and tried to lock the update in the same record.
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from myxml where id = 2 for update; Empty set (43.81 sec)
As you can see from the above example, I could not select the record for 43 seconds when the transaction was processed by another application in window No. 1. As soon as the transaction was completed, I should select the record, but since id 2 was changed to id 3 for the transaction, which was completed first, the record was not returned.
My question is, what are the disadvantages of using the "for update" syntax? If I do not complete the transaction running in window 1, will the record be locked forever?
source share