Creating a deadlock for testing.

Troubleshooting

Problem

At times it is nessiccary to test concurency issues by creating a deadlock situation within db2.

Symptom

This is a simple way to create a deadlock situation for testing purposes.

In session 1:

In session 2:

In session 1:

db2 +c "lock table tab2 in share mode"
Session 1 should be waiting for commit.

In session 2:

db2 +c "lock table tab1 in share mode"
Session 2 should be waiting on session 1. Both are now in a deadlock.

Both session will be deadlocked until the deadlock detector finds it and will then error with -911 and one of the transactions will be rolled back and the other will continue.

The error produced will be as follows:

SQL0911N The current transaction has been rolled back because of a deadlock or timeout. Reason code "2". SQLSTATE=40001

[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Performance - Lock-Latch","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"9.8;9.7;9.5;9.1;8.2;10.1;10.5","Edition":"Advanced Enterprise Server;Enterprise Server;Express;Express-C;Personal;Workgroup Server","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Log InLog in to view more of this document

This document has the abstract of a technical article that is available to authorized users once you have logged on. Please use Log in button above to access the full document. After log in, if you do not have the right authorization for this document, there will be instructions on what to do next.

Was this topic helpful?

Document Information

More support for:
Db2 for Linux, UNIX and Windows

Software version:
9.8, 9.7, 9.5, 9.1, 8.2, 10.1, 10.5

Operating system(s):
AIX, HP-UX, Linux, Solaris, Windows

Document number:
500181

Modified date:
01 May 2025

UID

swg21654131

IBM Support

Tips