MySQL并发控制：锁机制

hguisu

已于 2024-03-27 14:10:43 修改

阅读量1.1w

点赞数 3

分类专栏： MySQL 文章标签： mysql mysql锁事务

于 2012-03-13 16:42:11 首次发布

本文链接：https://blog.csdn.net/hguisu/article/details/7349562

版权

MySQL 专栏收录该内容

14 篇文章 17 订阅

订阅专栏

当数据库中有多个操作需要修改同一数据时，不可避免的会产生数据的脏读。这时就需要数据库具有良好的并发控制能力，这一切在MySQL中都是由服务器和存储引擎来实现的。

当一个select语句在执行时可以施加读锁，这样就可以允许其它的select操作进行，因为在这个过程中数据信息是不会被改变的这样就能够提高数据库的运行效率。当需要对数据更新时，就需要施加写锁了，不在允许其它的操作进行，以免产生数据的脏读和幻读。本文用例表：

CREATE TABLE test (
    id int(11) NOT NULL,
    index_key int(11) NOT NULL,
    score int(11) NOT NULL default 0,
    PRIMARY KEY(id),
    KEY (index_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

一、MySQL并发控制--锁

1.1、锁基本概述：

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

2、锁保证数据并发访问的一致性、有效性；

3、锁冲突也是影响数据库并发访问性能的一个重要因素。加锁是消耗资源的，锁的各种操作，包括获得锁、检测锁是否是否已解除、释放锁等。

4、锁是MySQL在服务器层和存储引擎层的的并发控制。

5、MySQL用到了很多这种锁机制,比如行锁,表锁,读锁,写锁等,都是在操作之前先上锁.这些锁统称为悲观锁(Pessimistic Lock)

相对其他数据库而言,MySQL的锁机制比较简单,其中显著地特点是不同的存储引擎支持不同的锁机制.比如,MyISAM和MEMORY存储引擎采用的是表级锁(table-level locking); BDB存储引擎采用的是页面锁(page-level locking),但也支持表级锁; InnoDB存储引擎既支持行级锁(row-level locking),也支持表级锁,但默认情况下是采用行级锁.

1.2、加锁机制：

乐观锁：先修改，保存时判断是够被更新过，应用级别.假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。乐观锁不能解决脏读的问题。

乐观锁, 顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库如果提供类似于write_condition机制的其实都是提供的乐观锁。

悲观锁：先获取锁，再操作修改，数据库级别. 假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作

悲观锁，顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。

1.3、锁类型：

共享锁和排它锁

解决并发问题最有效的方案是引入了锁的机制，锁在功能上分为共享锁(shared lock)和排它锁(exclusive lock)即通常说的读锁和写锁。

读锁：是共享的，或者说是相互不阻塞的（共享锁Shared Lock，也叫S锁）。多个客户在同一时刻可以同时读取同一个资源而不相互干扰。当一个select语句在执行时可以施加读锁，这样就可以允许其它的select操作进行，因为在这个过程中数据信息是不会被改变的这样就能够提高数据库的运行效率。产生共享锁的sql语句，读取时对行记录加一个S锁： select * from users where id =1 lock in share mode。如果事务T1某行R的S锁，则其他事务可以同时持行R的S锁，但是不能对行R加X锁。

写锁：是排他的，一个写锁会阻塞其他的写锁和读锁 (Exclusive Lock，也叫X锁，也叫做写锁、独占锁、互斥锁)。即当需要对数据更新时，就需要施加写锁了，不在允许其它的操作进行，以免产生数据的脏读和幻读。读取时对行记录加X锁：select * from ad_plan for update;

如果事务T1持有了行R上的X锁，则其他任何事务不能持有行R的X锁，必须等待T1在行R上的X锁释放。如果事务T1在行R上保持S锁，则另一个事务T2对行R的锁的请求按如下方式处理：

T2可以同时持有S锁
T2如果想在行R上获取X锁，必须等待其他事务对该行添加的S锁或X锁的释放。

意向锁-Intention Locks

另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。

意向锁是表级锁，表示table中的row所需要的锁(S锁或X锁)的类型。意向锁分为意向共享锁(IS锁)和意向排它锁(IX锁)。

意向共享锁（IS）:IS锁表示当前事务意图在表中的行上设置共享锁. 事务在给一个数据行加共享锁前必须先取得该表的IS锁。

意向排他锁（IX）：事务意图给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

意向锁是InnoDB自动加的，不需用户干预。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁；

意向锁不会阻止除了全表锁定请求之外的任何锁请求。
意向锁的主要目的是显示事务正在锁定某行或者正意图锁定某行。

锁的兼容矩阵如下：

1.4、锁粒度

一种提高共享资源并发性的方式就是让锁定对象更有选择性。尽量只锁定需要修改的部分数据，而不是所有的资源。更理想的方式是，只对会修改的数据片进行精确的锁定。在给定的资源上，锁定的数据量越少，则系统的并发程度越高。所谓的锁策略，就是在锁的开销和数据的安全性之间寻求平衡。

根据锁的级别或密度来划分，MySQL有三种锁的级别：表级、行级、页级。

表级锁（table lock）: 表锁的Mysql中最基本的锁策略，并且是开销最小的策略。它会锁定整张表，一个用户在对表进行写操作前，需要先获取写锁，这会阻塞其他用户对该表的所有读写操作。

行级锁（row lock）：行级锁可以最大程度地支持并发处理（同时也带来了最大的锁开销），行级锁只在存储引擎层实现，而Mysql服务器层没有实现。

页面锁:开销和加锁时间介于表锁和行锁之间;会出现死锁;锁定粒度介于表锁与行锁之间,并发度一般。

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

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

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

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

不同粒度锁的比较：

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

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

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

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

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

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

3）行级锁只在存储引擎层实现，而Mysql服务器层没有实现。行级锁更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统

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

二、MySQL事务

事务就是一组原子性的sql查询，事务内的语句，要么全部执行成功，要么全部执行失败。

2.1、事务ACID原则

数据库事务要完整、一致的执行对数据库的操作，必须满足事务ACID 原则，即原子性(Atomicity) 、一致性(Consistency)、隔离性(Isolation)、持久化(Durability)，这4项原则就是国际上公认的数据库事务标准，支撑事务应用的数据库系统必须要满足ACID原则。
原子性( Atomicity):强调事务的不可分割。事务中包含的所有操作要么都做，要么都不做，没有第三种情况。

一致性( Consistency):事务执行前后，数据的完整性保持一致。比如说A向B转账，转账前和转账后AB两个账户的总金额必须是一致的。

隔离性( Isolation):隔离性是数据库允许多个并发事务同时对数据进行读写的能力，隔离性可以防止事务并发执行时由于交叉执行导致数据不一致的问题。

持久性( Durability):事务完成后，对数据的修改是永久的，即使出现系统故障也不会丢失。

2.2、为什么需要隔离性：事务并发造成的问题

如果事务之间不是互相隔离的，可能将会出现以下问题。

1、脏读：读到了未提交的数据

当一个事务正在访问数据，并对数据进行了修改，但是该修改确未提交到数据库中，这时另外一个事务也访问这个数据，然后使用了这个修改的数据，那么读到的这个数据就是脏数据。解决方法：在写的时候加锁。

2、不可重复读：读已修改的数据

一次事务内的两次（多次）相同查询，查询到了不同的结果。（在读数据的时候加锁）

3、幻读：读提交了插入/删除的数据

一次事务内多次查询返回的结果集不一样（比如增加了或者减少了行记录）。（串行化即可解决）。

脏读--------->不可重复读----------->幻读，事务的隔离性在不断提高，但是并行不断降低。

不可重复读和幻读比较：两者有些相似，但是前者针对的是update，后者针对的insert/delete。

4、更新丢失

当两个事务选择同一行，然后更新数据，由于每个事务都不知道其他事务的存在，就会发生丢失更新的问题，（你我同时读取同一行数据，进行修改，你commit之后我也commit，那么我的结果将会覆盖掉你的结果）。

2.3、隔离级别

1、未提交读（read-uncommitted）：在一个事务中，可以读取到其他事务未提交的数据变化，这种读取其他会话还没提交的事务，叫做脏读现象，在生产环境中切勿使用。

2.已提交读（read-committed）：在一个事务中，可以读取到其他事务已经提交的数据变化，这种读取也就叫做不可重复读，因为两次同样的查询可能会得到不一样的结果。

3.可重复读（repetable-read）：MySQL默认隔离级别，在一个事务中，直到事务结束前，都可以反复读取到事务刚开始时看到的数据，并一直不会发生变化，避免了脏读、不可重复读现象，但是它还是无法解决幻读问题。

4.可串行化（serializable）：这是最高的隔离级别，它强制事务串行执行，避免了前面说的幻读现象，简单来说，它会在读取的每一行数据上都加锁，所以可能会导致大量的超时和锁争用问题。

隔离级别总结：
read-uncommitted（0）: 浏览访问级别，存在脏读、不可重复读、幻读
read-committed（1）: 游标稳定级别，存在不可重复度、幻读
repetable-read（2）: 存在幻读
serializable（3）: 隔离级别，保证事务安全，但完全串行，性能低

隔离级别	读数据一致性	脏读	不可重复读	幻读
未提交读	最低级别，只保证不读取物理上损坏的数据	有	有	有
已提交读	语句级	无	有	有
可重复读	事务级	无	无	有
可串行化	最高级别，事务级	无	无	无

这四种事务隔离级别是指定的SQL标准，InnoDB默认的隔离级别是REAPEATABLE READ，但与其他数据库不同的时，它同时使用了Next-Key-Lock锁的算法，能够避免幻读的产生，因此能够完全满足事务的隔离性要求，即达到SERIALIZABLE隔离级别。

隔离级别越低，事务请求的锁越少或持锁时间越短，因此大部分数据库的默认隔离级别为READ COMMITED。但是有相关的分析也指出，隔离级别的性能开销几乎一样，因此用户无须通过调整隔离级别来提高性能。

查看和修改事务隔离级别的命令:

查看当前会话隔离级别：select @@tx_isolation;

查看系统当前隔离级别：select @@global.tx_isolation;

设置当前会话隔离级别：set session transaction isolatin level repeatable read;

设置系统当前隔离级别：set global transaction isolation level repeatable read;

三、MyISAM表锁

3.1、MyISAM表级锁模式

在MyISAM中是使用的表锁，在获得所需的全部锁时，要么全部满足，要么等待，因此不会出现死锁。MyISAM的每次的读写都会隐性的加上读写锁，并未出现过锁死的情况。读锁是共享的，而写锁是独占的，意味一个session在写的时候，另一个session必须等待。

MySQL表级锁有有两种模式: 表共享读锁(Table Read Lock) 和表独占写锁(Table Write Lock)

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

对于MyISAM表的读操作, 不会阻塞其他用户对同一表的读请求, 但会阻塞同一表的写请求; 对于MyISAM表的写请求, 则会阻塞其他用户对同一表的读和写操作; 所以 MyISAM表的读操作与写操作之间,以及写操作之间是串行的. 所以,当一个线程获得对一个表的写锁后,只有持有锁的线程可以对表进行更新操作.其他线程的读,写操作都会等待,知道锁被释放为止。

MyISAM作为引擎的表中,在执行查询语句之前,会自动给涉及的所有表加读锁,在执行更新操作(UPDATE,INSERT,DELETE)前,会自动给涉及的表加写锁.

默认情况下，写锁比读锁具有更高的优先级：当一个锁释放时，这个锁会优先给写锁队列中等候的获取锁请求，然后再给读锁队列中等候的获取锁请求。（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就暂时将写请求的优先级降低，给读进程一定获得锁的机会。

3.2、MyISAM加表锁方法：

自动加读锁：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表中有没有空洞，都允许在表尾并发插入记录。

3.3、查询表级锁争用情况：

可以通过检查 table_locks_waited 和 table_locks_immediate 状态变量来分析系统上的表锁的争夺，如果 Table_locks_waited 的值比较高，则说明存在着较严重的表级锁争用情况：

mysql> SHOW STATUS LIKE 'Table%';
+-----------------------+---------+
| Variable_name | Value |
+-----------------------+---------+
| Table_locks_immediate | 1151552 |
| Table_locks_waited | 15324 |
+-----------------------+---------+

四、InnoDB锁实现方式

什么是行锁锁、间隙锁？

Record Lock：单个行记录上的加锁，即我们通常讲的行锁，它的实质是通过对索引的加锁实现；只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。在事务隔离级别为读已提交下，仅采用Record Lock。
Gap Lock：锁定一个区间范围(即记录间的间隙，阻止插入防止幻读的发生)，但不包含记录本身。

Next-Key Lock：即Record Lock和Gap Lock的结合，锁定一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题。

4.1、INNODB行锁的情况

行锁Record Lock：单个行记录上的锁，我们通常讲的行锁，它的实质是通过对索引的加锁实现；只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。即record lock锁住的永远是索引，而非记录本身，即使该表上没有任何索引，那么innodb会在后台创建一个隐藏的聚集主键索引，那么锁住的就是这个隐藏的聚集主键索引。

对于INNODB表，在绝大部分情况下都应该使用行锁。在个别特殊事务中，可以考虑使用表锁。

1、表字段进行变更：
2、事务需要更新大部份或全部数据，表又比较大，默认的行锁不仅使这个事务执行效率低，可能造成其他事务长时间锁等待和锁冲突，这种情况考虑使用表锁来提高事务的执行速度。
3、事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。这种情况可以考虑一次性锁定事务涉及的表，避免死锁，减少数据库因事务回滚带来的开销。
4、使用表锁注意两点
　　(1) lock tables虽然可以给innodb加表锁，但表锁不是由innodb存储引擎层管理，则是由上层mysql server负责。仅当autocommit=0, innodb_table_locks=1(默认设置)时,innodb层才知道mysql加的表锁,mysql server也才能感知innodb加的行锁。
　　(2) 用lock tables对innodb表加锁时要注意, 要将autocommit 设置为0,否则mysql 不会给表加锁；事务结束前，不要用unlock tables释放表锁，因为它会隐式的提交事务。 commit 或rollback 并不能释放用lock tables 加的表锁。必须用unlock tables释放表锁。

4.2 InnoDB的间隙锁

MySQL为了更好地解决幻读问题，在INNODB引擎里的可重复读隔离级别（(即repeatable read)）下加入了Gap Lock（间隙锁）的机制。对于间隙锁可以先大致理解为，MySQL为了避免新的数据插入造成幻读，加大了行锁的粒度，锁住了某个索引的数据区间。

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

Next-Key锁:即record lock和gap lock的结合)对数据行进行加锁，即锁定一个范围，并且锁定记录本身，这样可以有效防止幻读的发生. InnoDB默认加锁方式是next-key 锁。
上面的案例一session 1中的sql是：select * from news where number=4 for update ;

Gap Lock间隙锁实质上是对索引前后的间隙上锁，不对索引本身上锁。根据检索条件向左寻找最靠近检索条件的记录值A，作为左区间，向右寻找最靠近检索条件的记录值B作为右区间，即锁定的间隙为（A，B）。

例如index_key是索引，select * from test where index_key=7 for update; 由于index_key=7行记录不存在，造成间隙锁：

next-key锁锁定的范围为间隙锁+记录锁，即区间（5，7），（7，10）加间隙锁，同时index_key=7的记录加记录锁，next-key锁定区间（5,10）

InnoDB使用间隙锁的目的：

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

innodb自动使用间隙锁的条件：

1、离级别为可重复读隔（RR）级别下。
2、检索条件必须有索引（没有索引的话，mysql会全表扫描，那样会锁定整张表所有的记录，包括不存在的记录，此时其他事务不能修改不能删除不能添加）
3、where条件是范围或者记录不存在（where id<7 或者where id=7 的id=7记录不存在）
4、间隙锁（无论是S还是X）只会阻塞insert操作。

关闭间隙锁(gap lock)方法:

在my.cnf里面的设置：
[mysqld]
innodb_locks_unsafe_for_binlog = 1

或者将会话隔离级别改到RC下

4.3 InnoDB SQL加锁情况

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

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

1)、UPDATE语句：

