😈 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

Right table

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