What does the locking method of concurrency control do?

In this type of protocol, any transaction cannot read or write data until it acquires an appropriate lock on it. There are two types of lock:

1. Shared lock:

• It is also known as a Read-only lock. In a shared lock, the data item can only read by the transaction.
• It can be shared between the transactions because when the transaction holds a lock, then it can't update the data on the data item.

2. Exclusive lock:

• In the exclusive lock, the data item can be both reads as well as written by the transaction.
• This lock is exclusive, and in this lock, multiple transactions do not modify the same data simultaneously.

There are four types of lock protocols available:

1. Simplistic lock protocol

It is the simplest way of locking the data while transaction. Simplistic lock-based protocols allow all the transactions to get the lock on the data before insert or delete or update on it. It will unlock the data item after completing the transaction.

2. Pre-claiming Lock Protocol

• Pre-claiming Lock Protocols evaluate the transaction to list all the data items on which they need locks.
• Before initiating an execution of the transaction, it requests DBMS for all the lock on all those data items.
• If all the locks are granted then this protocol allows the transaction to begin. When the transaction is completed then it releases all the lock.
• If all the locks are not granted then this protocol allows the transaction to rolls back and waits until all the locks are granted.

3. Two-phase locking (2PL)

• The two-phase locking protocol divides the execution phase of the transaction into three parts.
• In the first part, when the execution of the transaction starts, it seeks permission for the lock it requires.
• In the second part, the transaction acquires all the locks. The third phase is started as soon as the transaction releases its first lock.
• In the third phase, the transaction cannot demand any new locks. It only releases the acquired locks.

There are two phases of 2PL:

Growing phase: In the growing phase, a new lock on the data item may be acquired by the transaction, but none can be released.

Shrinking phase: In the shrinking phase, existing lock held by the transaction may be released, but no new locks can be acquired.

In the below example, if lock conversion is allowed then the following phase can happen:

1. Upgrading of lock (from S(a) to X (a)) is allowed in growing phase.
2. Downgrading of lock (from X(a) to S(a)) must be done in shrinking phase.

Example:

The following way shows how unlocking and locking work with 2-PL.

Transaction T1:

• Growing phase: from step 1-3
• Shrinking phase: from step 5-7
• Lock point: at 3

Transaction T2:

• Growing phase: from step 2-6
• Shrinking phase: from step 8-9
• Lock point: at 6

4. Strict Two-phase locking (Strict-2PL)

• The first phase of Strict-2PL is similar to 2PL. In the first phase, after acquiring all the locks, the transaction continues to execute normally.
• The only difference between 2PL and strict 2PL is that Strict-2PL does not release a lock after using it.
• Strict-2PL waits until the whole transaction to commit, and then it releases all the locks at a time.
• Strict-2PL protocol does not have shrinking phase of lock release.

It does not have cascading abort as 2PL does.

Concurrency control and locking is the mechanism used by DBMSs for the sharing of data. Atomicity, consistency, and isolation are achieved through concurrency control and locking. See ACID Properties.

When many people may be reading the same data item at the same time, it is usually necessary to ensure that only one application at a time can change a data item. Locking is a way to do this. Because of locking, all changes to a particular data item will be made in the correct order in a transaction. See isolation. 

The amount of data that can be locked with the single instance or groups of instances defines the granularity of the lock. The types of granularity are illustrated here are:

• Page Locking
• Cluster Locking
• Class or Table Locking
• Object or Instance Locking

Additional information on this subject along with examples of multi-user considerations can be found in the Object Database Handbook.

More Detail on Concurrency Control and Locking

• Page Locking
• Cluster Locking
• Class or Table Locking
• Object or Instance Locking

Principal

Barry & Associates, Inc.

The Savvy Manager's Guide

Douglas K Barry is also the author of a book that explains Web Services, service-oriented architecture, and Cloud Computing in an easy-to-understand, non-technical manner.

Web Services, Service-Oriented Architectures, and Cloud Computing: The Savvy Manager's Guide (Second Edition)

by with David Dick

This is a guide for the savvy manager who wants to capitalize on the wave of change that is occurring with Web Services, service-oriented architecture, and—more recently—Cloud Computing. The changes wrought by these technologies will require both a basic grasp of the technologies and an effective way to deal with how these changes will affect the people who build and use the systems in our organizations. This book covers both issues. Managers at all levels of all organizations must be aware of both the changes that we are now seeing and ways to deal with issues created by those changes.

What is the purpose of locking in concurrency control?

What is concurrency control and methods to do it?

What is locking and its uses?