Skip to main content

Search

Welcome to Tibero GTS!

Solution for ERROR 12033_LOCKFAIL_IN_NOWAIT

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.
Note
Error 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

Caution
Lock 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 
  • NULL value for single-node
TYPE
Type of WLOCK 
  • For DDL on a specific object: WLOCK_DD_OBJ
  • For privilege-related DDL (Grant, Revoke): WLOCK_DD_USER, WLOCK_DD_OBJAUTH, WLOCK_DD_SYSAUTH
ID1, ID2
Key used when setting the lock 
  • competition occurs only for locks with the same type + same key 
  • For WLOCK_DD_OBJ, lock is set on the object, ID1 is the owner_id of the object, ID2 is the object id or name hash value
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

  1. Check the DDL statement that caused the lock fail in sys.log.
  2. Find the ID1 and ID2 values from the DDL statement.
  3. Search GV$LOCK for instance id and session id using ID1 and ID2 values.
  4. Search sys.log for the session found in the corresponding instance id.
  5. 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
 
 
 

Related to