In this post, we'd like to talked about 2 topics:
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.
greatv service
👍