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

 

三、死鎖的產生原因

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>。 保持事務在一個批處理中，可以最小化事務的網絡通信往返量，減少完成事務可能的延遲並釋放鎖。