Concurrency of DB2 Common database

In the domain of database management systems (DBMS), the term "concurrency" is used to represent the ability of more than one application to access the same data at the same time (from a user's perspective). Because one of the main advantages of a DBMS is that it can share data across multiple users and multiple applications, the database system should provide a way to manage concurrent access to data. The DBMS must ensure consistent state of data and integrity of the data.

One way to achieve this effect is to implement a serial-only (serial-only) pattern to handle database requests. That is, each transaction waits for another transaction (with a higher priority or before it starts) to complete its work. However, the level of performance generated by this approach is simply unacceptable for current online systems and customer anomalies.

Alternatively, the DBMS can manage access to data from multiple applications in a locked way. A lock is a software mechanism that allows the greatest possible throughput (by maximizing concurrent access to data) while maintaining data integrity and consistency.

The importance of concurrency control

Without an effective method of controlling concurrency, data integrity and consistency can be compromised. The DBMS must protect the database to prevent the following conditions from occurring:

Lost Updates--suppose application A and application B read the same row in the database at the same time, and all the new values are computed for one of the columns. If application a first updates the row with its new value, and then application B updates the same row, the first update (executed by application a) is lost.

Non-repeatable read-Some application processes may require the following sequence of events to be completed: program A reads a specific row from the table and then continues with other SQL requests. Later, program A reads the starting line again, and must find the same value in all columns as the first read. Without proper concurrency control, another application might modify the row data between the two read operations.

Access to uncommitted data-application A updates the values of some columns in a row, and application B reads the new (updated) value of the row before committing the modification. If application A then "revokes" the update value (either through a SQL ROLLBACK statement in program logic, or because an error is automatically rolled back by DB2 UDB), application B's processing of the row is based on uncommitted (and possibly incorrect) data.

While maintaining data integrity, the ability to provide simultaneous access to data by multiple applications is called concurrency control.

Lock

A lock is a software mechanism that is used by DB2 UDB to complete concurrency control. A lock is essentially a control block that associates a DB2 UDB object or resource with an application and controls how other applications access the same object or resource. The application associated with the DB2 UDB resource is known as "hold" or "own" the lock.

By using a lock, DB2 UDB (Managing the database) can prevent these types of problems from occurring. The DB2 UDB manages these locks in conjunction with another MVS address space irlm. IRLM will track these locks and their owners to determine whether the DB2 UDB resource requested by the application is available for this type of work. Resources can be locked or shared, depending on the type of processing that is done by the "holder" of the lock on the current resource, and the type of processing that is expected by the requesting application.

Lock mode

The two most commonly used lock modes are shared and exclusive. A shared lock is associated with a read-only operation, which means that the application holding the lock can read the data and other applications can read it. An exclusive lock is associated with a write operation, which means that the application holding the lock is qualified to update the data, but the data cannot be used by other applications until the lock owner completes the update (submits the modification to the database) and releases the lock.

DB2 UDB and IRLM use other types and subtypes of lock mode to achieve locking and concurrency control. You can find more detailed descriptions of this in the DB2 UDB administration manual.