共计 15725 个字符，预计需要花费 40 分钟才能阅读完成。
1.InnoDB 的锁定机制
InnoDB 存储引擎反对行级锁，反对事务处理，事务是有一组 SQL 语句组成的逻辑处理单元，他的 ACID 个性如下：
- 原子性(Atomicity): 事务具备原子不可分割的个性，要么一起执行，要么都不执行。
- 一致性(Consistency): 在事务开始和事务完结时，数据都保持一致状态。
- 隔离性(Isolation): 在事务开始和完结过程中，事务放弃着肯定的隔离个性，保障事务不受内部并发数据操作的影响。
- 持久性(Durability): 在事务实现后，数据将会被长久化到数据库中。
并发事务能进步数据库资源的利用率，进步了数据库的事务吞吐量，但并发事务也存在一些问题，次要包含：
- 更新失落(Lost Update): 两个事务更新同一条数据，但第二个事务中途失败退出，导致两个批改都生效了；因为此时数据库没有执行任何锁操作，并发事务并没有被隔离。(古代数据库曾经不存在这种问题)
- 脏读(Dirty Reads): 一个事务读了某行数据，然而另一个事务曾经更新了这行数据，这是十分危险的，很可能导致所有的操作被回滚。
- 不可反复读: 一个事务对一行数据反复读取两次(屡次)，可是失去了不同的后果, 在两次读取过程中，有可能存在另一个事务对数据进行了批改。
- 幻读: 事务在操作过程中进行两次查问，第二次查问后果蕴含了第一次没有呈现的数据。呈现幻读的次要起因是两次查问过程中另一个事务插入新的数据。
数据库并发中的“更新失落”通常应该是完全避免的，但避免更新失落数据，并不能单靠数据库事务管制来解决，须要应用程序对要更新的数据加必要的锁来解决，而以上呈现的数据库问题都必要由数据库提供肯定的事务隔离机制来解决。为了防止数据库事务并发带来的问题，在规范 SQL 标准中定义了 4 个事务的隔离级别，不同的隔离级别对事务处理不一样。
数据库隔离级别的比拟
| 隔离级别 | 读数据一致性 | 脏读 | 不可反复读 | 幻读 |
|---|---|---|---|---|
| 未提交读 (Read uncommitted) |
最低级别，只能保障不读取物理上损坏的数据 | 是 | 是 | 是 |
| 已提交读 (Read committed) |
语句级 | 否 | 是 | 是 |
| 可反复读 (Repeatable read) |
事务级 | 否 | 否 | 是 |
| 可序列化 (Serializable) |
最高级别，事务级 | 否 | 否 | 否 |
InnoDB 存储引擎实现了 4 中行锁，别离时共享锁(S)、排他锁(X)、动向共享锁(IS)、动向排他锁(IX)。
- 共享锁：大家都能读，然而不能改，只有其中一个独占共享锁时候能力改；
- 排它锁：我要改，你们都不能改，也不能读(但能够 MVCC 快照读)
了解意向锁
意向锁不会和行级的 S 和 X 锁抵触，只会和表级的 S 和 X 锁抵触
意向锁是为了防止遍历全副行锁
思考这个例子：
事务 A 锁住了表中的一行，让这一行只能读，不能写。
之后，事务 B 申请整个表的写锁。
如果事务 B 申请胜利，那么实践上它就能批改表中的任意一行，这与 A 持有的行锁是抵触的。
数据库须要防止这种抵触，就是说要让 B 的申请被阻塞，直到 A 开释了行锁。
数据库要怎么判断这个抵触呢？
step1：判断表是否已被其余事务用表锁锁表
step2：判断表中的每一行是否已被行锁锁住。
留神 step2，这样的判断办法效率切实不高，因为须要遍历整个表。
于是就有了意向锁。
在意向锁存在的状况下，事务 A 必须先申请表的动向共享锁，胜利后再申请一行的行锁。
在意向锁存在的状况下，下面的判断能够改成
step1：不变
step2：发现表上有动向共享锁，阐明表中有些行被共享行锁锁住了，因而，事务 B 申请表的写锁会被阻塞。
1.1 通过索引检索数据，上共享锁，行锁(如果不通过索引，会应用表锁)
1.1 通过索引检索数据，上共享锁，行锁
SessionA SessionB
mysql> set autocommit=0; mysql> set autocommit=0;
Query OK, 0 rows affected (0.02 sec) Query OK, 0 rows affected (0.02 sec)
mysql> select * from test; mysql> select * from test;
--------------------------------------------------------------------------------
+----+-------+-------+-------+ +----+-------+-------+-------+
| id | name | money | level | | id | name | money | level |
+----+-------+-------+-------+ +----+-------+-------+-------+
| 1 | tom | 100 | 1 | | 1 | tom | 100 | 1 |
| 2 | jack | 200 | 2 | | 2 | jack | 200 | 2 |
| 3 | lucas | 300 | 3 | | 3 | lucas | 300 | 3 |
+----+-------+-------+-------+ +----+-------+-------+-------+
3 rows in set (0.00 sec) 3 rows in set (0.00 sec)
--------------------------------------------------------------------------------
对主键索引上共享锁, 其余事务也能获取到共享锁
mysql> select * from test where
id=1 lock in share mode;
+----+------+-------+-------+
| id | name | money | level |
+----+------+-------+-------+
| 1 | tom | 100 | 1 |
+----+------+-------+-------+
1 row in set (0.01 sec)
--------------------------------------------------------------------------------
事务 B 也能持续加共享锁
mysql> select * from test where
id=1 lock in share mode;
+----+------+-------+-------+
| id | name | money | level |
+----+------+-------+-------+
| 1 | tom | 100 | 1 |
+----+------+-------+-------+
1 row in set (0.01 sec)
但无奈更新，因为事务 A 也加了共享锁
mysql> update test set level=11 where id=1;
ERROR 1205 (HY000): Lock wait timeout exceeded;
try restarting transaction
MORE:
无奈加排它锁
select *from test where id=1 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded;
try restarting transaction
能够更新未加锁的，比方
mysql> update test set level=11 where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
--------------------------------------------------------------------------------
事务 A 也无奈更新，因为事务 B 加了共享锁
mysql> update test set level=11 where id=1;
ERROR 1205 (HY000): Lock wait timeout excee
ded; try restarting transaction
--------------------------------------------------------------------------------
任意一个开释共享锁，则独占共享锁的事务能够更新
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
--------------------------------------------------------------------------------
事务 B 开释锁，事务 A 独占，能够更新了
mysql> update test set level=11 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 01.2 通过索引检索数据，上排他锁，行锁
1.2 通过索引检索数据，上排他锁，行锁
SessionA SessionB
mysql> set autocommit=0; mysql> set autocommit=0;
Query OK, 0 rows affected (0.02 sec) Query OK, 0 rows affected (0.02 sec)
mysql> select * from test; mysql> select * from test;
--------------------------------------------------------------------------------
+----+-------+-------+-------+ +----+-------+-------+-------+
| id | name | money | level | | id | name | money | level |
+----+-------+-------+-------+ +----+-------+-------+-------+
| 1 | tom | 100 | 1 | | 1 | tom | 100 | 1 |
| 2 | jack | 200 | 2 | | 2 | jack | 200 | 2 |
| 3 | lucas | 300 | 3 | | 3 | lucas | 300 | 3 |
+----+-------+-------+-------+ +----+-------+-------+-------+
3 rows in set (0.00 sec) 3 rows in set (0.00 sec)
--------------------------------------------------------------------------------
对主键索引上排他锁, 其余事务也能获取到共享锁
mysql> select *from test where
id=1 for update;
+----+------+-------+-------+
| id | name | money | level |
+----+------+-------+-------+
| 1 | tom | 100 | 1 |
+----+------+-------+-------+
1 row in set (0.01 sec)
--------------------------------------------------------------------------------
事务 B 则不能持续上排它锁，会产生期待
mysql> select *from test where id=1 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded;
try restarting transaction
MORE:
也不能更新，因为更新也是上排它锁
mysql> update test set level=2 where id=1;
ERROR 1205 (HY000): Lock wait timeout exceeded;
try restarting transaction
也不能上共享锁
mysql> select * from test where level=1 lock in share mode;
ERROR 1205 (HY000): Lock wait timeout exceeded;
try restarting transaction
--------------------------------------------------------------------------------
事务 A 能够更新
mysql> update test set level=11 where id=1;
Query OK, 1 row affected (0.08 sec)
Rows matched: 1 Changed: 1 Warnings: 0
--------------------------------------------------------------------------------
开释排它锁
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
--------------------------------------------------------------------------------
事务 A 开释锁，事务 B 就能够加排它锁了
mysql> select * from test where id=1 for update;
+----+------+-------+-------+
| id | name | money | level |
+----+------+-------+-------+
| 1 | tom | 100 | 1 |
+----+------+-------+-------+
1 row in set (0.00 sec)1.3 通过索引更新数据，也是上排他锁，行锁
对于 update,insert,delete 语句会主动加排它锁
1.3 通过索引更新数据，也是上排他锁，行锁
SessionA SessionB
mysql> set autocommit=0; mysql> set autocommit=0;
Query OK, 0 rows affected (0.02 sec) Query OK, 0 rows affected (0.02 sec)
mysql> select * from test; mysql> select * from test;
--------------------------------------------------------------------------------
+----+-------+-------+-------+ +----+-------+-------+-------+
| id | name | money | level | | id | name | money | level |
+----+-------+-------+-------+ +----+-------+-------+-------+
| 1 | tom | 100 | 1 | | 1 | tom | 100 | 1 |
| 2 | jack | 200 | 2 | | 2 | jack | 200 | 2 |
| 3 | lucas | 300 | 3 | | 3 | lucas | 300 | 3 |
+----+-------+-------+-------+ +----+-------+-------+-------+
3 rows in set (0.00 sec) 3 rows in set (0.00 sec)
--------------------------------------------------------------------------------
更新 id= 1 的行，就给该行上了排它锁，其余事务
无奈更新该行
mysql> update test set level=11 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
--------------------------------------------------------------------------------
事务 B 则不能更新 id= 1 的行，会产生期待
mysql> update test set level=21 where id=1;
ERROR 1205 (HY000): Lock wait timeout exceeded;
try restarting transaction
MORE:
也不能上排它锁
mysql> select *from test where id=1 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded;
try restarting transaction
也不能上共享锁
mysql> select * from test where level=1 lock in share mode;
ERROR 1205 (HY000): Lock wait timeout exceeded;
try restarting transaction
--------------------------------------------------------------------------------
开释排它锁
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
--------------------------------------------------------------------------------
事务 A 开释锁，事务 B 就能够加排它锁了
mysql> select * from test where id=1 for update;
+----+------+-------+-------+
| id | name | money | level |
+----+------+-------+-------+
| 1 | tom | 100 | 11|
+----+------+-------+-------+
1 row in set (0.00 sec)2.1 脏读
// 脏读
//2.1 脏读
SessionA SessionB
mysql> set autocommit=0; mysql> set autocommit=0;
Query OK, 0 rows affected (0.02 sec) Query OK, 0 rows affected (0.02 sec)
set session transaction isolation set session transaction isolation level read uncommitted;
level read uncommitted; Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test; mysql> select * from test;
--------------------------------------------------------------------------------
+----+-------+-------+-------+ +----+-------+-------+-------+
| id | name | money | level | | id | name | money | level |
+----+-------+-------+-------+ +----+-------+-------+-------+
| 1 | tom | 100 | 1 | | 1 | tom | 100 | 1 |
| 2 | jack | 200 | 2 | | 2 | jack | 200 | 2 |
| 3 | lucas | 300 | 3 | | 3 | lucas | 300 | 3 |
+----+-------+-------+-------+ +----+-------+-------+-------+
3 rows in set (0.00 sec) 3 rows in set (0.00 sec)
--------------------------------------------------------------------------------
mysql> update test set level=100 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
--------------------------------------------------------------------------------
// 脏读
mysql> select *from test where id=1;
+----+------+-------+-------+
| id | name | money | level |
+----+------+-------+-------+
| 1 | tom | 100 | 100 |
+----+------+-------+-------+
1 row in set (0.00 sec)
--------------------------------------------------------------------------------
rollback;
Query OK, 0 rows affected (0.01 sec)
mysql> select *from test where id=1;
+----+------+-------+-------+
| id | name | money | level |
+----+------+-------+-------+
| 1 | tom | 100 | 1 |
+----+------+-------+-------+
1 row in set (0.00 sec)
2.2 不可反复读
2.2 不可反复读
// 脏读
SessionA SessionB
mysql> set autocommit=0; mysql> set autocommit=0;
Query OK, 0 rows affected (0.02 sec) Query OK, 0 rows affected (0.02 sec)
set session transaction isolation set session transaction isolation level read uncommitted;
level read uncommitted; Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test; mysql> select * from test;
--------------------------------------------------------------------------------
+----+-------+-------+-------+ +----+-------+-------+-------+
| id | name | money | level | | id | name | money | level |
+----+-------+-------+-------+ +----+-------+-------+-------+
| 1 | tom | 100 | 1 | | 1 | tom | 100 | 1 |
| 2 | jack | 200 | 2 | | 2 | jack | 200 | 2 |
| 3 | lucas | 300 | 3 | | 3 | lucas | 300 | 3 |
+----+-------+-------+-------+ +----+-------+-------+-------+
3 rows in set (0.00 sec) 3 rows in set (0.00 sec)
--------------------------------------------------------------------------------
mysql> update test set level=100 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
--------------------------------------------------------------------------------
mysql> select *from test where id=1;
+----+------+-------+-------+
| id | name | money | level |
+----+------+-------+-------+
| 1 | tom | 100 | 100 |
+----+------+-------+-------+
1 row in set (0.00 sec)
--------------------------------------------------------------------------------
mysql> update test set level=1000 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
--------------------------------------------------------------------------------
// 不可反复读
// 读三次，第一次是 level 是 1，第二次是 100，第三次是 1000
mysql> select *from test where id=1;
+----+------+-------+-------+
| id | name | money | level |
+----+------+-------+-------+
| 1 | tom | 100 | 1000|
+----+------+-------+-------+
1 row in set (0.00 sec) 2.3 幻读
//2.3 幻读
SessionA SessionB
mysql> set autocommit=0; mysql> set autocommit=0;
Query OK, 0 rows affected (0.02 sec) Query OK, 0 rows affected (0.02 sec)
set session transaction isolation set session transaction isolation level read uncommitted;
level read uncommitted; Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test; mysql> select * from test;
--------------------------------------------------------------------------------
+----+-------+-------+-------+ +----+-------+-------+-------+
| id | name | money | level | | id | name | money | level |
+----+-------+-------+-------+ +----+-------+-------+-------+
| 1 | tom | 100 | 1 | | 1 | tom | 100 | 1 |
| 2 | jack | 200 | 2 | | 2 | jack | 200 | 2 |
| 3 | lucas | 300 | 3 | | 3 | lucas | 300 | 3 |
+----+-------+-------+-------+ +----+-------+-------+-------+
3 rows in set (0.00 sec) 3 rows in set (0.00 sec)
--------------------------------------------------------------------------------
mysql> update test set level=100 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
--------------------------------------------------------------------------------
mysql> select *from test where id=1;
+----+------+-------+-------+
| id | name | money | level |
+----+------+-------+-------+
| 1 | tom | 100 | 100 |
+----+------+-------+-------+
1 row in set (0.00 sec)
--------------------------------------------------------------------------------
mysql> insert into test (name, money，level) VALUES ('tim',250，4);
Query OK, 1 row affected (0.01 sec)
--------------------------------------------------------------------------------
// 幻读
// 读两次，第二次多了 tim 的数据
// 如果是 rr 级别，须要应用以后读 select * from test lock in share mode; 否则因为 MVCC 的缘故，是读不到 tim 的
mysql> select * from test;
+----+-------+-------+-------+
| id | name | money | level |
+----+-------+-------+-------+
| 1 | tom | 100 | 1 |
| 2 | jack | 200 | 2 |
| 3 | lucas | 300 | 3 |
| 4 | tim | 250 | 4 |
+----+-------+-------+-------+
4 row in set (0.00 sec) 3 间隙锁(Net-Key 锁)
MVCC 使 RR 级别下，事务以后读，来防止了读状况下的幻读问题，但如果写更新时候呢？在范畴更新的同时，往范畴内插入新数据，怎么办？于是就有了间隙锁，在更新某个区间数据时，将会锁定这个区间的所有记录。例如 update XXX where id between 1 and 100，就会锁住 id 从 1 到 100 之间的所有的记录。值得注意的是，在这个区间中假如某条记录并不存在，该条记录也会被锁住，这时，如果另一个事务往这个区间增加数据，就必须期待上一个事务开释锁资源。应用间隙锁有两个目标，一是避免幻读；二是满足其复原和赋值的需要。3.1 范畴间隙锁，显式左开右闭区间
// 间隙锁(Net-Key 锁) 范畴间隙锁，左开右闭区间
SessionA SessionB
mysql> set autocommit=0; mysql> set autocommit=0;
Query OK, 0 rows affected (0.02 sec) Query OK, 0 rows affected (0.02 sec)
mysql> select * from test; mysql> select * from test;
--------------------------------------------------------------------------------
+----+-------+-------+-------+ +----+-------+-------+-------+
| id | name | money | level | | id | name | money | level |
+----+-------+-------+-------+ +----+-------+-------+-------+
| 1 | tom | 100 | 1 | | 1 | tom | 100 | 1 |
| 2 | jack | 200 | 2 | | 2 | jack | 200 | 2 |
| 3 | lucas | 300 | 3 | | 3 | lucas | 300 | 3 |
+----+-------+-------+-------+ +----+-------+-------+-------+
3 rows in set (0.00 sec) 3 rows in set (0.00 sec)
--------------------------------------------------------------------------------
mysql> update test set level=0
where money between 0 and 200;
Query OK, 2 rows affected (0.02 sec)
Rows matched: 2 Changed: 2 Warnings: 0
实践上应该锁定 [0,300) 这个区间
--------------------------------------------------------------------------------
插入 money= 0 期待
mysql> insert into test (name, money,level) VALUES ('tim',0,0);
ERROR 1205 (HY000): Lock wait timeout exceeded;
try restarting transaction
插入 money=90 期待
mysql> insert into test (name, money,level) VALUES ('tim',90,0);
ERROR 1205 (HY000): Lock wait timeout exceeded;
try restarting transaction
插入 money=100 期待
mysql> insert into test (name, money,level) VALUES ('tim',100,0);
ERROR 1205 (HY000): Lock wait timeout exceeded;
try restarting transaction
插入 money=299 期待
mysql> insert into test (name, money,level) VALUES ('tim',299,0);
ERROR 1205 (HY000): Lock wait timeout exceeded;
try restarting transaction
插入 money=300 ok
mysql> insert into test (name, money,level) VALUES ('tim',300,0);
Query OK, 1 row affected (0.00 sec)3.2 单个间隙锁 隐式区间
上大节是指定 update 某个区间，那如果说是只 update 一个值呢？还会有间隙锁么？
// 间隙锁(Net-Key 锁) 单个间隙锁，左开右闭区间
SessionA SessionB
mysql> set autocommit=0; mysql> set autocommit=0;
Query OK, 0 rows affected (0.02 sec) Query OK, 0 rows affected (0.02 sec)
mysql> select * from test; mysql> select * from test;
--------------------------------------------------------------------------------
+----+-------+-------+-------+ +----+-------+-------+-------+
| id | name | money | level | | id | name | money | level |
+----+-------+-------+-------+ +----+-------+-------+-------+
| 1 | tom | 100 | 1 | | 1 | tom | 100 | 1 |
| 2 | jack | 200 | 2 | | 2 | jack | 200 | 2 |
| 3 | lucas | 300 | 3 | | 3 | lucas | 300 | 3 |
+----+-------+-------+-------+ +----+-------+-------+-------+
3 rows in set (0.00 sec) 3 rows in set (0.00 sec)
--------------------------------------------------------------------------------
mysql> update test set level=0
where money = 200;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
实践上应该锁定 [0,300) 这个区间
--------------------------------------------------------------------------------
插入 money=0 ok
mysql> insert into test (name, money,level) VALUES ('tim',0,0);
Query OK, 1 row affected (0.00 sec)
插入 money=90 ok
mysql> insert into test (name, money,level) VALUES ('tim',90,0);
Query OK, 1 row affected (0.00 sec)
插入 money=100 期待
mysql> insert into test (name, money,level) VALUES ('tim',100,0);
ERROR 1205 (HY000): Lock wait timeout exceeded;
try restarting transaction
插入 money=150 期待
mysql> insert into test (name, money,level) VALUES ('tim',150,0);
ERROR 1205 (HY000): Lock wait timeout exceeded;
try restarting transaction
插入 money=200 期待
mysql> insert into test (name, money,level) VALUES ('tim',200,0);
ERROR 1205 (HY000): Lock wait timeout exceeded;
try restarting transaction
插入 money=240 期待
mysql> insert into test (name, money,level) VALUES ('tim',240,0);
ERROR 1205 (HY000): Lock wait timeout exceeded;
try restarting transaction
插入 money=300 ok
mysql> insert into test (name, money,level) VALUES ('tim',300,0);
Query OK, 1 row affected (0.00 sec)当不指定区间时，隐式的区间为索引 B + 数前后两个节点的值所确定的区间，也是左开右闭，对于上述例子，就是 [0,300) 这个区间。
正文完
