介绍
在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用之外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。
一、MySQL锁的分类
MySQL锁的特点是不同的存储引擎支持不同的锁机制。MyISAM、MEMORY、CSV等非事务性的存储引擎采用的是表锁；BDB存储引擎采用的是页锁，也支持表锁；InnoDB存储引擎既支持行锁，也支持页锁，也支持表锁，默认采用行锁。
表锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低
行锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高
页锁：开销和加锁时间介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般
从锁的角度来说：表锁更适合以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行锁更适合有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。
二、MyISAM表锁
MySQL的表锁有两种模式：表共享读锁和表独占写锁。 MySQL通过四个队列来维护这两种锁定，两个存放当前正被锁定中的读和写的信息，两个存放等待读和写的信息，这四个队列分别为：
read->lock：读锁定，当前请求获取锁定的资源没有被写锁定，也没有在写锁定等待队列中有优先级更高的写锁等待，立即进入read->lock；如果不满足，进入读等待队列read_wait->lock
write->lock：写锁定，当前请求写的资源没有被写锁定，并且没有在写锁定等待队列里面，那么再检测是否在读等待队列里面，如果没有，进入当前写队列write->lock；如果有，进入写等待队列write_wait->lock
如何加表锁
MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作 （UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁。给MyISAM表显示加锁：
Lock tables table_name1 read local, table_name2 read local;......Unlock tables;复制
上面的例子在LOCK TABLES时加了local选项，其作用就是在满足MyISAM表并发插入条件的情况下，允许其他用户在表尾并发插入记录
在给表加表锁时，必须同时取得所有涉及到表的锁，并且只能访问显式加锁的这些表，不能访问未加锁的表；同时，如果加的是读锁，那么只能执行查询操作，而不能执行更新操作。MyISAM总是一次获得SQL语句所需要的全部锁。这也正是MyISAM表不会出现死锁的原因
测试数据
create table test_myisam_lock(id bigint(20) not null auto_increment comment 'ID',score int DEFAULT 0 COMMENT 'score',name varchar(64) DEFAULT NULL COMMENT 'name',primary key(id),key inx_col(score))engine=MyISAM auto_increment=1default charset=utf8mb4 COMMENT 'test_myisam_lock表';insert into test_myisam_lock (score,name) values ('10','张三');insert into test_myisam_lock (score,name) values ('20','李四');insert into test_myisam_lock (score,name) values ('30','王五');insert into test_myisam_lock (score,name) values ('40','小六');insert into test_myisam_lock (score,name) values ('50','小七');复制
示例：加读锁
对MyISAM表加读锁，不会阻塞其他进程对同一表的读请求，但是会阻塞对同一表的写请求。并且它自己不能读其他未加锁的表。只有当读锁释放后，才会执行其他进程的写操作
示例：加写锁
对MyISAM表加写锁，会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其他进程的读写操作
简而言之，就是读锁会阻塞写，但是不会阻塞读，而写锁会把读和写都阻塞
示例：别名加锁
当使用表锁时，不仅需要一次锁定用到的所有表，而且，同一个表在SQL语句中出现多少次，就要通过与SQL语句中相同的别名锁定多少次。
step 1: 对test_myisam_lock加锁
step 2: 通过别名访问会提示错误
step 3: 需要对别名分别加锁
step 4: 执行成功
并发插入
前面介绍local选项时，其作用就是在满足MyISAM表并发插入条件的情况下，允许其他用户在表尾并发插入记录。MyISAM存储引擎有一个系统变量concurrent_insert，专门用以控制其并发插入的行为，其值分别可以为0、1、2。
当concurrent_insert设置为0时，不允许并发插入
当concurrent_insert设置为1时，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。MySQL的默认设置
当concurrent_insert设置为2时，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录
可以利用MyISAM存储引擎的并发插入特性，来解决应用中对同一表查询和插入的锁争用。可以将concurrent_insert系统变量设为2，总是允许并发插入。同时，通过定期在系统空闲时段执行OPTIMIZE TABLE语句来整理空间碎片，收回因删除记录而产生的中间空洞。
MyISAM的锁调度
如果一个进程请求MyISAM表的读锁，同时另一个进程请求同一表的写锁，MySQL如何处理呢？答案是写进程先获得锁。不仅如此，即使读请求先到锁等待队列，写请求后到，写锁也会插到读锁请求之前，这是因为MySQL认为写请求一般比读请求要重要。这也正是MyISAM表不太适合于有大量更新操作和查询操作应用的原因，因为大量的更新操作会造成查询操作很难获得读锁。可以通过一些设置来调节MyISAM 的调度行为。
通过指定启动参数LOW_PRIORITY_UPDATES，使MyISAM引擎默认给予读请求以优先的权利
通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低
通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级
另外，MySQL也提供了一种折中的办法来调节读写冲突，即给系统参数max_write_lock_count设置一个合适的值，当一个表的读锁达到这个值后，MySQL就暂时将写请求的优先级降低，给读进程一些获得锁的机会。
MyISAM的锁分析
查看表是否加锁
show open tables;复制
0表示未加锁，1表示加锁
可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺：
show status like 'table%';复制
table_locks_immediate：产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1
table_locks_waited：出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次锁值加1），此值高则说明存在较严重的表级锁定争用情况
三、InnoDB锁
InnoDB与MyISAM的最大不同有两点：一是支持事务；二是采用了行级锁。我们介绍InnoDB锁以MySQL默认隔离级别RR为前提，并且文中示例以本机MySQL5.7版本为例。
快照读和当前读
在并发事务处理带来的问题中，更新丢失完全是可以避免的。防止更新丢失，并不能单靠数据库事务控制器来解决，需要应用程序对要更新的数据加必要的锁来解决，因此，防止更新丢失应该是应用的责任。
脏读、不可重复读和幻读，都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。数据库实现事务隔离的方式，基本可以分为两种：
在读取数据前，对其加锁，阻止其他事务对数据进行修改
另一种是不用加任何锁，通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot），并用这个快照来提供一定级别（语句级或事务级）的一致性读取。从用户的角度，好像是数据库可以提供同一数据的多个版本，因此，这种技术叫做数据多版本并发控制（MultiVersion Concurrency Control，简称MVCC）。
快照读，读取的是记录的可见版本 (有可能是历史版本)，不用加锁。简单的select操作，属于快照读，不加锁。如下：
select * from table_name where ?;复制
当前读，读取的是记录的最新版本，并且，当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。如下：
select * from table_name where ? lock in share mode;select * from table_name where ? for update;insert into table_name values (...);update table_name set ? where ?;delete from table_name where ?;复制
其中，除了第一条语句，对读取记录加S锁 (共享锁)外，其他的操作，都加的是X锁 (排它锁)。
行锁
InnoDB实现了以下两种类型的行锁：
共享锁（s）：又称读锁。允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改
排他锁（Ｘ）：又称写锁。允许获取排他锁的事务更新数据，阻止其他事务取得相同的数据集共享读锁和排他写锁。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。
排他锁指的是一个事务在一行数据加上排他锁后，其他事务不能再在其上加其他的锁。mysql InnoDB引擎默认的修改数据语句：update，delete，insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型。所以加过排他锁的数据行在其他事务种是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select …from…查询数据，因为普通查询没有任何锁机制。
事务可以通过以下语句显式给记录集加共享锁或排他锁：
共享锁（S）：
SELECT * FROM table_name WHERE ... LOCK IN SHARE MOD复制
排他锁（X）：
SELECT * FROM table_name WHERE ... FOR UPDATE复制
测试数据
CREATE TABLE `test_innodb_lock` (`id` bigint(20) NOT NULL COMMENT 'id',`code` int DEFAULT '0' COMMENT 'code',`name` varchar(64) DEFAULT '' COMMENT 'name') ENGINE=InnoDB AUTO_INCREMENT=1DEFAULT CHARSET=utf8mb4 COMMENT='test_innodb_lock';insert into test_innodb_lock (id,code,`name`) values (1, 1, 'a');insert into test_innodb_lock (id,code,`name`) values (2, 2, 'b');insert into test_innodb_lock (id,code,`name`) values (3, 3, 'c');insert into test_innodb_lock (id,code,`name`) values (4, 4, 'd');insert into test_innodb_lock (id,code,`name`) values (5, 5, 'e');复制
InnoDB行锁是通过给索引上的索引列加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。
示例：非索引字段加锁
step1：session1查询test_innodb_lock表
step2：session2查询test_innodb_lock表，结果与session1相同
step3：session1关闭自动提交，模拟事务
step4：session2关闭自动提交，模拟事务
step5：session1按照非索引字段更新id = 1
step6：session2按照非索引字段更新id = 2，阻塞
step7：session1提交事务释放锁之后，session2的更新执行成功
注意：当一个InnoDB表没有任何索引时， 则行级锁会施加在隐含创建的聚簇索引上，所以说当一条sql没有走任何索引时，那么将会在每一条聚集索引后面加X锁，这个类似于表锁，但原理上和表锁应该是完全不同的。在该示例中，我们可以查看锁对应的信息：
#在MySQL8中，有些系统表已经不存在，查看相关锁信息的方式可能会不同#查看锁等待select * from information_schema.innodb_lock_waits;#查看锁状态select trx_id,trx_state,trx_started,trx_tables_locked,trx_rows_locked from information_schema.innodb_trx;#查看锁的信息select lock_id,lock_trx_id,lock_mode,lock_type,lock_index,lock_rec,lock_data from information_schema.innodb_locks;复制
lock_type：RECORD表示行锁
lock_index：GEN_CLUSE_INDEX表示聚集索引
所以通过非索引条件检索数据，行锁会施加在隐含创建的聚簇索引上
接下来我们给表字段建立索引如下：
alter table test_innodb_lock add primary key (id) ;alter table test_innodb_lock add index inx_c(code);复制
添加两条数据：
insert into test_innodb_lock (id,code,`name`) values (6, 1, 'f');insert into test_innodb_lock (id,code,`name`) values (7, 1, 'g');复制
添加索引之后测试结果：
step1：session1普通查询code = 1成功
step2：session2普通查询code = 2成功
step3：session1加排他锁查询code = 1成功
step4：session2加排他锁查询code = 2成功
结论：InnoDB行锁是通过给索引上的索引项加锁来实现的。
示例：行锁演示
step1：session1关闭自动提交，模拟事务
step2：session2关闭自动提交，模拟事务
step3：session1加排他锁查询code = 1成功
step4：session2加排他锁查询name = 'c'成功
step5：session2加排他锁查询name = 'f'阻塞
step6：session1提交事务释放锁之后，session2查询成功
意向锁
为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB也引入了意向锁（Intention Locks）的概念，所以也就有了意向共享锁和意向排他锁，这两种意向锁都是InnoDB内部使用的，属于表锁。
如果某些资源已经有了一个共享锁，那么在这些资源上面可以添加其他的共享锁，但不能添加排他锁；如果某些资源已经有了一个排他锁，那么在这些资源上不能添加其他的排他锁和共享锁，只能等待当前锁的释放，并获取锁资源后，才能对其加锁，但可以对其添加意向锁，即如果等待事务想要添加的是排他锁，那么可以在锁定行的所在表添加意向排他锁，如果等待事务想要添加的是共享锁，那么可以在锁定行所在表添加意向共享锁。
意向共享锁（IS）：事务打算给数据行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
意向排他锁（IX）：事务打算给数据行加排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。
InnoDB行锁模式兼容性列表
意向锁是InnoDB自动加的，不需用户干预。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁。 在实际生产中，意向锁可以用来防止DML与DDL冲突，避免在执行DML过程中使用DDL对表结构进行了更改。
示例：意向锁示例
step1：session1关闭自动提交，模拟事务
step2：session2关闭自动提交，模拟事务
step3：session1查询test_innodb_lock表
step4：session2查询test_innodb_lock表，结果与session1相同
step5：session1只使用索引更新数据id = 3记录
step6：session2只使用非索引字段更新上一步step5数据记录，阻塞
step6：session1提交事务释放锁之后，session2更新成功
结论：因为session2的update语句由于没有使用索引，需要在数据表上加意向排他锁，但在id=3这条记录上，已经存在排他锁了，所以session2的update语句被阻塞。
间隙锁
当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做间隙（GAP)，InnoDB也会对这个间隙加锁，这种锁机制就是所谓的间隙锁 （Next-Key锁）。
示例：间隙锁演示
step1：session1关闭自动提交，模拟事务
step2：session2关闭自动提交，模拟事务
step3：session1查询test_innodb_lock表
step4：session2查询test_innodb_lock表，结果与session1相同
step5：session1使用索引字段范围查询code>4记录
step6：session2在上一步step5的范围内，插入数据，阻塞
step7：session1提交事务释放锁之后，session2插入成功
InnoDB使用间隙锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求，对于上面的例子，要是不使用间隙锁，如果其他事务插入了code大于4的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；另外一方面，是为了满足其恢复和复制的需要。
还要特别说明的是，InnoDB除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁！
示例：不存在记录加间隙锁
step1：session1关闭自动提交，模拟事务
step2：session2关闭自动提交，模拟事务
step3：session1查询test_innodb_lock表
step4：session2查询test_innodb_lock表，结果与session1相同
step5：session1加排他锁查询一条不存在的id=9记录
step6：session2插入一条新纪录，阻塞
step7：session1回滚事务释放锁之后，session2插入成功
示例：间隙锁避免部分幻读
insert into test_innodb_lock (id,code,`name`) values (8, 88, 'hh');insert into test_innodb_lock (id,code,`name`) values (10, 8, 'j');insert into test_innodb_lock (id,code,`name`) values (12, 8, 'l');insert into test_innodb_lock (id,code,`name`) values (13, 14, 'm');复制
前提：session1和session2关闭自动提交，模拟事务
step1：session1查询test_innodb_lock表
step2：session2查询test_innodb_lock表，结果与session1相同
step3：session1通过索引更新数据code=8记录
step4：session2新增一条记录，新增记录的索引列值与上一步step3的索引列值相同，都是code=8，此时session2阻塞，session1回滚事务释放锁后插入成功
step5：session1通过索引更新数据code=8记录
step6：session2新增一条记录，新增记录的索引列值小于上一步step5的索引列值，但大于或等于已有数据记录中最大小于检索索引（code=8）的索引值5，此时session2阻塞，session1回滚事务释放锁后插入成功
step7：session1通过索引更新数据code=8记录
step8：session2新增一条记录，新增记录的索引列值小于上一步step7的索引列值，但小于已有数据记录中最大小于检索索引（code=8）的索引值5，此时session2插入成功
step9：session1通过索引更新数据code=8记录
step10：session2新增一条记录，新增记录的索引列值大于上一步step9的索引列值，但大于或等于已有数据记录中最小大于检索索引（a=8）的索引值14，此时session2插入成功
step11：session1通过索引更新数据code=8记录
step12：session2新增一条记录，新增记录的索引列值大于上一步step11的索引列值，但小于已有数据记录中最小大于检索索引（a=8）的索引值14，此时session2阻塞，session1回滚事务释放锁后插入成功
结论：当一个事务在通过索引更新数据时，它会将该索引的前后紧紧相邻的索引记录锁住，包括那些根本就不存在的索引值，锁定的区间为左闭右开区间，即[x，y），其中x为小于事务中SQL语句索引值的最大值，y为大于事务中SQL语句索引值的最小值。如下图：
在本例中，事务中SQL语句索引值为8，索引其锁定的区间为[5，14），所以另外一个事务在做insert操作时，索引值大于或等于5且小于14的索引记录都将被阻塞。需要注意的是，当更新事务的索引值为已有记录中最大值时，这时所有大于该索引值的记录，其他事务的insert操作都将被阻塞。这就是InnoDB间隙锁的具体表现。所以说，InnoDB的间隙锁避免了部分幻读，但不是全部，因为它锁定的是一个区间，而不是整张表。下面我们再看一个间隙锁在什么情况下能完全避免幻度。
示例：间隙锁避免幻读
前提：session1和session2关闭自动提交，模拟事务
step1：session1通过非索引字段更新一条记录
step2：session2插入一条完全不相关的数据，此时session2阻塞。说明当session1通过非索引字段更新一条数据时，整张表就会被锁住
结论：
InnoDB的间隙锁是可以避免数据出现幻读，但只是避免部分出现幻读，当一个事务是通过索引来更新数据是，另外一个事务在前一个事务索引值前后的左闭右开区间是不能并行插入数据的，必须等待上一个事务提交或回滚
当前一个事务不是通过索引字段来进行更新操作时，那么InnoDB的这种间隙锁就能够完全避免幻读的出现，因为它会将整个表锁住，在当前事务提交或回滚之前，会阻塞insert操作
页锁
页锁，粒度介于行锁和表锁之间，获取和释放锁资源的负担也介于行锁和表锁之间，并发性同样也介于行锁和表锁之间，和行锁一样，页锁也可能发生死锁。
示例：页锁演示
前提：session1和session2关闭自动提交，模拟事务
step1：session1同时使用索引字段a和非索引字段b更新一条数据
step2：session2同时使用索引字段code（并且索引值与session1的值相同）和非索引字段更新另外一条数据，此时session2阻塞，session1回滚事务释放锁后插入成功
结论：虽然两个事务最终更新的数据不是同一条数据，但仍然可能被锁定，这是因为两条SQL语句都使用了相同的索引值（code=1），行级锁上升为页级锁。
SQL语句对应的锁
加锁读，修改和删除SQL语句都会在索引扫描过的每一行增加锁，也就是说不光是在where条件限制的索引行上增加行锁，也会对扫描到的间隙增加间隙锁
如果SQL语句是使用二级索引查找数据而且施加的是排他锁，则InnoDB也会在对应的聚簇索引行上施加锁
如果SQL语句没有任何索引可以使用，则MySQL需要扫描全表数据，而每行数据都会被施加锁，所以一个良好的习惯是为InnoDB添加合适的索引
针对不同的语句， InnoDB会施加不同的锁：
Select…from语句属于一致性读， 在默认情况下不施加任何的锁，除非在可串行化隔离级别下，会施加共享next-key锁在扫描的索引行上，当碰到使用唯一索引查找唯一值时只在唯一值上施加行锁
Select…lock in share mode语句会在索引扫描行上施加共享next-key锁，除非是当碰到使用唯一索引查找唯一值时只在唯一值上施加行锁
Select…for update语句会对扫描索引的行上施加排他next-key锁，除非是当碰到使用唯一索引查找唯一值时只在唯一值上施加行锁
Update语句会对扫描索引的行上施加排他next-key锁，除非是当碰到使用唯一索引查找唯一值时只在唯一值上施加行锁
Delete语句会对扫描索引的行上施加排他next-key锁，除非是当碰到使用唯一索引查找唯一值时只在唯一值上施加行锁
Insert语句会对索引扫描的行上施加锁，但不是next-key锁，所以不会阻止其他事务对该行值前的间隔上插入数据
Insert into T select…from S语句会对插入到T表的行施加排他锁（非间隙锁）， 而在默认隔离级别下会对访问的S表上的行施加共享next-key锁
当表上有外键约束时，对任何的insert，update和delete操作都会在需要检查外键约束的行上施加共享行锁
Lock table语句是施加表锁
具体针对sql语句的锁分析，要结合隔离级别，以及执行计划，确认是否走了索引等。
死锁
死锁可能发生在不同的事务都会对多个相同的表和相同的行上施加锁，但事务对表的操作顺序不相同。InnoDB有一套检测死锁的机制，默认是开启的，如果InnoDB检测到死锁的存在，那么就将影响数据行数最小的个事务回滚。
为了减少死锁的发生，要避免使用lock table语句，要尽量让修改数据的范围尽可能的小和快速
当不同的事务要修改多个表或者大量数据时，尽可能的保证修改的顺序在事务之间要一致
# 通过show variables like 'innodb_deadlock_detect'查看死锁检测机制是否开启#在死锁检测机制关闭的情况下，InnoDB会使用innodb_lock_wait_timeout参数来自动回滚等待足够时间的事务#可以通过show engine innodb status语句查看最后一次发生死锁的情况复制
InnoDB锁分析
可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况：
show status like 'innodb_row_lock%';复制
Innodb_row_lock_current_waits：当前正在等待锁定的数量
Innodb_row_lock_time：从系统启动到现在锁定总时长
Innodb_row_lock_time_avg：每次等待所花平均时间
Innodb_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间
Innodb_row_lock_waits：系统启动后到现在总共等待的次
优化建议
尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
合理设计索引，尽量缩小锁的范围
尽可能减少检索条件，避免间隙锁
尽量控制事务大小，减少锁定资源和时间长度
结语
本文详解介绍了MySQL锁的工作原理，相信大家理解了MySQL锁的原理，就能更好的理解MySQL事务隔离的实现。同时也能更好地分析如何进行sql优化。同时再次说明，本文所演示的锁机制都是在可重复读这一事务隔离级别，也就是说这是InnoDB可重复读事务隔离级别的一种实现方式。
推荐阅读
看完本文有收获？请转发分享给更多人
关注「并发编程之美」，一起交流Java学习心得
