mysql sql语句执行时的加锁监控 mysql加锁机制

加锁是实现数据库并发控制的一个非常重要的技术。当事务在对某个数据对象进行操作前，先向系统发出请求，对其加锁。加锁后事务就对该数据对象有了一定的控制，在该事务释放锁之前，其他的事务不能对此数据对象进行更新操作。

什么是锁？

锁是计算机协调多个进程或线程并发访问某一资源的机制。

• 锁保证数据并发访问的一致性、有效性；
• 锁冲突也是影响数据库并发访问性能的一个重要因素。
• 锁是Mysql在服务器层和存储引擎层的的并发控制。

为什么要加锁

数据库是一个多用户使用的共享资源。当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。

锁是用于管理对公共资源的并发控制。也就是说在并发的情况下，会出现资源竞争，所以需要加锁。

加锁解决了 多用户环境下保证数据库完整性和一致性。

Lock的对象是事务，用来锁定的是数据库中的对象，如表、页、行。并且一般lock的对象仅在事务commit或rollback后进行释放（不同事务隔离级别释放的时间可能不同）。

锁分类

• 共享锁||读锁||S 锁(share lock)：其他事务可以读，但不能写。允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
• 排他锁||写锁||X 锁(exclusive） ：其他事务不能读取，也不能写。允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。
  类型细分：

• 意向共享锁(IS Lock/intent share lock)
• 意向排他锁||互斥锁(IX Lock/intent exclusive lock)

• 悲观锁||保守锁（pessimistic locking）:假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。
  悲观锁是数据库层面加锁，都会阻塞去等待锁。
• 乐观锁(optimistic locking)：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。
  乐观锁是一种思想，具体实现是，表中有一个版本字段，第一次读的时候，获取到这个字段。处理完业务逻辑开始更新的时候，需要再次查看该字段的值是否和第一次的一样。如果一样更新，反之拒绝。之所以叫乐观，因为这个模式没有从数据库加锁，等到更新的时候再判断是否可以更新。
  缺点：并发很高的时候，多了很多无用的重试。乐观锁，不能解决脏读的问题。

锁策略:锁的粒度/粒度维度(lock granularity)

锁的开销是较为昂贵的，锁策略其实就是保证了线程安全的同时获取最大的性能之间的平衡策略。

行级锁(row-level locking)

行锁：即只允许事务读一行数据。行锁的粒度实在每一条行数据，当然也带来了最大开销，但是行锁可以最大限度的支持并发处理。

开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

最大程度的支持并发，同时也带来了最大的锁开销。

行级锁更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统

在 InnoDB 中，除单个 SQL 组成的事务外，锁是逐步获得的，这就决定了在 InnoDB 中发生死锁是可能的。

行级锁只在存储引擎层实现，而Mysql服务器层没有实现。 

表级锁(table-level locking)

表锁：允许事务在行级上的锁和表级上的锁同时存在。锁定整个表，开销最小，但是也阻塞了整个表。

开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

这些存储引擎通过总是一次性同时获取所有需要的锁以及总是按相同的顺序获取表锁来避免死锁。

表级锁更适合于以查询为主，并发用户少，只有少量按索引条件更新数据的应用，如Web 应用。

• 若一个用户正在执行写操作，会获取排他的“写锁”，这可能会锁定整个表，阻塞其他用户的读、写操作；
• 若一个用户正在执行读操作，会先获取共享锁“读锁”，这个锁运行其他读锁并发的对这个表进行读取，互不干扰。只要没有写锁的进入，读锁可以是并发读取统一资源的。

Mysql的表级别锁分为两类：元数据锁（Metadata Lock，MDL）、表锁。

元数据锁（Metadata Lock，MDL）

元数据锁(MDL) 不需要显式使用，在访问一个表的时候会被自动加上。这个特性需要MySQL5.5版本以上才会支持，

• 当对一个表做增删改查的时候，该表会被加MDL读锁
• 当对表做结构变更的时候，加MDL写锁

MDL锁规则：

• 读锁之间不互斥，所以可以多线程多同一张表进行增删改查。
• 读写锁、写锁之间是互斥的，为了保证表结构变更的安全性，所以如果要多线程对同一个表加字段等表结构操作，就会变成串行化，需要进行锁等待。
• MDL的写锁优先级比MDL读锁的优先级，但是可以设置max_write_lock_count系统变量来改变这种情况，当写锁请求超过这个变量设置的数后，MDL读锁的优先级会比MDL写锁的优先级高。（默认情况下，这个数字会很大，所以不用担心写锁的优先级下降）
• MDL的锁释放必须要等到事务结束才会释放

页面锁(page-level locking)

页级锁定是 MySQL 中比较独特的一种锁定级别，在其他数据库管理软件中也并不是太常见。

页面锁开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样是介于上面二者之间。另外，页级锁定和行级锁定一样，会发生死锁。

在数据库实现资源锁定的过程中，随着锁定资源颗粒度的减小，锁定相同数据量的数据所需要消耗的内存数量是越来越多的，实现算法也会越来越复杂。

不过，随着锁定资源颗粒度的减小，应用程序的访问请求遇到锁等待的可能性也会随之降低，系统整体并发度也随之提升。

使用页级锁定的主要是 BerkeleyDB 存储引擎。

全局锁

MySQL 提供全局锁来对整个数据库实例加锁。

FLUSH TABLES WITH READ LOCK 

这条语句一般都是用来备份的，当执行这条语句后，数据库所有打开的表都会被关闭，并且使用全局读锁锁定数据库的所有表，同时，其他线程的更新语句（增删改），数据定义语句（建表，修改表结构）和更新类的事务提交都会被阻塞。

在mysql 8.0 以后，对于备份，mysql可以直接使用备份锁。

LOCK INSTANCE FOR BACKUP UNLOCK INSTANCE

这个锁的作用范围更广，这个锁会阻止文件的创建，重命名，删除，包括 REPAIR TABLE TRUNCATE TABLE, OPTIMIZE TABLE操作以及账户的管理都会被阻塞。当然这些操作对于内存临时表来说是可以执行的，为什么内存表不受这些限制呢？因为内存表不需要备份，所以也就没必要满足这些条件。

MySQL不同的存储引擎支持不同的锁机制

所有的存储引擎都以自己的方式显现了锁机制，服务器层完全不了解存储引擎中的锁实现：

• MyISAM、MEMORY、CSV存储引擎采用的是表级锁（table-level locking）
• BDB(Berkeley DB) 存储引擎采用的是页面锁（page-level locking），但也支持表级锁
• InnoDB 存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。

• InnoDB行锁是通过给索引上的索引项加锁来实现的，InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！
• 行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁。行级锁的缺点是：由于需要请求大量的锁资源，所以速度慢，内存消耗大。

默认情况下，表锁和行锁都是自动获得的， 不需要额外的命令。

但是在有的情况下， 用户需要明确地进行锁表或者进行事务的控制， 以便确保整个事务的完整性，这样就需要使用事务控制和锁定语句来完成。

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

Innodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量较高的时候，Innodb的整体性能和MyISAM相比就会有比较明显的优势了。

但是，Innodb的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让Innodb的整体性能表现不仅不能比MyISAM高，甚至可能会更差。

InnoDB行级锁和表级锁

InnoDB锁模式：

InnoDB 实现了以下两种类型的行锁：

• 共享锁（S-shared）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
• 排他锁（X-exclusive）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

为了支持在不同粒度上进行加锁操作(允许行锁和表锁共存，实现多粒度锁机制)，InnoDB 还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁：

• 意向共享锁（IS- intent share lock）事务想要获得一张表中某几行的共享锁
  务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。
• 意向排他锁（IX -intent exclusive lock）事务想要获得一张表中某几行的排他锁
  事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的 IX 锁。

由于InnoDB存储引擎支持的是行级别的锁，因此意向锁其实不会阻塞除全表扫以外的任何请求。故表级意向锁与行级锁的兼容性如下所示

如果一个事务请求的锁模式与当前的锁兼容， InnoDB 就将请求的锁授予该事务； 反之， 如果两者不兼容，该事务就要等待锁释放

若将上锁的对象看成一棵树，那么对最下层的对象上锁，也就是对最细粒度的对象进行上锁，那么首先需要对粗粒度的对象上锁。例上图，如果需要对页上的记录r进行上X锁，那么分别需要对数据库A、表、页上意向锁IX，最后对记录r上X锁。若其中任何一个部分导致等待，那么该操作需要等待粗粒度锁的完成。举例来说，在对记录r加X锁之前，已经有事务对表1进行了S表锁，那么表1上已存在S锁，之后事务需要对记录r在表1上加上IX，由于不兼容，所以该事务需要等待表锁操作的完成。

意向锁到底有什么作用？

innodb的意向锁主要用户多粒度的锁并存的情况。比如事务A要在一个表上加S锁，如果表中的一行已被事务B加了X锁，那么该锁的申请也应被阻塞。如果表中的数据很多，逐行检查锁标志的开销将很大，系统的性能将会受到影响。为了解决这个问题，可以在表级上引入新的锁类型来表示其所属行的加锁情况，这就引出了“意向锁”的概念。

举个例子，如果表中记录1亿，事务A把其中有几条记录上了行锁了，这时事务B需要给这个表加表级锁，如果没有意向锁的话，那就要去表中查找这一亿条记录是否上锁了。如果存在意向锁，那么假如事务Ａ在更新一条记录之前，先加意向锁，再加Ｘ锁，事务B先检查该表上是否存在意向锁，存在的意向锁是否与自己准备加的锁冲突，如果有冲突，则等待直到事务Ａ释放，而无须逐条记录去检测。事务Ｂ更新表时，其实无须知道到底哪一行被锁了，它只要知道反正有一行被锁了就行了。

主要作用是处理行锁和表锁之间的矛盾，能够显示“某个事务正在某一行上持有了锁，或者准备去持有锁”

InnoDB 行锁实现方式：

行锁是加在索引上的

Innodb中的索引数据结构是 B+ 树，数据是有序排列的，从根节点到叶子节点一层层找到对应的数据。

普通索引，也叫做辅助索引，叶子节点存放的是主键值。主键上的索引叫做聚集索引，表里的每一条记录都存放在主键的叶子节点上。当通过辅助索引select 查询数据的时候，会先在辅助索引中找到对应的主键值，然后用主键值在聚集索引中找到该条记录。

举个例子，用name=Alice来查询的时候，会先找到对应的主键值是18 ，然后用18在下面的聚集索引中找到name=Alice的记录内容是 77 和 Alice。

表中每一行的数据，是组织存放在聚集索引中的，所以叫做索引组织表。

• InnoDB 行锁是通过给索引上的索引项加锁来实现的，这一点 MySQL 与 Oracle 不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB 这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁！
• 不论是使用主键索引、唯一索引或普通索引，InnoDB 都会使用行锁来对数据加锁。
• 只有执行计划真正使用了索引，才能使用行锁：即便在条件中使用了索引字段，但是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决定的，如果 MySQL 认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下 InnoDB 将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查 SQL 的执行计划（可以通过 explain 检查 SQL 的执行计划），以确认是否真正使用了索引。（更多阅读：MySQL索引总结）
• 由于 MySQL 的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然多个session是访问不同行的记录， 但是如果是使用相同的索引键， 是会出现锁冲突的（后使用这些索引的session需要等待先使用索引的session释放锁后，才能获取锁）。 应用设计的时候要注意这一点。

行锁的算法（算法维度）

• Record Lock（单行记录）
• Gap Lock（间隙锁，锁定一个范围，但不包含锁定记录）
• Next-Key Lock（Record Lock + Gap Lock，锁定一个范围，并且锁定记录本身， MySql 防止幻读，就是使用此锁实现）

记录锁、间隙锁、临键锁都是排它锁

记录锁(Record Lock)

事务加锁后锁住的只是表的某一条记录。

记录锁出现条件：精准条件命中，并且命中的条件字段是唯一索引；

例如：update user_info set name=’张三’ where id=1 ,这里的id是唯一索引。

Record Lock总是会去锁住索引记录，如果InnoDB存储引擎表在建立的时候没有设置任何一个索引，那么这时InnoDB存储引擎会使用隐式的主键来进行锁定。

记录锁的作用：加了记录锁之后可以避免数据在查询的时候被修改的重复读问题，也避免了在修改的事务未提交前被其他事务读取的脏读问题。

临键锁(Next-Key Lock)

临键锁是INNODB的行锁默认算法，它是记录锁和间隙锁的组合，临键锁会把查询出来的记录锁住，同时也会把该范围查询内的所有间隙空间也会锁住，再之它会把相邻的下一个区间也会锁住。

临键锁出现条件：范围查询并命中，查询命中了索引。

比如下面表的数据执行 select * from user_info where id>1 and id<=13 for update ;

会锁住ID为 1,5,10的记录；同时会锁住，1至5,5至10,10至15的区间。

临键锁的作用：结合记录锁和间隙锁的特性，临键锁避免了在范围查询时出现脏读、重复读、幻读问题。加了临键锁之后，在范围区间内数据不允许被修改和插入。

Next-Key Lock是结合了Gap Lock和Record Lock的一种锁定算法，在Next-Key Lock算法下，InnoDB对于行的查询都是采用这种锁定算法。

除了Next-Key Locking，还有Previous-Key Locking技术。

间隙锁(gap lcok )：

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

很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。

间隙锁的目的：

1. 防止幻读，以满足相关隔离级别的要求；
2. 满足恢复和复制的需要：

产生间隙锁的条件（RR事务隔离级别下）：

1. 使用普通索引锁定；
2. 使用多列唯一索引；
3. 使用唯一索引锁定多行记录。

以上情况，都会产生间隙锁

推荐阅读《MySQL的锁机制 - 记录锁、间隙锁、临键锁》

MySQL 通过 BINLOG 录入执行成功的 INSERT、UPDATE、DELETE 等更新数据的 SQL 语句，并由此实现 MySQL 数据库的恢复和主从复制。MySQL 的恢复机制（复制其实就是在 Slave Mysql 不断做基于 BINLOG 的恢复）有以下特点：

• 一是 MySQL 的恢复是 SQL 语句级的，也就是重新执行 BINLOG 中的 SQL 语句。
• 二是 MySQL 的 Binlog 是按照事务提交的先后顺序记录的， 恢复也是按这个顺序进行的。

由此可见，MySQL 的恢复机制要求：在一个事务未提交前，其他并发事务不能插入满足其锁定条件的任何记录，也就是不允许出现幻读。

这张图里出现了三种锁

• 记录锁：单行记录上的锁
• 间隙锁：锁定记录之间的范围，但不包含记录本身。
• Next Key Lock: 记录锁+ 间隙锁，锁定一个范围，包含记录本身。

不是所有索引都会加上Next-key Lock的，在查询的列是唯一索引（包含主键索引）的情况下，Next-key Lock会降级为Record Lock。

CREATE TABLE z (a INT,b INT,PRIMARY KEY(a),KEY(b));// a是主键索引，b是普通索引
INSERT INTO z select1,1;
INSERT INTO z select3,1;
INSERT INTO z select5,3;
INSERT INTO z select7,6;
INSERT INTO z select10,8;

这时候在会话A中执行 SELECT*FROM z WHERE b=3FOR UPDATE ，索引锁定如下：

这时候会话B执行的语句落在锁定范围内的都会进行waiting

SELECT * FROM z WHERE a =5 LOCK IN SHARE MODE;
INSERT INTO z SELECT 4,2;
INSERT INTO z SELECT 6,5;

 

用户可以通过以下两种方式来显示的关闭Gap Lock：

• 将事务的隔离级别设为 READ COMMITED。
• 将参数innodblocksunsafeforbinlog设置为1。

从上面的例子可以看出来，Gap Lock的作用是为了阻止多个事务将记录插入到同一个范围内，设计它的目的是用来解决Phontom Problem（幻读问题）。在MySQL默认的隔离级别（Repeatable Read）下，InnoDB就是使用它来解决幻读问题。

InnoDB加锁方法：

• 意向锁是 InnoDB 自动加的， 不需用户干预。
• 对于 UPDATE、 DELETE 和 INSERT 语句， InnoDB 会自动给涉及数据集加排他锁（X)；
• 对于普通 SELECT 语句，InnoDB 不会加任何锁；
  事务可以通过以下语句显式给记录集加共享锁或排他锁：

