Database: Transactions: Locking

Lock types - unlocked, read, write

A particular resource may be in three kinds of lock states.

Lock granularity - database, table, page, row, field

To prevent conflicts, various parts of a database may be locked, typically by the DBMS in the process of carrying out a transaction, but in some cases explictly by the user or DBA. In a database with a high level of concurrent usage, locking at a higher level, which is easier, also causes the greatest performance degradation. Typical levels of locking are given below.
Database locking
The entire database is locked while it is being updated by one user. This is simple to do, but is often unacceptable for normal usage. It may be done when the DBA is doing some maintenance on the database (backing up, restoring, reorganizing, ...).
Table locking
In some databases, a table corresponds to a file. The operating system file locking facilities can be used to lock a table. Or the database can enforce the locking. Efficient, but may lead to unacceptable congestion in a busy database.
Page locking
A page is a disk concept, not a logical database concept. Disk files are divided into pieces that are written into physically separate sections of the disk, eg, disk sectors. Instead of locking an entire table, only physical access to the particular disk page needs to be locked in some cases. This operation is typically efficiently supported by the operating system and has the advantage of leaving the remaining portions of the table accessible by other users.
Row locking
This prevents any changes to a particular row while a transaction is being carried out. This blocks few other concurrent users, but has a high implementation overhead.
Field locking
Few databases support locking a single field. The overhead for managing this level of locking is so high that it is generally considered unacceptable.


A deadlock occurs when two or more users are mutually blocked by each others locks on resources. Rarely occurs if a DBMS is creating the locks and users are not explicitly locking resources.

[TO DO] Example. Prevention, detection, and avoidance.