How to cause deadlock on MySQL

Question

For test purposes, I need to generate a "1213: Deadlock" on MySQL so that UPDATE query can't update a table.

I am not not quite sure how to cause deadlock?

Answer 1

There are numerous posts for this by using two sessions.

https://dba.stackexchange.com/questions/309/code-to-simulate-deadlock

http://www.xaprb.com/blog/2006/08/08/how-to-deliberately-cause-a-deadlock-in-mysql/

Method copied from the second article above

First, choose an unused table name. I’ll use test.innodb_deadlock_maker. Here are the statements you need to execute:

create table test.innodb_deadlock_maker(a int primary key) engine=innodb;
insert into test.innodb_deadlock_maker(a) values(0), (1);

Now the table and its data are set up. Next, execute the following on two different connections:

-- connection 0

set transaction isolation level serializable;
start transaction;
select * from test.innodb_deadlock_maker where a = 0;
update test.innodb_deadlock_maker set a = 0 where a <> 0;

-- connection 1

set transaction isolation level serializable;
start transaction;
select * from test.innodb_deadlock_maker where a = 1;
update test.innodb_deadlock_maker set a = 1 where a <> 1;

Answer 2

CREATE TABLE `table1` ( `id` INT NOT NULL AUTO_INCREMENT, `name` VARCHAR(255) NOT NULL, `marks` INT NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB;

INSERT INTO table1 (id, name, marks) VALUES (1, "abc", 5);

INSERT INTO table1 (id, name, marks) VALUES (2, "xyz", 1);

First Window

BEGIN;
UPDATE table1 SET marks=marks-1 WHERE id=1; -- X lock acquired on 1

Second Window

Update Again

BEGIN;
UPDATE table1 SET marks=marks+1 WHERE id=2; -- X lock acquired on 2
UPDATE table1 SET marks=marks-1 WHERE id=1; -- LOCK WAIT!

First Window (continued)

UPDATE table1 SET marks=marks+1 WHERE id=2; -- DEADLOCK!
COMMIT;