SELECT LOCK IN SHARE MODE

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.

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)

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.