MySQL Innodb表导致死锁日志情况分析与归纳

一. 什么时候使用表锁

  对于INNODB表,在绝大部分情况下都应该使用行锁。在个别特殊事务中,可以考虑使用表锁(建议)。
  1. 事务需要更新大部份或全部数据,表又比较大,默认的行锁不仅使这个事务执行效率低,可能造成其他事务长时间锁等待和锁冲突,这种情况考虑使用表锁来提高事务的执行速度(具我在sql server中的经历,该大表有上100w,删除40w,表锁有时会造成长时间未执行完成. 还是使用分批来执行好)。
  2. 事务涉及多个表,比较复杂,很可能引起死锁,造成大量事务回滚。这种情况可以考虑一次性锁定事务涉及的表,避免死锁,减少数据库因事务回滚带来的开销。
  使用表锁注意两点
    (1) lock tables虽然可以给innodb加表锁,但表锁不是由innodb存储引擎层管理,则是由上层mysql server负责。仅当autocommit=0, innodb_table_locks=1(默认设置)时,innodb层才知道mysql加的表锁,mysql server也才能感知innodb加的行锁。
    (2) 用lock tables对innodb表加锁时要注意, 要将autocommit 设置为0,否则mysql 不会给表加锁; 事务结束前,不要用unlock tables释放表锁,因为它会隐式的提交事务。 commit 或rollback 并不能释放用lock tables 加的表锁。必须用unlock tables释放表锁。

    下面在5.7版本数据库中,会话2也会阻塞,按上面说法是不会阻塞的,因为会话1没有设置SET autocommit =0(以后在论证)

-- 会话1 给city加表锁读,  不设置  SET autocommit =0
  LOCK TABLES city READ

  --  会话2 会阻塞
 UPDATE city SET CityCode='005' WHERE city_id=103  

  -- 会话1提交
 COMMIT;
 -- 会话1 释放表锁
 UNLOCK TABLES;

太阳集团所有网址16877 1

二. 关于死锁

  在myisam中是使用的表锁,在获得所需的全部锁时, 要么全部满足,要么等待,因此不会出现死锁。下面在innodb中演示一个死锁例子:

会话1

会话2

SET autocommit =0

SELECT * FROM city  WHERE city_id=103 FOR UPDATE;

SET autocommit =0

SELECT * FROM cityNew  WHERE city_id=103 FOR UPDATE;

-- 因为会话2 已获得排他锁, 该语句等待

 SELECT * FROM cityNew  WHERE city_id=103 FOR UPDATE;

 

 

-- 死锁

 SELECT * FROM city  WHERE city_id=103 FOR UPDATE;

错误代码: 1213

Deadlock found when trying to get lock; try restarting transaction

  上面案例中, 两个事务都需要获得对方持有的排他锁才能继续完成事务,这种循环锁等待就是典型的死锁。 发生死锁后,innodb会自动检测到,并使一个事务释放锁并回退(回滚),另一个事务得锁完成事务。

案例描述 在定时脚本运行过程中,发现当备份表格的sql语句与删除该表部分数据的sql语句同时运行时,mysql会检测出死锁,并打印出日志。
两个sql语句如下: (1)insert into backup_table select * from source_table
(2)DELETE FROM source_table WHERE Id>5 AND titleWeight<32768 AND joinTime<'$daysago_1week'
teamUser表的表结构如下:
PRIMARY KEY (`uid`,`Id`),
KEY `k_id_titleWeight_score` (`Id`,`titleWeight`,`score`),
ENGINE=InnoDB
两语句对source_table表的使用情况如下:

三. 锁等待查看    

  涉及外部锁或表锁,innodb并不能完全自动检测到死锁,这需要设置锁等待超时参数innodb_lock_wait_timeout来解决(设置需慎重),这个参数并不是只用来解决死锁问题,在并发下,大量事务无法立即获得所需锁而挂起,将占用大量资源,甚至拖跨数据库 (在sql server中默认是-1 总是等待)。

