Pessimistic Concurrency: LOCKING
This page is part of the book SQL-99 Complete, Really, by Peter Gulutzan & Trudy Pelzer. The authors have graciously allowed us to reproduce the contents of the book here. Because the book is about the SQL-99 standard, the contents of this and other pages in the book may not directly apply to MariaDB. Use the navigation bar to navigate the book.
The most common and best-known way to eliminate some or all of the transaction concurrency phenomena is locking. Typically, a lock works like this:
|"LOCK" the desired object||...|
|...||Wait: desired object is locked|
|read and/or change||...|
|read and/or change||...|
|Commit (which releases locks)||...|
|...||"LOCK" the desired object|
Here, the Object being locked might be a Column, a row, a page, a Table or the entire database. Incidentally, when a lock is on a Column, locks are said to be "finely granular"; when a lock is on a page or Table or database, locks are said to be "coarsely granular". DBMSs with coarse-granularity locking have less concurrency (because a lock on row#1 causes an unnecessary lock on other rows as well), but are efficient despite that because: the coarser the granularity, the fewer locks exist, and therefore searching the list of locks is quicker. At this moment, it appears that the majority of important DBMSs support locking by row, with some (non-standard) optional syntax that allows locking by Table.
A lock is much like a reservation in a restaurant. If you find that your desired seat has already been taken by someone who came before you, you must either wait or go elsewhere.
Usually an SQL DBMS supports at least two kinds of locks: "shared locks" and "exclusive locks". A shared lock exists because there is nothing wrong with letting two transactions read the same row; concurrency can only cause trouble if one transaction or the other is updating. Therefore, at retrieval time, a shared lock is made, and this shared lock does not block other transactions from accessing the same row (with another retrieval). At change time, the shared lock is upgraded to an exclusive lock, which blocks both reads and writes by other transactions. The use of different kinds of locks is something that distinguishes an SQL DBMS from a DBMS that depends on the operating system (operating systems like MS-DOS support exclusive locks only).
The famous irritant with a lock-based concurrency resolution mechanism is the "deadlock" (or deadly embrace), which goes like this:
|Lock Row #1||...|
|...||Lock Row #2|
|Attempt to Lock Row #2 -- WAIT, because it's locked||...|
|...||Attempt to Lock Row #1 -- WAIT, because it's locked|
Since Txn#1 is waiting for Txn#2 to release its lock, but Txn#2 is waiting for Txn#1 to release its lock, there can be no progress. The DBMS must detect situations like this and force one transaction or the other to "rollback" with an error.
Locking is reliable and popular. However, it is sometimes criticized for being based on an excessively pessimistic assumption: that something which you read could be something that you will change. The result is a profusion of "shared locks", the great majority of which turn out to be unnecessary because only a relatively small number of rows are actually updated.