1、如果使用唯一索引，InnoDB仅锁定索引记录本身，不锁定间隙。

2、如果使用非唯一索引，或者未命中索引，InnoDB使用间隙锁或者next-key锁来锁定索引范围，这样就可以阻止其他事务插入锁定范围。例如：UPDATE ... WHERE id=n 更新不存在的记录时，会造成间隙锁。

3、UPDATE ... WHERE ...在搜索遇到的每条记录上设置一个独占的next-key锁，如果是唯一索引只锁定记录。
4、当UPDATE修改聚簇索引时，将对受影响的二级索引采用隐式锁，隐式锁是在索引中对二级索引的记录逻辑加锁，实际上不产生锁对象，不占用内存空间。

例如update test set index_key=100 where id=10;执行的时候index_key=10的索引(index_key是二级索引) 会被加隐式锁，只有隐式锁产生冲突时才会变成显式锁(如S锁、X锁)。即此时另一个事务也去更新id=10这条记录，隐式锁就会升级为显示锁。
这样做的好处是降低了锁的开销。
UPDATE可能会导致新的普通索引的插入。当新的索引插入之前，会首先执行一次重复索引检查。在重复检查和插入时，更新操作会对受影响的二级索引记录采用共享锁定(S锁)。

2)、DELETE语句

