Document Type | Troubleshooting
Category | Monitoring/Inspection
Applicable Product Version | 6FS07
Error Code | 12033
Document Number | TMOTS014
Issue
During DDL execution, failure to acquire lock causes error 12033_LOCKFAIL_IN_NOWAIT to occur.
NoteError 12033 can be a legitimately occurring error.
Cause
It is highly likely that the session requesting the lock conflicts with another session or internal task execution.
Solutions
CautionLock Fail errors occur due to timing and are difficult to reproduce.Therefore, it is important to collect specific data at the time the issue occurs.
CallStack Dump (Error)
Since the timing of lock setting in DDL varies, if it is possible to identify when the lock fail occurs, analysis becomes easier.
- 'alter system dump callstack on error -12033 [on/off];' command can be used to leave a Callstack at Lock Fail. Callstack dump may cause performance issues, so it should only be enabled when necessary.
CallStack Dump (Session)
If a hang-like phenomenon occurs during DDL execution but no lock fail error occurs, use this method.
- 'alter system dump callstack [SID];' command can be used to collect the callstack of a session.
- SID is the session id of the session executing DDL, and callstack dump results are needed by repeating the command 5 or more times at 5~10 second intervals.
GV$LOCK
You can check the information of WLOCKs held by all instances in the GV$LOCK view.
For single-node structure, querying V$LOCK is sufficient, but in TAC environments, GV$LOCK must be queried.
Column Information of GV$LOCK
Column | Meaning |
|---|---|
INST_ID | Instance (node) ID
|
TYPE | Type of WLOCK
|
ID1, ID2 | Key used when setting the lock
|
SESS_ID | Session id holding or waiting for the lock |
CTIME | Duration (seconds) the lock has been held or waited for |
REQUESTED | Lock mode being waited on |
LMODE | Lock mode currently held |
1 : RS
2 : RX
3 : S
4 : SRX
5 : X
Example)
INST_ID TYPE ID1 ID2 LMODE REQUESTED SESS_ID CTIME
-------- ------------------------ ---------- ---------- ---------- ---------------- ----------- ----------
WLOCK_DD_OBJ 0 3265 0 1 98 137
WLOCK_DD_OBJ 0 3265 5 0 99 146
--Session 99 holds an X LOCK on WLOCK_DD_OBJ (0,3265) for 146 seconds,
and session 98 is waiting for an RS LOCK for 137 seconds
Checking Blocking Sessions
LOCKFAIL_IN_NOWAIT errors in DDL mostly correspond to the following cases.
- Failure to acquire WLOCK_DD_OBJ during DDL on an object
- Failure to acquire one of WLOCK_DD_USER, WLOCK_DD_OBJAUTH, WLOCK_DD_SYSAUTH during privilege-related DDL
How to Check Blocking Sessions
- Check the DDL statement that caused the lock fail in sys.log.
- Find the ID1 and ID2 values from the DDL statement.
- Search GV$LOCK for instance id and session id using ID1 and ID2 values.
- Search sys.log for the session found in the corresponding instance id.
- Using the instance/session id, check SQL_ID in GV$SESSION and verify the SQL_TEXT in GV$SQL.
--1. Check DDL statement that caused LOCKFAIL in SYS.LOG
[DDL-99] [I] DDL execution failed (ec=-12033) drop table tibero.t;
--2. Identify Lock ID from the statement
SQL select object_id from dba_objects where owner='TIBERO' and object_name='T';
OBJECT_ID
---------
3304
- Likely failed to set WLOCK_DD_OBJ lock with ID2=3304
--3. Search instance id and session id in GV$LOCK using ID1, ID2 of the lock
SQL select inst_id, sess_id from gv$lock where type='WLOCK_DD_OBJ' and id2=3304;
INST_ID SESS_ID
--------- --------
98
- Single environment, so INST_ID is Null, SESS_ID is 98
--4. Check SYS.LOG
[DDL-98][I] Executing DDL : drop table tibero.t
[DDL-99][I] Executing DDL : drop table tibero.t
[DDL-99][I] THROW. ec=ERROR_LOCKFAIL_IN_NOWAIT(-12033) [ Lock acquisition failed in NOWAIT mode. ]
[DDL-99] [I] DDL execution failed (ec=-12033) drop table tibero.t;
- Drop of TIBERO.T table started and no completion log exists, indicating drop is in progress