innodb的锁与隔离级别
如果无法正常显示，请先停止浏览器的去广告插件。
1. INNODB
2. Read uncommitted
Read committed
locks_unsafe_for_binlog
Repeatable read
default
Serializable
(innodb
)
3. INNODB
• innodb
•
RC
innodb RR
statement-based binary logging
4. RC
txn1：update t2 set c2 = c2+1 where c1 in (select c1 from t1);
txn2：upate from t1 where c1 = 2;
txn2：commit;
txn1：commit；
txn2：upate from t1 where c1 = 2;
txn2：commit;
txn1：update t2 set c2 = c2+1 where c1 in (select c1 from t1);
txn1：commit；
5. RR
txn1：update t2 set c2 = c2+1 where c1 in (select c1 from t1);
txn2：insert into t1 values(1,2,3);
txn2：commit;
txn1：commit;
txn2：insert into t1 values(1,2,3);
txn2：commit;
txn1：update t2 set c2 = c2+1 where c1 in (select c1 from t1);
txn1：commit;
6. INNODB
•
•
trx consistent read rec lock
7. •
snapshot read
• •
•
select from table
…
current read(lock read)
select from table lock in share mode;
select rom table for update;
delete/update table;
delete/update from in(select from table);
…
8. •
•
•
•
•
LOCK_IS
LOCK_IX
LOCK_S
LOCK_X
LOCK_AUTO_INC
9. •
•
REC LOCK(LOCK_REC_NOT_GAP)
RANGE LOCK
GAP LOCK(LOCK_GAP
NEXT LOCK(LOCK_ORDINARY
)
)
INSERT INTENTION LOCK(LOCK_INSERT_INTENTION)
10. Read Committed
Repeatable Read
(unsafe_for_binlog)
Repeatable Read Serializable
(safe_for_binlog)
consistent read consistent read consistent read autocommit=ture:
(stmt read view) (trx read view) (trx read view) consistent read
autocommit=false:
range lock(LOCK_S)
SELECT ... FROM ... rec lock(LOCK_S) rec lock(LOCK_S) range lock(LOCK_S) range lock (LOCK_S)
LOCK IN SHARE MODE
SELECT ... FROM ... rec lock(LOCK_X) rec lock(LOCK_X) range lock(LOCK_X) range lock(LOCK_X)
FOR UPDATE
UPDATE ... WHERE … rec lock(LOCK_X) rec lock(LOCK_X) range lock (LOCK_X) range lock(LOCK_X)
DELETE ... WHERE ...
DELETE …WHERE …(SELECT) rec lock(LOCK_S) rec lock(LOCK_S) range lock(LOCK_S) range lock(LOCK_S)
UPDATE … WHERE …(SELECT) consistent read consistent read range lock(LOCK_S) range lock(LOCK_S)
INSERT INTO SELECT (stmt read view) (trx read view)
CREATE TABLE AS SELECT
REPLACE INTO SELECT
SELECT ... FROM
11. Read Committed
Repeatable Read
(unsafe_for_binlog)
Repeatable Read
(safe_for_binlog)
INSERT/UPDATE duplicate rec(LOCK_S)
range lock (LOCK_X)
INSERT ... ON duplicate rec(LOCK_X)
DUPLICATE KEY UPDATE range lock (LOCK_X)
REPLACE
Serializable
12. •
• Clustered Index Unique Index
• L
•
LE
EQ
GE
G
13. UNIQUE
•
Unique
(unique index || primary key)
&& search_tuple’s field number == unique key number
&& (clustered index || search field not contain null)
Example
unique index(c1, c2)
c1 = 2 and c2 =2 ===>unique
c1=2
===>
c1=2 and c2 is null===>
unique
unique
14. •
• 1
• 2
unique
rec
unique
deleted
rec
not gap
gap lock
• 3
rec
• 4 primary key
• 5 Supremum record
• default
GE
rec
rec
gap lock
search_tuple
rec
not gap lock
gap lock Infimum record
next key lock
rec
next key lock
15. Unique扫描
扫描
扫描⽅向
条件 （特殊规则）
key = 2
不存在const值 存在const值
row key:{1,3} row key:{1,2,3}
gap(3)
move_up(1,2)
move_down(1,2,3)
key > 2
⾮Unique扫描
row key:{1,2,2,3}
rec(2)(not deleted)
ori(2)(deleted)
ori(2),ori(2), gap(3)
ori(1),ori(2),ori(2), gap(3)
move_up()
ori(3), gap(+Infinite)
move_down(3) ori(2),ori(3), gap(+Infinite) ori(1),ori(3), gap(+Infinite) ori(2),ori(3), gap(+Infinite)
move_up(4) ori(2),ori(2),ori(3), gap(+Infinite) ori(3), gap(+Infinite) 主键:rec(2),ori(3), gap(+Infinite)
⾮主键:ori(2),ori(3), gap(+Infinite)
move_down(3) ori(1), ori(2), ori(2), ori(3), ori(1), ori(3), gap(+Infinite) ori(1), ori(2), ori(3), gap(+Infinite)
gap(+Infinite)
move_up() ori(1), ori(2) ori(1), ori(3) ori(1), ori(2)
move_down(3) ori(1), gap(2) ori(1), gap(3) ori(1), gap(2)
move_up() ori(1), ori(2),ori(3) ori(1),ori(3) ori(1), ori(2),ori(3)
move_down(3) ori(1), ori(2),gap(3) ori(1),gap(3) ori(1), ori(2),gap(3)
key >= 2
key < 2
key <= 2
16. INSERT
不存在重复值
(Unique Index)
存在重复值
主键索引 ⼆级索引
INSERT(Insert)
INSERT( By Update)
next key check and waiting:
LOCK_GAP | LOCK_INSERT_INTENTION
deleted unique rec：
not gap lock
duplicate rec：
not gap lock
duplicate rec：
next key lock
17. •
/**
IS
IX
S
X
AI */
/* IS */ { FALSE, FALSE, FALSE, TRUE, FALSE},
/* IX */ { FALSE, FALSE, TRUE, TRUE, FALSE},
/* S */ { FALSE, TRUE,
FALSE, TRUE, TRUE },
/* X */ { TRUE, TRUE, TRUE, TRUE, TRUE },
/* AI */ { FALSE, FALSE, TRUE, TRUE, TRUE }
18. LOCK_REC_NOT_GAP LOCK_GAP LOCK_ORDINARY LOCK_INSERT_INTENTION
LOCK_REC_NOT_GAP TRUE FALSE TRUE FALSE
LOCK_GAP FALSE FALSE FALSE FALSE
LOCK_ORDINARY TRUE FALSE TRUE FALSE
LOCK_INSERT_INTENTION FALSE TRUE TRUE FALSE
19. •
•
INSERT INTENTION LOCK