看到孤独烟回答粉丝的一个问题, 就想要研究一下MySQL 锁的情况。下面是对MySQL manual locking的一个笔记。
SELECT ... LOCK IN SHARE MODE
sets an IS lock, and SELECT ... FOR UPDATE
sets an IX lock.(after version 8.0, share mode used this way select ... for share
, consistent with update)X | S | IX | IS | |
---|---|---|---|---|
X | Conflict | Conflict | Conflict | Conflict |
S | Conflict | Compatible | Conflict | Compatible |
IX | Conflict | Conflict | Compatible | Compatible |
IS | Conflict | Compatible | Compatible | Compatible |
The intention locking protocol is as follows:
Example:
CREATE TABLE `tt` (
`a` int(11) NOT NULL DEFAULT '0',
`b` varchar(10) COLLATE utf8mb4_bin DEFAULT NULL,
`c` varchar(10) COLLATE utf8mb4_bin DEFAULT NULL,
PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
insert into tt values (1, 'aaa', 'ccc');
insert into tt values (2, 'bbb', 'ccc');
insert into tt values (3, 'ccc', 'ccc');
start transaction;
select * from tt where a = 1 for update;
show engine innodb status;
可以看到如下输出,在 table 上有一个 IX 锁,在行上有一个 X 的 record lock.
2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 127707691, OS thread handle 0x7efbeb65d700, query id 4117992277 10.16.163.157 mams_test
TABLE LOCK table `test1`.`tt` trx id A06213D6 lock mode IX
RECORD LOCKS space id 6784 page no 3 n bits 72 index `PRIMARY` of table `test1`.`tt` trx id A06213D6 lock_mode X locks rec but not gap
Record lock, heap no 6 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 0000a0600c90; asc ` ;;
2: len 7; hex 780000c61b1e0f; asc x ;;
3: len 3; hex 646464; asc ddd;;
4: SQL NULL;
ps: 执行show engine innodb status
之前需要先将lock 监控打开,需要创建两张表,具体信息可见Enabling InnoDB Monitors
CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB;
CREATE TABLE innodb_lock_monitor (a INT) ENGINE=INNODB;
无index Example:
create table ttp (a int, b varchar(10));
insert into ttp values (1, 'cc');
insert into ttp values (1, 'dd');
start transaction;
select * from ttp where b = 'dd' for update;
show engine innodb status;
输出如下,可以看到MySQL自动生成了一个clustered index,而且由于没有索引,这次将表里的两条数据全部加了 X 锁:
TABLE LOCK table `test1`.`ttp` trx id A0622BA2 lock mode IX
RECORD LOCKS space id 6789 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `test1`.`ttp` trx id A0622BA2 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 6; hex 000001ae524d; asc RM;;
1: len 6; hex 0000a0622b13; asc b+ ;;
2: len 7; hex e1000014d20110; asc ;;
3: len 4; hex 80000001; asc ;;
4: len 2; hex 6363; asc cc;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 6; hex 000001ae524e; asc RN;;
1: len 6; hex 0000a0622b2b; asc b++;;
2: len 7; hex f9000084630110; asc c ;;
3: len 4; hex 80000001; asc ;;
4: len 2; hex 6464; asc dd;;
REPEATABLE READ
and SERIALIZABLE
isolation level. You can disable gap lock explicitly by changing the isolation level to READ COMMITTED
innodb_locks_unsafe_for_binlog = 1
(not in 8.0 doc)Example:
create table ttp (a int, b varchar(10));
insert into ttp values (1, 'cc');
insert into ttp values (2, 'dd');
insert into ttp values (20, 'dd');
insert into ttp values (25, 'dd');
create index idx_a on ttp(a); -- 必不可少
delete from ttp where a = 23;
show engine innodb status
显示如下:
TABLE LOCK table `test1`.`ttp` trx id A0640DCF lock mode IX
RECORD LOCKS space id 6789 page no 4 n bits 72 index `idx_a` of table `test1`.`ttp` trx id A0640DCF lock_mode X locks gap before rec
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000019; asc ;;
1: len 6; hex 000001ae5251; asc RQ;;
执行insert into ttp values(22, '');``insert into ttp values(24, '');
都不可以,但是insert into ttp values(25, '');
是OK的,说明不是一个next-key lock.
next-key lock range example: Suppose that an index contains the values 10, 11, 13, and 20.
(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)
For the last interval, the next-key lock locks the gap above the largest value in the index and the “supremum” pseudo-record having a value higher than any value actually in the index. The supremum is not a real index record, so, in effect, this next-key lock locks only the gap following the largest index value.
Example:
select * from ttp where a> 20 for update;
show engine innodb status
shows:
TABLE LOCK table `test1`.`ttp` trx id A067CC23 lock mode IX
RECORD LOCKS space id 6789 page no 4 n bits 80 index `idx_a` of table `test1`.`ttp` trx id A067CC23 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
Example:
session1:
start transaction;
select * from tt where a > 100 for update;
session2:
start transaction;
insert into tt(a) values (91); --waiting
show engine innodb status;
shows
insert into tt(a) values (91)
------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6784 page no 3 n bits 80 index `PRIMARY` of table `test1`.`tt` trx id A068EAF9 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000066; asc f;;
1: len 6; hex 0000a068e94a; asc h J;;
2: len 7; hex d600005a94011d; asc Z ;;
3: SQL NULL;
4: SQL NULL;
------------------
TABLE LOCK table `test1`.`tt` trx id A068EAF9 lock mode IX
RECORD LOCKS space id 6784 page no 3 n bits 80 index `PRIMARY` of table `test1`.`tt` trx id A068EAF9 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000066; asc f;;
1: len 6; hex 0000a068e94a; asc h J;;
2: len 7; hex d600005a94011d; asc Z ;;
3: SQL NULL;
4: SQL NULL;
---TRANSACTION A068E9BF, ACTIVE 66 sec
2 lock struct(s), heap size 376, 2 row lock(s)
MySQL thread id 127963261, OS thread handle 0x7efbf5bb0700, query id 4122875322 10.16.163.157 mams_test
TABLE LOCK table `test1`.`tt` trx id A068E9BF lock mode IX
RECORD LOCKS space id 6784 page no 3 n bits 80 index `PRIMARY` of table `test1`.`tt` trx id A068E9BF lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000066; asc f;;
1: len 6; hex 0000a068e94a; asc h J;;
2: len 7; hex d600005a94011d; asc Z ;;
3: SQL NULL;
4: SQL NULL;
compatiblity table:
acquiring ↓/acquired→ | Gap | Insert Intention | Record |
---|---|---|---|
Gap | Compatible | Compatible | Compatible |
Insert Intention | Conflict | Compatible | Compatible |
Record | Conflict | Compatible | Compatible |
SELECT statements will not lock any rows while running which is generally correct, however there a notable exception – INSERT INTO table1 SELECT * FROM table2. This statement will perform locking read (shared locks) for table2 table. It also applies to similar tables with where clause and joins.
from INSERT INTO … SELECT Performance with Innodb tables.
If a non-INSERT write operation is more likely to not match any row because the INSERT part is yet to come on the transaction, don’t do it or use REPLACE INTO or use READ-COMMITTED transaction isolation.
from One more InnoDB gap lock to avoid
REPEATABLE READ For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE, and DELETE statements, locking depends on whether the statement uses a unique index with a unique search condition, or a range-type search condition.