CONCURRENCY AND LOCKING

CONCURRENCY AND LOCKING

Oracle locks are used to control concurrent access to data. Locks are mechanisms intended to prevent destructive interaction between users accessing the same data. Locks are used to achieve two important goals, data concurrency and read consistency. Locks in Oracle fall into two categories.

Data Locks or DML Locks : These locks protect data. Table locks lock the entire table while Row locks just locks selected rows.

Dictionary Locks : These locks protect the structure of Database objects. Dictionary locks are of two types.
Parse Locks and
DDL Locks.
In this chapter we will discuss only data locks.

Summary Of Table Locks :

Tables can be locked in Exclusive(X), Share(S), RowExclusive(RX), RowShare(RS) and ShareRowExclusive(SRX) modes. Following table shows the posiility of operations against a table when that table is locked in a particular mode. The user who locks the table can perform any operation against the table. The following table describes the posibilities for other users on a table, which is locked by a user. For example, if a table is locked in exclusive mode, then locking that table in any mode and performing DML operations is not posible for other users. In the same way if a table is locked in share mode, then locking that table in share mode and row share mode is possible for other users.


Lock Table Command :

Syntax : Lock Table Tabname1, Tabname2,…, Tabnamen In Lockmode [Nowait];

Here Tabname1, Tabname2, …. , Tabnamen are the names of tables to be locked. Lock mode is one of the modes in which a table can be locked i.e. exclusive, share, row exclusive, row share and share row exclusive. Nowait will allow the user to do other work when another user locks a table, which he accesses. If nowait is not specified, then the system will hang if another user locks the table, which he accesses, until that user releases the lock.

Implicit Locking :

Implicit locking is the locking performed by Oracle automatically without Locking by the user manually. This implicit lock is generally done when updation or deletion is performed against a table and it locks only the rows that are affected by the update or delete statement.

Select…For Update : The select for update statement will overwrite the default locking mechanism. It is used in anticipation of performing an update. The difference between select for update and Update is that select for update locks the rows earlier in a transaction.

Syntax : Select col1, col2,…, coln from tablename For Update [ NOWAIT ]

Dead Lock :

It is quite possible in a multi user environment, that two users will lock each other out of resources. It is also possible that two users will end up locking each other out of different resources. This situation is called a Dead Lock, Because each user is waiting for resources held by the other user.

When Oracle detects a dead lock, it signals an error to one of the participating transactions and rolls back the current statement of that transaction. This resolves the Dead Lock, although other users may still wait until their resource is available. The signaled user should explicitly rollback their transaction.

Dead locks can be avoided if users accessing the same tables lock those tables in the same order as each other. You should pre-define order of access for all tables in your applications and then have all applications follow the same order. If this order is followed in all applications, deadlocks will be kept to a minimum.

Duration Of Locks :

All locks acquired during a transaction are released when the transaction is committed or rolled back.