oracle鎖表問(wèn)題怎么處理

本篇內(nèi)容介紹了“oracle鎖表問(wèn)題怎么處理”的有關(guān)知識(shí)，在實(shí)際案例的操作過(guò)程中，不少人都會(huì)遇到這樣的困境，接下來(lái)就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！

創(chuàng)新互聯(lián)建站總部坐落于成都市區(qū)，致力網(wǎng)站建設(shè)服務(wù)有網(wǎng)站制作、成都做網(wǎng)站、網(wǎng)絡(luò)營(yíng)銷策劃、網(wǎng)頁(yè)設(shè)計(jì)、網(wǎng)站維護(hù)、公眾號(hào)搭建、重慶小程序開發(fā)公司、軟件開發(fā)等為企業(yè)提供一整套的信息化建設(shè)解決方案。創(chuàng)造真正意義上的網(wǎng)站建設(shè)，為互聯(lián)網(wǎng)品牌在互動(dòng)行銷領(lǐng)域創(chuàng)造價(jià)值而不懈努力！

"ORA-00054: 資源正忙, 但指定以 NOWAIT 方式獲取資源, 或者超時(shí)失效"的快速解決方法今天在導(dǎo)一個(gè)臨時(shí)表的數(shù)據(jù)，導(dǎo)出完成后準(zhǔn)備清空數(shù)據(jù)，執(zhí)行truncate命令時(shí)，遇到如下問(wèn)題：

ORA-00054: 資源正忙, 但指定以 NOWAIT 方式獲取資源, 或者超時(shí)失效

解決方法如下：
=========================================================
SQL> select session_id from v$locked_object;

SESSION_ID
----------
       56

SQL> SELECT sid, serial#, username, osuser FROM v$session where sid = 56;

       SID    SERIAL# USERNAME                       OSUSER
---------- ---------- ------------------------------ ------------------------------
       56         2088 ghb                          fy

SQL> ALTER SYSTEM KILL SESSION '56,2088';

System altered

執(zhí)行完上述命令后，提示會(huì)話斷開。重新連接數(shù)據(jù)庫(kù)，然后執(zhí)行truncate操作，成功！


以下是原理部分
==============
Oracle數(shù)據(jù)庫(kù)的鎖類型

根據(jù)保護(hù)的對(duì)象不同，Oracle數(shù)據(jù)庫(kù)鎖可以分為以下幾大類：DML鎖（data locks，數(shù)據(jù)鎖），用于保護(hù)數(shù)據(jù)的完整性；DDL鎖（dictionary locks，字典鎖），用于保護(hù)數(shù)據(jù)庫(kù)對(duì)象的結(jié)構(gòu)，如表、索引等的結(jié)構(gòu)定義；內(nèi)部鎖和閂（internal locks and latches），保護(hù)數(shù)據(jù)庫(kù)的內(nèi)部結(jié)構(gòu)。

DML鎖的目的在于保證并發(fā)情況下的數(shù)據(jù)完整性，。在Oracle數(shù)據(jù)庫(kù)中，DML鎖主要包括TM鎖和TX鎖，其中TM鎖稱為表級(jí)鎖，TX鎖稱為事務(wù)鎖或行級(jí)鎖。

當(dāng)Oracle 執(zhí)行DML語(yǔ)句時(shí)，系統(tǒng)自動(dòng)在所要操作的表上申請(qǐng)TM類型的鎖。當(dāng)TM鎖獲得后，系統(tǒng)再自動(dòng)申請(qǐng)TX類型的鎖，并將實(shí)際鎖定的數(shù)據(jù)行的鎖標(biāo)志位進(jìn)行置位。這樣在事務(wù)加鎖前檢查TX鎖相容性時(shí)就不用再逐行檢查鎖標(biāo)志，而只需檢查TM鎖模式的相容性即可，大大提高了系統(tǒng)的效率。TM鎖包括了SS、SX、S、X 等多種模式，在數(shù)據(jù)庫(kù)中用0－6來(lái)表示。不同的SQL操作產(chǎn)生不同類型的TM鎖。

