今天碰到一次因死锁导致更新操作的sql事务执行时间过长，特将排查过程记录如下：
首先该sql事务的where条件已经命中了主键索引，而且表也不大，故可以排除扫表过慢原因。通过 show processlist;发现也只有该sql事务在操作这个表，初看起来似乎也不像是死锁的原因：
但通过咨询yellbehuang后发现，判断sql事务是否死锁不能简单通过show processlist来判断，而是要通过查询innodb锁的相关表来确定，和innodb锁有关的主要有三个表，
上面表的各个字段的含义如下：
可以通过select * from INNODB_LOCKS a inner join INNODB_TRX b on a.lock_trx_id=b.trx_id and trx_mysql_thread_id=线程id 来获取该sql的锁状态，线程id可以通过上面的show processlist来获得，执行结果如下：
此时发现，该sql连接确实处于LOCK WAIT锁等待状态
通过select * from innodb_lock_waits where requesting_trx_id=75CB26E5(即上面查询得到的lock_trx_id)可以得到当前拥有锁的事务ID 75CB26AE。
再通过select * from innodb_trx where lock_trx_id=75CB26AE获取sql语句与线程id
从上面的结果中看出，该事务处于running状态，但sql却为null，该线程id即对于上面show processlist的206机器的30764端口的连接，该连接处于sleep状态。为什么sql为null却依然占有锁?在查询相关资料和咨询jameszhou后，知道了这个实际和innodb 引擎的写机制有关，innodb执行写事务操作时，实际是先取得索引中该行的行锁(即使该表上没有任何索引，那么innodb会在后台创建一个隐藏的聚集主键索引)，再在缓存里写入，最后事务commit后正式写入DB中并释放锁。之所以sql为null，是因为该连接已经把sql update操作执行写入缓存中了，但是由于代码bug没有最后commit，导致一直占用着行锁，后续新的连接想写这一行数据却因为一直取不到行锁而处于长时间的等待状态。
那为什么innodb需要两次写?下面是我查询相关资料得出来的结论:
因为innodb中的日志是逻辑的，所谓逻辑就是比如当插入一条记录时，它可能会导致在某一个页面(这条记录最终被插入的位置)的多个偏移位置写入某个长度的值，比如页头的记录数，槽数，页尾槽数据，页中的记录值等等，这些本是一些物理操作，而innodb为了节约日志量及其它一些原因，设计为逻辑处理的方式，那就是它会在一个页面的基础上，把一条记录插入，那么在日志记录中记录的内容为表空间号、页面号、记录的各个列的值等等，在内部转换为上面的物理操作。
但这里的一个问题是，如果那个页面本身是错误的，这种错误有可能是因为写断裂(1个页面为16K，分多次写入，后面的有可能没有写成功，导致这个页面不完整)引起的，那么这个逻辑操作就没办法完成了，因为它的前提是这个页面还是正确的，完整的，因为如果这个页面不正确的话，这个页面里的数据是无效的，有可能产生各种不可预料的问题。
那么正是因为这个问题，所以必须要首先保证这个页面是正确的，方法就是两次写，它的思想最终是一种备份思想，也就是一种镜像。
innodb两次写的过程：
可以将两次写看作是在Innodb表空间内部分配的一个短期的日志文件，这一日志文件包含100个数据页。Innodb在写出缓冲区中的数据页时采用的是一次写多个页的方式，这样多个页就可以先顺序写入到两次写缓冲区并调用fsync()保证这些数据被写出到磁盘，然后数据页才被定出到它们实际的存储位置并再次调用fsync()。故障恢复时Innodb检查doublewrite缓冲区与数据页原存储位置的内容，若数据页在两次写缓冲区中处于不一致状态将被简单的丢弃，若在原存储位置中不一致则从两次写缓冲区中还原。
原文链接：
作者：陈文啸
戳这里，看该作者更多好文
活锁和死锁是怎么回事？
一、活锁如果事务T1封锁了数据R，事务T2又请求封锁R，于是T2等待。 T3也请求封锁R，当T1释放了R上的封锁之后系统首先批准了T3的请求，T2仍然等待。 然后T4又请求封锁R，当T3释放了R上的封锁之后系统又批准了T4的请求，...，T2有可能永远等待，这就是活锁的情形，如图8.4(a)所示。 避免活锁的简单方法是采用先来先服务的策略。 二、死锁如果事务T1封锁了数据R1，T2封锁了数据R2，然后T1又请求封锁R2，因T2已封锁了R2，于是T1等待T2释放R2上的锁。 接着T2又申请封锁R1，因T1已封锁了R1，T2也只能等待T1释放R1上的锁。 这样就出现了T1在等待T2，而T2又在等待T1的局面，T1和T2两个事务永远不能结束，形成死锁。 1. 死锁的预防在数据库中，产生死锁的原因是两个或多个事务都已封锁了一些数据对象，然后又都请求对已为其他事务封锁的数据对象加锁，从而出现死等待。 防止死锁的发生其实就是要破坏产生死锁的条件。 预防死锁通常有两种方法：① 一次封锁法一次封锁法要求每个事务必须一次将所有要使用的数据全部加锁，否则就不能继续执行。 一次封锁法虽然可以有效地防止死锁的发生，但也存在问题，一次就将以后要用到的全部数据加锁，势必扩大了封锁的范围，从而降低了系统的并发度。 ② 顺序封锁法顺序封锁法是预先对数据对象规定一个封锁顺序，所有事务都按这个顺序实行封锁。 顺序封锁法可以有效地防止死锁，但也同样存在问题。 事务的封锁请求可以随着事务的执行而动态地决定，很难事先确定每一个事务要封锁哪些对象，因此也就很难按规定的顺序去施加封锁。 可见，在操作系统中广为采用的预防死锁的策略并不很适合数据库的特点，因此DBMS在解决死锁的问题上普遍采用的是诊断并解除死锁的方法。 2. 死锁的诊断与解除① 超时法如果一个事务的等待时间超过了规定的时限，就认为发生了死锁。 超时法实现简单，但其不足也很明显。 一是有可能误判死锁，事务因为其他原因使等待时间超过时限，系统会误认为发生了死锁。 二是时限若设置得太长，死锁发生后不能及时发现。 ② 等待图法事务等待图是一个有向图G=(T,U)。 T为结点的集合，每个结点表示正运行的事务；U为边的集合，每条边表示事务等待的情况。 若T1等待T2,则T1、T2之间划一条有向边，从T1指向T2。 事务等待图动态地反映了所有事务的等待情况。 并发控制子系统周期性地（比如每隔1分钟）检测事务等待图，如果发现图中存在回路，则表示系统中出现了死锁。 DBMS的并发控制子系统一旦检测到系统中存在死锁，就要设法解除。 通常采用的方法是选择一个处理死锁代价最小的事务，将其撤消，释放此事务持有的所有的锁，使其它事务得以继续运行下去。 当然，对撤消的事务所执行的数据修改操作必须加以恢复。
进程同步的死锁和饿死的区别是什么
产生死锁的原因：一是系统提供的资源数量有限，不能满足每个进程的使用；二是多道程序运行时，进程推进顺序不合理。 产生死锁的必要条件是：1、互斥条件；2、不可剥夺条件（不可抢占）；3、部分分配；4、循环等待。 根据产生死锁的四个必要条件，只要使其中之一不能成立，死锁就不会出现。 为此，可以采取下列三种预防措施：1、采用资源静态分配策略，破坏部分分配条件；2、允许进程剥夺使用其他进程占有的资源，从而破坏不可剥夺条件；3、采用资源有序分配法，破坏环路条件。 死锁的避免不严格地限制死锁的必要条件的存在，而是系统在系统运行过程中小心地避免死锁的最终发生。 最著名的死锁避免算法是银行家算法。 死锁避免算法需要很大的系统开销。 解决死锁的另一条途径是死锁检测方法，这种方法对资源的分配不加限制，即允许死锁的发生。 但系统定时地运行一个死锁检测程序，判断系统是否已发生死锁，若检测到死锁发生则设法加以解除。 解除死锁常常采用下面两种方法：1、资源剥夺法；2、撤消进程法
SQL 中出现死锁现象，如何判断死锁的原因以及进程
ORACLE可以用这个语句来判断select username, sid, object_owner, object, , _text SQLfrom v$sqltext a, v$session b, v$access cwhere =_address and _value=_hash_value and = and IS NOT NULL
发表评论