Concurrency and locks
Concurrency is the ability of more than one application process to access the same data at essentially the same time.
An application for order entry is used by many transactions simultaneously. Each transaction makes inserts in tables of invoices and invoice items, reads a table of data about customers, and reads and updates data about items on hand. Two operations on the same data, by two simultaneous transactions, might be separated only by microseconds. To the users, the operations appear concurrent.
Why Db2 controls concurrency
- Lost updates
- Without concurrency control, two processes, A and B, might both read the same row from the database, and both calculate new values for one of its columns, based on what they read. If A updates the row with its new value, and then B updates the same row, A's update is lost.
- Access to uncommitted data
- Also without concurrency control, process A might update a value in the database, and process B might read that value before it was committed. Then, if A's value is not later committed, but backed out, B's calculations are based on uncommitted (and presumably incorrect) data.
- Unrepeatable reads
- Some processes require the following sequence of events: A reads a row from the database and then goes on to process other SQL requests. Later, A reads the first row again and must find the same values it read the first time. Without control, process B could have changed the row between the two read operations.
To prevent those situations from occurring unless they are specifically allowed, Db2 might use locks to control concurrency.
How Db2 uses locks
A lock associates a Db2 resource
with an application process in a way that affects how other processes
can access the same resource. The process associated with the resource
is said to hold
or own
the lock. Db2 uses
locks to ensure that no process accesses data that has been changed,
but not yet committed, by another process. For XML and LOB locks, Db2 also
uses locks to ensure that an application cannot access partial or
incomplete data
Locks might cause contention, which degrades performance, including situations such as suspensions, timeouts, and deadlocks.