在數(shù)據(jù)行上只有X鎖（排他鎖）。在 Oracle數(shù)據(jù)庫(kù)中，當(dāng)一個(gè)事務(wù)首次發(fā)起一個(gè)DML語(yǔ)句時(shí)就獲得一個(gè)TX鎖，該鎖保持到事務(wù)被提交或回滾。當(dāng)兩個(gè)或多個(gè)會(huì)話在表的同一條記錄上執(zhí)行 DML語(yǔ)句時(shí)，第一個(gè)會(huì)話在該條記錄上加鎖，其他的會(huì)話處于等待狀態(tài)。當(dāng)?shù)谝粋€(gè)會(huì)話提交后，TX鎖被釋放，其他會(huì)話才可以加鎖。

當(dāng)Oracle數(shù)據(jù)庫(kù)發(fā)生TX鎖等待時(shí)，如果不及時(shí)處理常常會(huì)引起Oracle數(shù)據(jù)庫(kù)掛起，或?qū)е滤梨i的發(fā)生，產(chǎn)生ORA-60的錯(cuò)誤。這些現(xiàn)象都會(huì)對(duì)實(shí)際應(yīng)用產(chǎn)生極大的危害，如長(zhǎng)時(shí)間未響應(yīng)，大量事務(wù)失敗等。

悲觀封鎖和樂(lè)觀封鎖

一、悲觀封鎖
鎖在用戶修改之前就發(fā)揮作用：
Select ..for update（nowait)
Select * from tab1 for update
用戶發(fā)出這條命令之后，oracle將會(huì)對(duì)返回集中的數(shù)據(jù)建立行級(jí)封鎖，以防止其他用戶的修改。
如果此時(shí)其他用戶對(duì)上面返回結(jié)果集的數(shù)據(jù)進(jìn)行dml或ddl操作都會(huì)返回一個(gè)錯(cuò)誤信息或發(fā)生阻塞。
1：對(duì)返回結(jié)果集進(jìn)行update或delete操作會(huì)發(fā)生阻塞。
2：對(duì)該表進(jìn)行ddl操作將會(huì)報(bào)：Ora-00054:resource busy and acquire with nowait specified.

原因分析
此時(shí)Oracle已經(jīng)對(duì)返回的結(jié)果集上加了排它的行級(jí)鎖，所有其他對(duì)這些數(shù)據(jù)進(jìn)行的修改或刪除操作都必須等待這個(gè)鎖的釋放，產(chǎn)生的外在現(xiàn)象就是其他的操作將發(fā)生阻塞，這個(gè)這個(gè)操作commit或rollback.
同樣這個(gè)查詢的事務(wù)將會(huì)對(duì)該表加表級(jí)鎖，不允許對(duì)該表的任何ddl操作，否則將會(huì)報(bào)出ora-00054錯(cuò)誤：:resource busy and acquire with nowait specified.

二、樂(lè)觀封鎖
樂(lè)觀的認(rèn)為數(shù)據(jù)在select出來(lái)到update進(jìn)取并提交的這段時(shí)間數(shù)據(jù)不會(huì)被更改。這里面有一種潛在的危險(xiǎn)就是由于被選出的結(jié)果集并沒有被鎖定，是存在一種可能被其他用戶更改的可能。因此Oracle仍然建議是用悲觀封鎖，因?yàn)檫@樣會(huì)更安全。

阻塞
定義：
當(dāng)一個(gè)會(huì)話保持另一個(gè)會(huì)話正在請(qǐng)求的資源上的鎖定時(shí)，就會(huì)發(fā)生阻塞。被阻塞的會(huì)話將一直掛起，直到持有鎖的會(huì)話放棄鎖定的資源為止。
4個(gè)常見的dml語(yǔ)句會(huì)產(chǎn)生阻塞
INSERT
UPDATE
DELETE
SELECT…FOR UPDATE


INSERT

Insert發(fā)生阻塞的唯一情況就是用戶擁有一個(gè)建有主鍵約束的表。當(dāng)2個(gè)的會(huì)話同時(shí)試圖向表中插入相同的數(shù)據(jù)時(shí)，其中的一個(gè)會(huì)話將被阻塞，直到另外一個(gè)會(huì)話提交或會(huì)滾。一個(gè)會(huì)話提交時(shí)，另一個(gè)會(huì)話將收到主鍵重復(fù)的錯(cuò)誤?；貪L時(shí)，被阻塞的會(huì)話將繼續(xù)執(zhí)行。

UPDATE 和DELETE當(dāng)執(zhí)行Update和delete操作的數(shù)據(jù)行已經(jīng)被另外的會(huì)話鎖定時(shí)，將會(huì)發(fā)生阻塞，直到另一個(gè)會(huì)話提交或會(huì)滾。

