DB2 database locking for a single session

DB2 databaseAfter years of development and progress, many new versions have been developed, and these versions are better than one. DB2 databases have started from version 8.2, you can specify a locking policy for each DB2 database 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 wait lock

Wait for the specified lock time

Values of parameters configured in the locktimeout database during lock wait

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 --------------'

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% 74% 6e % 22% 2

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

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 set to-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.

The DB2 database locking technique for a single session is introduced here. I believe that you have mastered the skills described above, which will be very helpful for your future work, I hope everyone can make some gains.