• 共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。 其他 session 仍然可以查询记录，并也可以对该记录加 share mode 的共享锁。但是如果当前事务需要对该记录进行更新操作，则很有可能造成死锁。
• 排他锁（X）：SELECT * FROM table_name WHERE ... FOR UPDATE。其他 session 可以查询该记录，但是不能对该记录加共享锁或排他锁，而是等待获得锁

隐式锁定：

InnoDB在事务执行过程中，使用两阶段锁协议：

• 随时都可以执行锁定，InnoDB会根据隔离级别在需要的时候自动加锁；
• 锁只有在执行commit或者rollback的时候才会释放，并且所有的锁都是在同一时刻被释放。

显式锁定 ：

select ... lock in share mode //共享锁 
select ... for update //排他锁

select for update

在执行这个 select 查询语句的时候，会将对应的索引访问条目进行上排他锁（X 锁），也就是说这个语句对应的锁就相当于update带来的效果。

select *** for update 的使用场景：为了让自己查到的数据确保是最新数据，并且查到后的数据只允许自己来修改的时候，需要用到 for update 子句。

select * from user where id=10 for update    

通过锁住聚集索引中的节点来锁住这条记录（锁住id=10的索引，即锁住了这条记录）。

select * from user where name=‘b’ for update

这里的name上加了唯一索引，唯一索引本质上是辅助索引，加了唯一约束。所以会先在辅助索引上找到name为d的索引记录，在辅助索引中加锁，然后查找聚集索引，锁住对应索引记录。