Select …for update

當(dāng)一個(gè)用戶發(fā)出select..for update的錯(cuò)作準(zhǔn)備對(duì)返回的結(jié)果集進(jìn)行修改時(shí)，如果結(jié)果集已經(jīng)被另一個(gè)會(huì)話鎖定，就是發(fā)生阻塞。需要等另一個(gè)會(huì)話結(jié)束之后才可繼續(xù)執(zhí)行?？梢酝ㄟ^(guò)發(fā)出 select… for update nowait的語(yǔ)句來(lái)避免發(fā)生阻塞，如果資源已經(jīng)被另一個(gè)會(huì)話鎖定，則會(huì)返回以下錯(cuò)誤：Ora-00054:resource busy and acquire with nowait specified.

死鎖-deadlock
定義:當(dāng)兩個(gè)用戶希望持有對(duì)方的資源時(shí)就會(huì)發(fā)生死鎖.
即兩個(gè)用戶互相等待對(duì)方釋放資源時(shí),oracle認(rèn)定為產(chǎn)生了死鎖,在這種情況下,將以犧牲一個(gè)用戶作為代價(jià),另一個(gè)用戶繼續(xù)執(zhí)行,犧牲的用戶的事務(wù)將回滾.
例子：
1：用戶1對(duì)A表進(jìn)行Update，沒有提交。
2：用戶2對(duì)B表進(jìn)行Update，沒有提交。
此時(shí)雙反不存在資源共享的問(wèn)題。
3：如果用戶2此時(shí)對(duì)A表作update,則會(huì)發(fā)生阻塞，需要等到用戶一的事物結(jié)束。
4：如果此時(shí)用戶1又對(duì)B表作update，則產(chǎn)生死鎖。此時(shí)Oracle會(huì)選擇其中一個(gè)用戶進(jìn)行會(huì)滾，使另一個(gè)用戶繼續(xù)執(zhí)行操作。
起因:
Oracle的死鎖問(wèn)題實(shí)際上很少見，如果發(fā)生，基本上都是不正確的程序設(shè)計(jì)造成的，經(jīng)過(guò)調(diào)整后，基本上都會(huì)避免死鎖的發(fā)生。

DML鎖分類表

表1 Oracle的TM鎖類型
鎖模式 鎖描述 解釋 SQL操作
0 none
1 NULL 空 Select
2 SS(Row-S) 行級(jí)共享鎖，其他對(duì)象只能查詢這些數(shù)據(jù)行 Select for update、Lock for update、Lock row share
3 SX(Row-X) 行級(jí)排它鎖，在提交前不允許做DML操作 Insert、Update、Delete、Lock row share
4 S(Share) 共享鎖 Create index、Lock share
5 SSX(S/Row-X) 共享行級(jí)排它鎖 Lock share row exclusive
6 X(Exclusive) 排它鎖 Alter table、Drop able、Drop index、Truncate table 、Lock exclusive


1.關(guān)于V$lock表和相關(guān)視圖的說(shuō)明

Column      Datatype                                     Description
ADDR        RAW(4 |8)                                    Address of lock state object
KADDR       RAW(4 |8)                                   Address of lock
SID         NUMBER                                       Identifier for session holding or acquiring the lock
TYPE        VARCHAR2(2)                                  Type of user or system lock.The locks on the user types are obtained by user applications. Any process that is blocking   others is likely to be holding one of these locks. The user type locks are:TM - DML enqueue TX - Transaction enqueue UL - User supplied
--我們主要關(guān)注TX和TM兩種類型的鎖
--UL鎖用戶自己定義的，一般很少會(huì)定義，基本不用關(guān)注
--其它均為系統(tǒng)鎖，會(huì)很快自動(dòng)釋放，不用關(guān)注
ID1          NUMBER                                           Lock identifier #1 (depends on type)
ID2          NUMBER                                           Lock identifier #2 (depends on type)
---當(dāng)lock type 為TM時(shí)，id1為DML-locked object的object_id
---當(dāng)lock type 為TX時(shí)，id1為usn+slot，而id2為seq。
--當(dāng)lock type為其它時(shí)，不用關(guān)注

LMODE              NUMBER                       Lock mode in which the session holds the lock:
0 - none
1 - null (NULL)
2 - row-S (SS)
3 - row-X (SX)
4 - share (S)
5 - S/Row-X (SSX)
6 - exclusive (X)
--大于0時(shí)表示當(dāng)前會(huì)話以某種模式占有該鎖，等于0時(shí)表示當(dāng)前會(huì)話正在等待該鎖資源，即表示該會(huì)話被阻塞。
--往往在發(fā)生TX鎖時(shí)，伴隨著TM鎖，比如一個(gè)sid=9會(huì)話擁有一個(gè)TM鎖，一般會(huì)擁有一個(gè)或幾個(gè)TX鎖，但他們的id1和id2是不同的

REQUEST       NUMBER              Lock mode in which the process requests the lock:
0 - none
1 - null (NULL)
2 - row-S (SS)
3 - row-X (SX)
4 - share (S)
5 - S/Row-X (SSX)
6 - exclusive (X)
--大于0時(shí)，表示當(dāng)前會(huì)話被阻塞，其它會(huì)話占有改鎖的模式
CTIME         NUMBER              Time since current mode was granted
BLOCK         NUMBER              A value of either 0 or 1, depending on whether or not the lock in question is the blocker.


2.其它相關(guān)視圖說(shuō)明
視圖名 描述 主要字段說(shuō)明
v$session 查詢會(huì)話的信息和鎖的信息。
sid,serial#：表示會(huì)話信息。
program：表示會(huì)話的應(yīng)用程序信息。
row_wait_obj#：表示等待的對(duì)象,和dba_objects中的object_id相對(duì)應(yīng)。
lockwait :該會(huì)話等待的鎖的地址,與v$lock的kaddr對(duì)應(yīng).
v$session_wait 查詢等待的會(huì)話信息。 sid：表示持有鎖的會(huì)話信息。
Seconds_in_wait：表示等待持續(xù)的時(shí)間信息
Event：表示會(huì)話等待的事件，鎖等于enqueue

dba_locks 對(duì)v$lock的格式化視圖。
Session_id：和v$lock中的Sid對(duì)應(yīng)。
Lock_type：和v$lock中的type對(duì)應(yīng)。
Lock_ID1： 和v$lock中的ID1對(duì)應(yīng)。
Mode_held,mode_requested：和v$lock中的lmode,request相對(duì)應(yīng)。

v$locked_object 只包含DML的鎖信息，包括回滾段和會(huì)話信息。
Xidusn,xidslot,xidsqn：表示回滾段信息。和v$transaction相關(guān)聯(lián)。
Object_id：表示被鎖對(duì)象標(biāo)識(shí)。
Session_id：表示持有鎖的會(huì)話信息。
Locked_mode：表示會(huì)話等待的鎖模式的信息，和v$lock中的lmode一致。


以下是命令行部分
================
1.查詢數(shù)據(jù)庫(kù)中的鎖

select * from v$lock;
select * from v$lock where block=1;

2.查詢被鎖的對(duì)象

select * from v$locked_object;

3.查詢阻塞
查被阻塞的會(huì)話
select * from v$lock where lmode=0 and type in ('TM','TX');

查阻塞別的會(huì)話鎖
select * from v$lock where lmode>0 and type in ('TM','TX');

4.查詢數(shù)據(jù)庫(kù)正在等待鎖的進(jìn)程
select * from v$session where lockwait is not null;

5.查詢會(huì)話之間鎖等待的關(guān)系
select a.sid holdsid,b.sid waitsid,a.type,a.id1,a.id2,a.ctime from v$lock a,v$lock b
where a.id1=b.id1 and a.id2=b.id2 and a.block=1 and b.block=0;

6.查詢鎖等待事件
select * from v$session_wait where event='enqueue';

解決方案：
--首先得到被鎖對(duì)象的session_id
select session_id from v$locked_object;

--通過(guò)上面得到的session_id去取得v$session的sid和serial#，然后對(duì)該進(jìn)程進(jìn)行終止。
SELECT sid, serial#, username, osuser FROM v$session where sid = session_id;

--最后殺會(huì)話
ALTER SYSTEM KILL SESSION 'sid,serial';

“oracle鎖表問(wèn)題怎么處理”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識(shí)可以關(guān)注創(chuàng)新互聯(lián)網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實(shí)用文章！

網(wǎng)頁(yè)名稱：oracle鎖表問(wèn)題怎么處理
路徑分享：http://m.jiaotiyi.com/article/pcgjjj.html