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

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!
-> みんな走ってる、止まる条件は誰かが止まったら。でもみんな誰かが止まるの待ってるから、誰も止まりません。