Tips for locking a single session in a DB2 database

Since MySQL 8.2, you can specify a locking policy for each DB2 session. In some earlier versions, you only configure an application by configuring the DB2 database configuration parameter LOCKTIMEOUT. If the application cannot obtain the lock, how long will the transaction be rolled back.

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 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%6e%74%22%2  
  
  
0%22%6c%6f%63%6b%22%20%22%74%69%6d%65%6f%75%74%22%20  
 
 

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

1. Use "db2 + c" to open two DB2 database 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')  
 
 

The above content is an introduction to the locking technique of a single session in the DB2 database. I hope you will gain some benefits.