DB2 V9.5 Locking

Environment
Step 1:   Create a sample database using the following command

db2sampl

This will create sample database which is shifted with DB2 software. It help to learn new concepts of DB2 and also for testing purpose.

Step 2: Open three command line which will help us see the transactions

Screen 1:
This screen is used by user A.

Screen 2: 
This screeen is used by user B.

Screen 3:
This screen is used to monitor how the transaction work. Here we will use the "db2pd" command to see the lock on the tables

Step 3:

Screen 1 (Txn 01)	Screen 2 (Txn 02)	Screen 3
db2 connect to sample user usera using usera	db2 connect to sample user userb using userb	db2 connect to sample
db2 +c update employee set salary = salary * 0.1	db2 +c update employe set bonus = salary * 0.1 where job = 'MANAGER'	db2 "select *from employee"

Step 4:
After performing the TXN given in Step 3, we can see that Screen 2 TXN will pause because DB2 has held the lock on all row because of the transaction done on Screen 1. This gives us the good chance in learning the DB2 Locks. Now in order to learn more about DB2 LOCK we will be using Screen 3 for monitoring the locks.

Perform the following operation

Single Partition	Multiple Partition
db2pd -d sample -locks wait showlocks	db2pd -d alldbs -locks wait showlocks

Output

In this TranHdl = 2 is performed by Txn 01 and TranHdl = 9 is performed by Txn 02. Txn02 [TranHdl = 9] in our case is in LOCK WAITING state.

What is Extracted

Parameter	TXN 01	TXN 02
TranHdl	2	9

Step 5:
Now Txn 01 has updated all the records from employee table we can see the details of it using the following command. It shows all the records that are updated as part of Txn01.

db2pd -d sample -locks 2 -- Here 2 is the value taken from Step 4 TranHdl column.

db2pd -d sample -locks 9 -- Here 9 is the value taken from Step 4 TranHdl column.

When Txn02 try to update the record, DB2 block the DML operation and block the transaction resulting into LOCK WAIT problem. We can see what transaction is trying to update usnig the following command. Here it want to update a set of ROW. When it trys to aquire the LOCK for first row to update, it fails and goes in LOCK WAITING state as show in row 2 in above screen shot.


Step 6:
To get the list of all transaction running in the system execute the below command. The screen shot is truncated. Therefore i have split it into two screen shot. Hereafter if the image is followed one after another without any text, its a continuation of the previous screen.

db2pd -sample -transactions

We have got all the transaction executing in the DB2 Server. Now our concern is only with the lock waiting transaction that was identified in Step 4. In our case it was TranHdl (2 and 9). In order to pull the details of specific transaction we execute the command

db2pd -sample -transactions 2

From the above screen shot very important information can be extracted.
1) Firstlsn and Lastlsn can be used to identified how many log files it spanned. Using the column value we can also find which log file it belong to using the db2flsn .
2) You can Txn State which help us to understand what operation the Txn was doing.
3) TID provides the transaction ID which can be used to find more information about the transactions.
4) We can also find the application that is executing the transaction using the value in the AppHandl column value. In our case the Application 77 (AppHandl=77) is permforming the Txn.


db2pd -sample -transactions 9

In the above screen, we can see that there is no LOG space used by this transaction as the transaction is in LOCK WAITING state. Application 90 (AppHandl=90) is performing the Txn 02.


What is extracted

Parameter	TXN 01	TXN 02
TranHdl	2	9
AppHandl	77	90

From this we can conclude that Application 77 (AppHandl = 77) is holding LOCK on which Application 90 (AppHandl = 90) is waiting.


Step 7:

After getting the Transaction Details and Application Details, our next task would be to find the details about the application. There are three way to find the detials about the application.

1) db2 list application show detail

In the column Status shows the Txn state. In this Application 77 (Txn 01) is in UOW Waiting which mean it waiting for application to any more transaction i.e it is right now idle and the application is not performing any operation on database.
Application 90 (Txn 02) show Lock-wait status which mean it is Waiting for Lock to be released by other transaction.

2) db2pd -agents app=77

