掌握数据库死锁的成因与解决策略:一篇文章带你看懂

发表时间: 2022-11-21 15:16

一、什么是死锁

加锁(Locking)是数据库在并发访问时保证数据一致性和完整性的主要机制。任何事务都需要获得相应对象上的锁才能访问数据,读取数据的事务通常只需要获得读锁(共享锁),修改数据的事务需要获得写锁(排他锁)。当两个事务互相之间需要等待对方释放获得的资源时,如果系统不进行干预则会一直等待下去,也就是进入了死锁(deadlock)状态。

二、死锁产生的场景

1.数据库表准备

1.1. 创建数据库表

#创建数据库create database chuanzhi;#选择数据库use chuanzhi;#创建测试表CREATE TABLE `tb_heima` (  `id` INT NOT NULL,  `username` VARCHAR(45) NULL,  PRIMARY KEY (`id`));#插入测试数据INSERT INTO `tb_heima` (`id`, `username`) VALUES ('1', '传智教育-教育行业A股IPO第一股');

2.死锁场景演示

2.1. 打开一个dos窗口

  • mysql -u账号 -p密码,进入mysql命令行输入:

  • 开启一个事务,mysql> begin;

  • 执行更新语句,mysql> update tb_heima set username = 'heima' where id =1;

注意:此时更新是成功的,但并未提交事务。

2.2.再打开一个dos窗口

  • 登录mysql,开启另一个事务操作同上。
  • 再次执行更新语句,mysql> update tb_heima set username = 'heima' where id =1;

注意:此时第二个窗口处于等待中,需要等待第一个窗口释放写锁才能执行成功。

  • 等待一段时候后,第二个窗口报错了

注意:由于第一个窗口未提交事务,导致第二个窗口在等待一段时候后,超过锁定等待超时。

三、解决死锁问题

通过mysql客户端工具连接,按照以下步骤执行。

  • select * from information_schema.innodb_lock_waits; #锁等待的信息,可以看到堵塞和被堵塞者

注意:超过锁定等待超时后,此条记录会消失,也就是说正在阻塞的记录在此表才能查看到。

  • select * from information_schema.innodb_trx; # 查询锁事务状态信息

注意:查询出死锁后,得到trx_mysql_thread_id死锁的id。

  • kill sessionId; # 杀掉有问题的session
kill 15;kill 16;
  • 再次执行select * from information_schema.innodb_trx; # 查询锁事务状态信息

注意:确认此表是否存在锁事务记录,如果没有则说明死锁问题已经解决。

四、如何避免死锁

  1. 对于数据库的多表操作时,尽量按照相同的顺序进行处理,尽量避免同时锁定两个资源,如操作A和B两张表时,总是按先A后B的顺序处理, 必须同时锁定两个资源时,要保证在任何时刻都应该按照相同的顺序来锁定资源。
  2. 所有的update和delete操作必须走唯一索引
  3. SQL语句中不要使用太复杂的关联多表的查询;使用“执行计划”对SQL语句进行分析,对于有全表扫描的SQL语句,建立相应的索引进行优化。
  4. 把SELECT放在Update语句前
  5. 避免事务中的用户等待交互