DELETE FROM ... WHERE ...在搜索遇到的每条记录上设置一个独占的next-key锁,如果是唯一索引只锁定记录。

3）、INSERT

INSERT区别于UPDATE系列单独列出，是因为它的处理方式较为特别。
插入行之前，会设置一种插入意向锁，插入意向锁表示插入的意图。如果其它事务在要插入的位置上设置了X锁，则无法获取插入意向锁，插入操作也因此阻塞。
INSERT在插入的行上设置X锁。该锁是一个Record锁，并不是next-key锁，即只锁定记录本身，不锁定间隙，因此不会阻止其他会话在这行记录前的间隙中插入新的记录。

4）、select for update

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

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

5）、select lock in share mode

in share mode 子句的作用就是将查找到的数据加上一个 share 锁，这个就是表示其他的事务只能对这些数据进行简单的select 操作，并不能够进行 DML 操作。select *** lock in share mode 使用场景：为了确保自己查到的数据没有被其他的事务正在修改，也就是说确保查到的数据是最新的数据，并且不允许其他人来修改数据。但是自己不一定能够修改数据，因为有可能其他的事务也对这些数据使用了 in share mode 的方式上了 S 锁。

性能影响：
select for update 语句，相当于一个 update 语句。在业务繁忙的情况下，如果事务没有及时的commit或者rollback 可能会造成其他事务长时间的等待，从而影响数据库的并发使用效率。
select lock in share mode 语句是一个给查找的数据上一个共享锁（S 锁）的功能，它允许其他的事务也对该数据上S锁，但是不能够允许对该数据进行修改。如果不及时的commit 或者rollback 也可能会造成大量的事务等待。

