Locks

A lock in a database performs a similar function to a lock on a door—it prevents access, but to data
instead of a room. Locks are used to isolate activity during a transaction and are automatically released
when a transaction is concluded through a COMMIT or a ROLLBACK.
There are two basic types of interactions with data, which call for two different types of locks. When you read a piece of data, you don’t care if other people also read it. This type of data sharing is the core of a multiuser database. This requirement calls for a shared lock, sometimes known as a read lock. Many different people can read the same data, and many shared locks can be placed on an individual row. Shared locks prevent the problems of non-repeatable reads.

But since there is only one physical copy of a row in a database, you cannot have more than one user writing to a row at a time. To enforce this limitation, and to avoid problems like lost updates, the database uses exclusive locks, sometimes known as write locks. An exclusive lock prevents more than one user from accessing a piece of data.
You may have noticed our strict use of the phrase “piece of data” rather than “row.” Locks are a physical
entity placed on a physical entity. A lock can be placed on a row, or the page that row resides in. As you
will quickly realize in the discussion of contention that follows, page locks can cause a lot more performance
degradation than a row lock. Oracle was one of the first databases to support row locking, but other
databases have gradually implemented this level of lock granularity.

Since an exclusive lock only allows a single user for a piece of data, the database cannot issue an exclusive
lock for a piece of data that already has a shared lock on it. This limitation makes sense from the point of
the user who is writing data, as well as the user who is reading the data. If the database allowed another
user to change the data in the middle of your transaction, the result could be either a lost update, a
nonrepeatable read, or some phantom data.