为什么聚簇索引上的记录也要加锁？

试想一下，如果有并发的另外一个SQL，是直接通过主键索引id=30来更新，会先在聚集索引中请求加锁。如果只在辅助索引中加锁的话，两个并发SQL之间是互相感知不到的。

select lock in share mode

in share mode 子句的作用就是将查找到的数据加上一个 share 锁，这个就是表示其他的事务只能对这些数据进行简单的select 操作，并不能够进行 DML 操作。

select *** lock in share mode 使用场景：为了确保自己查到的数据没有被其他的事务正在修改，也就是说确保查到的数据是最新的数据，并且不允许其他人来修改数据。但是自己不一定能够修改数据，因为有可能其他的事务也对这些数据 使用了 in share mode 的方式上了 S 锁。

for update 和 lock in share mode 的区别：

前一个上的是排他锁（X 锁），一旦一个事务获取了这个锁，其他的事务是没法在这些数据上执行 for update ；后一个是共享锁，多个事务可以同时的对相同数据执行 lock in share mode。

显式锁定对性能影响(performance impact)：

select for update 语句，相当于一个 update 语句。在业务繁忙的情况下，如果事务没有及时的commit或者rollback 可能会造成其他事务长时间的等待，从而影响数据库的并发使用效率。

select lock in share mode 语句是一个给查找的数据上一个共享锁（S 锁）的功能，它允许其他的事务也对该数据上S锁，但是不能够允许对该数据进行修改。如果不及时的commit 或者rollback 也可能会造成大量的事务等待。

