【MySQL系列】记插入产生的死锁问题

???欢迎来到我的博客，很高兴能够在这里和您见面！希望您在这里可以感受到一份轻松愉快的氛围，不仅可以获得有趣的内容和知识，也可以畅所欲言、分享您的想法和见解。

推荐:kwan 的首页,持续学习,不断总结,共同进步,活到老学到老
导航
檀越剑指大厂系列:全面总结 java 核心技术,jvm,并发编程 redis,kafka,Spring,微服务等
常用开发工具系列:常用的开发工具,IDEA,Mac,Alfred,Git,typora 等
数据库系列:详细总结了常用数据库 mysql 技术点,以及工作中遇到的 mysql 问题等
新空间代码工作室:提供各种软件服务,承接各种毕业设计,毕业论文等
懒人运维系列:总结好用的命令,解放双手不香吗能用一个命令完成绝不用两个操作
数据结构与算法系列:总结数据结构和算法,不同类型针对性训练,提升编程思维,剑指大厂

非常期待和您一起在这个小小的网络世界里共同探索、学习和成长。??? 欢迎订阅本专栏

博客目录

- 一.问题背景
- - 1.事务信息查询
  - 2.分析
- 二.分析步骤
- - 1.表信息
  - 2.分析过程
  - 3.策略建议
  - 4.死锁可能得原因

一.问题背景

1.事务信息查询

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX WHERE trx_query LIKE '%student%';

2.分析

从提供的日志信息来看，这是一个数据库事务（trx_id: 4165875）的状态报告，它显示了事务的一些关键信息。事务目前处于LOCK WAIT状态，意味着它正在等待获取一个它需要的锁。

以下是一些可能导致死锁的原因分析：

事务等待锁：trx_state: LOCK WAIT表明事务正在等待一个锁。trx_wait_started给出了等待开始的时间。
锁请求：trx_requested_lock_id显示了事务正在请求的锁的 ID，这有助于进一步分析锁请求的具体情况。
事务权重：trx_weight: 7可能表示事务的权重，这可能影响它获取锁的优先级。
MySQL 线程 ID：trx_mysql_thread_id: 9168表示与该事务关联的 MySQL 线程 ID。
插入操作：trx_query显示了一个INSERT语句，事务正在尝试插入数据到student表中。
锁定的行数：trx_rows_locked: trx_rows_modified: 5表明事务已经锁定了 5 行并修改了它们。
隔离级别：trx_isolation_level: REPEATABLE READ表示事务使用的是可重复读隔离级别，这可能导致更多的锁争用和死锁。
唯一性检查和外键约束：trx_unique_checks: 1和trx_foreign_key_checks: 1表示事务正在执行唯一性检查和外键约束检查，这可能会导致锁等待。
锁结构：trx_lock_structs: 2可能表示事务持有两个锁结构。
锁内存使用：trx_lock_memory_bytes: 1128显示了事务使用的锁内存字节数。
并发票据：trx_concurrency_tickets可能表示事务的并发票据数量，这可能影响其并发能力。
警告信息：日志末尾的1 row in set, 1 warning (0.04 sec)表明在执行过程中有一个警告，但没有提供警告的具体内容，这可能是导致死锁的一个线索。

二.分析步骤

1.表信息

CREATE TABLE `student` (`doc_id` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,`name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,`file_id` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',PRIMARY KEY (`doc_id`),UNIQUE KEY `doc_id_key` (`name`,`doc_id`),KEY `file_id_idx` (`file_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

2.分析过程

根据您提供的student表的定义，我们可以分析一些可能导致死锁的原因：

唯一性约束：UNIQUE KEY doc_id_key (name,doc_id) 表示name和doc_id的组合必须是唯一的。如果在插入操作中尝试插入一个已经存在的name和doc_id组合，将会导致违反唯一性约束，从而触发锁等待。
主键约束：PRIMARY KEY (doc_id) 表示doc_id是表的主键，必须是唯一的。如果尝试插入一个已经存在的doc_id，同样会触发锁等待。
索引：KEY file_id_idx (file_id) 是一个索引，虽然它不直接导致唯一性冲突，但如果其他事务持有该索引的锁，当前事务在尝试插入数据时可能需要等待。
并发插入：如果多个事务同时尝试插入具有相同name和不同doc_id的记录，它们可能会互相等待对方释放锁，从而导致死锁。
隔离级别：事务使用的是REPEATABLE READ隔离级别，这可能会导致更多的锁争用和死锁，因为在这个隔离级别下，事务会锁定读取到的所有行，以防止其他事务修改它们。
锁的粒度：InnoDB 存储引擎使用的是行级锁，但在某些情况下，如在插入大量数据时，可能会升级为表级锁，这增加了死锁的可能性。
事务大小：如果事务包含了大量的插入操作，可能会导致锁的竞争更加激烈，从而增加死锁的风险。

3.策略建议

为了解决死锁问题，可以考虑以下策略：

确保插入的数据不违反任何唯一性约束。
优化事务的大小，避免大事务导致的锁竞争。
考虑使用更低的隔离级别，如READ COMMITTED，以减少锁的持续时间。
使用更细粒度的锁，如乐观锁，来减少锁争用。
分析并优化索引，以减少索引导致的锁等待。
在设计表结构时，考虑使用更合理的字段和约束，以减少潜在的死锁风险。

4.死锁可能得原因

根据提供的student表结构和事务日志信息，以下是可能导致死锁的原因：

违反唯一性约束：事务尝试插入一个具有已存在的name和doc_id组合的记录，由于表中有一个唯一索引doc_id_key，这将导致违反唯一性约束，事务将等待其他事务释放锁。
锁升级：如果事务开始时只锁定了行级锁，但在执行过程中需要锁定更多的行或整个表，可能会发生锁升级。如果其他事务已经持有相关行或表的锁，当前事务将等待这些锁被释放。
锁的顺序：如果两个或多个事务按照不同的顺序请求锁，它们可能会互相等待对方持有的锁，从而导致死锁。例如，如果事务 A 首先锁定了doc_id为 X 的记录，然后尝试锁定name为 Y 的记录，而事务 B 首先锁定了name为 Y 的记录，然后尝试锁定doc_id为 X 的记录，就会发生死锁。
长事务：长时间运行的事务持有锁不放，可能导致其他事务长时间等待，增加了死锁的风险。
高并发：在高并发环境下，许多事务同时请求相同的资源，增加了死锁的可能性。
隔离级别：使用REPEATABLE READ隔离级别可能会导致更多的锁争用，因为在这个级别下，事务会锁定读取到的所有行，以防止其他事务修改它们。
锁的兼容性问题：如果事务请求的锁与其他事务持有的锁不兼容，也可能导致死锁。
外键约束：如果KnowledgeDoc表与其他表之间存在外键关系，违反外键约束也可能导致死锁。