for update 和 lock in share mode 的区别：

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

4.4 InnoDB事务自动提交执行

MySQL的InnoDB引擎默认操作模式就是autocommit自动提交模式。这就表示除非显式地开始一个事务，否则每个查询都被当做一个单独的事务自动执行。我们可以通过设置autocommit的值改变是否是自动提交autocommit模式。

例如，开启autocommit的情况下：执行了insert into test values(2) ，mysql默认会帮你开启事务，并且在这条插入语句执行完成之后，默认帮你提交事务（默认帮你执行commit语句）。如果没有开启autocommit，mysql就不会提交事务执行commit，这时候select * from test是不会看到 id=2的记录。

4.5 InnoDB行锁实现方式

1、InnoDB 行锁是通过给索引上的索引项加锁来实现的，这一点 MySQL 与 Oracle 不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB 这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁！

2、不论是使用主键索引、唯一索引或普通索引，InnoDB 都会使用行锁来对数据加锁。

3、只有执行计划真正使用了索引，才能使用行锁：即便在条件中使用了索引字段，但是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决定的，如果 MySQL 认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下 InnoDB 将使用表锁，而不是行锁。因此，在分析锁冲突时，
可以通过 explain 检查 SQL 的执行计划，以确认是否真正使用了索引。

4、由于 MySQL 的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然多个session是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的（后使用这些索引的session需要等待先使用索引的session释放锁后，才能获取锁）。应用设计的时候要注意这一点。

五、for update的使用场景

　　如果遇到存在高并发并且对于数据的准确性很有要求的场景，是需要了解和使用for update的。

　　比如涉及到金钱、库存等。一般这些操作都是很长一串并且是开启事务的。如果库存刚开始读的时候是1，而立马另一个进程进行了update将库存更新为0了，而事务还没有结束，会将错的数据一直执行下去，就会有问题。所以需要for upate 进行数据加锁防止高并发时候数据出错。

记住一个原则：一锁二判三更新

1、排他锁的申请前提

　　排他锁的申请前提是需要：没有其他线程对该结果集中的任何行数据使用排他锁或共享锁，否则申请会阻塞。

　　for update仅适用于InnoDB，且必须在事务块(BEGIN/COMMIT)中才能生效。在进行事务操作时，通过“for update”语句，MySQL会对查询结果集中每行数据都添加排他锁，其他线程对该记录的更新与删除操作都会阻塞。

　　排他锁包含行锁、表锁。

2、数据一致性

　　假设有A、B两个用户同时各购买一件 id=1 的商品，用户A获取到的库存量为 1000，用户B获取到的库存量也为 1000，用户A完成购买后修改该商品的库存量为 999，用户B完成购买后修改该商品的库存量为 999，此时库存量数据产生了不一致。

　　有两种解决方案。

3、悲观锁方案：

　　每次获取商品时，对该商品加排他锁。也就是在用户A获取 id=1 的商品信息时对该行记录加锁，期间其他用户阻塞等待访问该记录。

　　悲观锁适合写入频繁的场景。

begin;
select * from goods where id = 1 for update;
update goods set stock = stock - 1 where id = 1;
commit;

4、乐观锁方案：

　　每次获取商品时，不对该商品加锁。在更新数据的时候需要比较程序中的库存量与数据库中的库存量是否相等，如果相等则进行更新，反之程序重新获取库存量，再次进行比较，直到两个库存量的数值相等才进行数据更新。

　　乐观锁适合读取频繁的场景。

// 不加锁获取 id=1 的商品对象
select * from goods where id = 1
begin;

// 更新 stock 值，这里需要注意 where 条件 “stock = cur_stock”，只有程序中获取到的库存量与数据库中的库存量相等才执行更新
update goods set stock = stock - 1 where id = 1 and stock = cur_stock;
commit;

