How does DB2 database lock a single session?

The following articles mainly describe how to lock a single session in a DB2 database. If you are curious about the technique of locking a single session in a DB2 database, the following articles will unveil its mysteries. I hope you will gain some benefits after browsing.

We all know that since MySQL 8.2, you 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 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.