Databases Reference
In-Depth Information
Locks
A database uses a system of
locks
to prevent transactions from interfering with each
other. A lock prevents users from modifying data. Database systems use locks to keep
one transaction from overwriting changes added by another transaction.
Figure 8-1
illustrates the potential problems that could occur if a system did not use
locks. Transaction A reads a piece of data; Transaction B reads the same piece of data
and commits a change to the data. When Transaction A commits the data, its change
unwittingly overwrites the changes made by Transaction B, resulting in a loss of data
integrity.
Figure 8-1. Transactions over time
Two types of locks are used to avoid this type of problem. The first is called a
write
lock
, or an
exclusive lock
. An exclusive lock is applied and held while changes are made
to data in the course of a transaction and released when the transaction is ended by
either a COMMIT or a ROLLBACK statement. A write lock can be held by only one
user at a time, so only one user at a time can change that data.
Some databases also use
read locks
, or
shared locks
. A read lock can be held by any
number of users who are merely reading the data, since the same piece of data can be
shared among many readers. However, a read lock prevents a write lock from being
placed on the data, as the write lock is an exclusive lock. In
Figure 8-1
, if a read lock
were placed on the data when Transaction A began, Transaction B would be able to read
the same data but would be prevented from acquiring a write lock on the data until
Transaction A ended.
