Who Is Holding The Lock On The Table?
Did you ever get an email or a phone call from a developer telling you they are getting an error when trying to drop a table? It sounds something like this:
“What is going on, I cannot drop table A, I am getting an error:”
SQL> drop table HR.EMPLOYEES; drop table HR.EMPLOYEES * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
The mistake that some of us do initially, is to look for blocking sessions in the database. But the above error that the developer is getting, most likely has nothing to do with blocking. Rather it has all to do with locking.
Why do we have locks in the database?
The database locks are in place to prevent incorrect updates of the data, or incorrect alters of the underlying object structures, while
multiple transactions are accessing the shared data in the database. Locks are there to help maintain the database consistency and integrity.
There are two main categories of locks:
-exclusive locks
-shared locks
There is only one exclusive lock that can be obtained on a table or row at a time.
The exclusive locks prevents the resource to be shared. An exclusive lock is obtained when the session modifies the data.
The transaction that locks the resource in exclusive mode is the only one that can modify the resource, until the exclusive lock is released.
There are many shared locks that can be obtained on a table or row. The sessions that read data usually get a shared lock on the resource.
Oracle is trying to lock the resource at the lowest level possible. There are locks at the row level and there are locks at the table level.
When you want to modify data (update one row of a table), Oracle is locking exclusively that one row, and is locking the table in shared mode.
Other sessions can access the data (read), modify other rows in the table, but cannot alter the structure of the table, or drop the table.
When someone is altering the resource (table), then an exclusive table lock is obtained. If another session has already an exclusive lock on the resource, then the exclusive table lock cannot be obtained.
Now that the locking has been understood, the error message the developer was getting is more clear.
What you need to do to help the developer out, is to find who is holding the lock, and in what mode. Once that information is known, you have 2 options:
– ask the “locking” user to release the lock
– kill the “locking” user’s session.
Below is a script to provide you the answer to the question: Who is locking the table?
col object for A30 col object_type for A12 col serial# for 999999999 col osuser for A15 col lock_mode for A25 col username for A15 select c.owner || '.' || c.object_name object, c.object_type, DECODE(a.locked_mode, 0, NONE , 1, '1 - Null' , 2, '2 - Row Share Lock' , 3, '3 - Row Exclusive Table Lock.' , 4, '4 - Share Table Lock' , 5, '5 - Share Row Exclusive Table Lock.' , 6, '6 - Exclusive Table Lock' , locked_mode, ltrim(to_char(locked_mode,'990'))) lock_mode, b.inst_id as node, b.sid, b.serial#, b.status, b.username, b.osuser from gv$locked_object a , gv$session b, dba_objects c where b.sid = a.session_id and a.object_id = c.object_id and a.inst_id=b.inst_id; --Sample Output OBJECT OBJECT_TYPE LOCK_MODE node sid SERIAL# STATUS USERNAME OSUSER ------------- ----------- ----------------------------- ----- ----- ------- ----- ------- -------- HR.EMPLOYEES TABLE 3 - Row Exclusive Table Lock. 1 397 26193 INACTIVE HRAPP drobete
If you enjoyed this article, and would like to learn more about databases, please sign up below, and you will receive
The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!
–Diana
Hi Diana,
Thank you for this great article, I have a correction for the query in the decode line as it was not working with me, so I corrected the line to be:
DECODE(a.locked_mode, 0, ‘NONE’
Also, I have a question, In most mission-critical environments, we can identify locks, but we can’t kill the corresponding sessions because it might hold an important financial transaction, my question is, do we have a way to unlock these locks without killing the session?
I don’t think you will be able to release the locks without killing the session (not that I am aware of)
NONE Is NULL —> “DECODE(a.locked_mode, 0, NULL”
[…] You can start your troubleshooting by checking regular blocking/locking issues. I wrote about that in a previous blog post, Who Is Holding The Lock On The Table? […]