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)这个区间。
发表回复