Oracle in a Nutshell

Locks

A database uses a system of locks to prevent transactions from interfering with each other. One transaction could interfere with another by attempting to change a piece of data that another transaction is in the process of also changing. Figure 3-1 illustrates this potential problem. 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 goes to commit the data, its change unwittingly overwrites the changes made by Transaction B, resulting in a loss of data integrity.

Figure 3-1. Transactions over time

Normally, two types of locks are used to avoid this problem. The first type of lock 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 is released when the transaction is ended by either a COMMIT or a ROLLBACK. 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, because there are no potential conflicts with read-only access. However, a read lock prevents a write lock from being placed on the data, as the write lock is an exclusive lock. In Figure 3-1, if a read lock were placed on the data when Transaction A began, Transaction ...