默认的读操作，上锁吗

默认是 MVCC 机制（“一致性非锁定读-consistent nonlocking read”）保证 RR 级别的隔离正确性，是不上锁的。

可以选择手动上锁：select xxxx for update (排他锁); select xxxx lock in share mode(共享锁)，称之为“一致性锁定读”。

使用锁之后，就能在 RR 级别下，避免幻读。当然，默认的 MVCC 读，也能避免幻读。

既然 RR 能够防止幻读，那么，SERIALIZABLE 有啥用呢？防止丢失更新。

这个时候，我们必须使用 SERIALIZABLE 级别进行串行读取。

最后，行锁的实现原理就是锁住聚集索引，如果你查询的时候，没有正确地击中索引，MySql 优化器将会抛弃行锁，使用表锁。

InnoDB 在不同隔离级别下的一致性读及锁的差异：

锁和多版本数据（MVCC）是 InnoDB 实现一致性读和 ISO/ANSI SQL92 隔离级别的手段。

因此，在不同的隔离级别下，InnoDB 处理 SQL 时采用的一致性读策略和需要的锁是不同的：

对于许多 SQL，隔离级别越高，InnoDB 给记录集加的锁就越严格（尤其是使用范围条件的时候），产生锁冲突的可能性也就越高，从而对并发性事务处理性能的 影响也就越大。

