Oracle鎖表解決方法的詳細記錄

Posted on by

前言

鎖表或鎖超時相信大傢都不陌生，經常發生在DML語句中，產生的原因就是數據庫的獨占式封鎖機制，當執行DML語句時對表或行數據進行鎖住，直到事務提交或回滾或者強制結束當前會話。

對於我們的應用系統而言鎖表大概率會發生在SQL執行慢並且沒有超時的地方（一條SQL由於某種原因（Spoon工具做數據抽取與推送）一直執行不成功並且一直不釋放資源）因此寫出高效率SQL也尤為重要！還有另外情況也會發生鎖表，就是高並發場景，高並發會帶來的問題就是Spring事務會造成數據庫事務未提交產生死鎖（當前事務等待其他事務釋放鎖資源）！從而拋出異常java.sql.SQLException: Lock wait timeout exceeded;。

那麼如何解決鎖表或鎖超時呢？臨時性解決方案就是找出鎖資源競爭的表或語句，直接結束當前會話或sesstion，強制釋放鎖資源。例如

解決方法如下：

1、session1修改某條數據但是不提交事務，session2查詢未提交事務的那條記錄

2、session2嘗試修改

我們可以看到修改未提交事務的記錄會處於一直等待狀態，直到對方釋放鎖資源或強制關閉session1。這裡也說明瞭Oracle做到瞭行級鎖！

這裡隻是簡單的模擬瞭出現鎖表情況，可以一眼看出就是session1導致的鎖表。實際開發中遇到這種情況一般都是使用SQL直接查出鎖資源競爭的表或語句然後進行資源的強制釋放！！

3、session3查詢競爭資源的表或語句，強制釋放資源

-- 查詢未提交事務的session信息，註意執行以下SQL，用戶需要有DBA權限才行
SELECT
    L.SESSION_ID,
    S.SERIAL#,
    L.LOCKED_MODE AS 鎖模式,
    L.ORACLE_USERNAME AS 所有者,
    L.OS_USER_NAME AS 登錄系統用戶名,
    S.MACHINE AS 系統名,
    S.TERMINAL AS 終端用戶名,
    O.OBJECT_NAME AS 被鎖表對象名,
    S.LOGON_TIME AS 登錄數據庫時間
FROM V$LOCKED_OBJECT L
    INNER JOIN ALL_OBJECTS O ON O.OBJECT_ID = L.OBJECT_ID
    INNER JOIN V$SESSION S ON S.SID = L.SESSION_ID
WHERE 1 = 1

查詢結果如下

對我們強制釋放資源有用的隻有前面兩個字段，例如

-- 強制 結束/kill 鎖表會話語法
ALTER SYSTEM KILL SESSION 'SESSION_ID, SERIAL#';

-- 強制殺死session1，讓session2可以修改id=5的那條記錄
ALTER SYSTEM KILL SESSION '34, 111';

強制殺死session1後，註意觀察session2的執行情況！我們會發現session2的等待會立即終止並執行！相信小夥伴們都有一個疑惑，session_id有29和34，如何確定他們屬於session1還是session2，保證殺死的是session1讓session2成功執行DML語句?

其實也很簡單，這裡的判斷方式就是session1執行更新但不提交事務，可先用以上SQL查詢未提交事務的session信息，此時查到的就是session1的信息。

總結

到此這篇關於Oracle鎖表解決的文章就介紹到這瞭,更多相關Oracle鎖表解決內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：