DEV Community

Cover image for Deadlocks | MySQL
jmbharathram
jmbharathram

Posted on

Deadlocks | MySQL

What is this article about?

This post is about a certain type of database locks called "Deadlocks". I would like to show you how they occur using a simple example.

How do the deadlocks happen?

Essentially deadlocks happen when multiple sessions are attempting to get exclusive locks on the same resources(table records)

What is the scenario?

Imagine a busy e-commerce website. Two book sellers are trying to update certain attributes of the books they have listed on this e-commerce website.

When they make changes on the website, the underlying application will modify PRODUCTS table. In reality, the application might update more than one table. But to keep it simple, we will focus on PRODUCTS table only.

Alt Text

What are the requirements?

  1. Disable autocommit in your MySQL database.

  2. Set the transaction level to "READ COMMITTED"

You may run these commands first in all your database sessions.

set autocommit=0;

set session transaction isolation level read committed;

How to simulate a deadlock?

Step 1: Seller A updates the quantity of the book "The common path to uncommon success" (product_id = 1).

Alt Text

Step 2: Seller B updates the price of the book "Tiny Habits" (product_id = 2) through a different session.

Alt Text

Step 3: Seller B also attempts to update the price of the book "Tiny Habits" (product_id = 1) in the same session as above.

Alt Text

This command will hang because Seller A still hasn't executed a "commit" operation.

Step 4: Seller A attempts to update the quantity of the book "The common path to uncommon success" (product_id = 1).

Alt Text

MySQL decided to rollback Seller A's transaction to avoid two sessions waiting on each other endlessly.

Top comments (0)