数据库死锁

最新推荐文章于 2024-08-11 13:30:00 发布

okiwilldoit

最新推荐文章于 2024-08-11 13:30:00 发布

阅读量2k

点赞数 2

分类专栏：数据库学习

本文链接：https://blog.csdn.net/okiwilldoit/article/details/51851713

版权

数据库学习专栏收录该内容

33 篇文章 0 订阅

订阅专栏

最近做项目时，将原先单条插入更新数据库时改为批量插入更新。这样做的好处是降低了QPS(sql语句的数量)，但是同时也带来一个问题，DB的行锁急剧增加。

由于批量更新执行时间长，导致资源被长时间锁定，从而导致了大量的死锁产生，即出现以下错误信息：

Deadlock found when trying to get lock; try restarting transaction

借这个机会，研究一下数据库死锁的问题。

一. 什么是数据库死锁？

学过操作系统的人都知道，只有在并发的情况下，才会发生死锁。

下面的图可以形象地说明死锁的形成，四辆车在一个环形车道上行驶，如果没有外力作用，这4辆车将无法运行。

死锁发生在当多个进程访问同一数据库时，其中每个进程拥有的锁都是其他进程所需的，由此造成每个进程都无法继续下去。

简单的说，进程A等待进程B释放他的资源，B又等待A释放他的资源，这样就互相等待就形成死锁。

产生死锁的四个必要条件是：

1）互斥条件： 指进程对所分配到的资源进行排它性使用，即在一段时间内某资源只由一个进程占用。如果此时还有其它进程请求资源，则请求者只能等待，直至占有资源的进程用毕释放。

2）请求和保持条件： 指进程已经保持至少一个资源，但又提出了新的资源请求，而该资源已被其它进程占有，此时请求进程阻塞，但又对自己已获得的其它资源保持不放。

3）不剥夺条件： 指进程已获得的资源，在未使用完之前，不能被剥夺，只能在使用完时由自己释放。

4）环路等待条件： 指在发生死锁时，必然存在一个进程——资源的环形链，即进程集合{P0，P1，P2，···，Pn}中的P0正在等待一个P1占用的资源；P1正在等待P2占用的资源，……，Pn正在等待已被P0占用的资源。

这四个条件缺一不可。

二. 数据库死锁检测

我们mysql用的存储引擎是innodb，从打印的错误日志来看，innodb主动探知到死锁，并回滚了某一苦苦等待的事务。那么innodb是怎么探知死锁的？

直观方法是在两个事务相互等待时，当一个等待时间超过设置的某一阀值时，对其中一个事务进行回滚，另一个事务就能继续执行。这种方法简单有效，在innodb中，参数innodb_lock_wait_timeout用来设置超时时间。

仅用上述方法来检测死锁太过被动，innodb还提供了wait-for graph算法来主动进行死锁检测，每当加锁请求无法立即满足需要并进入等待时，wait-for graph算法都会被触发。

我们怎么知道上图中四辆车是死锁的？他们相互等待对方的资源，而且形成环路！我们将每辆车看为一个节点，当节点1需要等待节点2的资源时，就生成一条有向边指向节点2，最后形成一个有向图。 我们只要检测这个有向图是否出现环路即可，出现环路就是死锁！这就是wait-for graph算法。

innodb将各个事务看为一个个节点，资源就是各个事务占用的锁，当事务1需要等待事务2的锁时，就生成一条有向边从1指向2，最后行成一个有向图。

三.InnoDB锁原理

我们知道，innodb最大的贡献就是支持了事务和行锁，由于锁的粒度更细，所以能更好的支持并发。

InnoDB实现了以下两种类型的行锁：

共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。

排他锁（X)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。

意向锁是InnoDB自动加的，不需用户干预。这里不做过多分析。

对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及的数据集加排他锁（X)；

对于普通SELECT语句，InnoDB不会加任何锁；

但是select语句可以显式的加共享锁和排他锁。

·共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。

·排他锁（X)：SELECT * FROM table_name WHERE ... FOR UPDATE。

用SELECT ... IN SHARE MODE获得共享锁，主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行UPDATE或者DELETE操作。

