Concurrent functions of DB2 Universal Database (1)

In the database management system (DBMS) field, the term "concurrency" is used to indicate the ability of more than one application to access the same data from the user's perspective.

One of the main advantages of DBMS is that it can share data among multiple users and multiple applications. Therefore, the database system should provide a method for managing concurrent data access. DBMS must ensure that data consistency and integrity are maintained.

One way to achieve this is to implement the serial-only mode to process database requests. That is, each transaction has to wait for another transaction to have a higher priority or start earlier than it) to complete its work. However, for the current online system and customer exceptions, the performance level produced by this processing method is simply unacceptable.

Another method is that DBMS canLockTo manage the data access of multiple applications. A lock is a software mechanism used to allow as much throughput as possible to access data concurrently as possible while maintaining data integrity and consistency ).

Importance of concurrency control
Without an effective method to control concurrency, data integrity and consistency may be compromised. DBMS must protect the database to prevent the following situations:

• Update loss-- Assume that application A and application B read the same row from the database at the same time and calculate the new value for A column. If application A updates the row with its new value and application B updates the same row, the first update executed by application A will be lost.
• Non-repeated read-- Some application processes may require that the following event sequence be completed: program A reads A specific row from the table and continues with other SQL requests. Later, program A reads the row starting again and must find the same value as the first read in all columns. In the absence of proper concurrency control, another application may modify the row of data between the two read operations.
• Access unsubmitted data-- Application A updates the values of some columns in A row. before submitting the modification, application B reads the new update value of the row. If application A then "revokes" the update value through the SQL ROLLBACK statement in the program logic, or DB2 UDB automatically rolls back the value due to an error, application B processes the row based on the uncommitted data and may be incorrect.

While maintaining data integrity, the ability to provide multiple applications to access data at the same time is calledConcurrency Control.

Lock
A lock is a software mechanism used by DB2 UDB to implement concurrency control. A lock is essentially a control block that associates DB2 UDB objects or resources with applications and controls how other applications access the same object or resource. Applications associated with DB2 UDB resources are called "hold" or "own" the lock.

By using locks, DB2 UDB manages the database) to prevent the above issues. DB2 UDB works with another MVS address space IRLM to manage these locks. IRLM will track these locks and their owners to determine whether the DB2 UDB Resources requested by the application are available for this type of work. The resource can beLockedOrShared, Depending on the type of processing performed by the "owner" of the lock on the current resource, and the type of processing expected by the request application.

Lock mode
The two most common lock modes are:SharedAndExclusive. The shared lock is associated with read-only operations, which means that the application holding the lock can read data, while other applications can also read the data. Exclusive locks are associated with write operations, which means that the application holding the lock is eligible to update data, but before the lock owner completes the update and submits the modification to the database) and releases the lock, other applications cannot use the data.

DB2 UDB and IRLM use lock modes of other types and sub-types to implement lock and concurrency control. You can find more details about this in the DB2 UDB Administration Manual.

Lock Granularity
In addition to various lock modes, DB2 UDB provides different lock levels to control the scope of locked data. Various levels indicate the locking granularity used by DB2 UDB, which can range from a single row to the entire tablespace. DB2 UDB uses different lock modes based on the lock granularity.

The reason for having multiple lock levels is simple. Some applications may require the permission to read or update a large range of data, while other applications may require only a much narrower access range. If you can use only one lock level, the overall system performance will be reduced. For example, locking too much data at once forces other applications to wait for unnecessary requests. Otherwise, DB2 UDB may use too many system resources to attempt to lock additional data resources. Multiple Levels of lock granularity can greatly improve the concurrency level.

Hold
If an application process requests a lock and the lock is owned by another application process and cannot be shared, the process is calledTemporarily suspended. The request application will be suspended, that is, it will temporarily stop running. The priority of lock requests is as follows: New lock requests are queued in the order of receipt. Requests from applications that already hold locks and requests for Lock escalation are served before requests from new applications. In those groups, the request order is "first-in-first-out, first-out, and FIFO )".

Timeout
When the application is in the pending state, see the preceding figure) beyond the preset interval, the application will be terminated. This application is called timeout. Before terminating the application, an appropriate error message is received in SQLCA. SQLCASQL communication domain) is a fixed-size storage area reserved by SQL applications. It is used to transmit conditional code and other information from DB2 UDB to programs.

Some operations, such as COMMIT and ROLLBACK, cannot time out. In the following sub-title resource timeout, we will discuss the preset time interval that determines how long an application process can wait for resources.

Deadlock
When two or more applications each hold a lock on the resources required by the other application, without these resources, those applications cannot continue to do their work, A deadlock occurs.

The following is a simple deadlock scenario:

1. Application A accesses table T and requests exclusive non-shareable locks on page X.
2. Application B accesses table T and requests the exclusive lock on page Y.
3. Then, application A requests the lock on page Y while still holding the exclusive lock on page X. Application A will be suspended because application B has the exclusive lock on page Y.
4. Then, application B requests the lock on page X while still holding the exclusive lock on page Y. Application B will be suspended because application A has the exclusive lock on page X.
5. This is a deadlock. Neither application A nor application B can continue to work.

After a preset interval, see the discussion under the title deadlock time.) DB2 UDB terminates the current unit of work, because an application is stuck in a deadlock state, it usually does the least work ). This will release the lock held by the Terminator and allow the remaining applications to continue. DB2 UDB will send descriptive error messages and messages to the sqlca of the terminated application.

