DEV Community

Kaziu
Kaziu

Posted on

๐Ÿ˜ˆ Let's run deadlock manually ! [SQL]

๐Ÿ’Ž What is deadlock?

This is a situation that multiple users wait for releasing each locks, and forever lock wont release.

๐Ÿ’Ž Let's do deadlock

1๏ธโƒฃ create these tables

-- schema
CREATE TABLE Lefty (
    Id INT NOT NULL AUTO_INCREMENT,
    Number INT,
    PRIMARY KEY(Id)
);
CREATE TABLE Righty (
    Id INT NOT NULL AUTO_INCREMENT,
    Number INT,
    PRIMARY KEY(Id)
);

-- data
INSERT INTO Lefty (Number) VALUES (1);
INSERT INTO Righty (Number) VALUES (1);
Enter fullscreen mode Exit fullscreen mode

Lefty table

Id Number
1 1

Right table

Id Number
1 1

2๏ธโƒฃ Let's start

1.At first both tables start transaction, in other words they start BLOCKING

โ–ผ Imagine 2 users(tabs) try to do something now at same time
sql

2.Execute UPDATE Righty SET Number = Number+1; from left tab
query block

As you see UPDATE SQL waits... and does't finish because Righty was locked before. It's gonna wait forever

3.While executes order2, run UPDATE Lefty SET Number = Number+1; from right tab
Then deadlock error appeared !! Both users wait for releasing each locks now.
deadlock

Nobody can stop running because everybody waits for someone stops to stop running!


If deadlock error didn't appear, you could change autocommit setting

mysql> SELECT @@session.autocommit;
+----------------------+
| @@session.autocommit |
+----------------------+
|                    1 |
+----------------------+

-- change autocommit to 0
SET autocommit=0;
Enter fullscreen mode Exit fullscreen mode

Thanks for reading ๐Ÿ˜Ž


(just note for me in the future)
Nobody can stop running because everybody waits for someone stops to stop running!
-> ใฟใ‚“ใช่ตฐใฃใฆใ‚‹ใ€ๆญขใพใ‚‹ๆกไปถใฏ่ชฐใ‹ใŒๆญขใพใฃใŸใ‚‰ใ€‚ใงใ‚‚ใฟใ‚“ใช่ชฐใ‹ใŒๆญขใพใ‚‹ใฎๅพ…ใฃใฆใ‚‹ใ‹ใ‚‰ใ€่ชฐใ‚‚ๆญขใพใ‚Šใพใ›ใ‚“ใ€‚

Discussion (0)