但是如果当前事务也需要对该记录进行更新操作，则很有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用SELECT... FOR UPDATE方式获得排他锁。

在5.5中，information_schema 库中增加了三个关于锁的表：

innodb_trx ## 当前运行的所有事务

innodb_locks ## 当前出现的锁

innodb_lock_waits ## 锁等待的对应关系

innodb_locks记录了当前的锁的信息，其表的结构依次如下：

a) lock_id：锁的id以及被锁住的空间id编号、页数量、行数量

b) lock_trx_id：锁的事务id。

c) lock_mode：锁的模式。

d) lock_type：锁的类型，表锁还是行锁

e) lock_table：要加锁的表。

f) lock_index：锁的索引。

g) lock_space：innodb存储引擎表空间的id号码

h) lock_page：被锁住的页的数量，如果是表锁，则为null值。

i) lock_rec：被锁住的行的数量，如果表锁，则为null值。

j) lock_data：被锁住的行的主键值，如果表锁，则为null值。

锁与索引的关系

假设我们有一张消息表（msg），里面有3个字段。假设id是主键，token是非唯一索引，message没有索引。

CREATE TABLE msg (

id int,

token int,

message varchar(100),

primary key(id),

index(token)

)

innodb对于主键使用了聚簇索引，这是一种数据存储方式，表数据是和主键一起存储，主键索引的叶结点存储行数据。

对于普通索引，其叶子节点存储的是主键值。

插入几条数据：

insert into msg values (1,20,'abc1');

insert into msg values (2,21,'abc2');

insert into msg values (3,22,'abc3');

insert into msg values (4,23,'abc4');

insert into msg values (5,21,'abc2');

聚簇索引的存储结构如下所示：

id	token	message
1	20	abc1
2	21	abc2
3	22	abc3
4	23	abc4
5	21	abc2

因为token是普通索引，即二级索引，其存储结构如下，节点是主键值：

token	id
20	1
21	2
21	5
22	3
23	4

下面分析下索引和锁的关系。

1.delete from msg where id=2;

由于id是主键，因此直接锁住整行记录即可，会对该行加X锁。

2. delete from msg where token=21;

由于token是二级索引，因此首先锁住二级索引（两行），接着会锁住相应主键所对应的记录；

3. delete from msg where message='abc1';

message没有索引，所以走的是全表扫描过滤。这时表上的各个记录都将添加上X锁。

从上面的分析可以得出结论：

innodb的行级锁并不是直接锁记录，而是锁索引；

如果一条SQL语句用到了主键索引，mysql会锁住主键索引；

如果一条语句操作了非主键索引，mysql会先锁住非主键索引，再锁定主键索引。

四. 死锁成因

了解了innodb锁的基本原理后，下面分析下死锁的成因。如前面所说， 死锁一般是事务相互等待对方资源，最后形成环路造成的。下面简单讲下造成相互等待最后形成环路的例子。

一般情况只发生锁超时，就是一个进程需要访问数据库表或者字段的时候，另外一个程序正在执行带锁的访问（比如修改数据），那么这个进程就会等待，当等了很久锁还没有解除的话就会锁超时，报告一个系统错误，拒绝执行相应的SQL操作。

发生死锁的情况比较少，比如一个进程需要访问两个资源（数据库表或者字段），当获取一个资源的时候进程就对它执行锁定，然后等待下一个资源空闲，这时候如果另外一个进程也需要两个资源，而已经获得并锁定了第二个资源，那么就会死锁，因为当前进程锁定第一个资源等待第二个资源，而另外一个进程锁定了第二个资源等待第一个资源，两个进程都永远得不到满足。

在本次项目中造成死锁的原因： 相同表记录行锁冲突

这种情况比较常见，两个事务在执行数据批量更新时，事务A处理的的id列表为[1,2]，而事务B处理的id列表为[2,1]。

事务A执行id=1的更新，id=1行锁被锁定；同时事务B更新id=2，id=2行锁也被锁定。

这样事务1为了执行id=2的更新，必要要等待id=2的行锁释放；但是事务2还未结束，行锁还未被释放，所以就无法继续执行。

同样，事务2也无法执行id=1的更新。最终形成了如下图所示的环路等待：