MySQL锁（读锁、共享锁、写锁、S锁、排它锁、独占锁、X锁、表锁、意向锁、
本文说明的是MySQL锁，和操作系统或者编程语言的锁无关。概念
作用：在并发情况下让数据正确的读写。
优点：并发情况下对数据读写可控，防止出错。
缺点：降低性能、增加难度。
分类
[*]数据操作类型划分
[*]读锁（共享锁、S锁）
[*]写锁（排它锁、独占锁、X锁）
[*]粒度划分
[*]表级锁
[*]S锁、X锁
[*]意向锁
[*]自增锁
[*]元数据锁
[*]行级锁
[*]记录锁
[*]间隙锁
[*]临键锁
[*]插入意向锁
[*]页级锁
[*]严格度划分
[*]悲观锁
[*]乐观锁
[*]加锁方式
[*]隐式锁
[*]显示锁
[*]其它
[*]全局锁
[*]死锁
测试用表
CREATE TABLE `cs` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`num1` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '数字列1',
`num2` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '数字列2',
`s1` varchar(2000) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '字符串列1',
`s2` varchar(2000) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '字符串列2',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `temp`.`cs` (`id`, `num1`, `num2`, `s1`, `s2`) VALUES (1, 1, 1, 'abc', 'xyz');
INSERT INTO `temp`.`cs` (`id`, `num1`, `num2`, `s1`, `s2`) VALUES (2, 2, 2, 'ABC', 'XYZ');读锁、共享锁、S锁
读锁、共享锁、S锁（Share Lock）是一个东西。
锁的是：允许同时有多个事务，对数据只能读，不能写。
写法：
select ... lock in share mode;
select ... for share; #MySQL8测试：
步骤会话A会话B备注1start transaction;start transaction;双方开启事务2select * from cs where id = 1 lock in share mode;select * from cs where id = 1 lock in share mode;双方添加共享锁，都能成功添加3commit;commit;正常提交事务，无报错写锁、排它锁、X锁、独占锁
写锁、排它锁、X锁（Exclusive Lock）、独占锁是同一个东西。
锁的是：仅允许同时有1个锁独占该事务，具有排它性，不允许其它任何类型的锁再占用该事务。
select ... for update;测试：
X锁排斥S锁1
步骤会话A会话B备注1start transaction;start transaction;双方开启事务2select * from cs where id = 1 lock in share mode;select * from cs where id = 1 for update;会话A添加S锁，会话B添加X锁3/阻塞会话B添加X锁被阻塞，说明X锁有排它性4commit;/会话B X锁添加成功5/commit;会话B提交事务，结束流程X锁排斥S锁2
步骤会话A会话B备注1start transaction;start transaction;双方开启事务2select * from cs where id = 1 for update;select * from cs where id = 1 lock in share mode;会话A添加X锁，会话B添加S锁3/阻塞会话B添加S锁被阻塞，说明X锁有排它性4commit;/会话B S锁添加成功5/commit;会话B提交事务，结束流程X锁排斥X锁
步骤会话A会话B备注1start transaction;start transaction;双方开启事务2select * from cs where id = 1 for update;select * from cs where id = 1 for update;双方添加X锁3/阻塞会话B添加X锁阻塞，证明排它性4commit;/会话B X锁添加成功5/commit;会话B提交事务，结束流程S锁、X锁常见误区
注意：在同一个事务里面，行级X锁或S锁，允许其它SQL语句对此行的写操作。
如下，每条SQL都能成功执行。
所以说锁，锁的是对外的事务，对内（当前会话）不做限制。
start transaction;
select * from cs where id = 1 for update;
update cs set num1 = 2 where id = 1;
commit;
start transaction;
select * from cs where id = 1 lock in share mode;
update cs set num1 = 2 where id = 1;
commit;注意：在任意个事务里面，可以对同一条不存在的数据共同添加X和S锁，不会阻塞。
事务A
start transaction;
select * from cs where id = 1234 for update;
select * from cs where id = 1234 lock in share mode;
事务B
start transaction;
select * from cs where id = 1234 for update;
select * from cs where id = 1234 lock in share mode;
事务A
commit;
事务B
commit;注意：事务A加了X锁，不影响事务B读这条数据。
事务A
start transaction;
select * from cs where id = 1 for update; #能正常读出数据
事务B
start transaction;
select * from cs where id = 1;
事务A
commit;
事务B
commit;对阻塞超时时间的优化
遇到X锁会阻塞，默认阻塞50秒，50秒过后阻塞停止，但不会自动的回滚或者提交事务。MySQL提供了一些阻塞时间的优化方向
方式1：配置优化
查看阻塞时间，单位秒
show variables like 'innodb_lock_wait_timeout';
或
select @@innodb_lock_wait_timeout;
修改
set session innodb_lock_wait_timeout = 10; #当前会话生效
或
set global innodb_lock_wait_timeout = 10; #全局生效，重启后新配置丢失
注意global的修改对当前线程是不生效的，只有建立新的连接才生效
或者修改配置文件
vim /etc/my.cnf
innodb_lock_wait_timeout = 10方式2：SQL语句层面，MySQL8新特性
select ... nowait：会立即停止，但报错。
select ...skip locked：会立即停止，但不会报错，结果中不包含被锁定的行。
表锁、页锁、行锁
这是锁定的粒度，3个依次递减，粒度越小越好。力度小意味着更少的资源被锁定，可以提高并发性。
表锁
表锁就是锁住了整张表，所以表锁比行锁，更不容易发生死锁的情况。
注意：MyISAM引擎使用select，会给当前表加上S锁，在写操作时会加上X锁。
查看一些数据库的表，都加了那些锁
show open tables;
In_use: 是否正在被使用，如果为 1 则表示该表正在被某个线程使用。
Name_locked: 是否被上了表级锁，如果为 1 则表示该表被锁定。
+--------------------+------------------------------------------------------+--------+-------------+
| Database | Table | In_use | Name_locked |
+--------------------+------------------------------------------------------+--------+-------------+
| performance_schema | events_waits_summary_by_user_by_event_name | 0 | 0 |
| performance_schema | events_waits_summary_global_by_event_name | 0 | 0 |
+--------------------+------------------------------------------------------+--------+-------------+
为某个表添加X锁
lock table 表名 write;
为某个表添加S锁
lock table 表名 read;
取消表锁，部分XS
unlock tables;表锁权限如下（MySQL5.7）
表锁类型当前会话对当前表可读当前会话对当前表可写其它会话对当前表可读其它会话对当前表可写S锁是否是阻塞X锁阻塞是阻塞阻塞表级锁->意向锁、意向共享锁、意向排它锁、IS锁、IX锁
意向锁（Intention Shared Lock）是自动维护的锁，用于表级别上表明事务对表中某个行的操作意向，意思是在表级别上声明了某个行已经加锁，优化事务之间的并发访问。意向锁分为两种：意向共享锁（IS）和意向排它锁（IX）。
MyISAM引擎不支持，InnoDB引擎支持。
MySQL添加行级锁，都会向上声明添加了意向锁。
注意意向排它锁与意向排它锁不冲突，意向排它锁与意向共享锁都不冲突，如果冲突了，锁的灵活性将大大降低。距离说明：两个不同的行X锁各生成一个IX锁，如果IX锁冲突，则无法创建两个行X锁，这种巨大的bug不允许出现。
优点：
[*]提高并发性：如果没有意向锁，在一个百万级的大表中加一个锁，可能需要逐行遍历，看看有没有加其它锁，会影响当前添加的锁，但是有了意向锁，在表的层面就可以获取先前的锁，提高性能。
[*]避免死锁：通过意向锁，可以快速判断哪些锁请求是互斥的，减少锁任务堆积引起的复杂度增加，复杂度一上来，就容易有死锁。
演示：行X锁与表S锁
步骤会话A会话B说明1start transaction;/会话A开启事务2select * from cs where id = 1 for update;/会话A加一个行X锁，相当于自动添加了一个IX锁3/lock table cs read;会话B添加表S锁4/阻塞这个过程就是表锁检测到了事先添加的IX锁，行级X锁排斥表级S锁，阻塞了5commit;成功加上了表级S锁会话A事务提交，锁资源释放6/unlock tables;释放表级S锁表级锁->自增锁、AI锁
自增锁（Auto Increment Lock）是指对自增长列（一般是主键）确保唯一性的一种锁机，如果没有自增锁，多个事务并发的执行，该列的值就可能一致。
为了避免重复，自增锁可能会导致插入操作的串行化，降低并发性能，这是它的缺点。
演示：
步骤会话A会话B说明1start transaction;start transaction;双方开启事务2INSERT INTO cs (num1, num2, s1, s2) VALUES (2, 2, 'ABC', 'XYZ');INSERT INTO cs (num1, num2, s1, s2) VALUES (2, 2, 'ABC', 'XYZ'),(2, 2, 'ABC', 'XYZ');双方新增3条记录3commitcommit;提交事务4select id from csselect id from cs并未发现报错，或者id重复的现象表级锁->元数据锁、MDL锁
MDL（Metadata Lock）听起来高大上，实际上就是DDL操作时自动加锁，所以不需要手动处理。
当要对表做增删改查操作时，会获取一个 MDL 读锁来阻止对表结构的修改。
更改表结构时，会加MDL写锁，阻塞其它线程的读写操作，直到结构变更操作完成。
演示 MDL读锁：
步骤会话A会话B说明1start transaction;/会话A开启事务2INSERT INTO cs (num1, num2, s1, s2) VALUES (2, 2, 'ABC', 'XYZ');alter table cs add index (num1);会话B新增一个普通索引3/阻塞会话B被阻塞4commit;索引添加成功MDL锁释放演示 MDL写锁：
步骤会话A会话B会话C说明1start transaction;start transaction;/AB方开启事务2INSERT INTO cs (num1, num2, s1, s2) VALUES (2, 2, 'ABC', 'XYZ');alter table cs add index (num2);/会话B新增一个普通索引3/阻塞/用会话A的MDL读锁去阻塞会话B4//select * from cs用会话B的MDL写锁去阻塞会话C5/阻塞阻塞MDL写锁阻塞了查询操作6rollback;rollback;成功查询出数据事务回滚，锁资源释放。但undo log不记录DDL语句，所以索引被添加无法回滚。行锁->记录锁、RL锁
记录锁（Record Lock）就是普通的对某一行上的X或者S锁。
注意InnoDB引擎下，支持行锁MyISAM不支持。
测试：
步骤会话A会话B备注1start transaction;start transaction;双方开启事务2select * from cs where id = 1 lock in share mode;select * from cs where id = 1 lock in share mode;双方添加共享锁，都能成功添加3commit;commit;正常提交事务，无报错行锁->间隙锁、GL锁
间隙锁（Gap Lock）就是在数据间隙加的锁，用于防止事务在一个范围内插入新的符合条件的行，以避免中间插入的幻读的问题（MySQL默认的RR隔离级别会产生幻读问题）。
例如一个一个不连续的id列，1,6,10，如果在1~6之间添加一个锁，就可以1~6防止幻读的插入操作，10后面间隙锁防止幻读就失效了，需要再次声明新的间隙锁。1~6之间没有具体的数据，所以X间隙锁，或者是S间隙锁，没有什么区别。
MySQL默认的RR级别会产生幻读，幻读是指在同一事务中，先后执行相同的查询范围，查询到的数量不一致（mysql的select机制测不出来，两个事务需要插入相同的id引起的报错才能测出来），在并发环境中，幻读通常是因为其他事务在同一范围内插入新数据导致的。以下的演示，不演示幻读，只演示间隙锁阻止幻读。
测试：
步骤会话A会话B备注1start transaction;start transaction;双方开启事务2select * from cs where id = 4 lock in share mode;INSERT INTO cs (id, num1, num2, s1, s2) VALUES (3, 1, 1, 'abc', 'xyz');会话A添加间隙锁，会话B插入数据这个区间的数据3/阻塞可见间隙锁可以阻止区间幻读4commit;commit;会话A关闭事务，会话B提交插入数据当前id最大值为10，测试大于10的幻读区间
步骤会话A会话B备注1start transaction;start transaction;双方开启事务2sselect * from cs where id = 14 lock in share mode;INSERT INTO cs (id, num1, num2, s1, s2) VALUES (10000, 1, 1, 'abc', 'xyz');会话A添加间隙锁，会话B插入id为10000的数据3/阻塞可见间隙锁可以防止最大记录 ~ +∞之间的幻读4commit;commit;会话A关闭事务，会话B提交插入数据当前id最大值为100，同样的方法测试小于100的幻读区间
步骤会话A会话B备注1start transaction;start transaction;双方开启事务2sselect * from cs where id = 10001 lock in share mode;INSERT INTO cs (id, num1, num2, s1, s2) VALUES (9999, 1, 1, 'abc', 'xyz');会话A添加间隙锁，会话B插入id为9999的数据3/成功插入间隙锁未能阻塞幻读插入4commit;commit;会话A关闭事务，会话B提交插入数据行锁->临键锁、NKL锁
临键锁（next-key lock）相当于记录锁+间隙锁，用于防止幻读的锁。
测试：现表中有id为1,6,10三条数据。
步骤会话A会话B备注1start transaction;start transaction;双方开启事务2select * from cs where id between 1 and 6 lock in share mode;INSERT INTO cs (id, num1, num2, s1, s2) VALUES (4, 1, 1, 'abc', 'xyz');会话A添加临键锁，会话B插入id为4的数据3/阻塞临键锁阻塞幻读插入4commit;commit;双方提交事务，结束流程行锁->插入意向锁、IIL锁
插入意向锁（Insert Intention Lock）是间隙锁的一种，由insert行插入前的操作。该锁表示插入的意图，即插入同一索引间隙的多个事务如果没有在间隙内的相同位置插入，则insert不需要相互等待。
通俗讲就是如果间隙锁得到了释放，则需要insert的多个事务，不会阻塞。
测试：现表中有id为1,6,10三条数据。
步骤会话A会话B会话C备注1start transaction;start transaction;start transaction;三方开启事务2select * from cs where id = 5 lock in share mode;INSERT INTO cs (id, num1, num2, s1, s2) VALUES (4, 1, 1, 'abc', 'xyz');INSERT INTO cs (id, num1, num2, s1, s2) VALUES (4, 1, 1, 'abc', 'xyz');事务A上间隙锁，其余事务插入数据3/阻塞阻塞间隙锁起作用阻塞B、C会话4commit;成功插入成功插入插入意向锁起作用，让两个插入的事务不发生阻塞5commit;commit;commit;提交事务，结束流程页锁
页锁是一种锁定级别，用于控制对数据表中的页（MySQL对表数据读写的基本单位）的访问。
由于SQL语句层面，DDL、DML、DQL，都是针对库表或者表数据的读写操作，对于页极少操作，页锁粒度较大，其它锁范围使用场景够用，所以了解概念就行。
悲观锁
之前写过详细的文章可进行参考：MySQL乐观锁与悲观锁。
悲观锁比较悲观，假设数据一定会造成冲突，属于MySQL层面的锁。通过加锁阻塞其他事务，悲观锁可以保证在任何时刻，只有一个事务能够修改或访问共享资源，从而实现了强一致性。这意味着在悲观锁机制下，每个事务的读写操作都是有序、线性的。
需要事务的参与。
悲观锁，最经典的场景就是防超卖，一共10个库存，由于并发情况下，两个请求查询到的库存都是10，一个请求下单6个，一个请求下单8个，如果不加锁使其在事务中阻塞（阻塞可以理解为强制让事务串行执行），那就会有超卖的情况发生，即使是超卖，库存也不会显示为负，因为并发情况下两个请求检测到的库存都是10,大于8和6，最终扣库存的结果不是10-8-6=-4（这是串行请求的理论值），而是2或者4。
适用场景
写多写操作的前提，是保证数据不出错，悲观锁的机制很符合。
优点
[*]强一致性：基于事务又加锁，一致性可以保证。
[*]实现简单：在事务中for update即可，开发者不需要在这上面关注太多。
缺点
[*]死锁风险：悲观锁在使用不当的情况下可能导致死锁。如果多个事务持有锁并相互等待对方释放锁的情况发生，就可能发生死锁。
[*]性能较低：悲观锁通常需要在整个事务过程中锁定资源，这可能导致其他事务阻塞。
简单测试：模拟并发情况下防超卖，假设cs表的num1字段为库存字段
步骤会话A会话B备注1start transaction;start transaction;双方开启事务2select num1 from cs where id = 1 for update;
update cs set num1 = num1 - 6 where id = 1 and num1 >= 6;select num1 from cs where id = 1 for update;
update cs set num1 = num1 - 8 where id = 1 and num1 >= 8;双方添加X行锁，并准备扣减库存3/阻塞此时必有一个事务阻塞，等这个事务扣完库存提交事务后，在执行另一个扣库存的事务4commit;commit;双方提交事务，双方代码执行成功，但会话B库存不足，最终剩余库存4，结束流程注意，实际的开发逻辑，与以上示例况有偏差。
若前端检测到库存不足，直接拦截用户的下单动作并提示，就不会有后端的流程。
若前端认为库存充足，后端可能是下单时预扣库存，预扣库存的环节提前就检测当前其库存是否够当前的购买数量，若不够，则直接终止流程，返回用户结果。
若库存够用但未支付，若超时30分钟未支付则取消订单（这个延时队列可以用Redis Zset 或者RabbitMQ等其它消息中间件实现），若支付，则算一笔成功的交易。
这是MySQL能抗住的情况，如果抗不住，就需要Redis+Lua的单线程事务来阻止超卖的发生。
乐观锁
之前写过详细的文章可进行参考：MySQL乐观锁与悲观锁
乐观锁很乐观，假设数据一般情况不会造成冲突，属于程序层面的逻辑锁，在数据进行更新时，才进行锁的检测。是通过添加一个版本号的方式实现的，每当数据这一行所在的数据发生变化，则对应的版本号+1，更新数据时，将版本号作为查询条件。
至于是否要加事务，看写操作单条数据还是写操作多条数据。
注意：网上很多解决方案用时间戳来做version字段，我持反对意见，并发可能是一瞬间的事，不到一秒就有好多请求，用时间戳粒度太大，用随机字符串都比用这个强。
适用场景
[*]读多写少：由于并发写操作较少，乐观锁的修改数据受影响行数为0概率也较低。
允许一定量的重试或不需要重试的场景：这个要根据业务，否则来回重试会降低性能。
优点
[*]实现简单：乐观锁在代码上就可以实现，不需要额外对数据库额外操作。
[*]无死锁风险：悲观锁有死锁风险，乐观锁没有。
无需重试情况下，性能较高：乐观锁机制在并发访问情况下，不需要像悲观锁那样阻塞其他事务，提供了更高的并发性能，前提当前业务需求能容忍写操作失败的情况。
缺点
[*]并发冲突：多加了一个where条件，只能保证数据最终不会出错，不能保证每条写操作的SQL都执行成功（也就是受影响行数>0）。
[*]不提供强一致性：强一致性要求数据的状态在任何时刻都保持一致，悲观锁是到写操作那一步才去验证，期间只是做了个where条件的过滤。
[*]ABA问题：一个字段的值在请求X中查询出来是A，后续代码实现乐观锁，因为并发量大，同时过来一个Y请求，将A值改成了B，因为一些业务原因又改成了A，整个过程虽然不影响请求X的结果，且能正常执行，但是联合其它数据，这个情况是否符合业务场景，不好说，所以最好的解决方案，就是专门做一个version字段，且不会与之前的version重复，即可，把这个version字段作为where条件，而不是存A或者B字段的所在字段作为where条件。
[*]当前的请求中了上一个乐观锁的招，导致的版本号不一样，需要重试，反复的重试也降低性能，由于这个原因，所以用于读操作多的场景。
简单测试：模拟并发情况下防超卖，假设cs表的num1字段为库存字段，s1为version字段，一共10个库存，两个请求查询到的库存都是10，一个请求下单6个，一个请求下单8个。
步骤会话A会话B备注1select * from cs where id = 1select * from cs where id = 1双方获取版本号和库存2update cs set num1 = num1 - 6,s1 = 'version2' where id = 1 and s1 = 'version1' and num1 >=6;update cs set num1 = num1 - 8,s1 = 'version1'where id = 1 and s1 = 'version1' and num1 >=8;乐观锁无需事务，但是需要多个原子性的业务场景，仍推荐加事务3扣库存成功虽然是并发场景，但A执行略快于B，此时版本号已修改为version2会话B执行成功，但库存扣减失败4/select * from cs where id = 1会话B进行重试，并重新获取版本号和库存5/update cs set num1 = num1 - 8,s1 = 'version3'where id = 1 and s1 = 'version2' and num1 >=8;双方代码执行成功，但会话B库存不足，最终剩余库存4，结束流程隐式锁
隐式锁不需要显式地编写锁定关键词，会自动加锁或解锁。
例如在未提交的事务中插入数据，就是通过隐式锁的方式避免其它事务对插入数据的读写，如果能读到了那就是脏读，如果能写那就是脏写。
隐式锁是一种延时加载的方式，在多个事务的情况下，在第二个事务访问时，才会被动的加锁，这样可以减少锁的数量。
由于不需要手动处理，了解机制就行。
步骤会话A会话B会话C备注1start transaction;start transaction;SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;AB开启事务，C检测锁2insert into cs(num1, num2, s1, s2) values(1,2,3,4);/SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;A插入数据，此时C未检测到有锁3/select * from cs lock in share mode;SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;insert操作会产生一个写锁，进而阻止事务B的读锁，此时事务C可以查看的到4/阻塞/在第二个事务访问时，事务A被动的加锁，通过事务C可以查看的到5commit;commit;/双方提交事务，会话A成功插入一条数据显式锁
显式声明的锁，for update，lock in share mode这种的。
全局锁
全局锁（Global Lock）的作用是锁定整个数据库实例，让整个库只能读不能写，粒度很大。
flush tables with read lock：获取全局读锁，对所有事务只能读不能写。
unlock tables：释放全局锁。
应用场景：备份数据库或者导出数据时，为了保证数据的一致性而需要锁定整个数据库实例，确保备份过程中数据不会发生变化。全局锁会影响数据库的正常运行。
测试：
步骤会话A会话B备注1flush tables with read lock;/会话A开启全局锁2/select * from cs;
desc cs;读数据表和表数据都没问题3/insert into cs(num1, num2, s1, s2) values(1,2,3,4);会话B插入数据4/阻塞全局锁禁止写操作5unlock tables;插入成功释放全局锁。数据插入成功6flush tables with read lock;/会话A开启全局锁7/alter table cs add index(num1);会话B为表字段加索引8/阻塞;全局锁阻塞DDL操作9unlock tables;索引添加成功释放全局锁，索引插入成功，结束会话死锁
之前写过关于死锁的详细文章：MySQL死锁。
MySQL 中的死锁是指两个或多个事务相互等待对方释放锁资源，导致它们永远无法继续执行的情况。（不会耕田,怎能下地 不会下地,怎么耕田）
测试：先cs表中有id为1,6的两条数据
步骤会话A会话B备注1start transaction;start transaction;双方开启事务2update cs set num1 = 1 where id = 1;update cs set num1 = 60 where id = 6;更新数据默认会产生X锁，这一步正常执行3update cs set num1 = 6 where id = 6;update cs set num1 = 10 where id = 1;更新数据默认会产生X锁4阻塞/事务B对于id=6的写锁并未释放，此时又新增了update请求，需要加X锁，所以阻塞5/Deadlock found when trying to get lock; try restarting transaction事务AB互相需要等待，导致死锁6commit;commit;双方提交事务，结束流程，事务A正确执行，事务B被回滚排查方法1
show engine innodb status;执行以上命令会得到大量日志，在LATEST DETECTED DEADLOCK与TRANSACTIONS之间寻找sql 语句，以此定位死锁源头。示例如下：
......
------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-01-23 23:48:30 0x1f00
*** (1) TRANSACTION:
TRANSACTION 805714, ACTIVE 12 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 3, OS thread handle 8076, query id 762 localhost ::1 root updating
update test set name = 'C++语言' where id = 2
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2575 page no 3 n bits 72 index PRIMARY of table `temp`.`test` trx id 805714 lock_mode X locks rec but not gap waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 4; hex 00000002; asc ;;
1: len 6; hex 0000000c4b53; asc KS;;
2: len 7; hex 34000002a80923; asc 4 #;;
3: len 3; hex 432b2b; asc C++;;
*** (2) TRANSACTION:
TRANSACTION 805715, ACTIVE 9 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 4, OS thread handle 7936, query id 766 localhost ::1 root updating
delete from test where id = 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 2575 page no 3 n bits 72 index PRIMARY of table `temp`.`test` trx id 805715 lock_mode X locks rec but not gap
Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 4; hex 00000002; asc ;;
1: len 6; hex 0000000c4b53; asc KS;;
2: len 7; hex 34000002a80923; asc 4 #;;
3: len 3; hex 432b2b; asc C++;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2575 page no 3 n bits 72 index PRIMARY of table `temp`.`test` trx id 805715 lock_mode X locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 00000001; asc ;;
1: len 6; hex 0000000c4b46; asc KF;;
2: len 7; hex ad000002b10110; asc ;;
3: len 1; hex 43; asc C;;
*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 805727
Purge done for trx's n:o < 805727 undo n:o < 0 state: running but idle
History list length 36
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 283132143509864, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283132143508992, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283132143508120, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283132143507248, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
......排查方法2
show status like 'innodb_row_lock%'执行以上命令后会得到一个表格，
Innodb_row_lock_current_waits 0 如果这个数字非零，表示有事务正在等待其他事务持有的行级锁。
Innodb_row_lock_time 18756 以毫秒为单位。它表示所有事务在竞争锁资源时，所消耗的总时间
Innodb_row_lock_time_avg 3126平均每个InnoDB行级锁的持有时间，毫秒为单位。用总的锁持有时间除以锁请求次数来计算。
Innodb_row_lock_time_max 7921单个InnoDB行级锁的最大持有时间，毫秒为单位。这个值表示所有事务竞争锁资源中的最长时间。
Innodb_row_lock_waits 6 这个指标表示有多少次事务在竞争锁资源时需要等待。排查方法3
该查询是用来获取当前正在运行的事务（INNODB_TRX表）、空闲状态的线程（PROCESSLIST表，COMMAND为Sleep）、线程对应的进程信息（threads表）、线程对应的当前执行中的SQL语句（events_statements_current表）的一些相关信息。
SELECT a.trx_id, a.trx_state, a.trx_started, a.trx_query, b.ID, b.USER, b.DB, b.COMMAND, b.TIME, b.STATE, b.INFO, c.PROCESSLIST_USER, c.PROCESSLIST_HOST, c.PROCESSLIST_DB, d.SQL_TEXT
FROM information_schema.INNODB_TRX a
LEFT JOIN information_schema.PROCESSLIST b ON a.trx_mysql_thread_id = b.id AND b.COMMAND = 'Sleep'
LEFT JOIN performance_schema.threads c ON b.id = c.PROCESSLIST_ID
LEFT JOIN performance_schema.events_statements_current d ON d.THREAD_ID = c.THREAD_ID;解决
MySQL会自动解决死锁问题，代价就是MySQL自行找到成本最低的事务，自动回滚。所以说解决方案不是解锁，而是避免。
避免
[*]降低事务粒度：轻量级的事务，锁定更少的资源，不容易发生死锁。
[*]尽快提交事务：锁能更快的释放。
[*]合理的索引设计: 合理设计数据库表的索引可以减少锁竞争，提高查询效率。
[*]一致的访问顺序: 当应用程序涉及多个表时，保持一致的访问顺序可以避免死锁。例如，如果事务A先锁定表X，再锁定表Y，那么事务B也应该按照相同的顺序锁定表X和表Y，从而避免死锁。
什么是最小成本回滚策略？
在 MySQL 中，当发生死锁时，MySQL 使用一种叫做"最小成本回滚"（InnoDB 中称为"最小编号事务回滚"）的策略来选择一个事务作为牺牲者并进行回滚，最小成本回滚策略是 MySQL 的默认行为，它会自动选择牺牲者并回滚事务。
最小成本回滚策略的原理是选择最小成本的事务作为牺牲者。评估算法如下：
[*]回滚事务所涉及的操作数量：回滚操作的数量越小，成本越低。
[*]回滚事务所占用的系统资源：回滚事务占用的系统资源越少，成本越低。
[*]回滚事务已执行的工作量：已执行的工作量越少，成本越低。
锁排查与监控
查询语句用于查看 InnoDB 存储引擎中关于行锁的相关统计信息
show status like 'innodb_row_lock%';
Innodb_row_lock_current_waits如果这个数字非零，表示有事务正在等待其他事务持有的行级锁。
Innodb_row_lock_time 以毫秒为单位。它表示所有事务在竞争锁资源时，所消耗的总时间
Innodb_row_lock_time_avg 平均每个InnoDB行级锁的持有时间，毫秒为单位。用总的锁持有时间除以锁请求次数来计算。
Innodb_row_lock_time_max 单个InnoDB行级锁的最大持有时间，毫秒为单位。这个值表示所有事务竞争锁资源中的最长时间。
Innodb_row_lock_waits 这个指标表示有多少次事务在竞争锁资源时需要等待。查看 InnoDB 存储引擎中当前活动的事务信息
SELECT * FROM information_schema.innodb_trx;
trx_id 事务的唯一标识符。
trx_state 事务的状态，如 RUNNING、LOCK WAIT、ROLLING BACK 等。
trx_started 事务启动的时间。
trx_requested_lock_id 请求的锁的标识符。
trx_wait_started 等待锁的开始时间。
trx_weight 事务的权重，用于死锁检测。
trx_mysql_thread_id MySQL 线程 ID。
trx_query 与事务相关的 SQL 查询语句。
trx_operation_state 事务内部操作的状态。
trx_tables_in_use 事务使用的表的数量。
trx_tables_locked 事务锁定的表的数量。
trx_lock_structs 事务内部使用的锁结构数量。
trx_lock_memory_bytes 用于事务锁定的内存字节数。
trx_rows_locked 事务锁定的行数。
trx_rows_modified 事务修改的行数。
trx_concurrency_tickets 用于事务并发控制的票数。
trx_isolation_level 事务的隔离级别。
trx_unique_checks 是否启用了唯一性检查。
trx_foreign_key_checks 是否启用了外键约束检查。
trx_last_foreign_key_error 最后一个外键错误信息。
trx_adaptive_hash_latched 是否适应性哈希被锁定。
trx_adaptive_hash_timeout 适应性哈希锁定超时次数。
trx_is_foreign_key_with_check是否用于外键约束检查。
trx_is_foreign_key 是否用于外键约束。查看 InnoDB 存储引擎中当前正在被锁定的对象（如表、行）的信息
SELECT * FROM information_schema.innodb_locks;
lock_id 锁的唯一标识符。
lock_trx_id持有该锁的事务的唯一标识符。
lock_mode 锁的模式，如 S（共享锁）或 X（独占锁）等。
lock_type 锁的类型，如 RECORD（行级锁）或 TABLE（表级锁）等。
lock_table 被锁定的表名。
lock_index 被锁定的索引名。
lock_space 被锁定的表空间的标识符。
lock_page 被锁定的页码。
lock_rec 锁定的记录。
lock_data 与锁相关的其他数据。查看 InnoDB 存储引擎中当前存在的锁等待情况
SELECT * FROM information_schema.innodb_lock_waits;
requesting_trx_id 请求锁的事务的唯一标识符。
requested_lock_id 请求的锁的唯一标识符。
blocking_trx_id 导致锁等待的阻塞事务的唯一标识符。
blocking_lock_id 导致锁等待的锁的唯一标识符。
来源:https://www.cnblogs.com/phpphp/p/18062571
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！
页:
[1]