Database: Transactions: Locking
Lock types - unlocked, read, write
A particular resource may be in three kinds of lock states.
- Unlocked. A resource is available.
- Shared lock (Read). If a user has a shared lock on a resource,
other users can still get their own shared lock on it.
For files the shared lock is 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.
- Exclusive lock (Write). Resources that have an exclusive lock
can not be locked by any other user. For files this type of lock, is used for writing.
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.
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.