Correct understanding of lock wait for DB2 Databases

We are going to discuss with you today that we are correctly analyzing the lock wait of the DB2 database. Referring to the lock is a technology that makes people feel safe and inconvenient, it plays an irreplaceable role in the database, but different databases have different implementation methods.

When you get used to using a database and access another database, you will feel a lot of inconvenience.

Locks, a technology that makes people feel safe and inconvenient, play an irreplaceable role in the database, but different databases have different implementation methods. When you get used to using a database and access another database, you will feel a lot of inconvenience.

Every person who starts to contact DB2 has such feelings more or less. There are so many types of locks in the database: S, IS, NS, X, IX, SIX, NX, U, Z .... From the perspective of name, a lot of things are similar. If you can understand his meaning and consider it in design, it is certainly good. If you don't understand him very well, it doesn't matter, most developers who use DB2 databases cannot fully understand them, so don't worry.

As a regular user of a DB2 database, I would like to share with you how to deal with the lock wait problem in the DB2 database and help you solve the lock problems encountered during the use of DB2.

Next, we will use a simple example to introduce how to analyze the lock wait problem in the database.

Scenario: Find the root cause of the database lock wait:

Create a simple table:

 
 

  
  
db2 "create table test_lock (col int, col2 char(10))"  
 
 

Open three command line windows

Run the following command in window 1:

 
 

  
  
db2 +c "insert into test_lock values(1,'aaa')"  
 
 

The DB20000I SQL command is successfully completed.

Run the following command in window 2:

 
 

  
  
db2 "select * from test_lock"  
 
 

We have seen that no response is returned for a long time. This is a question that many people have asked. I have executed a very simple operation, and the database is stuck and will not be returned. Why?

We use window 3 for analysis:

 
 

  
  
Db2 list applications show detail
  
  
XUXIAOF db2bp.exe 22 * LOCAL. DB2.090817071951 00012 1 0 4764 UOW is waiting 10:52:08. 685167 IBM-L3F6 sample c: \ DB2 \ NODE0000 \ SQL00001 \
  
  
XUXIAOF db2bp.exe 68 * LOCAL. DB2.090817075736 00003 1 0 4464 lock wait 10:53:24. 329893 IBM-L3F6 sample c: \ DB2 \ NODE0000 \ SQL00001 \
 
 

This command is always the simplest and most practical step for you to look at the Lock problem. There is no Lock wait in the database. You will know at first glance that if there is a long Lock-waiting (English environment) status or lock wait (Chinese environment) status, the database has a locked wait application. As shown above, window 2 does not return because of lock wait. Now, we use the db2pd tool to analyze the specific lock. Maybe, this is what we are most concerned about.

 
 

  
  
db2pd -d sample -locks show detail   
  
  
Address TranHdl Lockname Type Mode Sts Owner Dur HoldCount Att ReleaseFlg   
  
  
0x7F8911B0 8 03000500040080020000000052 Row .NS W 2 1 0 0x00 0x00000001 TbspaceID 3 TableID 5 PartitionID 0 Page 640 Slot 4 
 
 

After executing this command, you may see a lot of locks. Why should I find this one? Remember, the starting point of your analysis must be the application that is waiting for, that is, the application whose status (Sts) is W (waiting) as listed above, you may see a lot in your environment and can analyze them one by one.