因此， 我们在应用中， 应该尽量使用较低的隔离级别， 以减少锁争用的机率。实际上，通过优化事务逻辑，大部分应用使用 Read Commited 隔离级别就足够了。对于一些确实需要更高隔离级别的事务， 可以通过在程序中执行 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ 或 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE 动态改变隔离级别的方式满足需求。

InnoDB 行锁优化建议

合理利用 InnoDB 的行级锁定，做到扬长避短

1. 尽可能让所有的数据检索都通过索引来完成，从而避免 InnoDB 因为无法通过索引键加锁而升级为表级锁定。
2. 合理设计索引，让 InnoDB 在索引键上面加锁的时候尽可能准确，尽可能的缩小锁定范围，避免造成不必要的锁定而影响其他 Query 的执行。
3. 尽可能减少基于范围的数据检索过滤条件，避免因为间隙锁带来的负面影响而锁定了不该锁定的记录。
4. 尽量控制事务的大小，减少锁定的资源量和锁定时间长度。
5. 在业务环境允许的情况下，尽量使用较低级别的事务隔离，以减少 MySQL 因为实现事务隔离级别所带来的附加成本。

 

infoDB什么时候加表锁

对于 InnoDB 表，在绝大部分情况下都应该使用行级锁，因为事务和行锁往往是我们之所以选择 InnoDB 表的理由。

事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高该事务的执行速度。

事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁、减少数据库因事务回滚带来的开销。

在 InnoDB 下，使用表锁要注意以下两点：

1. 使用 LOCK TABLES 虽然可以给 InnoDB 加表级锁，但必须说明的是，表锁不是由 InnoDB 存储引擎层管理的，而是由其上一层──MySQL Server 负责的。仅当 autocommit=0（不自动提交，默认是自动提交的）、InnoDB_table_locks=1（默认设置）时，InnoDB 层才能知道 MySQL 加的表锁，MySQL Server 也才能感知 InnoDB 加的行锁。这种情况下，InnoDB 才能自动识别涉及表级锁的死锁，否则，InnoDB 将无法自动检测并处理这种死锁。
2. 在用 LOCK TABLES 对 InnoDB 表加锁时要注意，要将 AUTOCOMMIT 设为 0，否则 MySQL 不会给表加锁。事务结束前，不要用 UNLOCK TABLES 释放表锁，因为 UNLOCK TABLES 会隐含地提交事务。COMMIT 或 ROLLBACK 并不能释放用 LOCK TABLES 加的表级锁，必须用 UNLOCK TABLES 释放表锁。

正确的方式见如下语句，例如，如果需要写表 t1 并从表 t 读，可以按如下做：

SET AUTOCOMMIT=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
[do something with tables t1 and t2 here];
COMMIT;
UNLOCK TABLES;

 

MyISAM 表锁

MyISAM表级锁模式：

• 表共享读锁 （Table Read Lock）：不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；
• 表独占写锁 （Table Write Lock）：会阻塞其他用户对同一表的读和写操作；

MyISAM 表的读操作与写操作之间，以及写操作之间是串行的。当一个线程获得对一个表的写锁后， 只有持有锁的线程可以对表进行更新操作。 其他线程的读、 写操作都会等待，直到锁被释放为止。

默认情况下，写锁比读锁具有更高的优先级：当一个锁释放时，这个锁会优先给写锁队列中等候的获取锁请求，然后再给读锁队列中等候的获取锁请求。 （This ensures that updates to a table are not “starved” even when there is heavy SELECT activity for the table. However, if there are many updates for a table, SELECT statements wait until there are no more updates.）。

这也正是 MyISAM 表不太适合于有大量更新操作和查询操作应用的原因，因为，大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。同时，一些需要长时间运行的查询操作，也会使写线程“饿死” ，应用中应尽量避免出现长时间运行的查询操作（在可能的情况下可以通过使用中间表等措施对SQL语句做一定的“分解” ，使每一步查询都能在较短时间完成，从而减少锁冲突。如果复杂查询不可避免，应尽量安排在数据库空闲时段执行，比如一些定期统计可以安排在夜间执行）。

可以设置改变读锁和写锁的优先级：

• 通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。
• 通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低。
• 通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。
• 给系统参数max_write_lock_count设置一个合适的值，当一个表的读锁达到这个值后，MySQL就暂时将写请求的优先级降低，给读进程一定获得锁的机会。

MyISAM加表锁方法：

• 在执行查询语句（SELECT）前，会自动给涉及的表加读锁
• 在执行更新操作（UPDATE、DELETE、INSERT 等）前，会自动给涉及的表加写锁

这个过程并不需要用户干预，因此，用户一般不需要直接用 LOCK TABLE 命令给 MyISAM 表显式加锁。

在自动加锁的情况下，MyISAM 总是一次获得 SQL 语句所需要的全部锁，这也正是 MyISAM 表不会出现死锁（Deadlock Free）的原因。

MyISAM存储引擎支持并发插入，以减少给定表的读和写操作之间的争用：

如果MyISAM表在数据文件中间没有空闲块，则行始终插入数据文件的末尾。 在这种情况下，你可以自由混合并发使用MyISAM表的INSERT和SELECT语句而不需要加锁——你可以在其他线程进行读操作的时候，同时将行插入到MyISAM表中。 文件中间的空闲块可能是从表格中间删除或更新的行而产生的。 如果文件中间有空闲快，则并发插入会被禁用，但是当所有空闲块都填充有新数据时，它又会自动重新启用。 要控制此行为，可以使用MySQL的concurrent_insert系统变量。

