Correct Analysis and Operations on DB2 lock wait

Today, we will mainly tell you how to correctly analyze the actual operation process of DB2 lock wait. Locking gives us the feeling that it is a secure and inconvenient technology, it also 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.

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 DB2 lock wait problem 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 database DB2 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 DB2 lock wait in the database. You can see it at first glance, if there is a long Lock-waiting (English environment) status or the Lock wait (Chinese environment) status, the database has a locked wait application, as shown above, the reason window 2 does not return may be the 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.