五、死锁

5.1、死锁

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

MySQL死锁是指两个或者多个事务在同一资源上相互占用,并请求锁定对方占用的资源,从而导致恶性循环的现象。当多个事务试图以不同的顺序锁定资源时,就可能会产生死锁。多个事务同时锁定同一个资源时,也会产生死锁。

5.2、MyISAM表锁不会发生死锁

MyISAM表锁不会发生死锁，因为一次能获取全部的表锁，具有排他性，要么全部满足，要么等待，因此不会死锁。myisam的每次的读写都会隐性的加上读写锁，并未出现过锁死的情况。读锁是共享的，而写锁是独占的，意味一个session在写的时候，另一个session必须等待

5.3、InnoDB死锁产生

InnoDB的行锁复杂程度高于MyISAM，除单个SQL组成的事务外，锁是逐步获取的，这就可能导致死锁。

死锁是指两个或者多个事务相互等待对方释放锁, 形成死循环所造成的。

1、当事务试图以不同的顺序锁定资源时，就可能产生死锁。

2、多个事务同时锁定同一个资源时也可能会产生死锁。

如下图所示:

例如,设想下面两个事务同时处理StockPrice表:

事务1

START TRANSACTION;

UPDATE StockPrice SET close=45.50 WHERE stock id =4 and date = '2002-05-01';

UPDATE StockPrice SET close =19.80 WHERE stock id =3 and date ='2002-05-02;

CоммIT;

事务2

START TRANSACTION;

UPDATE StockPrice SET high = 20.12 MHERE stock id =3 and date =2002-05-02';

UPDATE StockPrice SET high = 47.20 KHERE stock id = 4 and date = "2002-05-01';

COMMIT;

如果凑巧,两个事务都执行了第一条UPDATE语句,更新了一行数据,同时也锁定了该行数据,接着每个事务都尝试去执行第二条UPDATE语句,却发现该行已经被对方锁定, 然后两个事务都等待对方释放锁,同时又持有对方需要的锁,则陷入死循环。除非有外部因素介入才可能解除死锁。

产生可能的几种死锁:

1）多个事务按不同的顺序锁定相同的数据集导致的死锁
如果多个事务按不同的顺序锁定相同的数据集，此时事务之间就会形成循环等待造成死锁，这是一种最常见也比较容易理解的死锁。经典例子：

首先，事务A 获取 id = 4 的锁（lock_mode X locks rec but not gap），事务 B 获取 id = 5 的锁；然后，事务 A 试图获取 id = 5的锁，而该锁已经被事务 B 持有，所以事务 A 等待事务 B 释放该锁，然后事务 B 又试图获取 id = 4 的锁，这个锁被事务 A 占有，于是两个事务之间相互等待，导致死锁。

2）索引不合理导致的死锁
由于InnoDB的锁是加在索引上的，因此索引不合理将直接导致锁定范围增大，发生锁冲突和死锁的的概率也随着增加。如果对索引和加锁机制不太了解的话，就很难定位死锁的原因。

事务一的加锁顺序为：id = 1->5，事务二的加锁顺序为：id = 10 -> 1，正好相反，所以会导致死锁。

3）唯一键值冲突导致的死锁
这个场景主要发生在三个或三个以上的事务同时进行唯一键值相同的记录插入操作，如图所示：

并发条件下，唯一键索引冲突可能会导致死锁，这种死锁一般分为两种，一种是rollback引发，另一种是commit引发。

rollback引发的Duplicate key死锁:

死锁产生的原因是事务一插入记录时，对(2,2)记录加X锁，此时事务二和事务三插入数据时检测到了重复键错误，此时事务二和事务三要在这条索引记录上设置S锁，由于X锁的存在，S锁的获取被阻塞。
事务一回滚，由于S锁和S锁是可以兼容的，因此事务二和事务三都获得了这条记录的S锁，此时其中一个事务希望插入，则该事务期望在这条记录上加上X锁，然而另一个事务持有S锁，S锁和X锁互相是不兼容的，两个事务就开始互相等待对方的锁释放，造成了死锁。

事务T2和事务T23为什么会加S锁，而不是直接等待X锁

事务T2的insert语句加的是隐式锁(隐式的Record锁、X锁)，但是其他事务插入同一行记录时，出现了唯一键冲突，事务一的隐式锁升级为显示锁。
事务二和事务三在插入之前判断到了唯一键冲突，是因为插入前的重复索引检查，这次检查必须进行一次当前读，于是非唯一索引就会被加上S模式的next-key锁，唯一索引就被加上了S模式的Record锁。
因为插入和更新之前都要进行重复索引检查而执行当前读操作，所以RR隔离级别下，同一个事务内不连续的查询，可能也会出现幻读的效果(但个人并不认为RR级别下也会出现幻读，幻读的定义应该是连续的读取)。而连续的查询由于都是读取快照，中间没有当前读的操作，所以不会出现幻读。

commit引发的Duplicate key死锁

产生的死锁和rollback死锁产生的原理一致。

3）插入意向间隙锁（Insert Intention）与间隙锁（GAP）冲突导致的死锁

插入意向锁(Insert Intention Locks)：插入意向锁本质上可以看成是一个Gap Lock

普通的Gap Lock 不允许在（上一条记录，本记录）范围内插入数据
插入意向锁Gap Lock 允许在（上一条记录，本记录）范围内插入数据

插入意向锁的作用是为了提高并发插入的性能，多个事务同时写入不同数据至同一索引范围（区间）内，并不需要等待其他事务完成，不会发生锁等待

但是需要注意：需要强调的是，虽然插入意向锁中含有意向锁三个字，但是它并不属于意向锁而属于间隙锁，因为意向锁是表锁而插入意向锁是行锁。

