SELECT LOCK IN SHARE MODE

Question

i have read this article from dev.mysql.

in that page is a example that when use select for update and dont use lock in share mode and says

Here, LOCK IN SHARE MODE is not a good solution because if two users read the counter at the same time, at least one of them ends up in deadlock when it attempts to update the counter

but first line of this page says

SELECT ... LOCK IN SHARE MODE: The rows read are the latest available, ** so if they belong to another transaction ** that has not yet committed, the read blocks until that transaction ends.

is there a paradox?

i mean two users dont read the counter at the same time beacause if they belong to another transaction the read blocks until that transaction ends.

score 25 · Accepted Answer · answered Jul 07 '11 at 07:32

25

If there is another transaction, that has modified the row, the SELECT ... LOCK IN SHARE MODE waits. If the row is not modified, it does not wait. Which leads to the first situation, that 2 transaction can SELECT ... LOCK IN SHARE MODE, but none of them can update the record (deadlock)

answered Jul 07 '11 at 07:32

Maxim Krizhanovsky

26,265
5
59
89

so select for update wait if bloked row modified or not modified by user1 before commit and lock in share mode wait only if blocked row is modified by user1 before commit? – user006779 Jul 07 '11 at 08:14
6

the difference is that you can select one and the same record in 2 transactions with select...lock in share mode, but you cannot with select...for update. select for update blocks another select for update or select lock in share mode on the same record. – Maxim Krizhanovsky Jul 07 '11 at 08:54

broadband · Answer 2 · 2013-12-16T07:53:56.630

Try this. Open two terminals e.g. powershell in Windows, xterm, console in Linux, .... Connect to MySQL:

create table child_codes (taken from MySQL documentation)

mysql> create table child_codes (counter_field integer);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into child_codes set counter_field = 1;
Query OK, 1 row affected (0.00 sec)

session 1 (terminal 1):              session 2 (terminal 2):                                                                        

                                            mysql> start transaction;
                                            Query OK, 0 rows affected (0.00 sec)

                                            mysql> select counter_field from child_codes
                                                      lock in share mode;
                                            +---------------+
                                            | counter_field |
                                            +---------------+
                                            |             1 |
                                            +---------------+
                                            1 row in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select counter_field from
          child_codes lock in share mode;
+---------------+
| counter_field |
+---------------+
|             1 |
+---------------+

                                             mysql> update child_codes set counter_field = 2;
                                             ERROR 1205 (HY000): Lock wait timeout exceeded;
                                             try restarting transaction

I too thought that if other transaction executes query select lock in share mode this query is blocked (waiting for other transaction to commit or rollbacks). But like Darhazer mentioned If the row is not modified, it does not wait. I believe this behaviour must be mentioned in MySQL documentation.

The example appears to be impractical and contradicts with, "*Other sessions can read the rows, but cannot modify them until your transaction commits. If any of these rows were changed by another transaction that has not yet committed, your query waits until that transaction ends and then uses the latest values.*" No blocking or deadlock should be detected, if another transaction released the lock held. http://dev.mysql.com/doc/refman/5.5/en/innodb-locking-reads.html — Tiny, Mar 04 '16 at 03:52

SELECT LOCK IN SHARE MODE

2 Answers2