如果你使用LOCK TABLES显式获取表锁，则可以请求READ LOCAL锁而不是READ锁，以便在锁定表时，其他会话可以使用并发插入。

• 当concurrent_insert设置为0时，不允许并发插入。
• 当concurrent_insert设置为1时，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个线程读表的同时，另一个线程从表尾插入记录。这也是MySQL的默认设置。
• 当concurrent_insert设置为2时，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录。

显示锁sql语句

• 共享读锁：lock table tableName read
• 独占写锁：lock table tableName write
• 同时加多锁：lock table t1 write，t2 read
• 批量解锁：unlock tables

MyISAM 表锁优化建议

缩短锁定时间

1. 唯一的办法就是让我们的 Query 执行时间尽可能的短
2. 尽量减少大的复杂 Query，将复杂 Query 分拆成几个小的 Query 分布进行。
3. 尽可能的建立足够高效的索引，让数据检索更迅速。
4. 尽量让 MyISAM 存储引擎的表只存放必要的信息，控制字段类型。
5. 利用合适的机会优化 MyISAM 表数据文件。

分离能并行的操作

MyISAM 存储引擎有一个控制是否打开 Concurrent Insert 功能的参数选项：

 

• concurrent_insert=2，无论 MyISAM 表中有没有空洞，都允许在表尾并发插入记录。
• concurrent_insert=1，如果 MyISAM 表中没有空洞（即表的中间没有被删除的行），MyISAM 允许在一个进程读表的同时，另一个进程从表尾插入记录。这也是 MySQL 的默认设置。
• concurrent_insert=0，不允许并发插入

可以利用 MyISAM 存储引擎的并发插入特性，来解决应用中对同一表查询和插入的锁争用。

例如，将 concurrent_insert 系统变量设为 2，总是允许并发插入；同时，通过定期在系统空闲时段执行 OPTIMIZE TABLE 语句来整理空间碎片，收回因删除记录而产生的中间空洞。

合理利用读写优先级

1. 通过执行命令 SET LOW_PRIORITY_UPDATES=1，使该连接读比写的优先级高，如果我们的系统是一个以读为主，可以设置此参数，如果以写为主，则不用设置。
2. 通过指定 INSERT、UPDATE、DELETE 语句的 LOW_PRIORITY 属性，降低该语句的优先级。
3. MySQL 也提供了一种折中的办法来调节读写冲突，即给系统参数 max_write_lock_count 设置一个合适的值，当一个表的读锁达到这个值后，MySQL 就暂时将写请求的优先级降低，给读进程一定获得锁的机会。
4. 需要长时间运行的查询操作，也会使写进程“饿死”，尽量避免出现长时间运行的查询操作，不要总想用一条 SELECT 语句来解决问题，因为这种看似巧妙的 SQL 语句，往往比较复杂，执行时间较长。
5. 多表级联。事务涉及多个表，比较复杂的关联查询，很可能引起死锁，造成大量事务回滚，这种情况若能一次性锁定事务涉及的表，从而可以避免死锁、减少数据库因事务回滚带来的开销。

死锁（Deadlock Free）

• 死锁产生：

• 死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环。
• 当事务试图以不同的顺序锁定资源时，就可能产生死锁。多个事务同时锁定同一个资源时也可能会产生死锁。
• 锁的行为和顺序和存储引擎相关。以同样的顺序执行语句，有些存储引擎会产生死锁有些不会——死锁有双重原因：真正的数据冲突；存储引擎的实现方式。

• 检测死锁：数据库系统实现了各种死锁检测和死锁超时的机制。InnoDB存储引擎能检测到死锁的循环依赖并立即返回一个错误。
• 死锁恢复：死锁发生以后，只有部分或完全回滚其中一个事务，才能打破死锁，InnoDB目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚。所以事务型应用程序在设计时必须考虑如何处理死锁，多数情况下只需要重新执行因死锁回滚的事务即可。
• 外部锁的死锁检测：发生死锁后，InnoDB 一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁，或涉及表锁的情况下，InnoDB 并不能完全自动检测到死锁， 这需要通过设置锁等待超时参数 innodb_lock_wait_timeout 来解决
• 死锁影响性能：死锁会影响性能而不是会产生严重错误，因为InnoDB会自动检测死锁状况并回滚其中一个受影响的事务。在高并发系统上，当许多线程等待同一个锁时，死锁检测可能导致速度变慢。 有时当发生死锁时，禁用死锁检测（使用innodb_deadlock_detect配置选项）可能会更有效，这时可以依赖innodb_lock_wait_timeout设置进行事务回滚。

 

MyISAM避免死锁：

在自动加锁的情况下，MyISAM 表不会出现死锁(MyISAM 总是一次获得 SQL 语句所需要的全部锁)。

InnoDB避免死锁：

