5.偏头痛杨的mysql教学系列之innodb锁机制篇，mysqlinnodb
前戏 各大名企（BAT,搜狐，网易等等）的java面试题都会围绕着各种锁来提问，出场率很高。 表级锁呀，行级锁呀，乐观锁呀，悲观锁呀，排它锁呀，共享锁呀等等。。。 这些到底是个啥？锁机制的水向来是很深的，但是你想以后吃技术这碗饭，就必须要通关这个关于锁的游戏！ 锁机制还与事务、事务隔离级别、并发、索引、表引擎等知识有关系， 是一个庞大的知识体系树，请大家进行系统学习。锁表是一种很严重的问题，假设现在有一万个人并发访问你的应用，你的应用里进行着各种CRUD的数据库操作， 带事务的，不带事务的。只要表一锁，其他9999个人全都访问不了你的应用，哪怕是一个简单的select， 这就是锁表的恐怖之处，有的时候你的系统慢，卡，你要分析瓶颈到底在哪里，是在数据库连接？ 还是在事务死锁？锁表？服务器内存？IO？CPU？入网/出网带宽？被攻击？
在大并发&悲观锁的情况下，锁表会造成灾难性的后果。表一旦被某个请求锁住， 会把大量并发用户的请求hung住，导致大面积超时，严重影响系统系能。
好了，废话很多的开场白之后，我们要思考一个问题，从只有几个人访问你的程序，到几百人，再到上万人， 几十万几百万等等，在大并发的情况下，你的程序和你的数据库依然坚挺吗？
注意：请务必看完事务的文章后才可阅读本文，不妨先复习一下事务的隔离级别。
表引擎 存储引擎定义如何存储数据，如何为存储的数据建立索引以及如何更新，查询数据等技术实现的方法。 mysql有多种表的存储引擎，包括：ARCHIVE，BLACKHOLE，CSV，InnoDB，MEMORY，MyISAM等等。 其中使用率最高的为：InnoDB（默认引擎），其次为MyISAM。 两个引擎主要的区别为：InnoDB支持行级锁，表级锁，支持事务，而MyISAM只支持表级锁，不支持事务。
注意：本文全部以innoDB存储引擎来讲解mysql锁机制（许多大厂DBA要求所有表全部使用innoDB引擎）。
什么是锁 锁是计算机协调多个进程或线程并发访问某一资源的机制。 在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。 如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题， 锁冲突也是影响数据库并发访问性能的一个重要因素。
通常在高并发场景中，多个事务同时&并发去修改一条数据，如果两个一起改可能会造成一系列的数据问题， 那我如果想让A先执行，B后执行，那么我就需要给数据加锁，把这条数据锁住， 使用锁机制保证同一时间只有一个事务能访问数据。
锁是用来管理对共享数据的并发访问，事务的隔离性是通过锁实现。 如果加锁过度，会导致并发性大幅度下降，而如果加锁不足，会导致数据出现一致性，隔离性等问题， 因此需要分业务场景来选择适合的锁机制。
白话文：事务A在操作数据的时候，获得了锁，那事务B也想操作相同的数据就得等待，等A执行完了，把锁释放掉， 接着B再获得锁，当A没释放前，别人都得等待。
锁的类型 我把mysql的锁分组&分类，方便记忆、消化、理解，不同种类的锁，维度不一样。
- 共享锁&排它锁&意向共享锁&意向排他锁
- 表级锁&行级锁
- 间隙锁
- 乐观锁&悲观锁
共享锁&排他锁&意向共享锁&意向排它锁
- 共享锁（S锁，share，共享读锁）
例如：事务A获取了若干数据的共享锁，事务B还可以获取这些数据的共享锁， 但是事务B没法获得事务A的排他锁，事务B必须等到事务A释放这些数据的共享锁之后，才能获取这些数据的排它锁。
Innodb存储引擎对select语句支持加共享锁： SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE 其它事务可以向被锁定的行加S锁，但是不允许添加X锁，否则会被阻塞。
白话文：我读的时候，你也能读，但我们都不能写。
- 排他锁（X锁，eXclusive，排他写锁)
例如：事务A获取了若干数据的排它锁，事务B不能获取这些数据的共享锁和排它锁， 事务B必须等到事务A释放这些数据的排他锁之后，才能获取这些数据的共享锁和排它锁。
InnoDB存储引擎对select语句支持加排它锁： SELECT * FROM table_name WHERE ... FOR UPDATE; 其他事务如果要对这些行进行dml（insert、delete、update）或者select for update操作都会被阻塞。
要试一下，这个排它锁，是只有update的时候，还是增删改的时候都有？
白话文：我读写的时候，你不能读也不能写。
- 意向共享锁（IS，intention share）
例如事务A执行sql： select * from t_user lock in share model，对t_user表上了一个意向共享锁， lock in share model就是说事务A在接下来要执行的语句中要获取共享锁。 这条sql执行成功后，事务A可以畅通无阻的去执行只需要共享锁的语句了。
- 意向排他锁（IX，intention exclusive）
例如事务A执行sql： select * from t_user for update，对t_user表上了一个意向排他锁， for update就是说事务A在接下来要执行的语句中要获取排他锁。 这条sql执行成功后，事务A可以畅通无阻的去执行只需要排他锁的语句了。
- 四种锁的总结
| 事务A（READ COMMITTED） | 事务B（READ COMMITTED） |
|
START TRANSACTION;
SELECT * FROM t_user WHERE user_name = 'ufo' LOCK IN SHARE MODE;
#为该条记录添加S锁 |
|
|
START TRANSACTION;
SELECT * FROM t_user WHERE user_name = 'ufo' LOCK IN SHARE MODE;
#为该条记录添加S锁， #因为S锁与S锁是兼容状态因此可以加锁成功。 |
|
|
UPDATE t_user SET user_name = 'alex' WHERE user_name = 'ufo';
#需要获取该条数据的X锁，进入等待状态， #因为该条数据的S锁被事务B拿走， #S锁与X锁不兼容，因此进入等待状态， #等待事务B释放S锁。 |
|
|
UPDATE t_user SET user_name = 'alex' WHERE user_name = 'ufo';
#事务死锁： #事务A在等待事务B释放该条数据的S锁， #事务B要执行更新操作需要获得该条数据的X锁， #而获取X锁需要等待事务A释放X锁， #而事务A又在等待事务B释放S锁，导致死锁发生。 |
|
| #事务B死锁发生后，S锁与X锁释放， #事务A更新执行成功。 |
| 事务A（REPEATABLE READ） | 事务B（READ COMMITTED） |
| set autocommit =0; START TRANSACTION; SELECT * FROM t_user WHERE user_name = 'ufo' FOR UPDATE #这块也可以是一个范围查询。 #事务A获得该条数据的X锁。 | |
| set autocommit =0; START TRANSACTION; SELECT * FROM t_user WHERE user_name = 'ufo' FOR UPDATE; #事务B进入等待状态，因为X锁与X锁不兼容， #因此等待事务A释放该条数据的X锁。 | |
|
UPDATE t_user SET user_name = 'alex' WHERE user_name = 'ufo';
COMMIT; #释放该条数据的X锁 |
|
|
SELECT * FROM t_user WHERE user_name = 'alex' FOR UPDATE
#不再等待，获取该条数据的X锁。 |
| 事务A（REPEATABLE READ） | 事务B（READ COMMITTED） |
|
START TRANSACTION
SELECT * FROM t_user WHERE user_name = 'alex' LOCK IN SHARE MODE
获得该条数据的S锁 |
|
|
START TRANSACTION
UPDATE t_user SET user_name = 'ufo' WHERE user_name = 'alex'
Lock wait timeout exceeded; try restarting transaction 准备获得该条数据的X锁，但是由于事务A已经获取了S锁，因此被堵塞，进入等待，直到事务超时。 |
|
| COMMIT; #事务A释放S锁 | |
|
UPDATE t_user SET user_name = 'ufo' WHERE user_name = 'alex'
#事务A的S锁已经释放，事务B获得X锁。 #执行成功 |
锁和锁之间的关系，要么是兼容的，要么是互斥（不兼容）的。 锁a和锁b兼容是指：操作同样一组数据时，如果事务A获取了锁a,另一个事务B还可以获取锁b； 锁a和锁b不兼容是指：操作同样一组数据时，如果事务A获取了锁a，另一个事务B在事务A释放锁a之前无法获取锁b。
锁互斥&兼容关系表(yes表示兼容，no表示不兼容)
| 共享锁（S） | 排他锁（X) | 意向共享锁（IS） | 意向排他锁（IX） | |
| 共享锁（S） | yes | no | yes | no |
| 排他锁（X) | no | no | no | no |
| 意向共享锁（IS） | yes | no | yes | yes |
| 意向排他锁（IX） | no | no | yes | yes |
- 查看锁信息
CNR一致性非堵塞&非锁定读（Consistent Nonlocking Read） InnoDB通过数据行多版本控制（multi versioning）的方式来读行数据。 如果读的数据行正在执行delete、update操作，这时读操作不会因此等待行上的锁释放， 而是读数据行的一个快照版本数据。innoDB默认的select语句就是CNR。（例如：select xxx from t_uuu;） 那么相反的，一致性阻塞&锁定读就是之前讲的共享锁与排他锁加在select中的样子。
快照版本数据是指该行之前版本的数据，通过undo段来实现，undo用来在事务中回滚数据， 因此快照本身是没有额外的开销，读快照数据不需要上锁，因为没有必要对历史的数据进行修改。
CNR大幅度提高了读的并发性，在InnoDB存储引擎默认设置下，默认使用CRN。 快照数据是当前数据之前的历史版本，可能有多个版本。这种技术为行多版本技术。 由此带来的并发控制，称之为多版本并发控制（Multi Version Concurrency Control，MVCC）。 （MVCC请参考其他文章，本文暂不展开，高级java面试的重灾区）
不同事务隔离级别下，读取的方式不同： 在Read Committed和Repeatable Read模式下，innoDB使用默认的CNR。 在Read Committed隔离级别下，CNR读被锁定行的最新一份快照数据；（事务提交后的最新一份快照） 在Repeatable Read隔离级别下，CNR读被锁定行的事务开始时的快照数据；
| 事务A（REPEATABLE READ） | 事务B（READ COMMITTED） |
|
SET AUTOCOMMIT=0;
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; SELECT @@GLOBAL.tx_isolation,@@session.tx_isolation; START TRANSACTION; START TRANSACTION; SELECT * FROM t_user_transaction WHERE user_name = 'alex555' #保证可以查询到一条数据，数据自己造一下。 |
|
|
SET AUTOCOMMIT=0;
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; SET SESSION binlog_format = 'ROW'; #需要把binlog的格式调成ROW，不然会报错 SELECT @@GLOBAL.binlog_format,@@session.binlog_format; SELECT @@GLOBAL.tx_isolation,@@session.tx_isolation; START TRANSACTION; UPDATE t_user_transaction SET user_name = 'alex666' WHERE user_name = 'alex555'; #事务B中将数据改变，没有提交，这行数据被加了一个X锁。 |
|
|
SELECT * FROM t_user_transaction WHERE user_name = 'alex';
可以查询到一条数据
#根据innodb存储引擎的特性， #在Read Committed和Repeatable Read事务隔离级别下，会使用CNR，查询到的还是原来的数据。 （因为当前数据只被修改了一次，所以只有一个版本的数据。） |
|
| COMMIT; | |
|
SELECT * FROM
t_user_transaction WHERE user_name = 'alex555';
#可以查询到一条数据。
#在事务B提交了事务之后，再在事务A查询这条数据，在READ-COMMITTED和REPEATABLE-READ事务隔离级别下，得到的结果就会不一样。 #REPEATABLE-READ级别下：总是读取事务开始时的数据，在本例中，查询user_name = 'alex555'则会返回数据。 |
|
| COMMIT; | |
| SELECT * FROM t_user_transaction WHERE user_name = 'alex'; #查询不到数据，事务已提交，获取最新版本； |
| 事务A（READ COMMITTED） | 事务B（READ COMMITTED） |
|
SET AUTOCOMMIT=0;
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; SET SESSION binlog_format = 'ROW'; #需要把binlog的格式调成ROW，不然会报错 SELECT @@GLOBAL.binlog_format,@@session.binlog_format; SELECT @@GLOBAL.tx_isolation,@@session.tx_isolation; START TRANSACTION; SELECT * FROM t_user_transaction WHERE user_name = 'alex666'; #保证可以查询到一条数据，数据自己造一下。 |
|
|
SET AUTOCOMMIT=0;
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; SET SESSION binlog_format = 'ROW'; #需要把binlog的格式调成ROW，不然会报错 SELECT @@GLOBAL.binlog_format,@@session.binlog_format; SELECT @@GLOBAL.tx_isolation,@@session.tx_isolation; START TRANSACTION; UPDATE t_user_transaction SET user_name = 'alex777' WHERE user_name = 'alex666'; #事务B中将数据改变，没有提交，这行数据被加了一个X锁。 |
|
|
SELECT * FROM t_user_transaction WHERE user_name = 'alex666';
可以查询到一条数据
#根据innodb存储引擎的特性， #在Read Committed和Repeatable Read事务隔离级别下，会使用CNR，查询到的还是原来的数据。 （因为当前数据只被修改了一次，所以只有一个版本的数据。） |
|
| COMMIT; | |
|
#在事务B提交了事务之后，再在事务A查询这条数据，在READ-COMMITTED和REPEATABLE-READ事务隔离级别下，得到的结果就会不一样。
#READ-COMMITTED级别下：读取数据行的最新一个快照(fresh snapshot)。查询user_name = 'alex666'则会返回null，因为user_name已经被事务B修改了。 |
|
| COMMIT; | |
| SELECT * FROM t_user_transaction WHERE user_name = 'alex'; #查询不到数据，事务已提交； |
表级锁&行级锁 （注意：页级锁以及MyISAM表锁不在本文中讨论。） 以前我曾经天真的认为mysql的innodb引擎是表级锁，后来等我自己真实的测试后才发现， 原来不加索引的情况下，会锁住整个表！！！这的确是个灾难！
数据被锁上之后，如果是行级锁那还OK，因为只会影响一行&几行数据， 其他操作这条数据的数据库连接必须要等待这个锁解开才能继续操作， 但如果是表级锁，代表整个表全部被锁掉，所有访问这个表的数据库连接全部要等待。
很多教科书&技术文章里写着：“mysql的innodb引擎默认为行级锁”， 然后很多小伙伴就天真的认为，喏，行锁哟，就肆无忌惮的用起来了。有点类似于java的gc可以回收内存， 然后大家就肆无忌惮的使用内存一样。但其实，你们错了。。。 如果要进行的操作的where后面的列没有设置索引or主键，那么。。。的的确确是表锁。
InnoDB行锁是通过给索引上的索引项加锁来实现的，因此InnoDB这种行锁实现特点意味着： 只有通过索引条件检索数据，InnoDB才使用行级锁，如果没有索引， InnoDB会通过隐藏的聚簇索引来对记录加锁，会把整个表的数据全部上锁！跟表锁效果一样。
表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
Mysql的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录， 但是如果使用相同的索引键，是会出现锁冲突的。
表结构如下： CREATE TABLE `t_lock` ( `key_id` varchar(32) NOT NULL COMMENT '主键', `lock_name` varchar(50) DEFAULT NULL COMMENT '名称', `lock_phone` varchar(50) DEFAULT NULL COMMENT '电话', PRIMARY KEY (`key_id`), KEY `ddd` (`lock_name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `t_lock` */
insert into `t_lock`(`key_id`,`lock_name`,`lock_phone`) values ('1','11','111'),('2','11','222'),('3','33','333'),('4','44','333'),('a','aa','aaa'),('b','bb','bbb'),('c','cc','ccc'),('w','ww','www'),('x','xx','xxx'),('y','yy','yyy'),('z','zz','zzz');
情况1.操作索引（主键索引&唯一索引&普通索引）是行锁
| 事务A（REPEATABLE READ） | 事务B（READ COMMITTED） |
| START TRANSACTION | START TRANSACTION |
| SELECT * FROM t_user WHERE user_name = 'alex' FOR UPDATE | |
|
SELECT * FROM t_user WHERE user_name = 'alex' FOR UPDATE
等待，因为事务A获取了该条数据的X锁，等待事务A释放该条数据的X锁。 |
|
|
SELECT * FROM t_user WHERE user_name = 'gg3' FOR UPDATE
正常查询，事务B获取了该条数据的X锁 |
|
| COMMIT; | |
|
SELECT * FROM t_user WHERE user_name = 'alex' FOR UPDATE;
正常查询，事务B获取了该条数据的X锁 |
情况2.操作索引（主键索引&唯一索引&普通索引） +普通列是行锁
连接A（行锁） SELECT * FROM t_lock WHERE key_id = 'a' AND lock_phone = 'aaa' FOR UPDATE
连接B（行锁，处于被锁定等待状态，因为key_id='a'的这行已经被连接A锁上） SELECT * FROM t_lock WHERE key_id = 'a' FOR UPDATE
连接B（行锁，处于不被锁状态，因为key_id='b'的这行没有被连接A锁上） SELECT * FROM t_lock WHERE key_id = 'b' FOR UPDATE
情况3.索引（主键索引/唯一索引/普通索引） 查询出多条数据（行锁） 注意： 由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。应用设计的时候要注意这一点。
key_id（主键） user_name(索引) user_phone (非索引) 1 11 111 2 11 222
| 事务A（REPEATABLE READ） | 事务B（READ COMMITTED） |
| START TRANSACTION | START TRANSACTION |
| SELECT * FROM t_user WHERE user_name = '11' FOR UPDATE | |
|
SELECT * FROM t_user WHERE user_name = '11' FOR UPDATE
SELECT * FROM t_user WHERE key_id = '1' FOR UPDATE
SELECT * FROM t_user WHERE key_id = '2' FOR UPDATE
上述三条查询全部进入等待，等待事务A释放这组数据的X锁。 虽然是不同的数据，但是是相同的索引，所以需要等待。 SELECT * FROM t_user WHERE key_id = '3' FOR UPDATE 可以执行成功 |
|
| COMMIT | |
|
SELECT * FROM t_user WHERE user_name = '11' FOR UPDATE
SELECT * FROM t_user WHERE key_id = '1' FOR UPDATE
SELECT * FROM t_user WHERE key_id = '2' FOR UPDATE
执行成功 |
情况4.索引/主键没有数据符合条件，则无锁
连接A（无锁） SELECT * FROM t_lock WHERE key_id = 'xxx' FOR UPDATE
连接B（无锁） SELECT * FROM t_lock WHERE key_id = 'xxx' FOR UPDATE
情况5.只有普通列是表锁（恐怖）
| 事务A（REPEATABLE READ） | 事务B（READ COMMITTED） |
|
START TRANSACTION
SELECT * FROM t_user WHERE user_phone = 'www' FOR UPDATE
表锁，不管是否查询到记录，都会锁定表，直到事务提交。 注意：使用非索引列查询数据，即使查询不到数据也是会锁住表 |
START TRANSACTION
|
|
SELECT * FROM t_user WHERE user_phone = 'gg3' FOR UPDATE
SELECT * FROM t_user WHERE user_phone = 'ccc' FOR UPDATE
SELECT * FROM t_user WHERE user_name = 'alex' FOR UPDATE
上述查询全部处于等待状态，因为现在整个表都被锁住了。 无论是用索引还是不用索引查询都被锁住。 |
|
| COMMIT; |
情况6.主键不明确是表锁（恐怖）
连接A（表锁，关键字like同理） SELECT * FROM t_lock WHERE key_id <> 'xxx' FOR UPDATE
连接B（表锁，处于被锁定等待状态，整个表都被锁住了） SELECT * FROM t_lock WHERE lock_phone = 'bbb' FOR UPDATE
连接B（表锁，处于被锁定等待状态，整个表都被锁住了） SELECT * FROM t_lock WHERE key_id = 'b' FOR UPDATE
连接B（表锁，处于被锁定等待状态，整个表都被锁住了） SELECT * FROM t_lock WHERE key_id = 'b' AND lock_phone = 'bbb' FOR UPDATE （有一些情况会导致索引失效 ） 比如，函数，会是表锁。 SELECT * FROM t_user WHERE LOWER(key_id) = 'a' FOR UPDATE
注意： 即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的， 如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁， 而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。
注意： 检索值的数据类型与索引字段不同，虽然MySQL能够进行数据类型转换，但却不会使用索引。
情况7.索引列不明确是表锁（恐怖）
连接A（表锁，关键字like同理） SELECT * FROM t_lock WHERE lock_name <> 'xxx' FOR UPDATE
连接B（表锁，处于被锁定等待状态，整个表都被锁住了） SELECT * FROM t_lock WHERE lock_phone = 'bbb' FOR UPDATE
连接B（表锁，处于被锁定等待状态，整个表都被锁住了） SELECT * FROM t_lock WHERE key_id = 'b' FOR UPDATE
连接B（表锁，处于被锁定等待状态，整个表都被锁住了）
SELECT * FROM t_lock WHERE key_id = 'b' AND lock_phone = 'bbb' FOR UPDATE
间隙锁 当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时， InnoDB会给符合条件的已有数据记录的索引项加锁； 对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁， 这种锁机制就是所谓的间隙锁。
行锁又分为三种： Record lock（记录锁&行锁） ：对索引项加锁，即锁定一条记录。 Gap lock（间隙锁）：对索引项之间的‘间隙’、对第一条记录前的间隙或最后一条记录后的间隙加锁，即锁定一个范围的记录，不包含记录本身。 Next-key Lock：锁定一个范围的记录并包含记录本身（上面两者的结合）。
Next-Key Lock是行锁与间隙锁的组合，这样，当InnoDB扫描索引记录的时候，会首先对选中的索引记录加上行锁（Record Lock），再对索引记录两边的间隙加上间隙锁（Gap Lock）。如果一个间隙被事务T1加了锁，其它事务是不能在这个间隙插入记录的。
mysql的innoDB引擎间隙锁成功解决了幻读的问题。 如果5上锁，那间歇锁还会插进去吗
InnoDB存储引擎中不同SQL在不同隔离级别下锁比较 对于许多SQL，隔离级别越高，InnoDB给记录集加的锁就越严格（尤其是使用范围条件的时候）， 产生锁冲突的可能性也就越高，从而对并发性事务处理性能的影响也就越大。
因此，我们在应用中，应该尽量使用较低的隔离级别，以减少锁争用的机率。 实际上，通过优化事务逻辑，大部分应用使用Read Commited隔离级别就足够了。
| SQL | 条件 | Read Uncommited | Read Commited | Repeatable Read | Serializable |
| select | 相等 | None locks | Consisten Read/None lock | Consisten Read/None lock | Share locks |
| 范围 | None locks | Consisten Read/None lock | Consisten Read/None lock | Share Next-Key | |
| update | 相等 | exclusive locks | exclusive locks | exclusive locks | exclusive locks |
| 范围 | exclusive next-key | exclusive next-key | exclusive next-key | exclusive next-key | |
| insert | N/A | exclusive locks | exclusive locks | exclusive locks | exclusive locks |
| delete | 相等 | exclusive locks | exclusive locks | exclusive locks | exclusive locks |
| 范围 | exclusive next-key | exclusive next-key | exclusive next-key | exclusive next-key | |
| select ... from ... lock in share mode | 相等 | Share locks | Share locks | Share locks | Share locks |
| 范围 | Share locks | Share locks | Share Next-Key | Share Next-Key | |
| select * from ... for update | 相等 | exclusive locks | exclusive locks | exclusive locks | exclusive locks |
| 范围 | exclusive locks | share locks | exclusive next-key | exclusive next-key | |
| Insert into ... Select ... | innodb_locks_unsafe_for_binlog=off | Share Next-Key | Share Next-Key | Share Next-Key | Share Next-Key |
| innodb_locks_unsafe_for_binlog=on | None locks | Consisten read/None lock | Consisten read/None lock | Share Next-Key | |
| create table ... Select ... | innodb_locks_unsafe_for_binlog=off | Share Next-Key | Share Next-Key | Share Next-Key | Share Next-Key |
| innodb_locks_unsafe_for_binlog=on | None locks | Consisten read/None lock | Consisten read/None lock | Share Next-Key |
乐观锁&悲观锁
- 悲观锁(Pessimistic Lock)
- 乐观锁(Optimistic Lock)
如上图所示，如果更新操作顺序执行，则数据的版本（version）依次递增，不会产生冲突。 但是如果发生有不同的业务操作对同一版本的数据进行修改，那么，先提交的操作（图中B）会把数据version更新为2， 当A在B之后提交更新时发现数据的version已经被修改了，那么A的更新操作会失败。
- 两种锁各有优缺点
死锁（dead lock） 所谓死锁是指两个或多个事务，各自占有对方的期望获得的资源，形成的循环等待，彼此无法继续执行的一种状态。 发生死锁后，InnoDB一般都可以检测到，并使一个事务释放锁回退，另一个获取锁完成事务。
deadlock found when trying to get lock;try restarting transcation 自己的重新步骤：
SET autocommit=0
连接A（行锁） SELECT * FROM t_lock WHERE key_id = '1' FOR UPDATE
连接B（行锁） SELECT * FROM t_lock WHERE key_id = '2' FOR UPDATE
连接A（等待连接B释放） SELECT * FROM t_lock WHERE key_id = '2' FOR UPDATE
连接B（等待连接A释放，而连接A又等待连接B释放，因此出现死锁-dead lock，innodb将重启事务） SELECT * FROM t_lock WHERE key_id = '1' FOR UPDATE
常见减少死锁的解决方案： 1.减少事务中的长度，事务里干的事情越多，死锁的几率越大。 2.使用低隔离级别 3.如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。 4.在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率； 5.对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；
拓展阅读：CAS
总结 在前戏的时候已经说明了，锁机制的水很深，其中蕴含的知识点很多很杂，是一个庞大的知识体系树， 但锁机制，是你往高走必须要通关的游戏，共勉。
查看评论
本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处.
同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。