《MySQL数据库》MySQL锁分析
前言
作者在工作中遇到了往MySQL表中插入一条数据，结果插入不进去。并且同样的操作在Oracle中却可以插入，于是就专门研究了一下MySQL5.7锁的逻辑。
锁的介绍
介绍锁之前需要先了解一下事务隔离级别：https://www.cnblogs.com/jssj/p/13437036.html
MySQL 主要锁类型如下
(1)共享/排它锁(Shared and Exclusive Locks)
共享锁：select ...... lock in share mode; 其他事务无法修改，发送在读取操作。
排它锁：select ...... for update; 其他事务无法修改，发生新增修改操作
(2)意向锁(Intention Locks)
就是从行锁升级到表锁。
(3)记录锁(Record Locks)
对记录行进行更新操作时候的一种锁，防止事务1更新的时候，事务2也更新，导致最终数据问题。
(4)间隙锁(Gap Locks)
范围更新数据的时候，将这些范围里面数据中间的间隙也不能被修改或者插入。比如更新主键id 10 到 100 之前的数据，这个时候其他事务是无法插入 id等 10到100之间的记录的。
(5)临键锁(Next-key Locks)
将行锁和间隙锁结合：将间隙和大于目前索引值的全部锁定，不让其他事务插入。
(6)插入意向锁(Insert Intention Locks)
插入意向锁是一种特殊的间隙锁，但不同于间隙锁的是，该锁只用于并发插入操作。如果说间隙锁锁住的是一个区间，那么插入意向锁锁住的就是一个点。
(7)自增锁(Auto-inc Locks)
AUTO-INC LOCKS是一种表级别锁，当一个表格中存在AUTO_INCREMENT列，事务尝试在该表中插入记录时，会使用该中特殊的表级别锁。最简单的场景是，当A事务试图在该表中插入记录时，其他试图在该表中插入记录的事务必须等待A事务，以使A事务插入的记录获得连续的主键。
innodb_autoinc_lock_mode这个设置控制了Mysql使用何种算法进行auto-increment lock
我们看一个案例：
创建一张表：
CREATE TABLE test(id int primary key not null auto_increment, name VARCHAR(128));
插入一些数据
insert into test(name) VALUES('lili'); insert into test(name) VALUES('zhangsan'); insert into test(name) VALUES('leilei'); insert into test(name) VALUES('lisi'); insert into test(name) VALUES('wangwu'); insert into test(name) VALUES('zhaoliu');
测试：在一个地方开启事务，然后删除表数据并且不提交。
另外开启一个窗口，测试新增语句：
等待超时无法提交，在等待结束之前我们来看看mysql 给我们提供的锁表信息
select * from information_schema.innodb_trx t; -- 当前运行的所有事务 select * from information_schema.innodb_locks t; -- 当前出现的锁 select * from information_schema.innodb_lock_waits t; -- 锁等待的对应关系
innodb_trx 显示运行事务的情况，很明显有两个事务在运行，其中被锁事务的sql可以展示。
innodb_locks 该表比较重要的信息为，可以为我们提供两个事务为什么会互斥导致出现锁， 比如字段 lock_mode 是指具体的锁类型，X表示互斥锁，GAP表示间隙锁
innodb_lock_waits 该表给我展示的是互斥资源之间的关系。
这三张表各个字段含义：
innodb_trx
innodb_locks
innodb_lock_waits
案例
我们在进行修改表的ddl 操作时：发现会一直执行下面，不会停止。
通过：show processlist;
会出现：Waiting for table metadata lock，在等待锁的释放。
等待时间参数：
select @@lock_wait_timeout;
可以通过查询：
select * from information_schema.innodb_trx;
来找到running 的记录并且 kill trx_mysql_thread_id ； 生产需要谨慎使用，因为这边是不知道别kill掉的是什么？
下面在测试一个案例（该演示不要使用第三方工具例如Navicat 等）：
一个窗口删除数据，不提交。
另外一个窗口，更新这条记录，会出现锁等待。
这个时候我们查询mysql的`performance_schema`.events_statements_current 表可以看到sql语句的执行情况。
如果生产数据量大的话其实也是不好排查的。
将mysql 表关联起来找到引起锁等等待的事务语句
SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query FROM information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;
将图中449作为下面sql的条件：
SELECT a.sql_text, c.id, d.trx_started FROM `performance_schema`.events_statements_current a JOIN `performance_schema`.threads b ON a.thread_id = b.thread_id JOIN information_schema.`PROCESSLIST` c ON b.processlist_id = c.id JOIN information_schema.innodb_trx d ON c.id = d.trx_mysql_thread_id WHERE 1 = 1 and c.id = 449 -- blocking_thread 编号 ORDER BY d.trx_started;
这样我们就找到了是什么sql语句没有提交事务，导致锁等待的出现。
总结
MySQL查询未提交事务的目前知晓的是一定要出现锁等待才能查到，不然就获取不到未提交事务的执行情况，如果有人知道请一定要分享给我，谢谢。
