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, 1551🔥, 0💬
Popular Posts:
How To Replace Given Values with NULL using NULLIF() in SQL Server Transact-SQL? Sometime you want t...
How To Use GO Command in "sqlcmd" in SQL Server? "sqlcmd" is a command line client application to ru...
Collections: Interview Questions MySQL Tutorials MySQL Functions Oracle Tutorials SQL Server Tutoria...
How To Round a Numeric Value To a Specific Precision in SQL Server Transact-SQL? Sometimes you need ...
Where to find answers to frequently asked questions on CREATE, ALTER and DROP Statements in MySQL? H...