对于 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)这个区间。
总结
到此这篇关于Mysql InnoDB锁定机制的文章就介绍到这了,更多相关Mysql InnoDB锁定机制内容请搜索
更多相关Mysql内容来自木庄网络博客
标签：Mysql
相关阅读 >>
mysql数据库导出数据字典文档word或者html的3个工具介绍
更多相关阅读请进入《mysql》频道 >>
数据库系统概念 第6版
本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。