--  下面是5秒  获取不到锁就超时
SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout';

太阳集团所有网址16877 2

太阳集团所有网址16877 3

死锁日志打印出的时间点表明,语句(1)运行过程中,当语句(2)开始运行时,发生了死锁。
当mysql检测出死锁时,除了查看mysql的日志,还可以通过show InnoDB STATUS G语句在mysql客户端中查看最近一次的死锁记录。由于打印出来的语句会很乱,所以,最好先使用pager less命令,通过文件内容浏览方式查看结果,会更清晰。(以nopager结束)
得到的死锁记录如下:

太阳集团所有网址16877 4

太阳集团所有网址16877 5
根据死锁记录的结果,可以看出确实是这两个语句发生了死锁,且锁冲突发生在主键索引上。那么,为什么两个sql语句会存在锁冲突呢?冲突为什么会在主键索引上呢?语句(2)得到了主键索引锁,为什么还会再次申请锁呢?
锁冲突分析
2.1 innodb的事务与行锁机制
MySQL的事务支持不是绑定在MySQL服务器本身,而是与存储引擎相关,MyISAM不支持事务、采用的是表级锁,而InnoDB支持ACID事务、 行级锁、并发。MySQL默认的行为是在每条SQL语句执行后执行一个COMMIT语句,从而有效的将每条语句作为一个单独的事务来处理。
2.2 两语句加锁情况 在innodb默认的事务隔离级别下,普通的SELECT是不需要加行锁的,但LOCK IN SHARE MODE、FOR UPDATE及高串行化级别中的SELECT都要加锁。有一个例外,此案例中,语句(1)insert into teamUser_20110121 select * from teamUser会对表teamUser_20110121(ENGINE= MyISAM)加表锁,并对teamUser表所有行的主键索引(即聚簇索引)加共享锁。默认对其使用主键索引。
而语句(2)DELETE FROM teamUser WHERE teamId=$teamId AND titleWeight<32768 AND joinTime<'$daysago_1week'为删除操作,会对选中行的主键索引加排他锁。由于此语句还使用了非聚簇索引KEY `k_teamid_titleWeight_score` (`teamId`,`titleWeight`,`score`)的前缀索引,于是,还会对相关行的此非聚簇索引加排他锁。
2.3 锁冲突的产生 由于共享锁与排他锁是互斥的,当一方拥有了某行记录的排他锁后,另一方就不能其拥有共享锁,同样,一方拥有了其共享锁后,另一方也无法得到其排他锁。所 以,当语句(1)、(2)同时运行时,相当于两个事务会同时申请某相同记录行的锁资源,于是会产生锁冲突。由于两个事务都会申请主键索引,锁冲突只会发生 在主键索引上。
常常看到一句话:在InnoDB中,除单个SQL组成的事务外,锁是逐步获得的。那就说明,单个SQL组成的事务锁是一次获得的。而此案例中,语句(2) 已经得到了主键索引的排他锁,为什么还会申请主键索引的排他锁呢?同理,语句(1)已经获得了主键索引的共享锁,为什么还会申请主键索引的共享锁呢?
死锁记录中,事务一等待锁的page no与事务二持有锁的page no相同,均为218436,这又代表什么呢?
我们的猜想是,innodb存储引擎中获得行锁是逐行获得的,并不是一次获得的。下面来证明。
死锁产生过程分析 要想知道innodb加锁的过程,唯一的方式就是运行mysql的debug版本,从gdb的输出中找到结果。根据gdb的结果得到,单个SQL组成的事 务,从宏观上来看,锁是在这个语句上一次获得的,但从底层实现上来看,是逐个记录行查询,得到符合条件的记录即对该行记录的索引加锁。
Gdb结果演示如下:

复制代码 代码如下:

