CODEWITHSUNDEEP
mysqltransactionslockingisolationmvcc
DarkSideOfTheMoon83
DarkSideOfTheMoon83

Reputation: 712

Mysql InnoDB row locking from read

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!

Upvotes: 4

Views: 5341

Answers (3)

giskard22
giskard22

Reputation: 883

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

Upvotes: 3

0xCAFEBABE
0xCAFEBABE

Reputation: 5666

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.

Upvotes: 1

Elena Sharovar
Elena Sharovar

Reputation: 356

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")')

Upvotes: 1

Related Questions