DEV Community

Cover image for MySql: Locks on (not unique)Index in Repeatable Read Transaction
ShakalakaB
ShakalakaB

Posted on

MySql: Locks on (not unique)Index in Repeatable Read Transaction

* For MySql 8(the version this article uses), we can view locks in table: performance_schema.data_locks. For versions prior to version 8, they are in
table information_schema.innodb_locks.

Key point: Gap lock would be created. Be careful, a gap, not just the index you are searching

For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it;
For other search conditions(not unique index), InnoDB locks the index range scanned, using gap locks or next-key locks to block insertions by other sessions into the gaps covered by the range.(source)

For example:

We create payment table, as following image shows. Column state is a normal index.

id description state customer_id
1 payment1 102 1
2 payment2 100 1
3 payment3 104 2
4 payment4 106 2
5 payment3 104 1
Example1
  • query:
  START TRANSACTION;
  SELECT * FROM payment WHERE state = 104 FOR UPDATE;(or UPDATE payment SET description = "updated from console" WHERE state = 104)
  SELECT OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;
  COMMIT ;
Enter fullscreen mode Exit fullscreen mode
  • lock from performance_schema.data_locks:
ENGINE OBJECT_NAME INDEX_NAME LOCK_TYPE LOCK_MODE LOCK_STATUS LOCK_DATA
INNODB payment NULL TABLE IX GRANTED NULL
INNODB payment state_index RECORD X GRANTED 104, 3
INNODB payment state_index RECORD X GRANTED 104, 5
INNODB payment PRIMARY RECORD X,REC_NOT_GAP GRANTED 3
INNODB payment PRIMARY RECORD X,REC_NOT_GAP GRANTED 5
INNODB payment state_index RECORD X,GAP GRANTED 106, 4
  • locks break down:

    • MySql add row locks on id=3, id=5 as they have 104 state, which means others transactions can't update them.
    • Mysql not only locks rows with state 104, it also crates a gap lock between state 104 and its next value(106, not inclusive) in the table, and it means insertion with state less than 106, greater than 104 would fail. For example, an insertion with state 105 would fail, but with state 106 would succeed.
    • IX- intention lock, table-level locks that indicate which type of lock (shared or exclusive) a transaction requires later for a row in a table. click here for more > The intention locking protocol is as follows: > > Before a transaction can acquire a shared lock on a row in a table, it must first acquire an IS lock or stronger on the table. > > Before a transaction can acquire an exclusive lock on a row in a table, it must first acquire an IX lock on the table.
    • X- exclusive lock, permits the transaction that holds the lock to update or delete a row. Click here for more
    • REC_NOT_GAP indicates a record-only lock, and means the S lock is a record lock.
    • GAP: gap lock, a gap between index records, or a lock on the gap before the first or after the last index record. It should be noted that the row marked as gap lock is not included as lock, so it means it still can be updated or insert.
Example2
  • query:
  START TRANSACTION;
  SELECT * FROM payment WHERE state = 106 FOR UPDATE;(or UPDATE payment SET description = "updated from console" WHERE state = 106)
  SELECT OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;
  COMMIT ;
Enter fullscreen mode Exit fullscreen mode
  • lock from performance_schema.data_locks:
ENGINE OBJECT_NAME INDEX_NAME LOCK_TYPE LOCK_MODE LOCK_STATUS LOCK_DATA
INNODB payment NULL TABLE IX GRANTED NULL
INNODB payment state_index RECORD X GRANTED supremum pseudo-record
INNODB payment state_index RECORD X GRANTED 106, 4
INNODB payment PRIMARY RECORD X,REC_NOT_GAP GRANTED 4
  • locks break down:
    • The SELECT FOR UPDATE is locking between 106 and the next value in the payment table. Since there is no next-value, it is locking until the supremum pseudo-record. In the above example, any value above 106 can't be inserted or updated.

Related Materials:

Discussion (0)