MySQL supports "for update" keyword. Here is how I tested that it is working as expected. I opened 2 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 the transaction and tried to take an update lock on 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 as the transaction was being processed by another application in the Window No 1. Once the transaction was over, I got to select the record, but since the id 2 was changed to id 3 by the transaction that was executed first, no record was returned.
My question is what are the disadvantages of using "for update" syntax? If I do not commit the transaction that is running in window 1 will the record be locked for-ever?
