MySQL锁详解

数据库的锁机制

什么是锁？锁是一种保障数据的机制

为何要用锁？在并发访问情况下，可能会出现脏读、不可重复读和幻读等读现象，为了应对这些问题，主流数据库都提供了锁机制，以及事务隔离级别的概念，而锁机制可以将并发的数据访问顺序化，以保证数据库中数据的一致性与有效性

锁的分类

一、按锁的粒度划分，可分为行级锁、表级锁。(mysql支持)

二、按锁级别划分，可分为共享锁、排他锁

三、按使用方式划分，可分为乐观锁、悲观锁

四、按加锁方式划分，可分为自动锁、显式锁

五、按操作划分，可分为DML锁、DDL锁

行级锁与表级锁

行级锁

行级锁是MySQL中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁和排他锁。

• 特点：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
• 支持引擎：InnoDB
• 行级锁分为行共享读锁(共享锁)与行独占写锁(排他锁)，如下所示
• 共享锁(S)：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
• 排他锁(X)：SELECT * FROM table_name WHERE ... FOR UPDATE

表级锁(偏向于读)

表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAN与INNODB都支持表级锁定。表级锁定分为表共享读锁(共享锁)与表独占写锁(排他锁)。

• 特点：开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。
• 支持引擎：MYISAM、INNODB
• 分类：表级锁定分为表共享读锁(共享锁)与表独占写锁(排他锁)，如下所示
• lock table 表名 read(write),表名 read(write)，.....;

总结:

• 优点:保障并发场景下的数据安全
• 缺点:降低了效率
• 所以我们在使用锁时应该尽可能缩小锁的范围，即锁住的数据越少越好，并发能力越高

行级锁之共享锁与排他锁

行级锁分为共享锁和排他锁两种。与行处理相关的sql有：insert、update、delete、select，这四类sql在操作记录行时，可以为行加上锁，但需要知道的是

• 对于insert、update、delete语句，InnoDB会自动给涉及的数据加锁，而且是排他锁(X);
• 对于普通的select语句，InnoDB不会加任何锁，需要我们手动自己加，可以加两种类型的锁，如下所示
• 共享锁(S)：SELECT ... LOCK IN SHARE MODE; --查出的记录行都会被锁住(别人写不行，但是可以读)
• 排他锁(X)：SELECT ... FOR UPDATE; – 查出的记录行都会被锁住(别人写不行，但是可以读)

共享锁(Share Lock)

共享锁又称为读锁，简称S锁，顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，获取到共享锁的事务只能读数据，不能修改数据直到已释放所有共享锁，所以共享锁可以支持并发读。

如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁或不加锁(在其他事务里一定不能再加排他锁，但是在事务T自己里面是可以加的)，反之亦然。

用法：SELECT ... LOCK IN SHARE MODE;

在查询语句后面增加 LOCK IN SHARE MODE，Mysql会对查询结果中的每行都加共享锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请共享锁，否则会被阻塞。其他线程也可以读取使用了共享锁的表，而且这些线程读取的是同一个版本的数据。

排他锁(exclusive Lock)

排他锁又称为写锁，简称X锁，顾名思义，排他锁就是不能与其他所并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再对该行加任何类型的其他锁(共享锁和排他锁)，但是获取排他锁的事务是可以对数据就行读取和修改。

用法：SELECT ... FOR UPDATE;

在查询语句后面增加 FOR UPDATE，Mysql会对查询结果中的每行都加排他锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请排他锁，否则会被阻塞。

特例：加过排他锁的数据行在其他事务中是不能修改数据的，也不能通过 for update 和 lock in share mode 锁的方式查询数据，但可以直接通过 select … from … 查询数据，因为普通select查询没有任何锁机制。

乐观锁与悲观锁

数据库管理系统(DBMS)中的并发控制的任务是确保在多个事务同时读取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。

乐观并发控制(乐观锁)和悲观并发控制(悲观锁)是并发控制主要采用的技术手段。无论是悲观锁还是乐观锁，都是人们定义出来的概念，可以认为是一种思想。其实不仅仅是关系型数据库系统中有乐观锁和悲观锁的概念，像memcache、hibernate、tair等都有类似的概念。

针对于不同的业务场景，应该选用不同的并发控制方式。所以不要把乐观并发控制和悲观并发控制狭义的理解为DBMS中的概念，更不要把他们和数据中提供的锁机制(行锁、表锁、排他锁、共享锁)混为一谈。在DBMS中，悲观锁正是利用数据库本身提供的锁机制来实现的。

悲观锁

