In a database application, concurrency means that multiple transactions may access or change data during overlapping periods. An auction-closing operation, for example, may update the winning bid while another transaction is working with the same data. Transactions need boundaries: start the work, commit it on success, or roll it back on failure. Keep database transactions short because open transactions consume resources and exclusive locks can prevent concurrent access. section 11.1.2 section 11.2.1
Two updates, one surviving value
Transaction A reads a price. Transaction B reads the same price before A finishes. A writes its new price; B then writes its own, overwriting A’s change. This is a lost update: without effective concurrency control, the last commit wins. section 11.2.1
Other anomalies depend on what one transaction can observe while another runs:
- Dirty read: a transaction reads another transaction’s uncommitted change, which may later be rolled back. section 11.2.1
- Unrepeatable read: reading the same data twice produces different values after another transaction commits a change. section 11.2.1
- Phantom read: repeating a query returns a different set of rows because another transaction added or deleted data. section 11.2.1
Isolation controls which effects of concurrent transactions can become visible. Complete isolation is costly in a multiuser system, so isolation levels trade some protection for performance and scalability. Choose a level by examining the anomalies an operation must avoid, rather than assuming every transaction requires the strongest setting. section 11.2.1
Table 11.1 ANSI isolation levels and the problems that they address (view table figure)
Compare the ANSI isolation levels against the listed read and update anomalies when deciding what protection a transaction needs. section 11.2.1