• 为了在单个InnoDB表上执行多个并发写入操作时避免死锁，可以在事务开始时通过为预期要修改的每个元祖（行）使用SELECT ... FOR UPDATE语句来获取必要的锁，即使这些行的更改语句是在之后才执行的。
• 在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁、更新时再申请排他锁，因为这时候当用户再申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁
• 如果事务需要修改或锁定多个表，则应在每个事务中以相同的顺序使用加锁语句。 在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会
• 通过SELECT ... LOCK IN SHARE MODE获取行的读锁后，如果当前事务再需要对该记录进行更新操作，则很有可能造成死锁。
• 改变事务隔离级别，如降低隔离级别（如果业务允许，将隔离级别调低也是较好的选择，比如将隔离级别从RR调整为RC，可以避免掉很多因为gap锁造成的死锁）
• 为表添加合理的索引。可以看到如果不走索引将会为表的每一行记录添加上锁，死锁的概率大大增大。

如果出现死锁，可以用 SHOW INNODB STATUS 命令来确定最后一个死锁产生的原因。返回结果中包括死锁相关事务的详细信息，如引发死锁的 SQL 语句，事务已经获得的锁，正在等待什么锁，以及被回滚的事务等。据此可以分析死锁产生的原因和改进措施。

加锁处理分析

下面两条简单的SQL，他们加什么锁？

select * from t1 where id = 10

delete from t1 where id = 10

如果要分析加锁情况，必须还要知道以下的一些前提，前提不同，加锁处理的方式也不同

1. id列是不是主键？
2. 当前系统的隔离级别是什么？
3. id列如果不是主键，那么id列上有索引吗？
4. id列上如果有二级索引，那么这个索引是唯一索引吗？
5. 两个SQL的执行计划是什么？索引扫描？全表扫描？

根据上述情况，有以下几种组合

1. id列是主键，RC隔离级别
2. id列是二级唯一索引，RC隔离级别
3. id列是二级非唯一索引，RC隔离级别
4. id列上没有索引，RC隔离级别
5. id列是主键，RR隔离级别
6. id列是二级唯一索引，RR隔离级别
7. id列是二级非唯一索引，RR隔离级别
8. id列上没有索引，RR隔离级别
9. Serializable隔离级别

排列组合还没有列举完全，但是看起来，已经很多了。真的有必要这么复杂吗？事实上，要分析加锁，就是需要这么复杂。但是从另一个角度来说，只要你选定了一种组合，SQL需要加哪些锁，其实也就确定了。接下来挑几个比较经典的组合

1. id主键+RC

这个组合，是最简单，最容易分析的组合。id是主键，Read Committed隔离级别，给定SQL：delete from t1 where id =  10; 只需要将主键上，id = 10的记录加上X锁即可。如下图1：

结论：id是主键时，此SQL只需要在id=10这条记录上加X锁即可。

2. id唯一索引+RC

这个组合，id不是主键，而是一个Unique的二级索引键值。那么在RC隔离级别下，delete from t1 where id = 10;  需要加什么锁呢？见下图2：

id是unique索引，而主键是name列。此时，加锁的情况由于组合一有所不同。由于id是unique索引，因此delete语句会选择走id列的索引进行where条件的过滤，在找到id=10的记录后，首先会将unique索引上的id=10索引记录加上X锁，同时，会根据读取到的name列，回主键索引(聚簇索引)，然后将聚簇索引上的name  = ‘d’ 对应的主键索引项加X锁。

结论：若id列是unique列，其上有unique索引。那么SQL需要加两个X锁，一个对应于id unique索引上的id =  10的记录，另一把锁对应于聚簇索引上的[name='d',id=10]的记录、

3. id非唯一索引+RC

相对于组合一、二，组合三又发生了变化，隔离级别仍旧是RC不变，但是id列上的约束又降低了，id列不再唯一，只有一个普通的索引。假设delete  from t1 where id = 10; 语句，仍旧选择id列上的索引进行过滤where条件，那么此时会持有哪些锁？同样见下图3：根据此图，可以看到，首先，id列索引上，满足id =  10查询条件的记录，均已加锁。同时，这些记录对应的主键索引上的记录也都加上了锁。与组合二唯一的区别在于，组合二最多只有一个满足等值查询的记录，而组合三会将所有满足查询条件的记录都加锁。

结论：若id列上有非唯一索引，那么对应的所有满足SQL查询条件的记录，都会被加锁。同时，这些记录在主键索引上的记录，也会被加锁。

 

4. id非唯一索引+RR

还记得前面提到的MySQL的四种隔离级别的区别吗？RC隔离级别允许幻读，而RR隔离级别，不允许存在幻读。但是在组合五、组合六中，加锁行为又是与RC下的加锁行为完全一致。那么RR隔离级别下，
组合七，Repeatable  Read隔离级别，id上有一个非唯一索引，执行delete from t1 where id = 10;  假设选择id列上的索引进行条件过滤，最后的加锁行为，是怎么样的呢？同样看下图1：

结论：Repeatable Read隔离级别下，id列上有一个非唯一索引，对应SQL：delete from t1 where id =  10;  首先，通过id索引定位到第一条满足查询条件的记录，加记录上的X锁，加GAP上的GAP锁，然后加主键聚簇索引上的记录X锁，然后返回；然后读取下一条，重复进行。直至进行到第一条不满足条件的记录[11,f]，此时，不需要加记录X锁，但是仍旧需要加GAP锁，最后返回结束。
什么时候会取得gap  lock或nextkey lock 这和隔离级别有关,只在REPEATABLE READ或以上的隔离级别下的特定操作才会取得gap  lock或nextkey lock。

5.id无索引+RC

