Skip to content

How Oracle Unlock Table

In this post, we'd like to talked about 2 topics:

  1. Lock Table
  2. Unlock Table

Lock Table

Oracle provides LOCK TABLE statements to let users lock table as their needs. For example:

SQL> conn hr/hr@orclpdb
Connected.
SQL> lock table employees in exclusive mode;

Table(s) Locked.

The lock mode for LOCK TABLE syntax can be one of the following levels.

  • ROW SHARE
  • ROW EXCLUSIVE
  • SHARE UPDATE
  • SHARE
  • SHARE ROW EXCLUSIVE
  • EXCLUSIVE

But how to make sure the table is locked? We can issue a query on V$LOCKED_OBJECT to determine it.

SQL> show user
USER is "SYSTEM"
SQL> column owner format a20;
SQL> column object_name format a20;
SQL> column object_type format a10;
SQL> select o.owner, o.object_name, o.object_type, l.locked_mode from all_objects o, v$locked_object l where o.object_id = l.object_id;

OWNER      OBJECT_NAME          OBJECT_TYPE          LOCKED_MODE
---------- -------------------- -------------------- -----------
HR         EMPLOYEES            TABLE                          6

The lock ID 6 means EXCLUSIVE lock mode.

After locking a table, other operations on the table may seem waiting and hanging of their SQL statements, this is expected behavior, they need to wait until the lock released.

Next, let's see how to release a table lock.

Unlock Table

There's no explicit UNLOCK TABLE statement to release the lock. You should use one of the following techniques to unlock tables.

COMMIT or ROLLBACK

COMMIT and ROLLBACK are transaction control statements, both have the ability to break the lock easily. If data is correctly changed, you can issue a COMMIT to confirm. Otherwise use a ROLLBACK to refuse changes. For example:

SQL> rollback;

Rollback complete.

Please note that, although SAVEPOINT is also a transaction control Statement, it cannot terminate a transaction, so no lock will be released.

Data Definition Language (DDL)

Since valid DDL implicitly commits all changes, it breaks all locks of the transaction above. For example:

SQL> alter table employees modify (employee_id number(10));

Table altered.

Please note that, TRUNCATE and GRANT statements are also DDL which moves LAST_DDL_TIME definitely.

User Exit

If an user exits its session normally, an implicit COMMIT follows. That releases all locks of the session.

Kill Session

If you can't control the session which owns locks, you can kill the session to force it to release locks, an implicit ROLLBACK follows.

In summary, to release a table lock, we need to issue a signal to terminate the transaction, which is essentially a COMMIT or ROLLBACK.

As you can see, it's pretty easy to unlock a table either intentionally or unintentionally. So, if you want a more permanent solution, you may consider freezing the table by entering READ ONLY mode.

2 thoughts on “How Oracle Unlock Table”

Leave a Reply

Your email address will not be published. Required fields are marked *