当我们要对一个数据库中的一条数据进行修改的时候，为了避免同时被其他人修改，最好的办法就是直接对该数据进行加锁以防止并发。这种借助数据库锁机制在修改数据之前先锁定，再修改的方式被称之为悲观并发控制(又名“悲观锁”,Pessimistic concurrency control，缩写“PCC”)。在关系数据库管理系统里，悲观并发控制是一种并发控制的方法。它可以阻止一个事务以影响其他用户的方式来修改数据。如果一个事务执行的操作对某行数据应用了锁，那只有当这个事务把锁释放，其他事务才能够执行与该锁冲突的操作。悲观并发控制主要用于数据争用激烈的环境，以及发生并发冲突时使用锁保护数据的成本要低于回滚事务的成本的环境中。

悲观锁，正如其名，它指的是对数据被外界(包括本系统当前的其他事务，以及来自外部系统的事务处理)修改持保守态度(悲观)，因此在整个数据处理过程中，将数据处于锁定状态。 悲观锁的实现，往往依靠数据库提供的锁机制(也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据)，现在互联网高并发的架构中，受到 fail-fast 思路的影响，悲观锁已经非常少见了。

在数据库中，悲观锁的流程如下:

1. 在对任意记录进行修改前，先尝试为该记录加上排他锁(exclusive locking)。
2. 如果加锁失败，说明该记录正在被修改，那么当前查询可能要等待或者抛出异常。 具体响应方式由开发者根据实际需要决定。
3. 如果成功加锁，那么就可以对记录做修改，事务完成后就会解锁了。
4. 其间如果有其他对该记录做修改或加排他锁的操作，都会等待我们解锁或直接抛出异常。

ps：行锁、表锁、读锁、写锁都是在操作之前先上排他锁

在数据表中的实现：在MySQL中使用悲观锁，必须关闭MySQL的自动提交，set autocommit=0，因为MySQL默认使用自动提交autocommit模式，在执行完sql后会自动提交并释放锁

总结

• 悲观并发控制主要用于数据争用激烈的环境，以及发生并发冲突时使用锁保护数据的成本要低于回滚事务的成本的环境中。
• 优点：悲观并发控制实际上是“先取锁再访问”的保守策略，为数据处理的安全提供了保证。
• 缺点：在效率方面，处理加锁的机制会让数据库产生额外的开销，还有增加产生死锁的机会；在只读型事务处理中由于不会产生冲突，也没必要使用锁，这样做只能增加系统负载；还有会降低了并行性，一个事务如果锁定了某行数据，其他事务就必须等待该事务处理完才可以处理那行数。

乐观锁

