ORA-00054
- Type: The enqueue resource type.
- Name: The enqueue resource name.
- Description: The enqueue resource description.
- ID1_value: The value of first identifier for the enqueue resource.
- ID1_description: The description of first identifier for the enqueue resource.
- ID2_value: The value of second identifier for the enqueue resource.
- ID2_description: The description of second identifier for the enqueue resource.
Cause
The current session is failing to acquire the specified lock on the specified resource because another session holds the lock.
Action
Retry the operation and see if the lock has been released. If not, identify which session holds the lock and on which resource using the information provided in the error message text and terminate the transaction of the session before retrying the operation.
Additional Information
To identify the session that is holding the lock in question,
run the following query. Note that this query requires
SELECT privileges on the GV$SESSION and GV$LOCK views.
For example:
SELECT l.inst_id, s.sid, s.serial#, s.username, s.sql_id,
l.ctime AS "LOCK_HOLD_TIME_ELAPSED_SECONDS",
s.client_identifier, s.module, s.action
FROM GV$SESSION s, GV$LOCK l
WHERE l.sid = s.sid
AND l.inst_id = l.inst_id
AND l.type = '<lock_type>'
AND l.id1 = <resource_id1_from_error_message>
AND l.id2 = <resource_id2_from_error_message>;
The column LOCK_HOLD_TIME_ELAPSED_SECONDS (ctime) is the
time since the current lock was granted. In other words, it
is the number of seconds for which the other session has held
the lock.
Consider this example of a failing DROP TABLE operation
because a session still holds a lock on the table:
DROP TABLE my_transactions;
DROP TABLE my_transactions
*
ERROR at line 1:
ORA-00054: Failed to acquire a lock (Type: "TM", Name: "DML",
Description: "Synchronizes accesses to an object") because it
is currently held by another session. The resource being locked
can be identified by 74855 ("Table") and 0 ("operation")
With the information provided by the error message, you can transform the previous query template to the following, gaining insight into which session holds the lock. For example:
SELECT l.inst_id, s.sid, s.serial#, s.username, s.sql_id,
l.ctime AS "LOCK_HOLD_TIME_ELAPSED_SECONDS",
s.client_identifier, s.module, s.action
FROM GV$SESSION s, GV$LOCK l
WHERE l.sid = s.sid
AND l.inst_id = l.inst_id
AND l.type = 'TM'
AND l.id1 = 74855
AND l.id2 = 0;
INST_ID SID SERIAL# USERNAME SQL_ID
---------- -------- -------- -------- -------------
1 147 41814 GERALD af0zhnff94c7v
LOCK_HOLD_TIME_ELAPSED_SECONDS CLIENT_IDENTIFIER MODULE
------------------------------ ----------------- --------
129 SQL*Plus
ACTION
----------
This output shows that the session on Oracle Database where
INST_ID is 1, SID is 147, SERIAL# is 41814,
and USERNAME is GERALD holds the lock.
Furthermore, we can infer that the session holds the lock for
129 seconds and is likely to be a SQL*Plus prompt, because the
client information column MODULE shows SQL*Plus.
At this stage, you can decide on the following options:
- Contact the end user of that session and ask to terminate the operation on the resource.
- Disconnect or terminate the session using the
ALTER SYSTEM DISCONNECT ... KILL SESSIONstatement. - Refrain from executing the current operations because users still have active operations on the resource.