当多个事务持有间隙锁，且事务相互插入对方锁定的索引间隙时，便造成了死锁。如图所示：

使用show engine innodb status查看死锁状态。先后出现lock_mode X locks gap before rec insert intention waiting和lock_mode X locks gap before rec字眼，是gap锁和插入意向锁的冲突导致的死锁。

回顾select...for update的加锁范围
首先看看两个事务中的select ... for update做了哪些加锁操作：
index_key=5时，首先会获取index_key=5的索引记录锁(Record锁)，根据之前gap锁的介绍，会在前一个索引和当前索引之间的间隙加锁，于是区间(1,5)之间被加上了X模式的gap锁。除此之外RR模式下，还会加next-key锁，于是区间(5,10]被加了next-key锁；因此，index_key=5的加锁范围是，区间(1,5)的gap锁，{5}索引Record锁，(5,10]的next-key锁。即区间(1,10)上都被加上了X模式的锁。
同理index_key=7的加锁范围是，区间(5,10)的gap锁,{7}索引Record锁，（10,+∞)的next-key锁。
由gap锁的特性，兼容矩阵中冲突的锁也可以被不同的事务同时加在一个间隙上。上述两个select ... for update语句出现了间隙锁的交集，code=5的next-key锁和code=10的gap锁有重叠的区域——(5,10)。

死锁的成因
当事务一执行插入语句时，会先加X模式的插入意向锁，即兼容矩阵中的IX锁。
但是由于插入意向锁要锁定的位置存在X模式的gap锁。兼容矩阵中IX和X锁是不兼容的，因此事务一的IX锁会等待事务二的gap锁释放。
事务二也执行插入语句，与事务一同样，事务二的插入意向锁IX锁会等待事务一的gap锁释放。
两个事务互相等待对方先释放锁，因此出现死锁。

在实际遇到的问题：

业务需求：在一个事务内把两行记录的唯一主键id2的互换：id2=3和id2=6互换。

当执行完最后一条sql update test1 set id2=6 where id=2; 事务尚未提交，这时候出现间隙锁。

id 行锁锁定记录行：(2,4)

id2 间隙锁锁定（1、6）和（6、9）

事务2执行update test1 set id2=11 where id=4;发现行 id=4被Record lock。

事务2执行update test1 set id2=2 where id=10;发现是插入意向锁冲突。

5.4、InnoDB死锁检测和恢复释放机制

为了解决这种问题,数据库系统实现了各种死锁检测和死锁超时机制。越复杂的系统,比如InnoDB存储引擎,越能检测到死锁的循环依赖,并立即返回一个错误。这种解决方式很有效,否则死锁会导致出现非常慢的查询。还有一种解决方式,就是当查询的时间达到锁等待超时的设定后放弃锁请求,这种方式通常来说不太好。InnoDB目前处理死锁的方法是,将持有最少行级排他锁的事务进行回滚(这是相对比较简单的死锁回滚算法)。

检测死锁：发生死锁后，InnoDB一般能够检测出来，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。

死锁恢复：死锁发生以后，只有部分或完全回滚其中一个事务，才能打破死锁，InnoDB目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚。所以事务型应用程序在设计时必须考虑如何处理死锁，多数情况下只需要重新执行因死锁回滚的事务即可。

外部锁的死锁检测：发生死锁后，InnoDB 一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁，或涉及表锁的情况下，InnoDB 并不能完全自动检测到死锁，这需要通过设置锁等待超时参数 innodb_lock_wait_timeout 来解决

说明：innodb_lock_wait_timeout设置锁等待的时间是50s，一旦数据库锁超过这个时间就会报错。这个参数并不是只用来解决死锁问题，在并发访问比较高的情况下，如果大量事务因无法立即获得所需的锁而挂起，会占用大量计算机资源，造成严重性能问题，甚至拖跨数据库。我们通过设置合适的锁等待超时阈值，可以避免这种情况发生。

死锁影响性能：死锁会影响性能而不是会产生严重错误，因为InnoDB会自动检测死锁状况并回滚其中一个受影响的事务。在高并发系统上，当许多线程等待同一个锁时，死锁检测可能导致速度变慢。有时当发生死锁时，禁用死锁检测（使用innodb_deadlock_detect配置选项）可能会更有效，这时可以依赖innodb_lock_wait_timeout设置进行事务回滚。

5.5、InnoDB锁相关分析命令

1、可以通过检查 InnoDB_row_lock 状态变量来分析系统上的行锁的争夺情况：

2、在5.5中，information_schema库中增加了三个关于锁的表（MEMORY引擎）；

innodb_trx ## 当前运行的所有事务

innodb_locks ## 当前出现的锁

innodb_lock_waits ## 锁等待的对应关系

查询是否锁表：show OPEN TABLES where In_use > 0;

查看当前的事务：SELECT * FROM INFORMATION_SCHEMA.innodb_trx ;

查看当前锁定的事务: SELECT * FROM INFORMATION_SCHEMA.innodb_locks ;

查看当前等锁的事务: SELECT * FROM INFORMATION_SCHEMA.innodb_lock_waits ;

或者SELECT * FROM INFORMATION_SCHEMA.innodb_trx WHERE trx_state='LOCK WAIT'\G

3、分析死锁的原因：SHOW ENGINE INNODB STATUS

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

5.6、避免死锁

通常来说，死锁都是应用设计问题，通过调整业务流程，数据库对象设计，事务大小，以及访问数据库的sql语句，绝大部分死锁都可以避免，下面介绍几种避免死锁的常用方法.

1、减少事务操作的记录数