相对于前面三个组合，这是一个比较特殊的情况。id列上没有索引，where id =  10;这个过滤条件，没法通过索引进行过滤，那么只能走全表扫描做过滤。对应于这个组合，SQL会加什么锁？或者是换句话说，全表扫描时，会加什么锁？这个答案也有很多：有人说会在表上加X锁；有人说会将聚簇索引上，选择出来的id  = 10;的记录加上X锁。那么实际情况呢？请看下图2：

由于id列上没有索引，因此只能走聚簇索引，进行全部扫描。从图中可以看到，满足删除条件的记录有两条，但是，聚簇索引上所有的记录，都被加上了X锁。无论记录是否满足条件，全部被加上X锁。既不是加表锁，也不是在满足条件的记录上加行锁。
有人可能会问？为什么不是只在满足条件的记录上加锁呢？这是由于MySQL的实现决定的。如果一个条件无法通过索引快速过滤，那么存储引擎层面就会将所有记录加锁后返回，然后由MySQL  Server层进行过滤。因此也就把所有的记录，都锁上了。

结论：若id列上没有索引，SQL会走聚簇索引的全扫描进行过滤，由于过滤是由MySQL  Server层面进行的。因此每条记录，无论是否满足条件，都会被加上X锁。但是，为了效率考量，MySQL做了优化，对于不满足条件的记录，会在判断后放锁，最终持有的，是满足条件的记录上的锁，但是不满足条件的记录上的加锁/放锁动作不会省略。同时，优化也违背了2PL的约束。

 

6：id无索引+RR

组合八，Repeatable Read隔离级别下的最后一种情况，id列上没有索引。此时SQL：delete from t1 where id =  10; 没有其他的路径可以选择，只能进行全表扫描。最终的加锁情况，图3所示：

结论：在Repeatable  Read隔离级别下，如果进行全表扫描的当前读，那么会锁上表中的所有记录，同时会锁上聚簇索引内的所有GAP，杜绝所有的并发 更新/删除/插入  操作。当然，也可以通过触发semi-consistent read，来缓解加锁开销与并发影响，但是semi-consistent  read本身也会带来其他问题，不建议使用。

7 id主键+RR

上面的四个组合，都是在Read Committed隔离级别下的加锁行为，接下来的四个组合，是在Repeatable  Read隔离级别下的加锁行为。
组合五，id列是主键列，Repeatable Read隔离级别，针对delete from t1 where id  = 10; 这条SQL，加锁与组合一：[id主键，Read Committed]一致。

8. 组合六：id唯一索引+RR

与组合五类似，组合六的加锁，与组合二：[id唯一索引，Read  Committed]一致。两个X锁，id唯一索引满足条件的记录上一个，对应的聚簇索引上的记录一个。

9. 组合九：Serializable

针对前面提到的简单的SQL，最后一个情况：Serializable隔离级别。对于SQL2：delete from t1 where id = 10;  来说，Serializable隔离级别与Repeatable Read隔离级别完全一致，因此不做介绍。
Serializable隔离级别，影响的是SQL1：select  * from t1 where id = 10;  这条SQL，在RC，RR隔离级别下，都是快照读，不加锁。但是在Serializable隔离级别，SQL1会加读锁，也就是说快照读不复存在，MVCC并发控制降级为Lock-Based  CC。

结论：在MySQL/InnoDB中，所谓的读不加锁，并不适用于所有的情况，而是隔离级别相关的。Serializable隔离级别，读不加锁就不再成立，所有的读操作，都是当前读。

五、死锁案例

1. 不同表相同记录行锁冲突

这种情况很好理解，事务A和事务B操作两张表，但出现循环等待锁情况。

2. 相同表记录行锁冲突

这种情况比较常见，之前遇到两个job在执行数据批量更新时，jobA处理的的id列表为[1,2,3,4]，而job处理的id列表为[8,9,10,4,2]，这样就造成了死锁。

3. 不同索引锁冲突

这种情况比较隐晦，事务A在执行时，除了在二级索引加锁外，还会在聚簇索引上加锁，在聚簇索引上加锁的顺序是[1,4,2,3,5]，而事务B执行时，只在聚簇索引上加锁，加锁顺序是[1,2,3,4,5]，这样就造成了死锁的可能性。

4. gap锁冲突

innodb在RR级别下，如下的情况也会产生死锁，比较隐晦。不清楚的同学可以自行根据上节的gap锁原理分析下。

结论：在MySQL/InnoDB中，所谓的读不加锁，并不适用于所有的情况，而是隔离级别相关的。Serializable隔离级别，读不加锁就不再成立，所有的读操作，都是当前读。

一些优化锁性能的建议

• 尽量使用较低的隔离级别；
• 精心设计索引， 并尽量使用索引访问数据， 使加锁更精确， 从而减少锁冲突的机会
• 选择合理的事务大小，小事务发生锁冲突的几率也更小
• 给记录集显示加锁时，最好一次性请求足够级别的锁。比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁
• 不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会
• 尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响
• 不要申请超过实际需要的锁级别
• 除非必须，查询时不要显示加锁。 MySQL的MVCC可以实现事务中的查询不用加锁，优化事务性能；MVCC只在COMMITTED READ（读提交）和REPEATABLE READ（可重复读）两种隔离级别下工作
• 对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能