# Database: Transactions: Locking

## Lock types - unlocked, read, write

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

- Unlocked. It's available for reading or writing.
- Read/shared lock. It can be read, but not written. This is someimes called a shared lock because many users can lock the same file for reading. Altho many users can read a file simultaneously, no one can lock the file for writing until all users who have locked it for reading have released it.
- Write/exclusive lock. Files that are locked for writing may not be read or written by any other users.

## 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 implmentation overhead.
Field locking
  A few databases support locking a single field. The overhead for managing this level of locking is so high that it is generally considered unacceptable.

## Deadlocks

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.