DB2 database for single session locking tips

DB2 starting with version 8.2, customers can specify a lockout policy for each DB2 session. In previous releases, you could configure an application only through the metabase configuration parameter Locktimeout if the lock is not available, how long to wait will roll back the transaction.

A single session can now specify a locking wait-by policy that is used when the lock required by the session is not immediately available. This policy indicates whether the session will take the following action: Returns SQLCODE and SQLSTATE when a lock is not available

Infinite wait Lock

Wait for the time specified for the lock

Use the value of the Locktimeout database configuration parameter while waiting for a lock

The lock wait method policy is specified by the new SET current lock TIMEOUT statement, which changes the value of the TIMEOUT private register. The current lock TIMEOUT Private register Specifies the number of seconds to wait for a lock before returning an error indicating that the lock cannot be obtained.

Although the value of the Locktimeout parameter applies to all locks, this new feature affects only the following locking types: rows, tables, index keys, and multidimensional cluster (MDC) block locking.

The syntax for the statement is as follows:

.-current-. .-=-.

>>-set--+---------+--lock timeout--+---+------------>

>--+-wait-----------------------+------------><

+-not Wait-------------------+

+-null-----------------------+

|--wait-. |

+-+------+--integer-constant-+

'-host-variable--------------'

For a detailed description of this command, see: Http://publib.boulder.ibm.com/infocenter/db2help/topic

/com.ibm.db2.udb.doc/admin/r0011874.htm?resultof=

%22%73%65%74%22%20%22%63%75%72%72%65%6e%74%22%2

0%22%6c%6f%63%6b%22%20%22%74%69%6d%65%6f%75%74%22%20

Here is an example of how to use this function (we use the sample database):

1, use "DB2 +c" to open two DB2 sessions. (+c parameter closes autocommit)

2. Execute the following statement in a session:

Connect to sample

CREATE TABLE Test (ID int,name char (10))

Commit

INSERT into test values (1, ' Test ')

3. Execute the following statement in another session:

Connect to sample

SELECT * FROM Test

Because the default locktimeout parameter is set to-1, the session waits until no results are returned. As shown in the following illustration:

4. Below we repeat the same operation in the first song window, using the following statement only in the second window

Set current lock timeout Don't wait

SELECT * FROM Test

We see that the SELECT statement does not wait, but instead returns the error directly. See figure below:

You can take the above method and test the other parameter settings yourself.

This statement can be used in programs and stored procedures. In addition, this feature can be easily used for CLI programs by setting the Db2cli.ini file configuration keyword:

LOCKTIMEOUT = Wait | Not Wait | Wait seconds | Seconds

By setting this parameter, the DB2 CLI automatically sends a "set current LOCK TIMEOUT" statement to the DB2 server based on the set value, which can also be displayed in the program by using the

"SET current LOCK TIMEOUT" reset.

You can use this statement flexibly to design appropriate DB2 session locking strategies based on the needs of your application.