Uploaded image for project: 'Percona Server'
  1. Percona Server
  2. PS-4049

LOCK IN SHARE MODE does not lock UPDATE

    Details

      Description

      LOCK IN SHARE MODE does not lock UPDATE if "select ... lock in share mode;" selected same row in the same transaction.

      How to repeat:

      CREATE TABLE `t` (
      `id` int(11) NOT NULL,
      `f` varchar(100) DEFAULT NULL,
      PRIMARY KEY (`id`)
      ) ENGINE=ROCKSDB;
      insert into t values(12345, 'value1'), (54321, 'value2');

      Session 1:
      session 1> insert into t values(12345, 'value1'), (54321, 'value2');
      Query OK, 2 rows affected (0.05 sec)
      Records: 2 Duplicates: 0 Warnings: 0

      session 1> begin;
      Query OK, 0 rows affected (0.00 sec)

      session 1> select * from t where id=12345 lock in share mode;
      -------------+

      id f

      -------------+

      12345 value1

      -------------+
      1 row in set (0.01 sec)

      Session 2:
      session 2> begin;
      Query OK, 0 rows affected (0.00 sec)

      session 2> select * from t where id=12345 lock in share mode;
      -------------+

      id f

      -------------+

      12345 value1

      -------------+
      1 row in set (0.00 sec)

      session 2> update t set f='value3' where id=12345;
      Query OK, 1 row affected (0.02 sec)
      Rows matched: 1 Changed: 1 Warnings: 0

      I expect UPDATE will be blocked in session 2, but it does not.

      However if I don't run select * from t where id=12345 lock in share mode; in "session 2" everything works as expected:

      session 1> begin;
      Query OK, 0 rows affected (0.00 sec)

      session 1> select * from t where id=12345 lock in share mode;
      -------------+

      id f

      -------------+

      12345 value1

      -------------+
      1 row in set (0.00 sec)

      session 2> begin;
      Query OK, 0 rows affected (0.00 sec)

      session 2> update t set f='value3' where id=12345;
      ERROR HY000: Lock wait timeout exceeded; try restarting transaction

      MTR test case attached

        Smart Checklist

          Attachments

            Activity

              People

              • Assignee:
                george.lorch George Lorch
                Reporter:
                sveta.smirnova Sveta Smirnova
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: