ORACLE Table Locking

ORACLE Table Locking

Locking protects a table when several users are accessing the same table. Locking is a concurrency control technique in oracle. It helps in data integrity while allowing maximum concurrency access to data. Oracle offers automatic locking whenever situation requires. This is called implicit locking.

When a transaction updates a row, it puts a lock so that no one can update the same row until it commits. When another transaction issues an update to the same row, it waits until the first one either commits or rolls back. After the first transaction performs a commit or rollback, the update by the second transaction is executed immediately, since the lock placed by the first transaction is now gone.

READ COMMITTED: If row locks are obtained by a certain transaction, then any other transaction that contains DML needs to wait until the row locks have been released by that particular transaction.

Types of locking

1. Implicit locks: Oracle automatically locks the rows whenever user performs DML operations.

2. Explicit locks: Provided by user and can be Row and Table Level.

• Row level Locks (TX): Used to lock selected rows of table. It is imposed by "for update" clause in select.

Select for Update

The Select for Update statement allows you to lock the records in the cursor result set.

You are not required to make changes to the records in order to use this statement.  

The record locks are released when the next commit or rollback statement is issued.

• Table level Locks (TM): Used to lock complete table and can be done in below modes

1) Shared mode
3) Exlcusive mode

4) Deadlock

Shared Lock 
This type is placed on a record when the record is being viewed (Read Operation using Select).
So, it permits other user to query the data on the tables but doesn’t allow any change. There can be multiple shared lock on a record at a time.

Exclusive lock
This is placed when Insert, Update or Delete command is performed (Write Operation).  There can be only one exclusive lock on a record at a time.

Deadlocks

A deadlock occurs when two or more sessions are waiting for data locked by each other, resulting in all the sessions being blocked. Oracle automatically detects and resolves deadlocks by rolling back the statement associated with the transaction that detects the deadlock.

The number one cause of deadlocks is un-indexed foreign keys

• if you update the parent table's primary key the child table will be locked in the absence of an index
• if I delete a parent table, the entire child table will be locked, again in the absence of an index.

Possible Causes of Lock Conflicts

Uncommitted changes

Long-running transactions

Unnecessarily high locking levels

NOTE- You can lock the tables manually with the below Query (Manual Locking)

LOCK TABLE employees IN EXCLUSIVE MODE;

Now, let’s understand this practically.

First understand the ACID property.

Atomicity: - In a transaction involving two or more discrete pieces of information, either all of the pieces are committed or none are.

Consistency: - A transaction either creates a new and valid state of data, or, if any failure occurs, returns all data to its state before the transaction was started.

ISOLATION: - Users (Sessions) don’t affects each other. So, if you see the below screenshot.

Session1(Black)- We have inserted New Department Finance.

Session2(Red)- We have inserted New Department Accounts.

But the department inserted at Session1 is not visible in Session2 and vice versa.

Durable: - Once data is committed (in the same session where changes was made), it will be permanent and visible in all the sessions to all the users.

We have to perform commit in both the sessions (Session1(Black) and Session2(Red)) so that changes will be visible in all the sessions. Check the screenshot below.

SHARED Lock: -  See the screenshot below.

Session1(Black)- Table is locked in Shared mode. You can also apply locks at other session as it is shared. Refer screenshot below allowing shared lock in both the sessions.

Session2(Red)- We won’t be able to Insert/Update/Delete anything until Table is locked in another session. Example is same as Exclusive mode. Refer screenshot below.

EXCLUSIVE Lock: - See the screenshot below.

Session1(Black)- Table is locked in Exclusive mode. You also can not apply lock in second (any session) if Table is Exclusively locked in First session (any session). Refer screenshot below- When we tried to change the Lock it went into hung state.

Session2(Red)- We won’t be able to Insert/Update/Delete anything until Table is locked in another session. Refer screenshot below- 

In the above screenshot Table is locked in Exclusive mode. So, to update the rows in Second session you need to release the lock in First Session.

Commit will release the EXCLUSIVE Lock in first session and data will be updated in Second session. Commit the changes to make them permanent.

Deadlock: - Deadlock will be released automatically by the ORACLE.

Session1(Black)- We have updated one record.

Session2(Red)- We have updated another record.

Now when we try to fire the same update (Session2 Update) in Session1, then it will go to hung state (Session1).

Now, try to fire the same update (Session1- first update) in Session2 and see what happens.

See the above screenshot, ORACLE has thrown an ORA- 00060 Deadlock Detected while waiting for Resource.

Locking Strategy

Lock Mode	Explanation
ROW SHARE	Allows concurrent access to the table, but users are prevented from locking the entire table for exclusive access.
ROW EXCLUSIVE	Allows concurrent access to the table, but users are prevented from locking the entire table with exclusive access and locking the table in share mode.
SHARE UPDATE	Allows concurrent access to the table, but users are prevented from locking the entire table for exclusive access.
SHARE	Allows concurrent queries but users are prevented from updating the locked table.
SHARE ROW EXCLUSIVE	Users can view records in table, but are prevented from updating the table or from locking the table in SHARE mode.
EXCLUSIVE	Allows queries on the locked table, but no other activities.

Different Locking Modes

Row-Level Locks (TX) 
Table Level Locks(TM) 

The V$Lock view has an ‘LMODE’ column which indicates the Lock mode in which the session holds the lock. The value of LMODE can vary from 0 to 6. The higher the value of LMODE, stronger is the lock.

• 0 - None 
• 1 - Null (NULL) 
• 2 - Row-S (RS) 
• 3 - Row-X (RX) 
• 4 - Share (S) 
• 5 - S/Row-X (SRX) 
• 6 - Exclusive (X) 

Let’s try out an example where we will update the Salary column of the EMP_SAL table for a particular Employee and use the V$LOCK view to check the corresponding locks it acquires:

Session1(Black)- Update salary of Employee and check the locks and Locking modes with the help of below Query.

Select

A.SID, A.Type, A.Lmode, A.Request

From V$Lock A,V$Session B Where A.SID=B.SID And B.Username='SYS';

So, it acquires an EXCLUSIVE row-level(TX) lock to the row it updates and a ROW-EXCLUSIVE table-level(TM) lock on the EMP_SAL table.

The session has not committed yet.

 Session2(Red)- Now let’s say from another session, the user wants to drop the table EMP_SAL which would require an EXCLUSIVE LOCK 

on the EMP_SAL table.

It throws an error because an EXCLUSIVE lock is denied on EMP_SAL as another session has already acquired a ROW-EXCLUSIVE(RX) lock on EMP_SAL 

and RX is not compatible with EXCLUSIVE(E) Mode.

To release the locks, Session1 simply needs to commit or rollback.

Lock table Statement: 

A LOCK TABLE statement manually overrides default locking. When a LOCK TABLE statement is issued on a view, the underlying base tables are locked. The following statement acquires exclusive table locks for the 
EMP_SAL table on behalf of the containing transaction:

SQL>LOCK TABLE EMP_SAL IN EXCLUSIVE MODE;

You can specify several tables or views to lock in the same mode; however, only a single lock mode can be specified for each LOCK TABLE statement.

Let’s see what the V$LOCK view has to say about the lock held by the session

LMODE  6 is EXCLUSIVE lock.

Let’s take another example where the table is locked is SHARE ROW EXCLUSIVE (SRX) mode.

LMODE 5 is SRX lock mode.

NOWAIT CLAUSE: 

You can also indicate if you do or do not want to wait to acquire the lock. If you specify the NOWAIT option, then you only acquire the table lock if it is immediately available. Otherwise an error is returned to notify that the lock is not available at this time. In this case, you can attempt to lock the resource at a later time. If NOWAIT is omitted, then the transaction does not proceed until the requested table lock is 
acquired. 

Example:

Session 1:

SQL> Lock table EMP_SAL IN SHARE ROW EXCLUSIVE MODE;

Session 2:

SQL> LOCK TABLE EMP_SAL IN SHARE MODE NOWAIT;

LOCK TABLE EMP_SAL IN SHARE MODE NOWAIT

 *ERROR at line 1:

ORA-00054: resource busy and acquire with NOWAIT specified

Note: ALTER TABLE and DROP TABLE commands have implicitly NOWAIT keyword specified.

Related Topics :
Click here to know- How to kill the session once the lock is identified.

Get involved and leave your Comments in the Box Below. The more people get involved, the more we all benefit. So, leave your thoughts before you leave the page.