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:

To simulate a deadlock in MySQL, you need two or more independent transactions where each transaction holds a lock that the other transaction is trying to acquire, creating a circular waiting situation without waiting for a timeout. If you are consistently encountering a lock wait timeout instead of a deadlock error, it may be because both sessions are locking the same row in the same order or you are not creating the necessary circular dependency between locks for MySQL to detect a deadlock.

Let’s simulate a deadlock in dbForge Studio for MySQL. First, in one tab, start a transaction and lock the row in the actor table with actor_id = 1 by executing

START TRANSACTION; 

SELECT * FROM actor WHERE actor_id = 1 FOR UPDATE;

In the second tab, trying to run the same query on that row will cause it to wait indefinitely until the first transaction is finished with either ROLLBACK or COMMIT. If the transaction times out, you will get the error Lock wait timeout exceeded; try restarting the transaction.

To create an actual deadlock, open a new session in the second tab and start a transaction locking the row with actor_id = 2 using

START TRANSACTION;  

SELECT * FROM actor WHERE actor_id = 2 FOR UPDATE;

Then go back to the first tab and execute

SELECT * FROM actor WHERE actor_id = 2 FOR UPDATE;

followed by the second tab running

SELECT * FROM actor WHERE actor_id = 1 FOR UPDATE;

This creates a circular wait that MySQL detects as a deadlock, resulting in the error Deadlock found when trying to get lock; try restarting transaction.