Mysql-15-Mysql死锁

前言

• 在数据库中有两种基本的锁类型：排它锁（Exclusive Locks，即X锁）和共享锁（Share Locks，即S锁）。当数据对象被加上排它锁时，其他的事务不能对它读取和修改。加了共享锁的数据对象可以被其他事务读取，但不能修改。数据库利用这两种基本的锁类型来对数据库的事务进行并发控制。

• InnoDB引擎提供了行级锁，表锁。MyISAM提供了表锁，如题，MySQL会发生死锁吗？

1. 案例一

• 在InnoDB引擎下，RR(REPEATABLE-READ)级别，如果多个事务争抢同一个资源，会发生死锁。在RR级别下，MySQL提供了next-key lock。假如一个索引的行有10,11,13,20
• 那么可能的next-key lock的包括

(无穷小, 10]
(10,11]
(11,13]
(13,20]
(20, 无穷大)

• 即：当你查询12时，如果数据未查到，那么将对(12,13]范围内的数据进行锁定。next-key lock的定义可以到官方具体查看，这里做个演示。

//查看隔离级别，
show variables like '%tx_isolation%';
// 设置隔离界别
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}
// 不设置自动提交
SET autocommit = 0;

演示

1. 首先将隔离级别都设置为RR级别的，并且不让事务自动提交
2. 根据上面的数据，在事务1中查询

// 得到空结果集，此时锁定的范围是(33,100]
select * FROM user where id=33 for update;

3. 在事务2中也进行查询

// 查询到空的结果，在事务2中锁定的范围是(34,100]
select * FROM user where id=34 for update

4. 在事务1中插入数据

// 虽然事务1锁定了范围，事务2也锁定了范围
insert into user values(35,'ac',10);

5. 在事务2中也插入数据

insert into user values(34,'ac',10)

解决方案

1. 设置死锁的超时时长

innodb_lock_wait_timeout=500

2. 查询到当前正在锁定的事务线程，将其杀死

// 可以看到正在运行的事务线程，还有运行状态
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

//trx_mysql_thread_id为上一条命令获取的结果，将具体的数字替换一下即可。
kill trx_mysql_thread_id

2. 案例二

• 索引不当导致全表扫描

1. 如果在事务中执行了一条不满足条件的语句，执行全表扫描，把行级锁上升为表级锁，多个这样的事务执行后，就很容易产生死锁和阻塞。
2. 类似的情况还有当表中的数据量非常庞大而索引建的过少或不合适的时候，使得经常发生全表扫描，最终应用系统会越来越慢，最终发生阻塞或死锁。

解决方案

• SQL语句中不要使用太复杂的关联多表的查询；
• 使用“执行计划”对SQL语句进行分析，对于有全表扫描的SQL语句，建立相应的索引进行优化。

打赏