DB2 database locking tips for a single session"

The following article mainly introduces the practical operation skills of the DB2 database for locking a single session. I have seen the actual operation skills of the DB2 database for locking a single session on the relevant website, and I think it is quite good, I will share it with you. The following is a detailed description of the article. I hope you can learn from it.

Starting from version 8.2, DB2 users can specify a locking policy 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.

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 DB2 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 above content is an introduction to the locking technique of a single session in the DB2 database. I hope you will gain some benefits.