In the above screen, ClientPid column show the value where the command is executed. It does not represent the user and/or application executing the transaction.

db2pd -agents app=90

3) db2pd -applications agent=8416 -db sample

In this the value provided with agent clause is the Co-Ordinator agent ID which can be used to find more details about the application. It can be extracted from Method 1 or Method 2 described above.

db2pd -applications agent=8416 -db sample

From the above two output it can be seen that SystemAuthID show the actual user name executing the transaction.
C-AnchID mean the current executing SQL statement C=Current
L-AnchID mean the last SQL statement that was executed L=Last


What is extracted

Parameter	TXN 01	TXN 02
TranHdl	2	9
AppHandl	77	90
SystemAuthID	DB2ADMIN	GGONSALVES
EDUID	8416	11760
C-AnchID	0	268
L-AnchID	36	268

Step 8:

Next find the statement executed by the applications. This will help us to find what are SQL query executed against the DB by each of this application. This SQL executed are extracted using the C-AnchID and L-AnchID collected from step 7.

db2pd -db sample -dynamic anch=36

In this we got the statement executed by the Txn 01 i.e Application 77. It show the all rows in the EMPLOYEE table is updated. The Statement executed was
"UPDATE employee SET salary = salary * 1.02".

db2pd -db sample -dynamic anch=268

The Statement executed by Txn 02 i.e Application 90 is
"UPDATE employee SET bonus = salary * 0.1 WHERE job = 'MANAGER'"

From the ouput of Application 90, we can see that the application is waiting on Lock to be released. The "Dynamic SQL Environments" shows the Isolation Level of the statement that is executed. The "Dynamic SQL Variations" show the Lock on which the application is waiting.

What is extracted

Parameter	TXN 01	TXN 02
TranHdl	2	9
AppHandl	77	90
SystemAuthID	DB2ADMIN	GGONSALVES
EDUID	8416	11760
C-AnchID	0	268
L-AnchID	36	268
Text (SQL Statement)	UPDATE employee SET salary = salary * 1.02xn	UPDATE employee SET bonus = salary * 0.1 WHERE job = 'MANAGER'

Step 9:

From the above operation, it can be summarized that the EMPLOYEE table is been accessed by Txn 01 and Txn 02 and each of this transaction had requested the exclusive lock on the row in this table. But since the Txn 01 was first to execute it got the Exclusive lock on the rows but it did not commit. Because the Txn 01 is not commited, Txn 02 request for exclusive lock on some rows was halted and Txn 02 goes in LOCK-WAITING state.

Two option can be used to resolve from this LOCK-WAITING state
1) LOCKTIMEOUT => This DB CFG parameter is defaulted to -1 which mean the application have to wait indefinately until the lock which it is requesting is released. In order to wait indefinately for a LOCK, it can be set to value other than -1. The value specified is the time after which the waiting transaction must rollback itself. It is given in seconds.

2) Execute the COMMIT operation which will allow the transaction to release the LOCK so that other Transaction can continue to operate.


Step 10:

In order to perform the above step in finding the LOCK details, the approach is to specific all the caluse in db2pd command. It will show different section for each of the caluse. The command is

db2pd -db sample -locks wait show locks -transactions -agents -applications -dynamic

The output of the above command will give details of all the "Transactions" , "Agents", "applications" and all "dynamic" statements. In order to cut shot the output following step can be carried out
1) Find the Transaction handle that are part of LOCK WAITING. We get Transaction Handle
db2pd -db sample -locks wait show locks

2) Find the application executing transaction. We get Application Handle
db2pd -db sample -transactions

3) Find the EDU ID for application that is executing. We get EDUID
db2pd -db sample -agents app=<Apphdl>

4) Find the L(Last) and C(Current) anchor ID for each application. We get C-AnchID and L-AnchID
db2pd -db sample -applications agent=

5) Finally execute the command to get all the detail in one output
db2pd -db sample -locks wait showlocks -transactions "TranHdl" -agents app="applhdl" -applications agent="AgentEDUID" -dynamic anch="C-AnchID ==OR== L-AnchID"