Any operation that involves more than a single CPU instruction is inherently non-atomic. In other words, it can be interrupted before it is finished. Interruting such an operation can leave data in an inconsistent state.

If another process starts using the inconsistent data

• that data can be corrupted
• the inconsistent data can cause the process to work incorrectly or crash.

Locking allows one process to request temporary exclusive access to the data so it can make a change and complete that change consistently before it relinquishes the lock.

Row-Level Locking

A lock can be shared or exclusive. A shared lock allows multiple processes to read the data, but will block any process requesting exclusive access to the data until the processes holding shared (read-only) locks on the data release those locks.

An exclusive (write) lock blocks any process requesting a lock (either shared or exclusive) until the exclusive lock is released. The process holding the exclusive lock can complete its update before it releases the lock. If other processes hold read locks the process requesting the exclusive lock will block until the read locks are released.

Some database systems provide a facility called Multi-Version Concurrency Control (MVCC) for database writes. A system that supports MVCC will write the new version of the data item (holding a lock on that record) and then dispose of the old record. Other processes can read the old record while the new one is being created, but processes requesting a write lock on the record will block until the transaction holding the lock has committed.

MVCC allows better concurrent access, as a non-MVCC system will block reads until the write as committed, whereas readers can see the previous version of the record in a MVCC system while the write operation is still in progress.

Table-Level Locking

At times, there may be queries that

• update or delete a large number of rows
• alter a tables structure
• come in such sheer numbers that the table is essentially treated as a FIFO queue

Some databases provide storage engines that does this by default, such as the MyISAM storage engine for MySQL. More robust databases may require perform explicit table locks. In a highly transactional, doing full table locking should be the exception rather than the norm.