Controlling concurrency on IBM DB2 for i
It has always been a challenge to maintain data consistency and integrity, while allowing multiple transactions to access the same data at the same time in multi-user environments. Transactions, isolation levels, and locks are mechanisms to help maintain data consistency. This ability is automatically supplied by the IBM DB2 for i database manager.
Choosing an appropriate isolation level (also known as commitment control level) ensures data integrity and also avoids unnecessary locking. But chances are that, lock timeouts can occur under some circumstances. It often happens that some high throughput database applications cannot tolerate waiting on locks that are issued during transaction processing, and some applications cannot tolerate processing uncommitted data, but still require non-blocking behavior for read transactions.
How can the application achieve the appropriate balance between availability and integrity? Here comes the answer, the concurrent access resolution option. This option directs the database manager on how to handle cases of record lock conflicts under certain isolation levels and can be used to minimize transaction wait time with integrity achieved to some extent.
Overview of concurrent access resolution control
Prior to IBM i 6.1 release, the isolation-level cursor stability (CS) only allow committed and consistent data to be accessed. Think about the situation that one transaction is waiting when it requests a lock on a record that is already held by another transaction and cannot be shared. The suspended transaction will temporarily stop running. In IBM i 6.1, the SKIP LOCKED DATA clause was introduced, but this feature only allows the transaction to skip the rows being incompatibly locked. In IBM i 7.1, a new feature in DB2 was introduced to allow users to access the currently committed image of data in case lock contention is encountered on the data row. Note that to enable concurrent access resolution support, journaling is a prerequisite.
Refer to the SQL Reference for a brief syntax difference between IBM i 6.1 and IBM I 7.1: Skip-locked-data-clause is available in IBM i 6.1 which includes the only option "SKIP LOCKED DATA", while concurrent-access-resolution-clause is available in IBM i 7.1 which includes "SKIP LOCKED DATA", "CURRENTLY COMMITED" and "WAIT FOR OUTCOME" options. Then follow the example below to understand what each option of the concurrent access resolution means and how it works. Note that changes to a salary are not typical for OLTP and the example here is used only to illustrate the behavior.
Figure 1. The original table EMPLOYEE
Figure 1 is the original table EMPLOYEE which is a table without any uncommitted changes or update locks. There are two jobs (job1 and job2). Suppose *CS commit control is specified for both jobs. Job1 is updating and job2 is performing a query on the EMPLOYEE table.
Figure 2. Stable EMPLOYEE table with uncommitted changes
Figure 2 illustrates a table with some uncommitted changes made by job1: salary of Sherry was changed from 2700 to 3000 and Aaron’s from 3500 to 4000 using the UPDATE statements, as shown in Listing 1.
Listing 1. UPDATE statements
UPDATE EMPLOYEE SET salary=(salary+300) WHERE Name='Sherry'; UPDATE EMPLOYEE SET salary=(salary+500) WHERE Name='Aaron';
Now, suppose job2 is running a query against the EMPLOYEE table after the uncommitted updates have been performed. As job1 is holding an UPDATE lock on the rows for Sherry and Aaron, and to read the record, job2 is holding READ lock on the records to be selected which are incompatible lock to UPDATE lock. How will DB2 for i behave when different concurrent access resolution options are used?
- If "WAIT FOR OUTCOME" or nothing is specified (as the default behavior): The user of job2 will get the "row in use" error after time out duration exceeded.
- If "SKIP LOCKED DATA" is specified: The user of job2 will get all the rows except the uncommitted rows. In this example, the user will get the data, as shown in Figure 3.
Figure 3. Results returned for job2 when "SKIP LOCKED DATA" is specified
- If "USE CURRENTLY COMMITTED" is specified: The user will get the rows with last committed ones. The database will retrieve the last committed version of the data from the journal. In this example, the user will get the data as shown in Figure 4.
Figure 4. Results returned for job2 when USE CURRENTLY COMMITTED is specified
As another example, assume that job2 is performing a searched UPDATE or DELETE (as shown in Listing 2) on the EMPLOYEE table while it contains uncommitted data.
Listing 2. A searched UPDATE or DELETE
UPDATE EMPLOYEE SET salary=(salary+1000) WHERE Name IN ('Sherry', 'Aaron', 'Ben')'; or DELETE FROM EMPLOYEE WHERE Name IN ('Sherry', 'Aaron', 'Ben');
How will DB2 for i behave when different concurrent access resolution options are used?
- If "WAIT FOR OUTCOME" or nothing is specified (as the default behavior): The user of job2 will get the "row in use" error.
- If "SKIP LOCKED DATA" is specified: The uncommitted rows will not be updated or deleted. The other rows will be updated or deleted.
- If "USE CURRENTLY COMMITTED" is specified: The user will get the "row in use" error as job1 is holding exclusive locks on these records.
Using the concurrent access resolution option in applications
As is known, the correct usage of the concurrent access resolution option is beneficial for applications that need to improve performance by reducing lock wait times and can tolerate missing data (with SKIP LOCKED DATA) or do not require the latest version of a row to be returned by the query (with USE CURRENTLY COMMITTED). With the support of the two options that are delivered in DB2 for IBM i 6.1 and DB2 for IBM i 7.1, developers now have more flexibility for resolving concurrency problems. At the same time, it is also the responsibility of the developer to consider when and how to use this option in their application environment. Questions, such as "Do I need to set the concurrent access resolution options in my application and which option is suitable? Any problems or unexpected results will occur during the process with the setting?" are often asked. The next part talks about the considerations of the concurrency option for developers during application design. Note that only in SQL Query Engine (SQE) will the USE CURRENTLY COMMITTED setting be recognized. The setting will be ignored when the query is implemented through Classic Query Engine (CQE). CQE will always run as if WAIT FOR OUTCOME were in effect.
SKIP LOCKED DATA usage considerations
SKIP LOCKED DATA allows a user to fetch rows from a table without waiting for row locks. When the option is specified, any row that is already locked by another job will be skipped. Why would developers want this option? Let's see a typical usage of work queue scenario when tables are used as a queue. Assume that a program may want to pick the next row off a queue of rows in a specific status waiting to be processed.
Table WORKQUEUE exists in a schema with row-level locking and has as part of its definition ELEMENT, PRIORITY, and STATUS columns, which contains the following values shown in Figure 5.
Figure 5. Table WORKQUEUE
Suppose that a transaction wants to pick up the top element in the work queue in OPEN status for processing. In this example, element 1 will be fetched. Note that read stability (RS) would be used on the SELECT statement to acquire the row lock, thus preventing another job from finding the same row before it is updated. After element 1 is fetched, an UPDATE statement (as shown in Listing 3) is issued to change the STATUS column in the first row from OPEN to IN-ANALYSIS, and that the UPDATE has not yet committed.
Listing 3. UPDATE statement to change the status
UPDATE WORKQUEUE SET STATUS = 'IN-ANALYSIS' WHERE ELEMENT = 1;
Suppose that a second transaction wants to retrieve the highest priority work items as well, the following SELECT statement (shown in Listing 4) is issued to find it out:
Listing 4. SELECT statements
SELECT ELEMENT FROM WORKQUEUE WHERE PRIORITY = '1' AND STATUS='OPEN' SKIP LOCKED DATA;
This query locates the second row, without waiting for the transaction that holds a lock on the row that contains the ELEMENT=1 value to commit or roll back its operation.
If multiple parallel transactions are requesting first due row to be processed, normally there will be severe locking contention issues. SKIP LOCKED DATA allows a transaction to ignore rows already being processed by other transactions that have not yet committed their updates. Note that at the same time, the potential downside of this option is that, result set may miss rows that meet the search criteria. Check and make sure if the application can tolerate incomplete results when using this option.
Typical working scenarios of CURRENTLY COMMITTED usage
Consider the following dead lock avoidance scenario, in which deadlocks are avoided using the CURRENTLY COMMITTED option. In this scenario, two applications update two separate tables, but do not yet commit. Each application then attempts to read (with a read-only cursor) from the table that the other application has updated.
Table 1. Scenarios of lock avoidance
|Step||Application A||Application B|
|1||update T1 set col1 = ? where col2 = ?||update T2 set col1 = ? where col2 = ?|
|2||select col1, col3, col4 from T2 where col2 >= ?||select col1, col5, from T1 where col5 = ? and col2 = ?|
Without currently committed semantics, these applications running under the cursor stability isolation level might create a deadlock, causing one of the applications or the two applications to fail. This happens when each application needs to read data that is being updated by the other application.
Under currently committed semantics, if the query in step 2 (for either application) happens to require the data currently being updated by the other application, that application does not wait for the lock to be released, making a deadlock impossible. The previously committed version of the data is located and used instead.
Here is another example called semi-real time reporting. This kind of daily report would be a typical and practical application to use the CURRENTLY COMMIITTED option. This report would include incomplete data, but it is acceptable to have a point-in-time view of the transaction processing results. The bank is validating its customer's VIP qualification at the end of each day. To attain VIP status, an individual's saving account cannot fall below ten thousand dollars. A query is submitted to query the customer table to fetch the ones with saving less than $10,000. For new VIPs, a "Welcome to the club" email is sent. As the bank business is running 24x7, it is certain that customers’ saving accounts are changing all the time. There will be an update lock held on the records for which the saving account is changing. In this processing, precise real time data is not necessary. Instead of contending or conflicting with an update lock related to a bank transaction, the CURRENTLY COMMITTED option can be used to fetch the committed version of the account data of each customer. By using concurrent access controls, the VIP qualification processing can be allowed to run without the fear of impact to more important bank processing.
One thing to be noted for the CURRENTLY COMMITTED option is that, sometimes the number of journal entries in the currently attached journal receiver may be very big. For example, large number of update operations would generate large number of journal entries accordingly. This would cause the look up of the corresponding record value in the journal receiver to cost much time. If the time is too long and exceeds the timeout threshold, DB2 would give up continue searching in the journal and thus CURRENTLY COMMITTED is not taking effect in such circumstances. In this case, the default behavior WAIT FOR OUTCOME is taken.
Summary of usage considerations
In summary, if the SKIP LOCKED DATA clause or the USE CURRENTLY COMMITTED clause is specified in query, make a note of the following considerations during application design:
- Choose the correct isolation level to allow proper use of this option.
- Understand that the SKIP LOCKED DATA clause and USE CURRENTLY COMMITTED clause will apply to all accessed tables for the statement being executed.
- Understand that the application behavior would change due to the usage of the concurrency option. By using SKIP LOCKED DATA, the query result will be limited only to those unlocked and committed rows, or compatibly locked rows being accessed at the time. The use of the new clause can significantly affect the query result as all qualified rows may not be returned to the user. By using CURRENTLY COMMITTED, queries that are just reading now have the flexibility to have DB2 simply retrieve the last committed version of a locked row from the journal instead of sitting and waiting for the conflicting lock to be released.
Specifying the concurrent access resolution option
This section describes how to use the concurrent access resolution options and the various methods to specify the option values.
The concurrent access resolution option can have one of the following values:
- WAIT FOR OUTCOME
This is the default. This option does not apply for read-only queries running under COMMIT(*NONE) or COMMIT(*CHG).
- USE CURRENTLY COMMITTED
This option applies if possible when the isolation level is CS without the KEEP LOCKS clause and is ignored otherwise.
- SKIP LOCKED DATA
This option applies only when isolation level NC, UR, CS, or RS is in effect. This option is ignored if it is specified when the isolation level that is in effect is RR.
The concurrent access resolution behavior is controllable at different levels. There are several places for users to specify the concurrent access resolution option attribute:
1. At system-wide level:
A QAQQINI option (SQL_CONCURRENT_ACCESS_RESOLUTION) is added for specifying the concurrent access resolution to use when it is not explicitly specified as a PREPARE attribute or as a precompile option. Refer to the details about the supported values for the QAQQINI option below:
When the concurrent access resolution option is not directly set by the application, it is set to the value of the SQL_CONCURRENT_ACCESS_RESOLUTION option in the query options file QAQQINI. This option accepts one of the following values:
- DEFAULT - the default value is set to *WAIT
- CURCMT - use currently committed
- WAIT - wait for outcome
Note that it is only system-wide if users update QUSRSYS/QAQQINI. More likely, users will use a library specific QAQQINI for the application build or application run.
This is an example statement of how to set SQL_CONCURRENT_ACCESS_RESOLUTION options in the QAQQINI file:
INSERT INTO USRLIB/QAQQINI(QQPARM,QQVAL) VALUES('SQL_CONCURRENT_ACCESS_RESOLUTION', '*CURCMT');
2. At program level:
The new CONCURRENT ACCESS RESOLUTION precompile option is available for all of the SQL precompiler commands, such as CRTSQLCI and CRTSQLRPGI.
The parameter keyword is CONACC and accepts the same value as QAQQINI.
For SQL functions and procedures, The SET OPTION statement (CONACC) can be used to set the concurrent access resolution option used in SQL procedures and functions. For details about the "CONCURRENT ACCESS RESOLUTION" options in the SET OPTION statement, refer to SQL reference.
3. At connection-level property/attribute:
The connection attribute can be set in IBM i Access middleware: ADO.NET, JDBC, ODBC, OLE DB, or through SQL CLI and Native JDBC Driver.
4. At statement level:
- PREPARE attribute-string
A new attribute, USE CURRENTLY COMMITTED, is added to the concurrent-access-resolution clause for the PREPARE statement.
- SQL statement
A new attribute, USE CURRENTLY COMMITTED, is added to concurrent-access-resolution-clause for the SQL statement.
Precedence rules for concurrent access resolution.
Refer to Figure 6 for the illustration of precedence rules for concurrent access resolution.
Figure 6. Precedence rules for concurrent access resolution.
A concurrent access resolution value specified on the SQL request has the highest precedence over any other settings. If the statement text of the statement being prepared contains a SKIP LOCKED DATA or USE CURRENTLY COMMITTED clause, that specification takes precedence over any value specified in the PREPARE attributes, precompile option, connection option, or QAQQINI file.
The second highest precedence comes in PREPARE attributes. If the statement text does not contain a SKIP LOCKED DATA , USE CURRENTLY COMMITTED clause, then any value specified in the PREPARE attributes takes precedence over any value specified on a precompile option or the QAQQINI file.
If there is no SKIP LOCKED DATA, USE CURRENTLY COMMITTED clause in the statement being prepared, and there is no SKIP LOCKED DATA, USE CURRENTLY COMMITTED, or WAIT FOR OUTCOME clause specified in the PREPARE attributes, then the value specified on the precompile CONCURRENT ACCESS RESOLUTION (CONACC) option or the CONNECTION option takes precedence over an INI option value, provided the precompile option is set to either USE CURRENTLY COMMITTED or WAIT FOR OUTCOME values.
Special considerations when using the concurrent access resolution options
Following points should be paid enough attention to when using concurrent access resolution options. Some of them are DB2 for IBM i specific and behaves differently with DB2 for Linux, UNIX and Windows (DB2 for LUW) and z/OS®.
The currently committed semantics will only affect read-only queries running with isolation level CS without the CS KEEP LOCKS clause. Unlike it is on LUW and z/OS, CURRENTLY COMMITTED would not affect isolation-level RS.
If the query includes references to LOB columns, then directive to use SKIP LOCKED DATA or USE CURRENTLY COMMITTED will be ignored and the qurey will operate under the WAIT FOR OUTCOME rules.
As with the SKIP LOCKED DATA setting, the USE CURRENTLY COMMITTED setting will be ignored when the query is implemented using CQE. CQE will always run as if WAIT FOR OUTCOME was in effect.
For more restrictions, refer to SQL reference for details.
This article has given a glimpse into the capabilities that are available through different concurrency control options introduced in IBM i 6.1 and IBM i 7.1. With SKIP LOCKED DATA and CURRENTLY COMMITTED support, cases of record lock conflicts under certain isolation levels can be easily handled and by choosing the appropriate option, the transaction wait time can be minimized with integrity achieved to a certain extent. Consider using the concurrency control options as a performance improvement solution when implementing IBM i applications.
- Participate in the discussion forum.
- Documentation for Concurrent Access Resolution Clause
- Documentation for Skip Locked Data Clause
- DB2 for i 7.1: More Than Meets the Eye