Concurrency and locks

Concurrency is the ability of more than one application process to access the same data at essentially the same time.

Begin program-specific programming interface information.

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

Concurrency must be controlled to prevent lost updates and such possibly undesirable effects as unrepeatable reads and access to uncommitted data.
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.