Mysql InnoDB row locking from read

Question

I have read mysql documentation and maybe I just don't understand it all. What I'm looking for is the row locking from reading for other sessions. While the row is locked I would like the other sessions to wait until the lock is released. I don't want the other sessions to read from snapshot! they must wait until release is made!

What I would like to get is exactly the same as the following example but for a single row instead of whole table lock:

START TRANSACTION
LOCK TABLES table1 WRITE
SELECT * FROM table1 WHERE id = 40912
UPDATE table1 SET column1 = 'value1' WHERE id = 40912
UNLOCK TABLES
COMMIT

Thank you in advance!

score 3 · Answer 1 · answered Dec 06 '12 at 18:06

The answer from Elena Sharovar is a way to do it, but perhaps doesn't fully explain what's required. Every process that's going to perform the SELECT needs to use GET_LOCK with the same string. If they do, MySQL will automatically wait (up to the number of seconds specified) to return a result until the other thread releases the lock.

Here's what happens, with time proceeding from top to bottom:

          THREAD 1                                 THREAD 2
DO GET_LOCK("table1.40912", 30)
SELECT * FROM table1 WHERE id=40912     DO GET_LOCK("table1.40912", 30)
[UPDATE query]                          [automatically waits]
[UPDATE query]                          [automatically waits]
DO RELEASE_LOCK("table1.40912")         [lock finally obtained, query terminates]
                                        SELECT * FROM table1 WHERE id=40912

score 1 · Answer 2 · answered Oct 24 '12 at 08:11

1

I did not try to do it yet, but it's possible using MySQL functions GET_LOCK(str,timeout) and RELEASE_LOCK(str). Here "str" is any string identificating your lock.

mysql_query('DO GET_LOCK("table1.40912", 60)')
mysql_query('SELECT * FROM table1 WHERE id = 40912')
mysql_query('UPDATE table1 SET column1 = 'value1' WHERE id = 40912')
mysql_query('DO RELEASE_LOCK("table1.40912")')

answered Oct 24 '12 at 08:11

Elena Sharovar

336
2
7

Hi! thank you for your answer but it seems that it's not working ... another session (select) still returns the values from the snapshot! – DarkSideOfTheMoon83 Oct 24 '12 at 08:59

score 1 · Answer 3 · answered Oct 24 '12 at 08:24

The best idea I have for this interesting problem (how to force MySQL to not read a snapshot, even if the snapshot is consistent) would be to read everything using "FOR UPDATE", as in "SELECT FOR UPDATE #select-clause#".

However, it is very important that these selects find the records using very narrow indexes, meaning they lock what they read. Having a SELECT running wild via a full table scan or even a USING WHERE (in EXPLAIN statements) would be bad, long locking times of large areas of the table could lead to potentially long reaction times.

I'd suggest you test it in your database abstraction layer.

You can read all about this mechanism in the isolation documentation.

Mysql InnoDB row locking from read

3 Answers3