如果使用insert…select语句备份表格且数据量较大，在单独的时间点操作，避免与其他sql语句争夺资源，或使用select into outfile加上load data infile代替 insert…select，这样不仅快，而且不会要求锁定
一个锁定记录集的事务，其操作结果集应尽量简短，以免一次占用太多资源，与其他事务处理的记录冲突。
更新或者删除表格数据，sql语句的where条件都是主键或都是索引，避免两种情况交叉，造成死锁。对于where子句较复杂的情况，将其单独通过sql得到后，再在更新语句中使用。
sql语句的嵌套表格不要太多，能拆分就拆分，避免占有资源同时等待资源，导致与其他事务冲突。

2. 按顺序对表进行操作：

在应用中，如果不同的程序并发操作多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会。按顺序对表进行操作，是很常用的一种避免死锁的操作。比如：有二个不一样的存储过程，同时在对一个表进行复杂的删改操作。这种情况可以考虑先让一个执行完成，再让另一个在执行。
在程序中以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能。比如常见的就是多线程下在程序中lock锁住，在进程下保持串行处理。

3、合理设置索引（索引的粒度为一条记录）

精心设计索引，并尽量使用索引访问数据，使加锁更精确，从而减少锁冲突的机会
选择合理的事务大小，小事务发生锁冲突的几率也更小

4、合理使用锁

不要申请超过实际需要的锁级别
给记录集显示加锁时，最好一次性请求足够级别的锁。比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁
除非必须，查询时不要显示加锁。 MySQL的MVCC可以实现事务中的查询不用加锁，优化事务性能；MVCC只在COMMITTED READ（读提交）和REPEATABLE READ（可重复读）两种隔离级别下工作

5、避免间隙锁

在默认级别Repeatable read下, 如果两个线程同时对相同条件记录用 select .. for update 加排它锁，在没有符合该条件记录情况下，两个线程都会加锁成功。当一个程序发现记录不存在，就试图插入一条新数据，如果两个线程都这么做，就会出现死锁。这是因为在Repeatable read下产生了间隙锁。这种情况下，将隔离级别改成Read commited,就可避免问题。
当在Repeatable read下，如果两个线程都先执行select .. for update。在判断是否存在符合条件的记录，如果没有，就插入记录，此时，只有一个线程能插入成功，另一个线程会出现锁等待，当第1个线程提交后，第2个线程如因为主键值重复，会出现异常。但却获得了一个排它锁，需要执行rollback释放排它锁。避免影响其它事务。
尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响。

批量插入INSERT INTO ... ON DUPLICATE KEY UPDATE是先判断如果没有，就插入记录否则就更新，在默认级别Repeatable read下，很容易造成间隙锁。

INSERT INTO b_table(id,sn,ip....,createTime,updateTime) VALUES
<foreach collection="list" item="item" index="index" separator=",">
(
            #{item.id ,jdbcType=VARCHAR },
            #{item.sn ,jdbcType=VARCHAR },
            #{item.ip ,jdbcType=VARCHAR },
            .....
            #{item.createTime ,jdbcType=TIMESTAMP },
            #{item.updateTime ,jdbcType=TIMESTAMP }
)
</foreach>
ON DUPLICATE KEY UPDATE
`ip`=VALUES(`ip`),
`updateTime`=VALUES(`updateTime`)

因此在java的事务编程设置事务级别为Read commited类解决：

@Transactional(rollbackFor = Exception.class, isolation = Isolation.READ_COMMITTED)
    public void batchSaveOrUpdate(List<MachinePowerDay> machinePowerDayList) {
         //.....
    }

6、避免使用唯一键值约束：
　　总结：尽管通过上面介绍和sql 优化等措施，可以大大减少死锁，但死锁很难完全避免。因此。在程序设计中总是捕获并处理死锁异常是一个很好的编程习惯。

5.7、innodb_trx表字段说明

innodb_trx表提供了信息关于在InnoDB中执行的当前的每个事务，包含是否事务是等待一个锁，当事务开始后事务正在执行的sql语句：
对 innodb_trx 表的每个字段进行解释：
trx_id：事务ID。只读事务和非锁事务是不会创建id的。
trx_state：事务状态，有以下几种状态：RUNNING、LOCK WAIT、ROLLING BACK 和 COMMITTING。
trx_started：事务开始时间。
trx_requested_lock_id：事务当前正在等待锁的标识，可以和 INNODB_LOCKS 表 JOIN 以得到更多详细信息。
trx_wait_started：事务开始等待的时间。
trx_weight：事务的权重。代表修改的行数和被事务锁住的行数。为了解决死锁，innodb会选择一个高度最小的事务来当做牺牲品进行回滚。已经被更改的非交易型表的事务权重比其他事务高，即使改变的行和锁住的行比其他事务低。
trx_mysql_thread_id：事务线程 ID，可以和 PROCESSLIST 表 JOIN。
trx_query：事务正在执行的 SQL 语句。
trx_operation_state：事务当前操作状态。
trx_tables_in_use：当前事务执行的 SQL 中使用的表的个数。
trx_tables_locked：当前执行 SQL 的行锁数量。因为只是行锁，不是表锁，表仍然可以被多个事务读和写
trx_lock_structs：事务保留的锁数量。
trx_lock_memory_bytes：事务锁住的内存大小，单位为 BYTES。
trx_rows_locked：事务锁住的记录数。包含标记为 DELETED，并且已经保存到磁盘但对事务不可见的行。
trx_rows_modified：事务更改的行数。
trx_concurrency_tickets：该值代表当前事务在被清掉之前可以多少工作，由 innodb_concurrency_tickets系统变量值指定。
trx_isolation_level：当前事务的隔离级别。
trx_unique_checks：是否打开唯一性检查的标识。
trx_foreign_key_checks：是否打开外键检查的标识。
trx_last_foreign_key_error：最后一次的外键错误信息。
trx_adaptive_hash_latched：自适应哈希索引是否被当前事务阻塞。当自适应哈希索引查找系统分区，一个单独的事务不会阻塞全部的自适应hash索引。自适应hash索引分区通过 innodb_adaptive_hash_index_parts参数控制，默认值为8。
trx_adaptive_hash_timeout：是否为了自适应hash索引立即放弃查询锁，或者通过调用mysql函数保留它。当没有自适应hash索引冲突，该值为0并且语句保持锁直到结束。在冲突过程中，该值被计数为0，每句查询完之后立即释放门闩。当自适应hash索引查询系统被分区（由 innodb_adaptive_hash_index_parts参数控制），值保持为0。

