Collections:
Experiment with Dead Locks in MySQL
How To Experiment Dead Locks in MySQL?
✍: FYIcenter.com
If you want to have some experience with dead locks, you can create two windows running two mysql transactions in two sessions at the REPEATABLE READ transaction isolation level. Then run some UPDATE statements as shown in the tutorial exercise below:
(session 1) mysql> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; Query OK, 0 rows affected (0.00 sec) mysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) mysql> UPDATE fyi_links SET notes='Lock1' where id=101; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 (Exclusive lock (X) placed on row id=101)
Switch to session 2:
(session 2) mysql> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; Query OK, 0 rows affected (0.00 sec) mysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) mysql> UPDATE fyi_links SET notes='Lock2' where id=110; Query OK, 1 row affected (0.10 sec) Rows matched: 1 Changed: 1 Warnings: 0 (Exclusive lock (X) placed on row id=110) mysql> UPDATE fyi_links SET notes='Good1' where id=101; (Blocked to wait for exclusive lock (X) on row id=101)
Switch to session 1:
(session 1) mysql> UPDATE fyi_links SET notes='Good2' where id=110; (Tried to wait for exclusive lock (X) row id=110) ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction (Transaction terminated with a rollback)
Switch to session 2:
(session 2) (Exclusive lock (X) on row id=101 released) (Block is removed on this transaction is removed) Query OK, 1 row affected (10.01 sec) Rows matched: 1 Changed: 1 Warnings: 0
⇒ Error: Deadlock Found When Trying to Get Lock in MySQL
⇐ What Is a Dead Lock in MySQL
2017-07-21, 3136🔥, 0💬
Popular Posts:
How To End a Stored Procedure Properly in SQL Server Transact-SQL? Where the end of the "CREATE PROC...
What Are the Differences between DATE and TIMESTAMP in Oracle? The main differences between DATE and...
What is sqlservr.exe - Process - SQL Server (SQLEX?PRESS) in SQL Server? Process sqlservr.exe is the...
How To End a Stored Procedure Properly in SQL Server Transact-SQL? Where the end of the "CREATE PROC...
What Is Program Global Area (PGA) in Oracle? A Program Global Area (PGA) is a memory buffer that is ...