binary & shared/exclusive locks in a DBMS.

Leave a comment

May 18, 2014 by Ozgur Ozden

When we have a database and two transactions namely T1 and T2 queries on this particular database, sometimes we may end up with a conflict. This conflict happens when either T1 or T2
wants to write and the other wants to read concurrently. When they both want to read we do not face with any conflict. Coronel (2011, p.426) depicts the situation with the following table.

no conflict

One of the main expectations from a database is reliability so we need to make sure that we don’t end up with conflicts on the database. So there are locking methods developed to prevent these conflicts. Locking is a system that can control the access to the data concurrently. We need to make sure that Locking system regulates the order of reading and writing activities in the database. In other words, locking grants permissions that which transaction has the access first and other should wait.
Common locks can be given as binary or shared/exclusive locks. These can be applied to table, database or a field Coronel (2011). Now let us try to take a look at these one by one.

Binary Lock: Normally binary locks have two states as locked which means that some transaction is being used the field or database, unlocked which means that field is not in use by any transaction. If any value is locked, let us say Z value locked as LOCK(Z), it can not be accessed or requested in the database until the lock is removed so other transaction is forced to wait.
There are some basic rules for binary locks and Thakur (n.d.) summarize them as below.
1- Certain transaction let us call it T1 should lock the item it will operate on as lock(Z) first. Once it is locked, it can be read or written. Any other transaction let us call it T2 should be in the wait state.
2- Transaction T1 should release the lock by unlock(Z) once the read write process completed. then the other transaction T2 may request the value of Z.
3- Transaction T1 should not issue a lock/unlock on Z which is already holds locked/unlocked state.
As an example for binary lock suppose you have a bank account and transaction T1 withdraws 100$ from your account X and deposits to account Y via transaction T2.

LOCK account(X)

READ account_balance(X)


UNLOCK account (X)

LOCK account(Y)

READ account_balance(Y)


UNLOCK account (Y)

This kind of lock is only gives access to a single transaction so it is said to be too restrictive nilavalagan (2009)

Shared lock: An access to a database granted to two transactions namely T1 and T2 if they both has requested to read only particular information from the database. If T1 has a shared lock on the information Z to read the information, another transaction T2 may have the same shared lock on information Z so they both can read.
Particular information, Z, in the database can have the following states
1- unlocked
2- read_lock(Z) – Shared lock
3- write_lock(Z) – Exclusive lock

Exclusive lock: If we want to write information into the database, we need to use exclusive lock. Suppose transaction T2 would like to update the information Z in the database so we need to issue an exclusive lock for T2 over the item Z, Coronel (2011). This means that only T2 can operate on this particular information. Since exclusive locks can be granted one at a time, other transaction must forced to wait and no other locks can be applied on the same information.
There are some rules for exclusive locks as indicated by nilavalagan (2009)

1. Transaction T1 should either perform a read_lock(Z) or write_lock(Z) before reading the item Z.
2. Transaction T1 should perform write_lock(Z) before start writing or updating the item Z
3. Transaction T1 should finish the process by performing an unlock(Z) on Z.
4. Transaction T1 can not issue a new lock on item Z if it is already shared or exclusively locked.

As a conclusion, data locking in extremely important to have consistent and reliable data. We should not forget to lock/unlock database as soon as we finish modifying. properly constructed locking system increases the data reliability.

Coronel (2011) Database Systems: Design, Implementation, and Management. 9th Edition, Cengage Learning
Dinesh Thahkur (n.d.) What is Lock? Type of Lock in DBMS.(Online) Available at: [Accessed at: 12.10.2013 ]
nilavalagan (2009) LOCKING TECHNIQUES FOR CONCURRENCY CONTROL (Online) Available at: [Accessed at: 12.10.2013 ]
Silberschatz, Database systems concepts. Lecture notes (6th ed.) McGraw Hill (Online) Available at: [Accessed at: 11.10.2013 ]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: