Locks in PL/SQL

Locks in PL/SQL is a way to maintain data integrity of the database. As oracle is a multi-user platform where tables used in a database acts as a global resource being shared by multiple users at the same time.

There is a possibility that the data may become inconsistent due to concurrent processing of data by multiple user at the same time. Therefore, locks play an important role to maintain concurrency control ensuring data integrity of stored data in the database.

PL/SQL: Types of Lock

Following are the two different types of locking in Oracle:

1. Implicit Locking
2. Explicit Locking

Implicit Locking

It is an automatic locking of the database done by the oracle engine on the basis of following two factors:

1. Type of lock to be applied.
2. Level of lock to be applied.

Let's see both of these factors one by one:

1. Type of lock to be applied

Depending upon the type of operation (read or write) to be performed on a database, there are two types of locks.

2. Level of lock to be applied:

Locking can be done on basis of three levels:

1. Row Level: It is used when a condition is applied in a query on a single row(or record) using WHERE clause.
2. Page Level: It is used when the condition is applied in a query on a certain set of data(certain records) using WHERE clause.
3. Table Level: It is used when the condition is applied in a query on the entire table of data(certain records) using WHERE clause.

Explicit Locking

This is a user-defined locking done on a database by the user according to their granted table privileges. Such type of locking overrides the implicit locking. Explicit Locking can be done by using one of the following ways:

1. Select...For Update statement

2. Lock Table statement

1. Select…For Update statement:

Following is the syntax for this statement:

Syntax:

SELECT * FROM tablename WHERE condition For Update Nowait;

Let's take a few example and see,

Suppose two clients, client X and client Y are performing the transactions on the same database table flight_detail

Following is the query run by Client X:

SELECT * from flight_detail where city='DELHI' For Update;

When the above SELECT statement is executed, the oracle engine automatically locks the record with city as Delhi and this lock can be released only when Client X will execute COMMIT or ROLLBACK command.

Now let's see the query run by Client Y:

SELECT * from flight_detail where city='DELHI' For Update;

Now the client Y executes the SELECT statement for the same record that has been already locked by the oracle engine for Client X. So in this case the client Y has to wait till the client X release the lock by executing the COMMIT statement.

Therefore, to overcome the unnecessary waiting time NOWAIT option can be used to inform the oracle engine that the record has already been locked and it can terminate the statement.

So if the Client Y executed the query with NOWAIT option then,

SELECT * from flight_detail where city='DELHI' For Update Nowait;

With the execution of the above statement, the oracle engine displays the message in the output Resource is Busy.

NOTE:

Select…For update statement cannot be used with the group functions, set operators ,group by and distinct clause.

Lock Table statement

We can also acquire lock on a table by executing the Lock table statement.

Following is the syntax:

LOCK TABLE tablename in lock mode WAIT/NOWAIT;

where,

Lock mode can be one of the following:

• EXCLUSIVE: allow the queries on the locked table.
• SHARE: allow queries but restricts UPDATE on a table.
• ROW EXCLUSIVE: allow concurrent access to the table by multiple users but restricts from locking table in exclusive or share mode.
• SHARE ROW EXCLUSIVE: allow to view the entire table records but restricts locking the table in share mode and also restricts UPDATE on a table.

WAIT indicates that the oracle engine will wait till the resource is freely available.

NOWAIT indicates that the oracle engine will not wait for resource to be available but would rather display the message to the user that Resource is Busy.