(gdb) b lock_rec_lock
 Breakpoint 1 at 0×867120: file lock/lock0lock.c, line 2070.
 (gdb) c
 Continuing.
 [Switching to Thread 1168550240 (LWP 5540)]
 Breakpoint 1, lock_rec_lock (impl=0, mode=5, rec=0x2aedbe01c1 “789200″, index=0x2aada734b8, thr=0x2aada74c18) at lock/lock0lock.c:2070
 2070 {
 Current language: auto; currently c
 (gdb) c
 Continuing.
 Breakpoint 1, lock_rec_lock (impl=0, mode=1029, rec=0x2aedbc80ba “200″, index=0x2aada730b8, thr=0x2aada74c18) at lock/lock0lock.c:2070
 2070 {
 (gdb) c
 Continuing.
 Breakpoint 1, lock_rec_lock (impl=0, mode=5, rec=0x2aedbe01cf “789200″, index=0x2aada734b8, thr=0x2aada74c18) at lock/lock0lock.c:2070
 2070 {
 (gdb) c
 Continuing.

(说明:”789200″为非聚簇索引,”200″为主键索引)

Gdb结果显示,语句(1)(2)加锁的获取记录为多行,即逐行获得锁,这样就解释了语句(2)获得了主键索引锁还再次申请主键索引锁的情况。
由于语句(1)使用了主键索引,而语句(2)使用了非聚簇索引,两个事务获得记录行的顺序不同,而加锁的过程是边查边加、逐行获得,于是,就会出现如下情况:

太阳集团所有网址16877 6

于是,两个事务分别拥有部分锁并等待被对方持有的锁,出现这种资源循环等待的情况,即死锁。此案例中被检测时候的锁冲突就发现在page no为218436和218103的锁上。
InnoDB 会自动检测一个事务的死锁并回滚一个或多个事务来防止死锁。Innodb会选择代价比较小的事务回滚,此次事务(1)解锁并回滚,语句(2)继续运行直至事务结束。
innodb死锁形式归纳 死锁产生的四要素:互斥条件:一个资源每次只能被一个进程使用;请求与保持条件:一个进程因请求资源而阻塞时,对已获得的资源保持不放;不剥夺条件:进程 已获得的资源,在末使用完之前,不能强行剥夺;循环等待条件:若干进程之间形成一种头尾相接的循环等待资源关系。
Innodb检测死锁有两种情况,一种是满足循环等待条件,还有另一种策略:锁结构超过mysql配置中设置的最大数量或锁的遍历深度超过设置的最大深度 时,innodb也会判断为死锁(这是提高性能方面的考虑,避免事务一次占用太多的资源)。这里,我们只考虑满足死锁四要素的情况。
太阳集团所有网址16877,死锁的形式是多样的,但分析到innodb加锁情况的最底层,因循环等待条件而产生的死锁只有可能是四种形式:两张表两行记录交叉申请互斥锁、同一张表则存在主键索引锁冲突、主键索引锁与非聚簇索引锁冲突、锁升级导致的锁等待队列阻塞。
以下首先介绍innodb聚簇索引与非聚簇索引的数据存储形式,再以事例的方式解释这四种死锁情况。
4.1聚簇索引与非聚簇索引介绍 聚簇索引即主键索引,是一种对磁盘上实际数据重新组织以按指定的一个或多个列的值排序,聚簇索引的索引页面指针指向数据页面。非聚簇索引(即第二主键索 引)不重新组织表中的数据,索引顺序与数据物理排列顺序无关。索引通常是通过B-Tree数据结构来描述,那么,聚簇索引的叶节点就是数据节点,而非聚簇 索引的叶节点仍然是索引节点,通常是一个指针指向对应的数据块。
而innodb在非聚簇索引叶子节点包含了主键值作为指针。(这样是为了减少在移动行或数据分页时索引的维护工作。)其结构图如下:
太阳集团所有网址16877 7

本文由太阳集团所有网址16877发布于太阳集团城网址送彩金,转载请注明出处:MySQL Innodb表导致死锁日志情况分析与归纳

您可能还会对下面的文章感兴趣: