DATABASE LOCKS

DATABASE LOCKS

Rating:

When many users are performing transactions on the same database, database locks become inevitable. When a resource is locked, database users are put in a queue on a First In First Out (FIFO) basis for accessing that resource.

In this article, a number of ways have been described to detect and resolve database lock conflicts. A special type of lock known as a deadlock is also described in detail.

A database lock provides the current user with an exclusive control over a resource till the transactions on a row/rows are either committed or rolled back.

Note: Locks can be acquired on one row, multiple rows, or an entire table.

Need for database locks

Database locks can be acquired explicitly, but Oracle can automatically employ an implicit lock on the required row/rows. Database locks are employed to ensure data integrity and also to avoid lock conflicts.

Exception to database locks

Queries form an exception to database locks. Queries never fail even if a row or multiple rows has/have acquired a lock/locks. This is because queries bypass locks by using a pre-locked image of data, which is stored in the undo tablespace.

There are two ways to obtain locks on a database:

1. Explicit
2. Implicit

Note: Sometimes an entire table is locked using the LOCK TABLE command to prevent the slowing down of transactions against a database.

An example

Consider that "employees" and "salary" are the names of two tables that are required to be locked. The SQL statements that are used to obtain a database lock are given below:

LOCK TABLE A1.employees, A1.salary
in exclusive mode;

Result: Tables locked
Assumption: The examples in this article are based on the A1 database. All the tables are in this A1 database.
Note: EXCLUSIVE is the highest possible level of a lock.

Database lock mode

The table mentioned below provides information about various lock modes that can be acquired at the table level:

Lock mode	Description
ROWSHARE	It is automatically acquired when the SELECT statement is given with the FOR UPDATE clause.
ROW EXCLUSIVE	It is automatically acquired with the UPDATE, INSERT, or DELETE commands.
SHARE	It is automatically acquired with the CREATE INDEX command.
SHARE ROW EXCLUSIVE	It is a special lock mode, similar to the EXCLUSIVE lock mode.
EXCLUSIVE	It avoids the execution of the INSERT, UPDATE, DELETE, CREATE INDEX, ALTER TABLE, DROP TABLE, and SELECT commands with the FOR UPDATE clause.

Note: Database locks are released whenever a commit or a roll back statement is issued.

Obtaining a lock on a particular row by use of the SELECT statement with the FOR UPDATE clause:

If a row in a STUDENT table (in which student_rollno = 19) is required to be locked explicitly, then the SQL statement that must be issued to acquire a lock is as given below:

SELECT* from A1.student
where student_rollno = 19
FOR UPDATE;

NOWAIT statement

When the NOWAIT statement is used with the LOCK TABLE statement, the resource, which has already been locked by a user, is locked explicitly by another user, who has issued the NOWAIT statement.

An example of how the NOWAIT statement is issued is given below:

LOCK TABLE employees, salary
in EXCLUSIVE mode NOWAIT;

In this example, suppose a user U2 has issued this statement. The employees and salary table was locked exclusively by another user U1 and now the lock has been acquired by the user U2.

Situations in which lock conflicts need to be resolved

There are some situations in which database lock conflicts need to be resolved manually. Some of them are mentioned below:

1. If a user makes an update, but forgets to perform a commit operation.
2. In case of long running transactions
3. Use of high locking levels unnecessarily

Detecting lock conflicts

Hitherto, the practice was to query against V, V, V, and V to obtain information on who is locking which resource.

However, in Oracle 10g, this is extremely easy by using the EM Database Control.

Another alternative to resolve a lock is to identify the session, which is holding the lock, and then killing that session by clicking the kill session tab in the database locks window of the EM Database Control.

Special situation of lock

A deadlock is a special type of lock. In a deadlock scenario, one or more users are waiting for a resource that is locked by a different user. The statement issued by a user that detects the deadlock for the first time, rolls back the statement.

The error message that is returned is ORA-00060: deadlock detected while waiting for the resource.

The figure given below depicts a deadlock:


Here, two database users are simultaneously waiting for resources in a circular fashion. In such a case, none of the users can access the resource, and this vicious circle continues, resulting in a deadlock situation.

Among the DBAs, a deadlock is considered as the source of trouble. There is no solution to avoid deadlocks.

A deadlock condition is also known as starvation, or deadly embrace.

The example given below describes a deadlock.

There are two users, U1 and U2, who are performing transaction T1 and T2 respectively on the table given below:

Transaction (T1) by user U1	Transaction (T2) by user U2	Time of transaction
UPDATE student set max_marks = 100 where stud_rollno = 19;	UPDATE student set stud_name = 'Smith' where stud_rollno = 20;	10:15
UPDATE student set stud_name = 'Smith' where stud_rollno = 19;	UPDATE student set max_mark = 100 where stud_rollno = 20;	10:30
ORA-00060: deadlock detected while waiting for the resource	control returned to this user	10:31

This article aids in understanding locks. It also helps to detect, and resolve lock conflicts. This article also helps to understand the situation of deadlock, which is considered as the source of trouble and is also unavoidable.

Rating: