本篇博文的mysql版本：5.7.2
1、概述
偏向InnoDB存储引擎，开销大，加锁慢，会出现死锁；锁定粒度最小，发生锁冲突的概率最底，并发度也最高。
InnoDB与MyISAM的最大不同点：一是支持事务，二是采用了行级锁。
2、准备建表语句和数据
-- 创建一张InnoDB存储引擎的数据表
CREATE TABLE `employee` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`dep_id` int(11) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`salary` decimal(10,2) DEFAULT NULL,
`cus_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=109 DEFAULT CHARSET=utf8;
-- 插入测试数据
INSERT INTO `employee` VALUES ('1', '鲁班', '1', '10', '1000.00', '1');
INSERT INTO `employee` VALUES ('2', '后裔', '1', '20', '2000.00', '1');
INSERT INTO `employee` VALUES ('3', '孙尚香', '1', '20', '2500.00', '1');
INSERT INTO `employee` VALUES ('4', '凯', '4', '20', '3000.00', '1');
INSERT INTO `employee` VALUES ('5', '典韦', '4', '40', '3500.00', '2');
INSERT INTO `employee` VALUES ('6', '貂蝉', '6', '20', '5000.00', '1');
INSERT INTO `employee` VALUES ('7', '孙膑', '6', '50', '5000.00', '1');
INSERT INTO `employee` VALUES ('8', '蔡文姬', '30', '35', '4000.00', '1');3、行锁的演示
注意：mysql5.5及以上版本，默认事务会进行自动提交。
使用2个客户端分别连接上mysql，我这里用的是Navicat Premium和mysql命令行版，下面的演示中我会将Navicat Premium称为：会话1。mysql命令行版称为：会话2。
2个mysql客户端示例截图如下：
会话1：
会话2：
3.1、在会话1中关闭mysql的事务的自动提交
set autocommit = 0 #关闭自动提交事务
示例截图：
在会话1中进行数据的更新操作 然后紧接着进行查询操作：
从上图中可以看到，将id=1的数据的name列的值改成了"李白"，并且当前自己查询自己，看到的数据也确实是修改后的数据。
此时我们在会话2中查询该表的数据：
从上图中可以看到 会话2中的mysql读取到的数据并不是会话1中修改的"李白"的这条数据，由此可见会话2中是看不到更新的内容的，只有会话1中进行了commit（提交）操作之后，会话2中才能看到最新修改的数据。
将会话1中的数据进行commit后，然后在会话2中执行查询操作，查看会话2中查询到的数据和会话1中查询到的数据是否一样
commit; -- 在会话1中执行commit操作
示例截图：
从上图中可以看到会话1中进行了commit之后，在会话2中查询到的数据就是最新的数据，和会话1中看到的数据是一模一样的。
从上面的示例中，可以进行以下总结：
执行更新操作的时候：自己（当前连接）可以查看到自己所更新的数据内容，非当前连接，看不到更新的数据内容，只有当前连接进行commit后，其它连接才能看到更新的数据内容。
3.2、将会话1和会话2中的事务的自动提交都关闭
注：上面我们已经将会话1中的事务的自动提交给关闭了，所以这里只需要关闭会话2中的事务的自动提交就可以了
在会话2中关闭mysql的事务的自动提交
set autocommit = 0; #关闭自动提交事务
示例截图：
会话1执行更新操作，会话2也执行更新操作，并且都是更新同一条记录
-- 会话1中执行如下SQL(会话1中先执行如下sql,注意 并没有进行commit操作)： update employee set name = '李白1' where id = 1; -- 会话2中执行如下SQL(会话1中执行完了之后,会话1中执行完了之后,在执行如下sql)： update employee set name = '李白2' where id = 1;
示例截图如下：
从上图中可以看到 会话1执行了更新操作后，紧接着会话2中也执行了更新同一条数据的sql，发现会话2中执行的sql语句被堵塞在哪里进行等待了。
为什么会话2中的sql执行会被阻塞在哪里进行等待？
答：因为会话1中正在执行，并且会话1中已经把这行数据进行锁死了（也就是说行锁默认就会给你加上了），所以会话2中执行更新同一条数据的时候会被阻塞在哪里进行等待。
只有会话1中的数据进行commit之后，之前在会话2中被阻塞在哪里的操作才会自动执行下去。
示例截图：
然后不要忘了在会话2中同样需要进行commit操作
示例截图：
此时无论在会话1还是会话2中执行查询操作，那么id=1的name列的值就应该变成了李白2。
示例截图：
从上面的示例中，可以进行以下总结：
会话1执行更新操作，会话2也执行更新操作，并且都是更新同一条记录：会话1没有提交事务时，会话2的更新会处于阻塞状态 ，当会话1进行commit的时候，会话2才会继续执行，会话2更新完毕之后，同样需要进行commit操作。
3.3、会话1和会话2同时更新数据，但更新的不是同一条记录，是否会互相影响？
答：不会受到彼此之间的影响，就不一 一截图了，可以自己试验看一下。
小总结：如果会话1和会话2同时更新同一条数据的话，那么其中一个会话会被阻塞等待在哪里，因为其中一个会话会将修改的那一行数据进行加锁（行锁），加锁的那一行进行commit之后（进行commit之后也就是等于释放锁了），其它会话才能依次进行操作。如果会话1和会话2更新的不是同一条数据，那么它们之间不会有任何影响。
一句话总结：多个事务操作同一行数据时，后来的事务处于阻塞等待状态。这样可以避免了脏读等数据一致性的问题。后来的事务可以操作其他行数据，解决了表锁高并发性能低的问题。另外InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为表锁。
4、索引失效，导致行锁变表锁
字段使用varchar类型时，没有添加引号，导致索引失效。就会造成行锁变表锁，另一个会话连接更新数据时，会造成阻塞。
4.1、行锁变表锁的演示
-- 添加一个复合索引 ALTER TABLE `lock`.`employee` ADD INDEX `idx_name_age`(`name`, `age`) USING BTREE;
会话1中的操作如下：
set autocommit = 0; -- 第1步 先关闭事务的自动提交 update employee set name= '韩信' where age = 10 -- 第2步 执行该条SQL语句
会话2中的操作如下：
set autocommit = 0; -- 第1步 同样先关闭事务的自动提交 update employee set name= '明世隐' where age = 35; -- 第2步 执行该条SQL语句
示例截图如下：
从上图中可以看到，会话1和会话2明明修改的不是同一条数据，但是会话2依然被阻塞在哪里进行等待了。。怎么办呢？只能等待会话1进行commit操作后，会话2才能继续进行下去啦。
为什么上面会话1中的update更新操作会造成行锁变成表锁？
答：因为上面我们建立了一个复合索引（name,age），而会话1中的where条件只有age，造成了不符合索引中的左前缀原则，导致没有用上该复合索引，所以导致索引失效，行锁就变成了表锁。从而虽然会话2中更新的数据和会话1中更新的数据不是同一条数据，会话2依然受到了影响。
5、如何锁定一行数据？
锁定一行指的是：在查询数据的时候，就把某一行数据进行加锁（行锁），不允许其它人进行修改了。锁定某一行的关键词语：for update;
会话1中操作：
set autocommit = 0; -- 查询id=1的数据，并锁定该行记录 select *from employee where id = 1 for update;
会话2中操作：
set autocommit = 0; -- 这里会被阻塞。上面会话1中在查询的时候已经加锁了。 update employee set name = 'zl' where id = 1;
5.1：for update的更多使用及注意细节
记住一个原则：一锁二判三更新
①、for update如何使用
使用姿势：
select * from table where xxx for update
②、for update的锁行及锁表操作示例
InnoDB存储引擎 默认是行级别的锁，当有明确指定的主键时候，是行级锁。否则是表级别。
例子：假设表foods ，存在有id跟name、status三个字段，id是主键，status有索引。
例1：(明确指定主键，并且有此记录，行级锁)
SELECT * FROM foods WHERE id=1 FOR UPDATE; SELECT * FROM foods WHERE id=1 and name='php最好的语言' FOR UPDATE;
例2：(明确指定主键/索引，若查无此记录，无锁)
SELECT * FROM foods WHERE id=-1 FOR UPDATE;
例3：(无主键/索引，表级锁)
SELECT * FROM foods WHERE name='php最好的语言' FOR UPDATE;
例4: (主键/索引不明确，表级锁)
SELECT * FROM foods WHERE id <> '3' FOR UPDATE; SELECT * FROM foods WHERE id LIKE '3' FOR UPDATE;
③、for update的注意点
⑴、for update 仅适用于InnoDB，并且必须开启事务，在begin与commit之间才生效。
⑵、要测试for update的锁表情况，可以利用MySQL的Command Mode，开启二个视窗来做测试。
④、for update的疑问点
当开启一个事务进行for update的时候，另一个事务也有for update的时候会一直等着，直到第一个事务结束吗？
答：会的。除非第一个事务commit或者rollback或者断开连接，第二个事务会立马拿到锁进行后面操作。
如果没查到记录会锁表吗？
答：会的。表级锁时，不管是否查询到记录，都会锁定表。
例2不是说无锁吗？为什么最后说都会锁定表？
答：说的是表级锁时会锁，例2主键查询，是行锁，查询不到记录不锁表，例3和例4这种表级锁，查询不到记录也会锁表。
⑤、关于行锁的小总结：由此可见，行锁都是基于索引来进行锁定的，只要字段有索引（无论是主键索引或者普通索引）并且有该条记录，都是能成功锁定该行记录的。
6、间隙锁
什么是间隙锁？
当我们使用范围条件，而不是相等条件检索数据，并请求共享或排它锁时，InnoDB会给符合条件的已有数据记录的索引项加锁，对于键值在条件范围内但并不存在的记录 叫做"间隙(GAP)"，InnoDB也会对这个"间隙"加锁，这种锁机制，就是所谓的间隙锁。间隙锁的主要作用就是防止幻读。
会话1中的操作
set autocommit = 0; update employee set name = 'wzyl' where id > 3 and id < 7; -- 将id为4、5、6这三行数据进行锁定。
会话2中的操作
set autocommit = 0; update employee set name = 'test' where id = 1; -- 可以执行 不会被阻塞 update employee set name = 'test' where id = 5; -- 不能执行，会被阻塞在那里进行等待,因为5这行数据在会话1中的范围锁定之内。
7、行锁的分析
7.1 查看有没有行锁在等待
可以使用如下语句对行锁进行分析（即 监控有没有锁 在等待）：
show status like 'innodb_row_lock%';
对于各个状态说明如下：
Innodb_row_lock_current_waits：当前正在等待 锁 的数量 即 当前有多少 锁 正在等待（重点关注）
Innodb_row_lock_waits：数据库系统启动到现在 一共发生过多少次 锁 等待的次数，如果数据库重启 则该值会重置为0（重点关注）
Innodb_row_lock_time：从系统启动到现在锁定总时间长度
Innodb_row_lock_time_avg：每次等待所花平均时间
Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花的时间长度
上面这5个状态变量，比较重要的是：Innodb_row_lock_waits、Innodb_row_lock_waits
尤其是当等待次数很高，而且每次等待时长也很大的时候，我们就要分析系统中为什么有这么多的等待，然后根据分析结果来制定优化。
7.2 查看哪个事务在等待（被阻塞了）
-- 使用以下语句查看有哪些事务在等到锁（被阻塞了） SELECT * FROM information_schema.INNODB_TRX WHERE trx_state='LOCK WAIT'; -- 上面语句查询出来的结果中，关注以下几列即可： trx_id: 事务id号 trx_state: 当前事务的状态 trx_mysql_thread_id: mysql连接层的 连接线程id号(即 执行 show processlist 语句后 显示出来的结果中的 id 列选项) trx_query: 被阻塞的执行语句(一般是要丢给开发的)
备注：其实还有一个语句 可以显示的比上面（7.2）这个操作 显示的更全面和细致，即下面的 7.3 操作 一般使用下面的 7.3 进行排查即可 上面的 7.2 了解下就行
7.3 查看锁源(锁源：就是 阻塞别人的罪魁祸首)，谁锁的我！
-- 使用以下语句查看 被锁和锁定它 的之间的关系 SELECT * FROM sys.innodb_lock_waits; -- ====>被锁的和锁定它的之间关系 -- 上面语句查询出来的结果中，关注以下几列即可： locked_table: 哪张表出现的锁等待，产生锁等待的表名 waiting_trx_id: 等待的事务id号(与上面 7.2 中的 trx_id 列对应) waiting_pid: 等待的线程id号(与上面 7.2 中的 trx_mysql_thread_id 列对应) blocking_trx_id: 锁源的事务id号 blocking_pid: 锁源的线程id号 即 执行 show processlist 语句后 显示出来的结果中的 id 列选项 可以理解为process_id。其实就是一个连接线程id号 -- 上面语句查询出来的结果中，以下几列了解 知道什么意思即可： locked_index: 在那个索引上锁住了, innodb的锁是基于索引来锁的 locked_type: 锁的类型 包含 行级锁(Record Lock)、间隙锁(Gap Lock)、Next-Key Lock锁(行锁和间隙锁组合起来就叫Next-Key Lock) waiting_query: 被阻塞的执行语句(与上面 7.2 中的 trx_query 列对应) waiting_lock_mode: 等待锁的类型，是一个什么级别的锁(X,S) 一般情况下都是X，X: 排它锁,阻塞所有的 sql_kill_blocking_connection: mysql的建议处理方式 直接kill掉锁源的连接 ps: mysql的建议一般不采纳。。必须从根上解决问题,万一下次又遇到了呢？即使临时kill掉 也要注意是否会影响到业务逻辑
备注：通过7.3的操作，就找到了被阻塞的人和阻塞别人的人
7.3的操作步骤 我们找到了 由 blocking_pid（会话） 产生的 对应的 blocking_trx_id（事务）导致了别人被阻塞了，即 这里 找到了锁源
7.4 根据锁源的pid 找到锁源SQL的线程id(thread_id)
注意：mysql内部有很多线程，包括 连接线程、专门负责执行sql语句的sql线程等等等等......，我们不能单纯的只通过 一个连接线程 直接找到 对应的sql语句，因为 一个连接线程 下边执行的语句太多啦，我们应该精准的找到 到底是哪一个sql线程 执行的这条sql语句才行。
所以说要找到那条准确的sql语句，必须准确的找到执行者（sql线程）是谁
-- 根据 锁源的连接线程id 找到 锁源的sql线程id（即 thread_id） SELECT * FROM performance_schema.threads WHERE processlist_id=1520; -- 这里的1520连接线程id号 根据自身实际情况换成对应的 -- 这里where条件中的 processlist_id 其实就是 7.3中的 blocking_pid
7.5 根据锁源的sql线程id 找到锁源的SQL语句
-- sql线程在执行的语句 SELECT * FROM performance_schema.`events_statements_current` WHERE thread_id=1789; -- 这里的1789sql线程id号 就是上面 7.4执行后 找到的thread_id 根据自身实际情况换成对应的 -- 上面语句查询出来的结果中，关注以下几列即可： thread_id: sql线程id号 sql_text: 锁源的sql语句，就是这条sql语句把其它sql语句给阻塞了 so 解决掉它 -- 执行语句的历史 SELECT * FROM performance_schema.`events_statements_history` WHERE thread_id=1789; -- 查看1789sql线程执行的历史语句
行锁分析尾声：7.1至7.5 就是排查锁的相关问题步骤 找到对应的sql语句后，该 改sql语句就改sql语句 该 改业务逻辑就改相关业务逻辑
8、死锁（Deadlock）的监控
死锁（Deadlock）：指两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象。若无外力作用，它们都将无法继续进行下去，此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的进程称为死锁进程。表级锁不会产生死锁，所以解决死锁主要还是针对于最常用的InnoDB存储引擎。
例如，如果线程A锁住了记录1并等待记录2，而线程B锁住了记录2并等待记录1，这样两个线程就发生了死锁现象
死锁是比上面第7点中说的锁等待更严重的一种锁，死锁一般会对业务逻辑产生很严重的影响
死锁的示例图如下：
上图中，左图那两辆车造成死锁了吗？没有！上图中，右图四辆车造成死锁了吗？是！
8.1 死锁的监控方法
-- 可以在mysql命令行模式下 使用以下语句查看 show engine innodb status\G -- 在列出的一大堆信息中，找Deadlock相关的信息 找到后 会看到死锁对应的事务id号等等......之类的 -- 在Navicat Premium或Navicat for Mysql等之类的图形化界面软件中执行以下语句 show engine innodb status -- 其实就是去掉了\G 为了照顾一下不是很懂的小盆友。。 但是show engine innodb status 只能显示最新的一条死锁 该方式无法完全捕获到系统曾经发生过的所有死锁信息
如果想要记录所有的死锁信息 可以打开innodb_print_all_deadlocks参数，打开后 可以将所有的死锁日志记录到errorlog中
show variables like '%deadlock%'; -- 查看死锁日志记录是否打开 set global innodb_print_all_deadlocks=1 -- 临时打开全局死锁日志记录，重启mysql的时候 该选项就失效了 -- 死锁日志永久生效，将innodb_print_all_deadlocks参数添加到mysql配置文件中 innodb_print_all_deadlocks = 1
别把最好的时光，浪费在无畏的等待与犹豫不决中
声明：禁止任何非法用途使用，凡因违规使用而引起的任何法律纠纷，本站概不负责。
精彩评论