Working Principle and related problems of DB2 lock wait

Today we will mainly talk about the DB2 lock wait. This article mainly uses a simple example to introduce the database lock and analyze the lock wait problem in the database in detail, locks, a technology that makes people feel safe and inconvenient, play an irreplaceable role in the database.

However, 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, there are a lot of similarities. If you can understand his meaning.

It is good to consider it in the design. If you don't understand it very well, it doesn't matter. Most people who use DB2 database development cannot fully understand it, so you don't have to worry about it. 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 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 locked wait applications. As shown above, the reason window 2 does not return is probably caused by DB2 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.

In this row, we can obtain this useful information. An application whose Transaction handle is 8 (TranHdl) is waiting for the lock 03000500040080020000000052 (Lockname), and the lock is being Transaction handle is 2 (Owner) application possession. The requested lock type is the NS lock on the row. The row requesting the lock is the 640th slots in table 5 of table 3 tablespace on page 4th of the tablespace. Now, let's see who holds the lock.

 
 

  
  
db2pd -d sample -locks show detail |find "03000500040080020000000052"  
  
  
0x7F890AB0 2 03000500040080020000000052 Row ..X G 2 1 0 0x08 0x40000000 TbspaceID 3 TableID 5 PartitionID 0 Page 640 Slot 4  
  
  
0x7F8911B0 8 03000500040080020000000052 Row .NS W 2 1 0 0x00 0x00000001 TbspaceID 3 TableID 5 PartitionID 0 Page 640 Slot 4 
 
 

The result of the above two rows is displayed, one of which is the waiting application we just saw. The other is in G (Granted) State and Transction handler is 2, it is exactly what we are looking for. The root cause of lock hold is not released. If you see another W lock, it is possible, because many applications may be waiting for this lock. We are looking for an application holding the lock, the analysis of the previous command is an application whose Transction handler is 2.

At this point, we have found that the application with the Transaction handle of 2 is not released when the lock is occupied. You can use the following command to check what the application is executing.

 
 

  
  
db2pd –d sample –transactions  
  
  
Transactions:  
  
  
Address AppHandl [nod-index] TranHdl Locks State Tflag Tflag2  
  
  
Firstlsn Lastlsn LogSpace SpaceReserved TID AxRegCnt GXID  
  
  
0x7FC21A80 22[000-00022] 2 3 WRITE 0x00000000 0x00000000 0x000002C1D098 0x000002C1D098 110 174 0x00000000185E 1 0  
 
 

Find the application whose TranHdl is 2. We can see this information. The application handle is 22, currently holding three locks, and the status is write. Note: When the LogSpace value is not 0, there must be uncommitted changes to this application. The log usage of this application is 110. Next we will check what this application is running.

 
 

  
  
db2pd -d sample -applications  
  
  
Address AppHandl [nod-index] NumAgents CoorEDUID Status C-AnchID C-StmtUID L-AnchID L-StmtUID Appid WorkloadID WorkloadOccID  
  
  
0x7AED8080 22[000-00022] 1 4764 UOW-Waiting 0 0 64 1 *LOCAL.DB2.090817071951 1 1  
 
 

Find the application whose application handle is 22, and then find the handle ID of the current statement and the handle ID of the previous statement. We can see that the current application does not have the statement being executed, the last statement executed is the statement with the L-AnchID 64 and the L-StmtUID 1. What is this statement?

 
 

  
  
db2pd -d sample –dyn  
  
  
Address AnchID StmtUID NumEnv NumVar NumRef NumExe Text  
  
  
0x7EAF4370 64 1 0 0 1 1 insert into test_lock values(1,'aaa')。 
 
 

Through the correspondence of dynamic statements (AnchID StmtUID is 64 and 1 respectively), we have found the root cause of the lock. The above statements occupy the lock and are not released, as a result, the query statement you run is not returned. In the middle, we also understand the output of some db2pd statements and use this information to analyze and ask questions.

If you are using version 9.5 or later, you can also use a simpler method to know the row on which the lock is located, as analyzed above, the requested table is locked in table 5 of table 3 and contains 640th slots on page 4th of the table space. Now let's take a look at the rows of records for which object is locked:

 
 

  
  
db2 "select tabname from syscat.tables where tbspaceid = 3 and tableid = 5"  
  
  
TABNAME  
  
  
TEST_LOCK  
 
 

Which Rows of records are locked on the table TEST_LOCK? Based on the composition of the database ROWID, pages is 640 and slot is 4. For large tablespace, rowid is converted to an integer of 640*65536 + 4. For regular tablespace, then, the rowid is converted to an integer of 640*256 + 4. I use the large tablespace, so the rowid is 640*65536 + 4 = 41943044.

You can find this record based on the table and rowid:

 
 

  
  
db2 "select * from test_lock where rid(test_lock)= 41943044 with ur"  
  
  
COL COL2  
  
  
1 aaa  
 
 

The result is the same as the analysis above. The DB2 lock wait generated in the 1 'aaa' row of the TEST_LOCK table. Note that the preceding statement must be executed with ur. Otherwise, you will also wait for the lock .....

Add:

In many cases, after knowing the applications handle of the database, you need to know which process of the business is connected for execution. You can use the following method to check the process:

 
 

  
  
db2pd -d smaple –agents  
  
  
Address AppHandl [nod-index] AgentEDUID Priority Type State ClientPid Userid ClientNm Rowsread Rowswrtn LkTmOt DBName  
  
  
0x7AB97A90 22[000-00022] 4764 0 Coord Inst-Active 5120  
  
  
XUXIAOF db2bp.exe 245 48 NotSet SAMPLE  
 
 

Find the application program.