Mysql和Oracle数据库死锁查看以及解决

一、Mysql数据库死锁排查

1.1 锁事务查询

1.1.1  查看正在锁的事务

SQL :

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

1.1.2 查看等待锁的事务

SQL：

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

1.1.3 查询正在执行的事务

SQL：

SELECT * FROM information_schema.INNODB_TRX；

示例：

备注：通过查看事务的trx_started（开始时间）来判断该事务的阻塞时间。

1.2 死锁解决

SQL :

 kill   线程ID

备注：线程ID指的是 1.1.3步骤中查询出来的 trx_mysql_thread_id。

二、Oracle数据库死锁排查

2.1  查看是否有死锁

SQL：

select s.username,l.object_id, l.session_id,s.serial#, s.lockwait,s.status,s.machine,s.program from v$session s,v$locked_object l where s.sid = l.session_id;

示例：

字段解析：

Username：<span style='color:rgb(244,67,54)' >死锁</span>语句所用的数据库用户；
SID: session identifier， session 标示符，session 是通信双方从开始通信到通信结束期间的一个上下文。
SERIAL#: sid 会重用，但是同一个sid被重用时，serial#会增加，不会重复。
Lockwait：可以通过这个字段查询出当前正在等待的锁的相关信息。
Status：用来判断session状态。Active：正执行SQL语句。Inactive：等待操作。Killed：被标注为删除。
Machine： <span style='color:rgb(244,67,54)' >死锁</span>语句所在的机器。
Program： 产生<span style='color:rgb(244,67,54)' >死锁</span>的语句主要来自哪个应用程序。

2.2  查看引起死锁的语句

SQL:

select sql_text from v$sql where hash_value in   (select sql_hash_value from v$session where sid in  (select session_id from v$locked_object));

示例：

2.3 死锁解决

SQL:

alter system kill session 'sid,s.serial#';

备注：多个session用逗号隔开。

2.4 自动生成killsql

SELECT distinct SESS.SID,
    SESS.SERIAL#,
    LO.ORACLE_USERNAME,
    LO.OS_USER_NAME,
    AO.OBJECT_NAME,
    LO.LOCKED_MODE,
    'ALTER SYSTEM KILL SESSION ''' || SESS.SID || ',' || SESS.SERIAL# || ''' immediate;',
    SESS.STATUS
FROM GV$LOCKED_OBJECT LO,
    DBA_OBJECTS     AO,
    GV$SESSION       SESS,
    Gv$process       p
WHERE AO.OBJECT_ID = LO.OBJECT_ID
AND LO.SESSION_ID = SESS.SID
and SESS.paddr = p.addr;

 

三、死锁的产生原因

3.1 死锁产生的原因

造成死锁的原因就是多个线程或进程对同一个资源的争抢或相互依，具体有下列情况：

a. 删除和更新之间引起的<span style='color:rgb(244,67,54)' >死锁</span>
b. 两个表之前不同顺序之间的相互更新操作引起的<span style='color:rgb(244,67,54)' >死锁</span>
c. 主子表上删除数据，缺少索引导致行级锁升级为表级锁，最终导致大量的锁等待和<span style='color:rgb(244,67,54)' >死锁</span>。

3.2  死锁的避免

 死锁不能完全避免，但可以使死锁的数量减至最少,下列方法有助于最大限度地降低死锁：  

1、按同一顺序访问对象
    如果所有并发事务按同一顺序访问对象，则发生<span style='color:rgb(244,67,54)' >死锁</span>的可能性会降低。例如，如果两个并发事务获得 Supplier 表上的锁，然后获得 Part 表上的锁，则在其中一个事务完成之前，另一个事务被阻塞在 Supplier 表上。第一个事务提交或回滚后，第二个事务继续进行。不发生<span style='color:rgb(244,67,54)' >死锁</span>。将存储过程用于所有的数据修改可以标准化访问对象的顺序。 
3、避免事务中的用户交互 避免编写包含用户交互的事务，因为运行没有用户交互的批处理的速度要远远快于用户手动响应查询的速度；
     例如答复应用程序请求参数的提示。例如，如果事务正在等待用户输入，而用户去吃午餐了或者甚至回家过周末了，则用户将此事务挂起使之不能完成。这样将降低系统的吞吐量，因为事务持有的任何锁只有在事务提交或回滚时才会释放。
 即使不出现<span style='color:rgb(244,67,54)' >死锁</span>的情况，访问同一资源的其它事务也会被阻塞，等待该事务完成。 
4、保持事务简短并在一个批处理中 
   在同一数据库中并发执行多个需要长时间运行的事务时通常发生<span style='color:rgb(244,67,54)' >死锁</span>。事务运行时间越长，其持有排它锁或更新锁的时间也就越长，从而堵塞了其它活动并可能导致<span style='color:rgb(244,67,54)' >死锁</span>。 保持事务在一个批处理中，可以最小化事务的网络通信往返量，减少完成事务可能的延迟并释放锁。