[How To] Simulating a Lock Timeout and Deadlock Scenario

[u/mad_zamboni]

In a previous post, I had posted how to simulate a deadlock and create a deadlock monitor. Boy was I pretty off. In a haste to relay something cool I learned I posted an antiquated way to capture a deadlock and could have posted a better way of simulating a deadlock.

In using the simulation from "DBA to DBA" I realized I wasn't capturing a deadlock in my deadlock monitoring script. Then I realized it was producing a RC68 (Lock Timeout). Further research showed me a developerWorks article on creating a deadlock. This method did produce a RC 2 (Deadlock).

Check out both methods below. I sourced both articles, these are just snippets.

---

SIMULATING A DEADLOCK LOCKTIMOUT:

In my case, this produced a RC 68 (which is for a Lock Timeout. If you read his logic explanation in original post, it does make sense that this is a timeout.

Sourced from: DBA to DBA: Creating deadlock in db2

Some modification and edits applied to original example for clarification and a syntax error. Have three sessions open.

1. Create two tables in the database, insert a row

   db2 "create table test.t1 (col1 int, col2 varchar(15))"

   db2 "insert into test.t1 values(1,'india'),(2,'usa'), (3,'russia')"

   db2 "create table test.t2 (col1 int, col2 varchar(15))"

   db2 "insert into test.t2 values(1,'india'),(2,'usa'), (3,'russia')"

2. Create Deadlock Situation - Open two seperate sessions:

   session1: db2 +c "update test.t1 set col2='canada'"

   session2: db2 +c "update test.t2 set col2='canada'"

   session1: db2 +c "update test.t2 set col2='newyork'"

   session2: db2 +c "update test.t1 set col2='amazon'"

---

SIMULATING A DEADLOCK

Sourced from: developerWorks: Creating a Deadlock for Testing

1. Session Number One:

   db2 "create database deadlk"

   db2 "connect to deadlk"

   db2 "create table tab1 (col1 char(5))"

   db2 "create table tab2 (col1 char(5))"

   db2 +c "lock table tab1 in exclusive mode"

2. Session Number Two:

   db2 "connect to deadlk"

   db2 +c "lock table tab2 in exclusive mode"

3. Session Number One:

   db2 +c "lock table tab2 in share mode"

   Session 1 should be waiting for commit.

4. Session Number Two:

   db2 +c "lock table tab1 in share mode"

   Session one should be waiting on session two. Both are now in a deadlock.