How to cause deadlock in MySQL

Question

For testing purposes, I need to create "1213: Deadlock" in MySQL so that the UPDATE query cannot update the table.

I'm not quite sure how to cause a dead end?

Answer 1

To do this, there are many messages 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/

The method copied from the second article above

First select the name of the unused table. I am using test.innodb_deadlock_maker. Here are the instructions you need to follow:

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 configured. Then make the following two connections:

- 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;

- 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);

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

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!

()

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