Database Management Systems (DBMS?) are designed to store any amount of data that can then be retrieved or manipulated at a later date.
In order to make sure that the data can be concurrently utilised in parallel by numerous processes or users, it is important to have some form of Locking enforced so that if a particular record is being modified or created, then another process is not allowed to simultaneously adjust the same record. If databases did not appoint locks, then the data could easily be overwritten and therefore become inconsistent and corrupt.
In order to resolve this problem, there are three main types of database locks; these can be summarised as follows (Thiru, n.d.):
Binary Lock
The word binary means, two potential states, either 1 or 0, true or false, or in the context of databases, locked or unlocked.
If a database entity is being written or accessed, then a binary lock is applied until the task is completed so that other processes can perform their own reads or writes (Ozden, 2014). The problem with this approach is that it is very restrictive when the database is heavily used or is part of a concurrent model.
Shared Lock
Shared locks – which are often referred to as simply read locks – are when multiple reads are occurring on a database entity or attribute and the database will apply a shared lock between all read queries on that data (Shankar, 2012). This is to maintain parity and so that the data remains correct and intact upon retrieval. This works very well and is commissioned in most DBMS? (Callison, n.d.).
Exclusive Lock
Exclusive locks – often referred to as write locks – are employed when data is being written to the database. An exclusive lock will be taken out on the entity being written to so that no other writes can occur on that same attribute at that time (Singh, 2017). Instead a queue will be built of other writes or updates being written to the database and they will become part of what is called a transaction log/list that will be applied to the database in order when the queue moves to the next items.
Imagine a scenario where a bank account was updated when someone spent $100.
At a high level view this would simply subtract $100 dollars from the bank account; but in actual fact, it is a much more complex task to perform.
First a read lock would be applied to the account balance, then the balance would be retrieved and the read lock removed. Next a write lock would be applied to the balance and $100 subtracted from the account. The write lock would then be removed and another write lock applied to the transaction history to insert a new record to track the history of where the $100 went and when. The write lock would then be removed and the whole transaction above would be committed to the database. If there were no errors then this transaction would be successful. If not, the whole process would be reversed and rolled-back to it?s previous state.
References
Thiru (n.d.) Types of Locks [Online] Myreadingroom.co.in, Available from: http://www.myreadingroom.co.in/notes-and-studymaterial/65-dbms/538-types-of-locks.html (Accessed on 3th December 2017)
Ozden, O. (2014) Binary & shared/exclusive locks in a DBMS. [Online] Oozden.wordpress.com, Available from: https://oozden.wordpress.com/2014/05/18/binary-sharedexclusive-locks-in-a-dbms/ (Accessed on 3rd December 2017)
Shankar, A. (2012) What’s the difference between an exclusive lock and a shared lock? [Online] StackOverflow.com, Available from: https://stackoverflow.com/questions/11837428/whats-the-difference-between-an-exclusive-lock-and-a-shared-lock (Accessed on 3rd December 2017)
Callison, J. (n.d.) Database Locking: What it is, Why it Matters and What to do About it [Online] MethodsAndTools.com, Available from: http://www.methodsandtools.com/archive/archive.php?id=83 (Accessed on 3rd December 2017)
Singh, C. (2017) Concurrency Control in DBMS [Online] BeginnersBook.com, Available from: https://beginnersbook.com/2017/09/concurrency-control-in-dbms/ (Accessed on 3rd December 2017)
Featured image by:?https://blog.acolyer.org/2016/02/24/a-critique-of-ansi-sql-isolation-levels/