How to simulate a deadlock on a row in mysql?

Question

To simulate a lock in mysql I can grab the row with the following:

BEGIN;
SELECT * FROM table WHERE id=1 FOR UPDATE;

Now, if I try and update that row (from another connection) it will raise the following error after innodb_lock_wait_timeout seconds (default: 50):

(1205, 'Lock wait timeout exceeded; try restarting transaction')

How would I simulate a deadlock then, so I get an error that looks like:

Deadlock found when trying to get lock; try restarting transaction”

When I try and query or update the row?

---

Update: even when trying to simulate the mysql deadlock example, I get Lock wait timeout exceeded; try restarting transaction rather than a deadlock message.

Answer 1

First of all, refering to your last edit, the example in the manual should work. If it doesn't, there is either a fundamental problem, or you are missing some detail, so I would start there and make sure that you get it working.

The deadlock example has 3 steps, and I suspect you may have missed the last one:

1. T1: select

2. T2: delete. T2 has to wait for T1 now. Waiting means, that MySQL currently still sees a possible way that both T1 and T2 can finish successfully! For example, T1 can just commit now. Noone knows, so T2 waits for what happens. If you wait too long in this step, you will get a timeout (which is what I suspect happened).

3. T1: delete. This will result in a deadlock in T2. You need this last step to create a non-resolvable conflict.

You should try that example first, and carefully, as the devil is in the details. Leading to a detail in your own example:

You are using SELECT ... FOR UPDATE. FOR UPDATE is actually a way to reduce the number of deadlocks (which is the opposite of what you want), at the price of locking more restrictively. E.g. you have more situation where MySQL waits just to be safe, instead of going on and hoping it will work out eventually (or not, hence deadlock). Note that the example in the manual uses LOCK IN SHARE MODE for that reason.

So to modify and expand your own example to get a deadlock, you can do

 T1: START TRANSACTION;
     SELECT * FROM table WHERE id=1 LOCK IN SHARE MODE;

 T2: START TRANSACTION;
     UPDATE table SET id=2 WHERE id=1 
      -- wait

 T1: UPDATE table SET id=2 WHERE id=1 
     -- deadlock in T2 

For completeness (and to exclude a potential misunderstanding): the row has to exists, if your table is e.g. empty, you won't get a deadlock.

If you use FOR UPDATE instead, you don't get a deadlock, but T2 keeps waiting until you commit/rollback T1. It has to do with the way locking works, but you can maybe get an idea of that if you add a select to T2:

 T1: START TRANSACTION;
     SELECT * FROM table WHERE id=1 LOCK IN SHARE MODE;

 T2: START TRANSACTION;
     SELECT * FROM table WHERE id=1 LOCK IN SHARE MODE;
     -- fine in shared mode. Waits here if you use `for update`!

 T1: UPDATE table SET id=2 WHERE id=1 
     -- wait

 T2: UPDATE table SET id=2 WHERE id=1 
     -- deadlock 

If you replace both LOCK IN SHARE MODE with FOR UPDATE, T2 will wait at/before the select, until T1 commits, without a deadlock.