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, 1423🔥, 0💬
Popular Posts:
How To List All Stored Procedures in the Current Database in SQL Server Transact-SQL? If you want to...
How To Create a Stored Program Unit in Oracle? If you want to create a stored program unit, you can ...
How To Use DATEADD() Function in SQL Server Transact-SQL? DATEADD() is a very useful function for ma...
How To Start MySQL Server in MySQL? If you want to start the MySQL server, you can run the "mysqld" ...
Where to find MySQL database server tutorials? Here is a collection of tutorials, tips and FAQs for ...