In designing applications, we take scalability into consideration, i.e. we want the application to simultaneously handle multiple user requests. When transactions run simultaneously, the result can be conflicting if there is no concurrency control. One way of implementing this control is the use of locks on a database resource (like a row, a range of rows, or a table).
Understanding database locking is necessary to properly code an application for concurrent/simultaneous access. Locks maintain data integrity by allowing only one transaction to modify a database object.
There are 2 major types of locks, Exclusive and Shared locks.
Shared (S) lock
Shared lock mode allows sharing of the resource among concurrent transactions. It is also called a read lock. Multiple transactions reading data can acquire shared locks to prevent concurrent access by a writer (who requires an exclusive lock). No transaction can alter a resource while it has a shared lock.
Exclusive (X) lock
An exclusive lock is obtained to modify the resource in isolation. It is also called a write lock. It prevents the resource from being shared. The first transaction to lock a resource in exclusive mode is the only transaction that can modify the resource until the lock is released.
Implicit lock
Locks can be acquired either implicitly or explicitly. Implicit locks are enforced by the database, while explicit locks are enforced by the programmer.
In processing a transaction, the database will automatically obtain the appropriate locks when executing DML/DDL. A statement that reads an object will obtain a read lock/shared lock; a statement that modifies an object will obtain a write/exclusive lock. The DBMS releases the lock at the end of the transaction.
Of course, the locking mechanism used by each DBMS will differ, but to maintain the ACID properties of a transaction, it is necessary that
- DML statements acquire an implicit exclusive row lock on the row or range to be modified and a shared lock on the table.
- DDL statements acquire an implicit exclusive lock on the table.
Explicit lock
Some transactions because of their nature may need to reserve access to all required resources in advance. The reason may be to prevent other sessions from modifying objects critical to the operation. The transaction can explicitly lock data and possess the exclusive right to it till a commit or rollback is issued. Explicit lock overrides the default implicit locking.
In MySQL database, the syntax for acquiring locks explicitly is lock tables car write;.
Deadlock
A deadlock is a situation where two database transactions wait for each other to release a lock.
In the diagram above, transaction 1 holds a lock on the car table and requires a lock on the driver table, while simultaneously, the case is reversed for transaction 2. Transaction 1 has to wait for transaction 2 to release the lock, but transaction 2 will not release it until the lock held by transaction 1 is freed. At this point, both transactions come to a standstill, forcing the DBMS to abort one of the transactions.
Conclusion
It is important to remember that locks will often be held even after the statement has been completed. A transaction may be busy with another activity while holding locks on tables because of statements issued earlier. For example, an application that requires user input as parameters within a transaction. This bad design, which causes deadlocks, can be prevented by releasing locks earlier, issuing commits or rollbacks within a long-running transaction, or splitting into different transactions.