ORA-00054: èµæºæ£å¿, ä½æå®ä»¥ NOWAIT æ¹å¼è·åèµæº, æè
è¶
æ¶å¤±æ
解å³æ¹æ³å¦ä¸ï¼
=========================================================
SQL> select session_id from v$locked_object;
SESSION_ID
----------
56
SQL> SELECT sid, serial#, username, osuser FROM v$session where sid = 142;
SID SERIAL# USERNAME OSUSER
---------- ---------- ------------------------------ ------------------------------
56 2088 ghb fy
SQL> ALTER SYSTEM KILL SESSION '56,2088';
System altered
æ§è¡å®ä¸è¿°å½ä»¤åï¼æ示ä¼è¯æå¼ãéæ°è¿æ¥æ°æ®åºï¼ç¶åæ§è¡truncateæä½ï¼æåï¼
以ä¸æ¯åçé¨å
==============
Oracleæ°æ®åºçéç±»å
æ ¹æ®ä¿æ¤ç对象ä¸åï¼Oracleæ°æ®åºéå¯ä»¥å为以ä¸å 大类ï¼DMLéï¼data locksï¼æ°æ®éï¼ï¼ç¨äºä¿æ¤æ°æ®çå®æ´æ§ï¼DDLéï¼dictionary locksï¼åå
¸éï¼ï¼ç¨äºä¿æ¤æ°æ®åºå¯¹è±¡çç»æï¼å¦è¡¨ãç´¢å¼ççç»æå®ä¹ï¼å
é¨éåé©ï¼internal locks and latchesï¼ï¼ä¿æ¤æ°æ®åºçå
é¨ç»æã
DMLéçç®çå¨äºä¿è¯å¹¶åæ
åµä¸çæ°æ®å®æ´æ§ï¼ãå¨Oracleæ°æ®åºä¸ï¼DMLé主è¦å
æ¬TMéåTXéï¼å
¶ä¸TMé称为表级éï¼TXé称为äºå¡éæè¡çº§éã
å½Oracle æ§è¡DMLè¯å¥æ¶ï¼ç³»ç»èªå¨å¨æè¦æä½ç表ä¸ç³è¯·TMç±»åçéãå½TMéè·å¾åï¼ç³»ç»åèªå¨ç³è¯·TXç±»åçéï¼å¹¶å°å®é
éå®çæ°æ®è¡çéæ å¿ä½è¿è¡ç½®ä½ãè¿æ ·å¨äºå¡å éåæ£æ¥TXéç¸å®¹æ§æ¶å°±ä¸ç¨åéè¡æ£æ¥éæ å¿ï¼èåªéæ£æ¥TMé模å¼çç¸å®¹æ§å³å¯ï¼å¤§å¤§æé«äºç³»ç»çæçãTMéå
æ¬äºSSãSXãSãX çå¤ç§æ¨¡å¼ï¼å¨æ°æ®åºä¸ç¨0ï¼6æ¥è¡¨ç¤ºãä¸åçSQLæä½äº§çä¸åç±»åçTMéã
å¨æ°æ®è¡ä¸åªæXéï¼æä»éï¼ãå¨ Oracleæ°æ®åºä¸ï¼å½ä¸ä¸ªäºå¡é¦æ¬¡åèµ·ä¸ä¸ªDMLè¯å¥æ¶å°±è·å¾ä¸ä¸ªTXéï¼è¯¥éä¿æå°äºå¡è¢«æ交æåæ»ãå½ä¸¤ä¸ªæå¤ä¸ªä¼è¯å¨è¡¨çåä¸æ¡è®°å½ä¸æ§è¡ DMLè¯å¥æ¶ï¼ç¬¬ä¸ä¸ªä¼è¯å¨è¯¥æ¡è®°å½ä¸å éï¼å
¶ä»çä¼è¯å¤äºçå¾
ç¶æãå½ç¬¬ä¸ä¸ªä¼è¯æ交åï¼TXé被éæ¾ï¼å
¶ä»ä¼è¯æå¯ä»¥å éã
å½Oracleæ°æ®åºåçTXéçå¾
æ¶ï¼å¦æä¸åæ¶å¤ç常常ä¼å¼èµ·Oracleæ°æ®åºæèµ·ï¼æ导è´æ»éçåçï¼äº§çORA-60çé误ãè¿äºç°è±¡é½ä¼å¯¹å®é
åºç¨äº§çæ大çå±å®³ï¼å¦é¿æ¶é´æªååºï¼å¤§éäºå¡å¤±è´¥çã
æ²è§å°éåä¹è§å°é
ä¸ãæ²è§å°é
éå¨ç¨æ·ä¿®æ¹ä¹åå°±åæ¥ä½ç¨ï¼
Select ..for updateï¼nowait)
Select * from tab1 for update
ç¨æ·ååºè¿æ¡å½ä»¤ä¹åï¼oracleå°ä¼å¯¹è¿åéä¸çæ°æ®å»ºç«è¡çº§å°éï¼ä»¥é²æ¢å
¶ä»ç¨æ·çä¿®æ¹ã
å¦ææ¤æ¶å
¶ä»ç¨æ·å¯¹ä¸é¢è¿åç»æéçæ°æ®è¿è¡dmlæddlæä½é½ä¼è¿åä¸ä¸ªé误信æ¯æåçé»å¡ã
1ï¼å¯¹è¿åç»æéè¿è¡updateædeleteæä½ä¼åçé»å¡ã
2ï¼å¯¹è¯¥è¡¨è¿è¡ddlæä½å°ä¼æ¥ï¼Ora-00054:resource busy and acquire with nowait specified.
åå åæ
æ¤æ¶Oracleå·²ç»å¯¹è¿åçç»æéä¸å äºæå®çè¡çº§éï¼ææå
¶ä»å¯¹è¿äºæ°æ®è¿è¡çä¿®æ¹æå é¤æä½é½å¿
é¡»çå¾
è¿ä¸ªéçéæ¾ï¼äº§ççå¤å¨ç°è±¡å°±æ¯å
¶ä»çæä½å°åçé»å¡ï¼è¿ä¸ªè¿ä¸ªæä½commitærollback.
åæ ·è¿ä¸ªæ¥è¯¢çäºå¡å°ä¼å¯¹è¯¥è¡¨å 表级éï¼ä¸å
许对该表çä»»ä½ddlæä½ï¼å¦åå°ä¼æ¥åºora-00054é误ï¼:resource busy and acquire with nowait specified.
äºãä¹è§å°é
ä¹è§ç认为æ°æ®å¨selectåºæ¥å°updateè¿å并æ交çè¿æ®µæ¶é´æ°æ®ä¸ä¼è¢«æ´æ¹ãè¿éé¢æä¸ç§æ½å¨çå±é©å°±æ¯ç±äºè¢«éåºçç»æé并没æ被éå®ï¼æ¯åå¨ä¸ç§å¯è½è¢«å
¶ä»ç¨æ·æ´æ¹çå¯è½ãå æ¤Oracleä»ç¶å»ºè®®æ¯ç¨æ²è§å°éï¼å 为è¿æ ·ä¼æ´å®å
¨ã
é»å¡
å®ä¹ï¼
å½ä¸ä¸ªä¼è¯ä¿æå¦ä¸ä¸ªä¼è¯æ£å¨è¯·æ±çèµæºä¸çéå®æ¶ï¼å°±ä¼åçé»å¡ã被é»å¡çä¼è¯å°ä¸ç´æèµ·ï¼ç´å°ææéçä¼è¯æ¾å¼éå®çèµæºä¸ºæ¢ã4个常è§çdmlè¯å¥ä¼äº§çé»å¡
INSERT
UPDATE
DELETE
SELECTâ¦FOR UPDATE
INSERT
Insertåçé»å¡çå¯ä¸æ
åµå°±æ¯ç¨æ·æ¥æä¸ä¸ªå»ºæ主é®çº¦æç表ãå½2个çä¼è¯åæ¶è¯å¾å表ä¸æå
¥ç¸åçæ°æ®æ¶ï¼å
¶ä¸çä¸ä¸ªä¼è¯å°è¢«é»å¡ï¼ç´å°å¦å¤ä¸ä¸ªä¼è¯æ交æä¼æ»ãä¸ä¸ªä¼è¯æ交æ¶ï¼å¦ä¸ä¸ªä¼è¯å°æ¶å°ä¸»é®éå¤çé误ãåæ»æ¶ï¼è¢«é»å¡çä¼è¯å°ç»§ç»æ§è¡ã
UPDATE åDELETEå½æ§è¡Updateådeleteæä½çæ°æ®è¡å·²ç»è¢«å¦å¤çä¼è¯éå®æ¶ï¼å°ä¼åçé»å¡ï¼ç´å°å¦ä¸ä¸ªä¼è¯æ交æä¼æ»ã
Select â¦for update
å½ä¸ä¸ªç¨æ·ååºselect..for updateçéä½åå¤å¯¹è¿åçç»æéè¿è¡ä¿®æ¹æ¶ï¼å¦æç»æéå·²ç»è¢«å¦ä¸ä¸ªä¼è¯éå®ï¼å°±æ¯åçé»å¡ãéè¦çå¦ä¸ä¸ªä¼è¯ç»æä¹åæå¯ç»§ç»æ§è¡ãå¯ä»¥éè¿ååº select⦠for update nowaitçè¯å¥æ¥é¿å
åçé»å¡ï¼å¦æèµæºå·²ç»è¢«å¦ä¸ä¸ªä¼è¯éå®ï¼åä¼è¿å以ä¸é误ï¼Ora-00054:resource busy and acquire with nowait specified.
æ»é-deadlock
å®ä¹:å½ä¸¤ä¸ªç¨æ·å¸æææ对æ¹çèµæºæ¶å°±ä¼åçæ»é.
å³ä¸¤ä¸ªç¨æ·äºç¸çå¾
对æ¹éæ¾èµæºæ¶,oracle认å®ä¸ºäº§çäºæ»é,å¨è¿ç§æ
åµä¸,å°ä»¥çºç²ä¸ä¸ªç¨æ·ä½ä¸ºä»£ä»·,å¦ä¸ä¸ªç¨æ·ç»§ç»æ§è¡,çºç²çç¨æ·çäºå¡å°åæ».
ä¾åï¼
1ï¼ç¨æ·1对A表è¿è¡Updateï¼æ²¡ææ交ã
2ï¼ç¨æ·2对B表è¿è¡Updateï¼æ²¡ææ交ã
æ¤æ¶ååä¸åå¨èµæºå
±äº«çé®é¢ã
3ï¼å¦æç¨æ·2æ¤æ¶å¯¹A表ä½update,åä¼åçé»å¡ï¼éè¦çå°ç¨æ·ä¸çäºç©ç»æã
4ï¼å¦ææ¤æ¶ç¨æ·1å对B表ä½updateï¼å产çæ»éãæ¤æ¶Oracleä¼éæ©å
¶ä¸ä¸ä¸ªç¨æ·è¿è¡ä¼æ»ï¼ä½¿å¦ä¸ä¸ªç¨æ·ç»§ç»æ§è¡æä½ã
èµ·å :
Oracleçæ»éé®é¢å®é
ä¸å¾å°è§ï¼å¦æåçï¼åºæ¬ä¸é½æ¯ä¸æ£ç¡®çç¨åºè®¾è®¡é æçï¼ç»è¿è°æ´åï¼åºæ¬ä¸é½ä¼é¿å
æ»éçåçã
DMLéå类表
表1 OracleçTMéç±»å
éæ¨¡å¼ éæè¿° 解é SQLæä½
0 none
1 NULL 空 Select
2 SS(Row-S) è¡çº§å
±äº«éï¼å
¶ä»å¯¹è±¡åªè½æ¥è¯¢è¿äºæ°æ®è¡ Select for updateãLock for updateãLock row share
3 SX(Row-X) è¡çº§æå®éï¼å¨æ交åä¸å
许åDMLæä½ InsertãUpdateãDeleteãLock row share
4 S(Share) å
±äº«é Create indexãLock share
5 SSX(S/Row-X) å
±äº«è¡çº§æå®é Lock share row exclusive
6 X(Exclusive) æå®é Alter tableãDrop ableãDrop indexãTruncate table ãLock exclusive
1.å
³äºV$lock表åç¸å
³è§å¾ç说æ
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
--æ们主è¦å
³æ³¨TXåTM两ç§ç±»åçé
--ULéç¨æ·èªå·±å®ä¹çï¼ä¸è¬å¾å°ä¼å®ä¹ï¼åºæ¬ä¸ç¨å
³æ³¨
--å
¶å®å为系ç»éï¼ä¼å¾å¿«èªå¨éæ¾ï¼ä¸ç¨å
³æ³¨
ID1 NUMBER Lock identifier #1 (depends on type)
ID2 NUMBER Lock identifier #2 (depends on type)
---å½lock type 为TMæ¶ï¼id1为DML-locked objectçobject_id
---å½lock type 为TXæ¶ï¼id1为usn+slotï¼èid2为seqã
--å½lock type为å
¶å®æ¶ï¼ä¸ç¨å
³æ³¨
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æ¶è¡¨ç¤ºå½åä¼è¯ä»¥æç§æ¨¡å¼å æ该éï¼çäº0æ¶è¡¨ç¤ºå½åä¼è¯æ£å¨çå¾
该éèµæºï¼å³è¡¨ç¤ºè¯¥ä¼è¯è¢«é»å¡ã
--å¾å¾å¨åçTXéæ¶ï¼ä¼´éçTMéï¼æ¯å¦ä¸ä¸ªsid=9ä¼è¯æ¥æä¸ä¸ªTMéï¼ä¸è¬ä¼æ¥æä¸ä¸ªæå 个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æ¶ï¼è¡¨ç¤ºå½åä¼è¯è¢«é»å¡ï¼å
¶å®ä¼è¯å ææ¹éç模å¼
CTIME NUMBER Time since current mode was granted
BLOCK NUMBER The lock is blocking another lock
0, 'Not Blocking',
1, 'Blocking',
2, 'Global',
--该éæ¯å¦é»å¡äºå¦å¤ä¸ä¸ªé
2.å
¶å®ç¸å
³è§å¾è¯´æ
è§å¾å æè¿° 主è¦å段说æ
v$session æ¥è¯¢ä¼è¯çä¿¡æ¯åéçä¿¡æ¯ã sid,serial#ï¼è¡¨ç¤ºä¼è¯ä¿¡æ¯ã
programï¼è¡¨ç¤ºä¼è¯çåºç¨ç¨åºä¿¡æ¯ã
row_wait_obj#ï¼è¡¨ç¤ºçå¾
ç对象,ådba_objectsä¸çobject_idç¸å¯¹åºã
lockwait :该ä¼è¯çå¾
çéçå°å,ä¸v$lockçkaddr对åº.
v$session_wait æ¥è¯¢çå¾
çä¼è¯ä¿¡æ¯ã sidï¼è¡¨ç¤ºææéçä¼è¯ä¿¡æ¯ã
Seconds_in_waitï¼è¡¨ç¤ºçå¾
æç»çæ¶é´ä¿¡æ¯
Eventï¼è¡¨ç¤ºä¼è¯çå¾
çäºä»¶ï¼éçäºenqueue
dba_locks 对v$lockçæ ¼å¼åè§å¾ã Session_idï¼åv$lockä¸çSid对åºã
Lock_typeï¼åv$lockä¸çtype对åºã
Lock_ID1ï¼ åv$lockä¸çID1对åºã
Mode_held,mode_requestedï¼åv$lockä¸
çlmode,requestç¸å¯¹åºã
v$locked_object åªå
å«DMLçéä¿¡æ¯ï¼å
æ¬åæ»æ®µåä¼è¯ä¿¡æ¯ã Xidusn,xidslot,xidsqnï¼è¡¨ç¤ºåæ»æ®µä¿¡æ¯ãå
v$transactionç¸å
³èã
Object_idï¼è¡¨ç¤ºè¢«é对象æ è¯ã
Session_idï¼è¡¨ç¤ºææéçä¼è¯ä¿¡æ¯ã
Locked_modeï¼è¡¨ç¤ºä¼è¯çå¾
çé模å¼çä¿¡
æ¯ï¼åv$lockä¸çlmodeä¸è´ã
以ä¸æ¯å½ä»¤è¡é¨å
================
1.æ¥è¯¢æ°æ®åºä¸çé
select * from v$lock;
select * from v$lock where block=1;
2.æ¥è¯¢è¢«éç对象
select * from v$locked_object;
3.æ¥è¯¢é»å¡
æ¥è¢«é»å¡çä¼è¯
select * from v$lock where lmode=0 and type in ('TM','TX');
æ¥é»å¡å«çä¼è¯é
select * from v$lock where lmode>0 and type in ('TM','TX');
4.æ¥è¯¢æ°æ®åºæ£å¨çå¾
éçè¿ç¨
select * from v$session where lockwait is not null;
5.æ¥è¯¢ä¼è¯ä¹é´éçå¾
çå
³ç³»
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';
解å³æ¹æ¡ï¼
select session_id from v$locked_object; --é¦å
å¾å°è¢«é对象çsession_id
SELECT sid, serial#, username, osuser FROM v$session where sid = session_id; --éè¿ä¸é¢å¾å°çsession_idå»åå¾v$sessionçsidåserial#ï¼ç¶å对该è¿ç¨è¿è¡ç»æ¢ã
ALTER SYSTEM KILL SESSION 'sid,serial';
example:
ALTER SYSTEM KILL SESSION '13, 8';
温馨提示:答案为网友推荐,仅供参考