还剩5页未读,继续阅读
文本内容:
漫谈死锁--前后每个MySQL DBA和开发大概率都会遇到死锁问题•本文是自己对死锁相关知识总结,介绍死锁是什么•MySQL如何检测死锁/处理死锁•死锁的案例,以及如何避免死锁
二、死锁死锁是并发系统中常□的问题•同样也会出现在数据库系统的并发读写请求场景中当两个及以上的事务•双方都在等待对方释放已经持有的锁或因为加锁顺序不一致造成循环等待锁资源,就会出现“死锁”举例来说A事务持有X1锁,申请X2锁B事务持有X2锁•申请X1锁A和B事务持有锁并且申请对方持有的锁进入循环等待,就造成死锁从死锁的定义来看,MySQL出现死锁的几个要素:a.两个或者两个以上事务b.每个事务都已经持有锁并且申请新的锁c.锁资源同时只能被同一个事务持有或者不兼容.d.事务之间因为持有锁和申请锁导致彼此循环等待三,MySQL的处理死锁机制死锁机制包含两部分:检测和处理把事务等待列表和锁等待信息列表通过事务信息进行wait-for graph检测,如果发现有闭环•则回滚undo log量少的事务;死锁检测本身也会算检测本身所需要的成本•以便应对检测超时导致的意外情况
3.1死锁检测当InnoDB事务尝试获取(请求)加一个锁,并且需要等待时,InnoDB会进行死锁检测.正常的流程如下.InnoDB初始化一个事务,当事务尝试申请加锁,并且需要等待时(waitjock)-innodb会开始进行死锁检测(deadlock_mark).进入至I」lock_deadlock_check_and_resolve()函数进行检测死锁和解决死锁.检测死锁过程中,是由计数器来进行限制次数的•在等待wait・for graph检测过程中遇到超时或者超过阈值,则停止检测..死锁检测的逻辑之一是等待图的处理过程•如果通过锁的信息和事务等待链构造出一个图,如果图中出现回路,就认为发生了死锁..死锁的回滚,内部代码的处理逻辑之一是比较undo的数量,回滚undo数量少的事务
3.2如何处理死锁《数据库系统实现》里面提到的死锁处理.超时死锁检测:当存在死锁时,想所有事务都能同时继续执行通常是不可能的,因此,至少一个事务必须中止并重新开始超时是最直接的办法,对超出活跃时间的事务进行限制和回滚..等待图等待图的实现•是可以表明哪些事务在等待其他事务持有的锁,可以在数据库的死锁检测里面加上这个机制来进行检测是否有环的形成..通过元素排序预防死锁:这个想法很美好,但现实很残酷,通常都是发现死锁后才去想办法解决死锁的原因-.通过时间戳检测死锁:对每个事务都分配一个时间戳,根据时间戳来进行回滚策略四.Innodb的锁类型首先我们要知道对于MySQL有两种常规锁模式♦LOCK_S(读锁,共享锁)♦LOCK_X(写锁•排它锁)最容易理解的锁模式,读加共享锁(in sharemode)•写加排它锁.其次对于唯一性检测堵塞来讲一般是LOCK_S.有如下几种锁的属性LOCK_REC_NOT_GAP(记录本身加锁)LOCK_GAP(本记录和上一条记录之间的间隙,LOCK_GAP和LOCK_GAP是兼容的)LOCK_ORDINARY(同时锁记录和GAP•也即Next Key锁)LOCKJNSERTJNTENTION(插入意向锁,其实是特殊的GAP锁•用于堵塞Insert操作)锁的属性可以与锁模式任意组合例如:lock-type_mode可以是Lock_X或者Lock_S locksgap beforerec表示为gap锁lock-type_modeLOCK GAPlocks recbut notgap表示为记录锁,非gap锁:lock-type_modeLOCK_REC_NOT_GAP insertintention表示为插入意向锁lock-type.modeLOCK INSERTJNTENTION waiting表示锁等待:lock-type_modeLOCK_WAIT关于Innodb锁的详细介绍可以移步官方文档或者MySQL•引擎特性•InnoDB事务锁系统简介五.锁信息解析下面是一个典型的唯一键堵塞输出LOCK WAIT2lock structs,heap size1136,1row lock⑸,undo logentries1MySQL threadid16253,OS threadhandle139825964828416,query id75730localhost rootupd insertinto testunqlvalues2;gaop11-----------TRX HASBEEN WAITING7SEC FORTHIS LOCKTO BEGRANTED:RECORD LOCKSspace id65page no3n bits72index PRIMARYof tabletxc\testunql trx id Recordlock,heap no3PHYSICAL RECORD:n fields4;compact format;info bits00:len4;hex80000002;asc;;1:len6;hex000000002d44;asc-D;;2:len7;hex c1000003450110;asc E;;3:len5;hex676167031;asc gaop1;;我们下面来解析一下部分可能不太好理解的部分•以便大家以后能够更清楚的理解它的含义:♦infimumfiisu premum-个page中包含这两个伪记录口中所有的行未删除或删除未purge的行逻辑上都连接到这两个虚拟列之间,表现为一个逻辑链表数据结构•其中supremum伪记录的锁始终为next_key_lock♦LOCK WAIT2lock structs这是LOCK的内存结构体源码中用lock」表示其可以包含lock_table_t tabjock;/*/table lock*/lock_rec_t rec_lock;/*/record lockV-般来说Innodb上锁都会对表级加上IX,这占用一个结构体然后分别对相关的记录进行加锁•每一个BLOCK会占用这样一个结构体.♦1row locks这个信息描述了当前事务加锁的行数,他是所有lock struct结构体中排除table lock以外所有加锁记录的总和-♦undo logentries1大约等于已经修改的记录数•每修改一行都会占用一个undo logentries n♦bits72和这个page相关的锁位图的大小,每一行记录都有1bit的位图信息与其对应,用来表示是否加锁•并且始终预留64bit例如我的表有9条数据•同时包含infimum和isupremum虚拟记录即64+9+2bits,即75bits但是必须被8整除向上取整为一个字节,结果也就是就是80bits注意不管是否加锁每行都会对应一个bit的位图.♦space id65page no3物理块所在位置.♦heap no3heap no存储在fixed_extrasize中heap no为物理存储填充的序号,□的空闲空间挂载在page free链表中(头插法)可以重用,但是重用ittheap no不变,如果一直是insert贝Uheap no不断增加,并不是按照ROWID(主键)排序的逻辑链表顺序,而是物理填充顺序♦lockmodeSlocks对应前面的LOCK_S♦locks recbut notgap waiting对应前面的LOCK_REC_NOT_GAP,并且处于堵塞[犬态-♦逐步加锁如果细心的朋友应该会发现在show engine中事务信息中的row lock在对大量行进行加锁的时候会不断的增加,因为加行锁最终会调用lock」ec_lock逐行加锁•这也会增加了大数据量加锁的触发死锁的可能性.六.Innodb不同事务加锁类型例子update tabset x=1where id=1;.索引列是主键,RC隔离级另i」,对记录记录加X锁.索引列是二级唯一索引•RC隔离级别.若id列是unique列•其上有unique索引那么SQL需要加两个X锁,一个对应于id unique索引上的id=10的记录,另一把锁对应于聚簇索引上的[name=d,id=10]的记录..索引列是二级非唯一索引,RC隔离级别若id列上有非唯一索引,那么对应的所有满足SQL查询条件的记录,都会被加锁同时,这些记录在主键索引上的记录,也会被加锁.索引列上没有索引•RC隔离级别若id列上没有索引•SQL会走聚簇索引的全扫描进行过滤•由于过滤是由MySQL Server层面进行的因此每条记录•无论是否满足条件•都会被加上X锁但是,为了效率考量,MySQL做了优化,对于不满足条件的记录,会在判断后放锁,最终持有的,是满足条件的记录上的锁,但是不满足条件的记录上的加锁/放锁动作不会省略同时,优化也违背了2PL的约束.♦索引列是主键,RR隔离级别对记录记录加X锁♦索引列是二级唯一索引,RR隔离级别对表加上两个X锁•唯一索引满足条件的记录上一个,对应的聚簇索引上的记录一个♦索引列是二级非唯一索引,RR隔离级别结论Repeatable Read隔离级另|」下•id列上有一个非唯一索引•对应SQL:deletef「omtl whereid=10;首先•通过id索引定位到第一条满足查询条件的记录,加记录上的X锁・加GAP上的GAP锁,然后加主键聚簇索引上的记录X锁«然后返回;然后读取下一条,重复进行,直至进行到第一条不满足条件的记录[11,f],此时•不需要加记录X锁,但是仍旧需要加GAP锁•最后返回结束-♦索引列上没有索引•RR隔离级别则锁全表这里需要重点说明insert和delete的加锁方式,因为目前遇到的大部分案例或者部分难以分析的案例都是和delete,insert操作有关insert的加锁方式insert的流程(有唯一索引的情况)比如insert N.找到大于N的第一条记录M,以及前一条记录P.如果M上面没有gap/next・key lock,进入第三步骤,否则等待(对其next-rec加insert intensionlock.由于有gap锁•所以等待).检查P:判断P是否等于N:如果不等则完成插入(结束)如果相等再判断P是否有锁,a如果没有锁:报1062错误(duplicate key),说明该记录已经存在,报重复值错误b加S-lock,说明该记录被标记为删除,事务已经提交,还没来得及purge c如果有锁则加S-lock,说明该记录被标记为删除,事务还未提交.delete的加锁方式♦在非唯一索引的情况下,删除一条存在的记录是有gap锁,锁住记录本身和记录两边的gap♦在唯一索引和主键的情况下删除一条存在的记录•因为都是唯一值•进行删除的时候,是不会有gap存在♦节唯一索引•唯一索引和主键在删除一条不存在的记录•均会在这个区间加gap锁♦通过会唯一索引和唯一索引去删除一条标记为删除的记录的时候,都会请求该记录的行锁•同时锁住记录之前的gap♦RC情况下是没有gap锁的,除了遇到唯一键冲突的情况•如插入唯一键冲突七.如何查看死锁.查看事务锁等待状态情况select*from information_schema.innodb_locks;select*from information_schema.innodb_lock_waits;select*frominformation_schema.innodb_trx;下面的查询可以得到当前状况下数据库的等待情况select r.trxjd wait_trx_id,r.trx_mysql_thread_id wait_thr_id,r.trx_query wait_query,b.trxjd block_trx_id,b.trx_mysql_thread_id block_thrd_id,b.trx_query block_query frominformation_schema.innodbjock_waits winner ioininformation schema.innodb trxb onb.trxid=w.blocking trxid innerjoin information_schema.innodb_trx ron r.trxjd=w.requesting_trx_id.打开下列参数•获取更详细的事务和死锁信息..innodb_print_all_deadlocks=ON innodb_status_output_locks=ON.查看innodb状态(包含最近的死锁曰志)show engineinnodb status;A一如何尽可能避免死锁.事务隔离级另使用readcommitted和binlog_format=row•避免RR模式带来的gap锁竞争„.合理的设计索弓I,区分度高的列放到组合索引前列,使业务sql尽可能通过索引定位更少的行•减少锁竞争..调整业务逻辑SQL执行顺序,避免update/delete□时•间持有锁的SQL在事务前面,(该优化视情况而定)在第12节中我们也分析过如果通过binlog寻找□期不提交的事务.选择合理的事务大小,小事务发生锁冲突的几率也更小.
5.
7.15版本之后提供了新的功能innodb_deadlock detect参数z可以关闭死锁检测,提高并发TPS,但是要注意设置锁等待时间innodb_lock_wait」imeout。