CODEWITHSUNDEEP
mysqllockinginnodb
Anpan
Anpan

Reputation: 1176

MySQL read lock with FOR UPDATE

The following assumes all statements are executed in transactions on MySQL 5.6 with an InnoDB table. Also, col1 and col2 together form a unique key.

If I do a SELECT * FROM table WHERE col1 = 1 AND col2 = 1 FOR UPDATE in Session 1, I get the data from that row and an exclusive lock on that row. So if I execute that same statement on Session 2, it will wait until that lock is released before doing anything or run into a timeout. So far, so good.

Now assume the SELECT ... FOR UPDATE returns an empty set and we try to insert something in another session:

-- Session 1
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM `table` WHERE `col1` = 1 AND `col2` = 1 FOR UPDATE;
Empty set (0.01 sec)
-- Do whatever else takes some time

-- Session 2
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `table` SET `col1` = 1, `col2` = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

This behavior is as I'd expect it. However, if we do this:

-- Session 1
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM `table` WHERE `col1` = 1 AND `col2` = 1 FOR UPDATE;
Empty set (0.00 sec)

-- Session 2
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM `table` WHERE `col1` = 1 AND `col2` = 1 FOR UPDATE;
Empty set (0.00 sec)

mysql> INSERT INTO `table` SET `col1` = 1, `col2` = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Now, what I actually want is that the second SELECT ... FOR UPDATE on the same (not yet existing) rows will wait in the same way an INSERT INTO will. How do I achieve this without locking the whole table, which would not be acceptable because other rows need to stay accessible.

Upvotes: 4

Views: 1680

Answers (1)

Rick James
Rick James

Reputation: 142540

When you let a transaction run more than a few seconds, you are asking for trouble. Write your code to be finished quicker. Your first code sample not innodb_lock_wait_timeout, which defaults to a ludicrously long 50 seconds.

You must write your code to catch errors -- such as the two different ones you got -- and take evasive action. The action is usually to start the entire transaction over. By then, the other session is likely to have finished whatever it was doing, and you should go through 'instantly'.

If you need a really long lock -- such as a shopping cart wherein a user is bouncing among web page for minutes, you must use some other mechanism for "locking", not InnoDB's transaction locking.

If you would like to further describe your application, we can discuss this further.

Upvotes: 1

Related Questions