六、锁案例

1、问题获取锁超时Lock wait timeout exceeded

我们app按钮没锁住功能，用户可以连续点击（一般按钮设计是点击事件完成后，按钮设置为不可点），导致出现大量并发请求，结果导致同一条update sql语句出现并发更新，造成lock tait。

java错误：Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction

原因分析

InnoDB表类型会出现锁等待的情况，在出现锁等待时，会根据参数innodb_lock_wait_timeout（默认50s）的配置，判断是否需要进行timeout的操作，如果等待时间超过了设置的时间就会报错。

按照经验列举锁等待超时出现的情况：
    1、在同一事务内先后对同一条数据进行插入和更新操作
    2、多台服务器操作同一数据库
    3、瞬时出现高并发现象，spring事务造成数据库死锁，后续操作超时抛出异常
    4、事务A对记录C进行更新/删除操作的请求未commit时，事务B也对记录C进行更新/删除操作。此时，B会等A提交事务，释放行锁。当等待时间超过innodb_lock_wait_timeout设置值时，会产生“LOCK WAIT”事务。
    5、数据库内存不足，导致无法执行写操作。

我们真正实际情况是遇到trx_state='RUNNING' 而不是 trx_state='LOCK WAIT'。需要结合trx_rows_locked 锁记录数选项和trx_started开始时间来看，如果开始时间比较早，同时有trx_rows_locked大于0,说明这个事务一直等锁没有提交。

临时解决办法是kill掉线程或者innodb_lock_wait_timeout 锁定等待时间改大

例如修改超时时间将 #innodb_lock_wait_timeout = 50 修改为 innodb_lock_wait_timeout = 500。但这同时也增加等待锁时间，容易使等待事务增多导致堆积问题。

根本解决方法还是需要找到锁表的事务，分析锁表原因，进行优化。

案例2、并发更新死锁情况

我们演示innodb中一个并发更新死锁情况：
CREATE TABLE `tb_info` (
`id` BIGINT(20) NOT NULL,
`uid` BIGINT(20) NOT NULL,
`apikey` BIGINT(20) NOT NULL,
`status` TINYINT(4) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_1` (`uid`,`apikey`),

KEY `idx_2` (`apikey`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

update tb_info set status=0 where apikey= '123'

这里采用的是INNODB做存储引擎，意味着会将update语句做为一个事务来处理。行级锁必须建立在索引的基础，这条更新语句用到了索引idx_2，所以这里肯定会加上行级锁。

行级锁并不是直接锁记录，而是锁索引，如果一条SQL语句用到了主键索引，mysql会锁住主键索引；如果一条语句操作了非主键索引，mysql会先锁住非主键索引，再锁定主键索引。

这个update语句会执行以下步骤：

1、由于用到了非主键索引，首先需要获取idx_2上的行级锁

2、紧接着根据主键进行更新，所以需要获取主键上的行级锁；

3、更新完毕后，提交，并释放所有锁。

如果在步骤1和2之间并发执行多个同样的sql：

session2：update tb_info set status=0 where apikey= '123'；锁住idx_2上的行级锁

session3：update tb_info set status=0 where apikey= '123', 主键上的行级锁；

一条语句获取了idx_2上的锁，等待主键索引上的锁；另一条语句获取了主键上的锁，等待idx_2上的锁，这样就出现了死锁。

基本解除正在死锁/等锁的状态有两种方法：

第一种方法：

1.查询是否锁表

show OPEN TABLES where In_use > 0;

2.查询进程threadid

show processlist ;
SELECT * FROM information_schema.processlist WHERE id='XXXX';

3.杀死进程id

kill id

第二种方法：

用下面三张表来查原因：

1、 innodb_trx ## 当前运行的所有事务
2、innodb_locks ## 当前出现的锁
3、innodb_lock_waits ## 锁等待的对应关系

如果数据库中有锁的话，查看 innodb_trx就可以看到对应的信息。通过查询知道是哪条语句锁了

1.查看下在锁的事务：

SELECT * FROM information_schema.innodb_trx;

SELECT * FROM information_schema.innodb_trx WHERE trx_state='LOCK WAIT'\G

获取trx_mysql_thread_id

1：查看当前的事务
SELECT * FROM information_schema.innodb_trx;

2：查看当前锁定的事务

SELECT * FROM information_schema.innodb_locks;

3：查看当前等锁的事务
SELECT * FROM information_schema.innodb_lock_waits;

4：查看当前等锁的事务获取trx_mysql_thread_id

SELECT * FROM information_schema.innodb_trx WHERE trx_state='LOCK WAIT'\G

5.杀死进程id：就是上面命令的trx_mysql_thread_id列

kill 线程ID

hguisu

关注

3
点赞
踩
19

收藏

觉得还不错? 一键收藏
打赏
1
评论
MySQL并发控制：锁机制

当数据库中有多个操作需要修改同一数据时，不可避免的会产生数据的脏读。这时就需要数据库具有良好的并发控制能力，这一切在MySQL中都是由服务器和存储引擎来实现的。当一个select语句在执行时可以施加读锁，这样就可以允许其它的select操作进行，因为在这个过程中数据信息是不会被改变的这样就能够提高数据库的运行效率。当需要对数据更新时，就需要施加写锁了，不在允许其...
复制链接

扫一扫