๐ 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);
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
2.Execute UPDATE Righty SET Number = Number+1;
from left tab
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.
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;
Thanks for reading ๐
(just note for me in the future)
Nobody can stop running because everybody waits for someone stops to stop running!
-> ใฟใใช่ตฐใฃใฆใใๆญขใพใๆกไปถใฏ่ชฐใใๆญขใพใฃใใใใงใใฟใใช่ชฐใใๆญขใพใใฎๅพ
ใฃใฆใใใใ่ชฐใๆญขใพใใพใใใ
Top comments (0)