Concurrency of utilities and commands
When SQL applications use transaction locks to control concurrent access to DB2 UDB objects, DB2 UDB utilities and commands can access DB2 UDB objects in other ways, that isClaim), discard drain)AndCompatibility rules.

A statement notifies DB2 UDB that it is currently accessing a specific object. After the statement is submitted, the statement usually does not exist. To access the DB2 UDB object in the next unit of work, the application requires a new declaration. The statement notifies DB2 UDB that it is currently paying attention to a DB2 UDB object or that activity exists on the object. As long as there are declarations on the DB2 UDB object, you cannot discard the drain before releasing those declarations ).

Giving up drain is an action to access a DB2 UDB object in the following ways:

1. Blocks any new declarations on objects.
2. Wait for all existing declarations on the released object.

DB2 UDB stops quiesce due to abandonment on the DB2 UDB object.) All applications that are currently declaring the resource are allowed to reach the submission point, but block them or any other application process) for new declarations. The Drain lock also prevents conflicting processes from dropping drain at the same time.

DB2 UDB generally controls the concurrent operations of a utility through a set of compatibility rules. If two utilities do not need to access the same DB2 UDB objects in incompatible mode at the same time, they are considered "compatible ". When a utility job starts, DB2 UDB checks the system to see if any other utility is currently processing the same DB2 UDB object. If the object is not currently accessed by another application, or if the other execution utility is compatible, the utility can continue.

Database Design Considerations
Achieving high concurrency should be one of the goals of guiding the initial design of the DB2 UDB database. When creating tables, you may have to consider many features that affect concurrency. You can add some features after the implementation is created, such as modifying the DB2 UDB object), but other features cannot be added, or at least a large number of repeated operations and/or disrupt the current implementation. Therefore, it is best to consider these issues from the very beginning.

Partition
I strongly recommend that you create a large table as a partition table. A partitioned table space only contains a single partitioned table. The table is divided into multiple partitions based on the key range of the partition index. Each partition is created as a separate dataset, and can be processed by DB2 UDB as a separate entity.

For a large number of batch operations such as INSERT, UPDATE, and DELETE, you can divide large jobs into smaller jobs and use the partition table structure. Many of these smaller jobs can run concurrently on different partitions) to reduce the time used by the entire batch operation, so that another application process can access the table earlier.

A partition table allows a utility job to act only on the selected partition in a similar way, allowing other jobs or application processes to concurrently access other partitions in the table. In many cases, the DB2 UDB utility itself has the ability to take advantage of Partitioned Tables, and in other cases, it only provides users with the option to design their workloads, to support a higher concurrency level of DB2 UDB data.

Lock upgrade
DB2 UDB uses upgrade technology to balance the concurrency requirements for locking performance overhead. When an application process holds a large number of page locks, row locks, or LOB locks on a single table or tablespace, DB2 UDB acquires the table or tablespace locks on the resource, then release the previous page lock, row lock, or LOB lock on the resource. This process is calledLock upgrade.

If a lock upgrade occurs on a table that uses the CREATE or ALTER statement with the lockpart yes lock, only the currently locked partition will be upgraded, and the unlocked partition will still not be locked. Once a lock upgrade occurs in the tablespace, it is necessary to use the tablespace lock to lock the unlocked partitions subsequently accessed.

When executing an application, DB2 UDB first uses the page lock or row lock, and continues to do so as long as the process accesses a relatively small number of pages or rows. When this application accesses many pages or rows, DB2 UDB will change to using Table locks, tablespace locks, or partition locks. The exact time for calling the lock upgrade is determined by the LOCKSIZE and LOCKMAX. values.

LOCKSIZE
LOCKSIZE is the option of the CREATE/alter tablespace statement. When an application process accesses a table in the TABLESPACE, it controls the type of lock that DB2 UDB obtains. That is, it determines the "size" of the lock ", this is also called lock granularity ). This option can be LOB, TABLESPACE, TABLE, PAGE, ROW, and ANY.

The LOCKSIZE parameter of the create tablespace statement defaults to ANY. Locksize any allows DB2 UDB to select the lock size. DB2 UDB usually uses locksize page for non-LOB tables, but locksize tablespace for LOB tables.

We recommend that you use this default value when creating a tablespace unless you have reasons to make other choices. If you choose to modify LOCKSIZE, you need to make a decision based on the performance monitoring results and concurrency characteristics of applications using the tablespace.

What size of lock is used?
In DB2 V4, row-level locks can be used. Previously, data pages were the smallest lock units. Many in the I/T industry assume that row locks are a panacea for concurrency problems, but in fact they cannot solve all concurrency problems. It may provide significant improvements in many lock waits, deadlocks, and timeouts. However, in other cases, DB2 UDB may consume resources when obtaining more locks without increasing concurrency proportionally.

Because IRLM needs to process the acquisition, maintenance, and release of row locks roughly the same as the page locks, therefore, the decision on the size of the specified lock is to weigh between the high lock overhead and the potential increase in concurrency.

Therefore, the use of page locks or row locks depends on the characteristics of your data and applications. If you are aware that there is a lot of competition on the data pages of tablespaces at the page lock level, consider using row locks. Locking at the row level rather than the page level can greatly reduce competition with other application processes, especially when access is random.

However, if multiple applications are updating the same row on a page in different order, the row lock may lead to more competition than the page. This is because, through the page lock, the second and subsequent applications must wait for the first application to complete before accessing the page, and they may time out. Through row locks, multiple applications can access rows on the same page at the same time, but if they try to access the same row set, a deadlock may occur.