1. Introduction

Both Java programming language and mysql database have the concept of locks. The purpose of locks is to protect shared resources and avoid data inconsistency caused by concurrent operations on shared resources. InnoDB Locking you can learn about Record Locks, Gap Locks, and next-key Locks. In this article, you can learn about SQL statement Locking.

2. Lock is introduced

  • Record Locks: row lock locks a row of records
  • Gap Locks: gap lock, to lock the range record
  • Next-Key LocksBy:Record LocksandGap Lockscomposition

Example 3.

3.1 create a table

create table t(
    id int(10) not null primary key,
    c int(10) not null,
    d int(10) not null,
index(c));
Copy the code

3.2 Inserting Data

insert into t values(0.0.0);
insert into t values(5.5.5);
insert into t values(10.10.10);
insert into t values(15.15.15);
insert into t values(20.20.20);
insert into t values(25.25.25);
insert into t values(30.30.30);
Copy the code

(0, 5), (5,10), (10,15), (15,20), (20,25), (25, +supremum); (-∞,0], (0, 5], (5,10], (10,15], (15,20], (20,25], (25, +supremum], because the next-key lock contains the row lock, it will form the front open and the back closed range

Before we start the demonstration, let’s take you through the specific locking rules

3.3 Locking rules

  • Next-key lock, next-key lock = gap lock + row lock, open front and close back
  • All objects accessed during the query are locked
  • A next-key lock is degraded to a row lock when a unique index is locked
  • If the last value does not meet the query condition, the next-key lock degrades to a gap lock
  • A range query on an index is accessed up to the first value that does not satisfy the condition

3.4 Lock unique Index Equivalent Query

3.4.1 Record the existence of equivalent query

session1 session2
begin;
select * from t where id = 5 for update;
begin;
insert into t values(8, 8, 8);pass
update t set c = c + 1 where id = 5;blocked
rollback;
rollback;

Select * from next key lock(0, 5); select * from next key lock(0, 5); select * from next key lock(0, 5)

3.4.2 Record non-existent equivalent query

session1 session2
begin;
select * from t where id = 7 for update;
begin;
insert into t values(8, 8, 8);blocked
update t set c = c + 1 where id = 10;pass
rollback;
rollback;

(id = 7); (id = 7); (id = 7); (id = 7);

3.5 Lock equivalent Query with Non-Unique Indexes

3.4.1 Record the existence of equivalent query

session1 session2
begin;
select * from t where c = 5 for update;
begin;
insert into t values(4, 4, 4);blocked
insert into t values(8, 8, 8);blocked
update t set d = d + 1 where c = 5;blocked
update t set d = d + 1 where c = 10;pass
rollback;
rollback;

Lock (0, 5) next key lock(0, 5) next key lock(0, 5) next key lock(0, 5) Lock range is (5,10), and finally lock range is (0,5], (5,10)

3.4.2 Record non-existent equivalent query

session1 session2
begin;
select * from t where c = 7 for update;
begin;
insert into t values(8, 8, 8);blocked
update t set c = c + 1 where c = 10;pass
rollback;
rollback;

Rule 1: Next key lock(5, 10); rule 4: Next key lock(5, 10)

3.6 Lock unique index Range Query

session1 session2
begin;
select * from t where id >= 10 and id < 15 for update;
begin;
insert into t values(12, 12, 12);blocked
update t set d = d + 1 where id = 10;blocked
rollback;
rollback;

Next key lock(5,10) next key lock(5,10) next key lock(5,10) next key lock(5,10

3.7 Locking Non-Unique Index Range Queries

session1 session2
begin;
select * from t where c >= 5 and c < 10 for update;
begin;
insert into t values(2, 2, 2);blocked
insert into t values(7, 7, 7);blocked
update t set d = d + 1 where c = 10;blocked
rollback;
rollback;

Next key lock(0,5); next key lock(0,5); next key lock(0,5); next key lock(0,5)

4. References

More explanations are available

  • Why am I running so slowly on only one line?
  • What is illusory? What’s wrong with illusory?
  • Why do I only change one line of statements and lock so many?