Practices for locking a single session in a DB2 database

Today we are going to discuss with you the practical operation skills for DB2 database to lock a single session. Starting from version 8.2, DB2 users can specify relevant locking policies for each DB2 session. In earlier versions, you can only configure an application by configuring the DB2 database configuration parameter LOCKTIMEOUT. If you cannot obtain the lock, how long will the transaction be rolled back.

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 database 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.

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 database session lock policy based on your application needs.