DB2 database locking for a single session

Starting from version 8.2, DB2 users can specify a locking policy for each DB2 session. In previous versions, you can only configure an application by configuring the Database Configuration Parameter LOCKTIMEOUT. If you cannot obtain the lock, how long will the transaction be rolled back.

A single session can now specify a lock wait Mode Policy, which is used when the lock required by the session cannot be obtained immediately. This policy indicates whether the session will take the following measures: Return SQLCODE and SQLSTATE when the lock cannot be obtained

◆ Infinite waiting for Lock

◆ Wait for the specified lock time

◆ Values of parameters configured in the locktimeout database when waiting for the lock

The LOCK wait Mode Policy is specified through the new set current lock timeout statement, which changes the value of the current lock timeout special register. The current lock timeout Special Register specifies the number of seconds to wait for the 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 function only affects the following lock types: Row, table, index, and multi-dimensional cluster (MDC) block locks.

The statement syntax is as follows:

.-CURRENT-. .-=-. >>-SET--+---------+--LOCK TIMEOUT--+---+------------> >--+-WAIT-----------------------+------------>< +-NOT WAIT-------------------+ +-NULL-----------------------+ | .-WAIT-. | +-+------+-- integer-constant -+ '- host-variable --------------'

The following example shows how to use this function (we use the SAMPLE database ):

1. Use "db2 + c" to open two DB2 sessions. (Close AUTOCOMMIT with the + c parameter)

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-1, this session will remain waiting and no result will be returned. As shown in:

4. We will repeat the same operation in the first song window, but use the following statement in the second window:

set current lock timeout not wait select * from test

We can see that the select statement does not wait, but returns an error directly. See:

You can test other parameter settings by yourself.

This statement can be used in programs and stored procedures. In addition, this function can be easily used by setting the configuration keyword of the db2cli. ini file for CLI programs. The keyword is:

LOCKTIMEOUT = WAIT | not wait | WAIT seconds | seconds

By setting this parameter, DB2 CLI will automatically send a "set current lock timeout" Statement to the DB2 Server Based on the setting value. This parameter value can also be displayed and used in the program.

Reset "set current lock timeout.

You can use this statement flexibly to design an appropriate DB2 session lock policy based on your application needs.

1. Troubleshooting of DB2 faults and solutions to general problems
2. In-depth working principle and connection process of DB2 proxy
3. Using functions to solve the problem of date and time in DB2
4. DBase: Common commands and skills DB2 must understand
5. How to improve IMPORT performance in DB2