Mysql for update

Question

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?

Answer 1

Yes, if transaction #1 does not commit, those records will be locked forever, unless the connection drops, or innodb decides to rollback the transaction due to a deadlock detection.

but since the id 2 was changed to id 3 by the transaction that was executed first, no record was returned.

Isn't that what you want? if not, then you are not using SELECT ... FOR UPDATE properly. see http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html