MySQL InnoDB: Intention Lock

This artical states how does Intention Lock work in MySql Server 8.0. Firstly, a testing database and table with seed data will be created. After that, we will see the difference when the table with Unique Constraint and without constraint.

1. Create table for testing (Do not contain Unique Constraints)

CREATE TABLE `test` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into test.test VALUES (1, 'aaa');
insert into test.test VALUES (2, 'bbb');
insert into test.test VALUES (3, 'ccc');
insert into test.test VALUES (4, 'ddd');

1.1 IS Lock

set autocommit = 0;
select id from test.test lock in share mode;

select ENGINE, ENGINE_TRANSACTION_ID, THREAD_ID, 
OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA 
from performance_schema.data_locks;

1.2 IX Lock

commit;
set autocommit = 0;
select id from test.test for update;

select ENGINE, ENGINE_TRANSACTION_ID, THREAD_ID, 
OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA 
from performance_schema.data_locks;

 

2. Add Unique Constraint

CREATE UNIQUE INDEX test_id_IDX USING BTREE ON test.test (id);

2.1  IS Lock

commit;
set autocommit = 0;
select id from test.test lock in share mode;

select ENGINE, ENGINE_TRANSACTION_ID, THREAD_ID, 
OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA 
from performance_schema.data_locks;

 

2.2 IX Lock

commit;
set autocommit = 0;
select id from test.test for update;

select ENGINE, ENGINE_TRANSACTION_ID, THREAD_ID, 
OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA 
from performance_schema.data_locks;

 

3. Difference between Constraint enabled and Constraint disabled

(Rememeber set autocommit = 0)

Without Unique Constraint

	Session#1	Session#2
T1	select * from test where id  = 1 for update ; (1 rows in set)
T2		select * from test where id  = 2 for update ; (Blocked)

As the screenshot is showing, Session#2 is blocked and the entire rows of the table have been locked by X lock. This is because the table does NOT have any unique constraint, so that InnoDB engine was using Table Lock. On the other hand, Session#2 has successfully obtained IX lock, although same lock has been granted by Session#1, which revealed that IX lock is compatible with IX lock.

With Unique Constraint

	Session#1	Session#2
T1	select * from test where id  = 1 for update ; (1 rows in set)
T2		select * from test where id  = 2 for update ; (1 rows in set)

After the unique constraint is added to the table and replay our testing case, we can see that Session#1 did not lock all the rows of the table. Instead, no session was blocked and Session#1 & Session#2 just locked two specific rows (id=1, id=2), becuase Record Lock was applied.