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.