在关系数据库管理系统里，乐观并发控制(又名“乐观锁"，optimistic concurrency control，缩写“OCC”)是一种并发控制的方法。它假设多用户并发的事务在处理时不会彼此互相影响，各事务能够在不产生锁的情况下处理各自影响的那部分数据。在提交数据更新之前，每个事务会先检查在该事务读取数据后，有没有其他事务又修改了该数据。如果其他事务有更新的话，正在提交的事务会进行回滚。

乐观锁( optimistic Locking) 相对悲观锁而言，乐观锁假设认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回用户错误的信息，让用户决定如何去做。相对于悲观锁，在对数据库进行处理的时候，乐观锁并不会使用数据库提供的锁机制。一般实现乐观锁的方式就是记录数据版本。

数据版本：为数据增加的一个版本标识。当读取数据时，将版本标识的值一同读出，数据每更新一次，同时对版本标识进行更新。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的版本标识进行比对，如果数据库表当前版本号与第一次取出来的版本标识值相等，则予以更新，否则认为是过期数据。

在数据库中，乐观锁的实现有两种方式

1、使用版本号实现

• 每一行数据多一个字段version，每次更新数据对应版本号+1，
• 原理：读出数据，将版本号一同读出，之后更新，版本号+1，提交数据版本号大于数据库当前版本号，则予以更新，否则认为是过期数据，重新读取数据

2、使用时间戳实现

• 每一行数据多一个字段time
• 原理：读出数据，将时间戳一同读出，之后更新，提交数据时间戳等于数据库当前时间戳，则予以更新，否则认为是过期数据，重新读取数据

乐观并发控制相信事务之间的数据竞争(data race)的概率是比较小的，因此尽可能直接做下去，直到提交的时候才去锁定，所以不会产生任何锁和死锁,。而乐观锁也需要灵活运用

总结：在乐观锁与悲观锁的选择上面，主要看下两者的区别以及适用场景就可以了

• 乐观锁并未真正加锁，效率高。一旦锁的粒度掌握不好，更新失败的概率就会比较高，容易发生业务失败。
• 悲观锁依赖数据库锁，效率低。更新失败的概率比较低。
• 随着互联网三高架构(高并发、高性能、高可用)的提出，悲观锁已经越来越少的被使用到生产环境中了，尤其是并发量比较大的业务场景。

Innodb存储引擎的锁机制

mysql常用存储引擎的锁机制

• MyISAM采用表级锁(table-level locking)
• InnoDB支持行级锁(row-level locking)和表级锁，默认为行级锁(偏向于写)(主要)

行级锁与表级锁的使用区分

MyISAM 操作数据都是使用表级锁，MyISAM总是一次性获得所需的全部锁，要么全部满足，要么全部等待。所以不会产生死锁，但是由于每操作一条记录就要锁定整个表，导致性能较低，并发不高。

InnoDB 与 MyISAM 的最大不同有两点：一是InnoDB支持事务；二是InnoDB采用了行级锁。

在Mysql中，行级锁并不是直接锁记录，而是锁索引。InnoDB 行锁是通过给索引项加锁实现的，而索引分为主键索引和非主键索引两种

1. 如果一条sql语句操作了主键索引，Mysql 就会锁定这条语句命中的主键索引(或称聚簇索引)
2. 如果一条语句操作了非主键索引(或称辅助索引)，MySQL会先锁定该非主键索引，再锁定相关的主键索引。
3. 如果没有索引，InnoDB 会通过隐藏的聚簇索引来对记录加锁。也就是说如果不通过索引条件检索数据，那么InnoDB将对表中所有数据加锁，实际效果跟表级锁一样。
4. 强调：命中了索引才会锁行，不是说有索引就可以锁行

在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能

1. 在不通过索引条件查询的时候，InnoDB 的效果就相当于表锁
2. 当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外不论是使用主键索引、唯一索引或普通索引，InnoD8 都会使用行锁来对数据加锁。
3. 由于 MySQL 的行锁是针对索引加的锁，不是针对记录加的锁，所以即便你的sql语句访问的是不同的记录行，但如果命中的是相同的被锁住的索引键，也还是会出现锁冲突的。
4. 即便在条件中使用了索引字段，但是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决定的，如果 MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下 InnoDB 将锁住所有行，相当于表锁。因此，在分析锁冲突时，别忘了检查 SQL 的执行计划，以确认是否真正使用了索引。

绝大部分情况使用行锁，但在个别特殊事务中，也可以考虑使用表锁

• 事务需要更新大部分数据，表又较大
• 若使用默认的行锁，不仅该事务执行效率低(因为需要对较多行加锁，加锁是需要耗时的)，而且可能造成其他事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高该事务的执行速度
• 事务涉及多个表，较复杂，很可能引起死锁，造成大量事务回滚
• 这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁、减少数据库因事务回滚带来的开销，当然应用中这两种事务也不能太多

三种行锁的算法

InnoDB有三种行锁的算法，都属于排他锁:

1、Record Lock（记录锁）

单个行记录上的锁

2、Gap Lock（间隙锁）

间隙锁，锁定一个范围，但不包括记录本身。GAP锁的目的，是为了防止同一事务的两次当前读出现幻读的情况。

当用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙(GAP)"，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁(Next-Key锁)。

例：假如employee表中只有101条记录，其depid的值分别是1,2,…,100,101，下面的SQL：select * from emp where depid >100 for update; 是一个范围条件的检索，并且命中了索引，InnoDB不仅会对符合条件的dempid值为101的记录加锁，也会对dempid大于101(这些记录并不存在)的“间隙”加锁。

3、Next-Key Lock（间隙锁定）

等于Record Lock结合Gap Lock，也就说Next-Key Lock既锁定记录本身也锁定一个范围，特别需要注意的是，InnoDB存储引擎还会对辅助索引下一个键值加上gap lock。

对于行查询，innodb采用的都是Next-Key Lock，主要目的是解决幻读的问题，以满足相关隔离级别以及恢复和复制的需要。

总结:

1. 如果没有命中索引，无论你筛选出哪一行，都会将整张表锁住
2. 如果命中了非唯一索引，并且是等值查询，会锁行还有间隙
3. 如果命中了非唯一索引，但是是范围查询，会锁行还有间隙
4. 如果命中了唯一索引，并且是等值查询，只会锁定行
5. 如果命中了唯一索引，并且是范围查询，会锁行还有间隙

死锁的问题

死锁是指两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象，若无外力作用，它们都将无法推进下去，此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等的进程称为死锁进程。

MYISAM中是不会产生死锁的，因为MyISAM总是一次性获得所需的全部锁，要么全部满足，要么全部等待。而在INNODB中，锁是逐步获得的，就造成了死锁的可能。

范例1：两个Session的两条SQL产生死锁

# 事务1
begin; -- 步骤1
select * from t1 where id=1 for update; -- 步骤2
update t1 set name="Alan" where id=5; -- 步骤5
# 事务2:
begin; -- 步骤3
select * from t1 where id=5 for update; -- 步骤4
delete from t1 where id=1; -- 步骤6

当执行步骤6的时候就会产生死锁，这个相当于每个事务执行两条SQL，分别持有了一把锁，然后加另一把锁，产生死锁，这个非常好理解，也是最常见的死锁。

范例2：两个Session的一条SQL产生死锁

如上图所示，只有多个事务同时运行的情况下才可能出现，但隐蔽性极强，虽然每个session都只有一条语句，仍旧会产生死锁。要分析这个死锁，首先必须用到本文前面提到的MySQL加锁的规则。针对session1，从name索引出发，读到的 [hdc，1]，[hdc，6] 均满足条件，不仅会加name索引上的记录X锁，而且会加聚族索引上的记录X锁，加锁顺序为先[1,hdc,100]，后[6,hdc,10]。而session2，从pubtime索引出发，[10,6]，[100,1] 均满足过滤条件，同样也会加聚簇索引上的记录X锁，加锁顺序为[6,hdc,10]，后[1,hdc,100]。发现没有，跟session1的加锁顺序正好相反，如果两个session恰好都持有了第一把锁，请求加第二把锁，死锁就发生了。

总结：一般会在高并发场景下发生，核心原理是命中了辅助索引，会先锁定辅助索引，再锁定聚集索引。一般这个情况下由后端开发人员修改逻辑语句。

结论:

1、关于死锁问题需要储备的知识

• 在MySQL中，行级锁并不是直接锁记录，而是锁索引。索引分为主键索引和非主键索引两种
• 如果一条sql语句操作了主键索引，MySQL就会锁定这条主键索引
• 如果一条语句操作了非主键索引，MySQL会先锁定该非主键索引，再锁定相关的主键索引
• 在UPDATE、DELETE操作时，MySQL不仅锁定WHERE条件扫描过的所有索引记录，而且会锁定相邻的键值，即所谓的 next-key locking

2、死锁产生的本质原理

• 死锁的发生与否，并不在于事务中有多少条SQL语句，死锁的关键在于两个(或以上)的session加锁的顺序不一致。
• 而使用本文上面提到的，分析MySQL每条SQL语句的加锁规则，分析出每条语句的加锁顺序，然后检查多个并发SQL间是否存在以相反的顺序加锁的情况，就可以分析出各种潜在的死锁情况，也可以分析出线上死锁发生的原因。

发生死锁后，InnoDB一般都可以检测到，并使一个事务释放锁回退，另一个获取锁完成事务。以下有多种方法可以避免死锁:

• 如果不同程序会并发读取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会；
• 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；
• 对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；
• 在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的。

多版本并发控制MVCC

MySQL的InnoDB存储引擎，实现的是基于多版本的并发控制协议–MVCC([Multi-Version concurrency contro]]

与MVVC相对的，是基于锁的并发控制(Lock-Based Concurency Control)。MVCC最大的好处，就是读不加锁，读写不冲突。在读多写少的DLTP应用中，读写不冲突是非常重要的，极大的增加了系统的并发性能，这也是为什么现阶段，几乎所有的RDBMS，都支持了MVCC。

在MVCC并发控制中，读操作可以分成两类：快照读(snapshot read)与当前读(current read)。快照读，读取的是记录的可见版本(有可能是历史版本)，不用加锁。当前读，读取的是记录的最新版本，并且当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。

在一个支持MVCC并发控制的系统中，哪些读操作是快照读？哪些操作又是当前读呢？以MySQL InnoDB为例：

1. 快照读：简单的select操作，属于快照读，不加锁。(当然，也有例外)
   select * from table where ?;

2. 当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。
   select * from table where ? lock in share mode;
select * from table where ? for update;
insert into table values (...);
update table set ? where ?;
delete from table where ?;

所有以上当前读的语句中，都读取的是记录的最新版本。并且读取之后，还需要保证其他并发事务不能修改当前记录，对读取记录加锁。其中除了第一条语句，对读取记录加S锁(共享锁)外，其他的操作都加的是X锁(排它锁)。