I. Introduction

Hello everyone, last time, my project had a fairly new bug compared to myself. That is the deadlock situation between transactions in SQL, then I also went to find out what the deadlock is to find a way to respond …. So today I have summarized some things I learned. To share with you, the limited knowledge is not deep enough, so everyone can comment for me to complete the best article.

II. Reiterate knowledge

As I said above, deadlock occurs in the process of performing many concurrent transactions, specifically, I will mention it later, Before that, we will repeat a little knowledge of transactions, this article. I did not go into the transaction details so I just summarized the knowledge to focus on the main issue that is deadlock

What is a transaction?

Transaction in SQL is a group of SQL statements, sequentially processing operations on the Transaction database used for the purpose bảo toàn data bảo toàn , 1 eat both to 0, with 4 properties:

• Atomicity : In principle “All or nothing”, the transaction will execute successfully if all SQL commands in that transaction run successfully. Conversely, the transaction will be stopped at the time an error occurred, and will rollback to the time before the transaction was run.
• Consistency : Ensures all operations on the database are changed after a transaction is successful and no errors occur.
• Isolation : Ensures this transaction operates independently of another transaction. Transaction 1 error will not affect transaction 2.
• Durability : Ensuring the outcome or impact of a transaction persists, even when a system failure occurs.

The transaction will run successfully and save the execution results of the SQL scripts on that transaction through the COMMIT command. If there is a ROLLBACK command when an SQL error occurs, the changes will be undone.

Use Transactions in MySQL

By default the autocommit property will be enabled , the transaction will be automatically completed without having to use either COMMIT or ROLLBACK. So we need to disable it: SET autocommit = 0 before starting a transaction. For example:

Use Transactions in Laravel

Surf everyone, Laravel has 2 ways to initiate a transaction. Option 1: The transaction will automatically COMMIT (if all SQL commands are run successfully in the transaction) or automatically ROLLBACK if an exception occurs.

Method 2: Laravel provides method 2: perform transactions manually vs try and catch , this is often used to do more than just ROLLBACK when there is an exception, such as Log .

Problem

Have you ever thought of the case where more than 1 concurrent transaction occurs? Then what will happen when those transactions impact on the same table, the same row? Or even more horrible is that this transaction occupies the resources that other transactions are in need … Let’s answer in the next sections. First of all, we need to go through the consistency principle of the relational database – MYSQL.

III. The consistency principle of relational database – MYSQL

MYSQL in particular and relational databases in general are the type of database with the highest data consistency. The data in the database is always in consistency at all times. That means with the same data, there can be no write (update, insert, delete) at the same time. Writing the same data will lead to mutual Lock to ensure the data is consistency. However, InnoDB in MYSQL handles SELECT with Consistent read mode, it will not Lock on the currently accessing table. That leads to the following situations:

• The Select command will not conflict with Update, Insert, and Delete commands. This can cause a number of problems such as, reading data incorrectly, data has not been updated with the latest updates if reading and writing data simultaneously.
• Select commands do not lock each other. Select commands perform share lock mode, which means that the execution of commands to read the same data at the same time is not affected by each other.
• The Insert, Update, and Delete commands are locked by row, not by table. That is, it is possible to execute those commands simultaneously on the same table as long as they do not dispute the same row of data.

  Lock to ensure consistency data is one of the major advantages of relational databases. So if you have used a relational database, you must know how to make use of it. Minimize the use of unlocking modes such as nolock mode because it can lead to misread data (rate, quantity, status …)

Based on the above principle, let’s analyze the above problem.

Analyze the problem

In the case of more than 1 transaction simultaneously processed on the same table, it will fall into the following cases (2 transactions: T1 & T2)

• Those 2 transactions are not processed on the same row:
  • If the config for that database is to be a lock table: the table will be locked until T1 is finished processing and committed then unlocked, then T2 will be processed. T2 sẽ được xử lý khi T1 chạy xong, trường hợp T1 chạy mãi không xong thì T2 sẽ bắn ra 1 exception là timeout
  • If the config for that database is to be lock row: row that is being processed in 2 transactions will be locked, but 2 transactions handle 2 different rows, so both are processed simultaneously.
• Those two transactions are processed into the same row: the processed row will be locked, T1 is finished processing, commit then T2 will execute, if T1 is processed slowly then T2 will fire exeption Lock wait timeout exceeded; try restarting transaction .

If I have the opportunity, I will learn more carefully and share with everyone about Locking in DB

Such is the case of Lock, it only affects partially on performance when transactions have to wait and execute sequentially. But, there will be cases where those transactions are conflict, resulting in no one being able to execute them. Such a case is known as DeadLock

IV. What is DeadLock?

Deadlock A state of 2 or more different transactions that cannot continue processing because each transaction is Locking the resources that other transactions need to process and release Lock. Leads to the transactions can never release Lock the resources it is holding.

For example the image above: Transaction 1 is locking the resource in Table 1, and it needs the resource in Table 2 to be processed to finish and release the resource. Table 1. At the same time, Transaction 2 is locking the resource in Table. 2, and need the resources in Table 1 to handle and release Lock. A waiting circle appears, at this time MYSQL will have to kill 1 transaction to release Lock that the transaction is holding, the other transaction is fully used by the resource and successfully executed (COMMIT or ROLLBACK).

Demo

Example 1: Create 2 tables: Table trans1 :

Table trans2

Start the execution of transaction: Transaction 1:

Transaction 2:

I use the DO SLEEP function to easily reproduce the DeadLock status, run 2 queries at the same time, an exception will appear.

Analysis: When 2 transactions are run, (1) & (3) of 2 Transansaction run successfully, at this time table trans1 is being trans1 by the Lock row just in (1), table trans2 is being locked the entire row in (3) -> Lock table. Sentence (2) will not be able to execute but on waiting state because table trans2 is locked -> cannot COMMIT and release Lock on table trans1 .

Similarly, sentence (4) will be on waiting state because table trans1 is trans1 1 row -> cannot COMMIT and release Lock on trans2 . A DeadLock state occurred , MYSQL was forced to kill a transaction.

Result: Transaction 1 will be executed, while Transaction 2 will be killed and an exception reported.

The second example is easier to understand: Transaction 1:

Transaction 2:

For this example, you self-analyze the problem that caused DeadLock .

Note: With example 1 above, if Transaction 2 is as follows:

Then there will not be a DeadLock situation because at this time (3) will not lock the entire row in table trans2 but only lock row vs id = 100 . At this point, 2 transactions will Lock different rows and will execute normally.

Try the example of read – write concurrently occur to see if DeadLock occurs, see if I mentioned that SELECT in InnoDB uses Consistent read mode. Transaction 1:

Transaction 2:

Result: Both transactions were successful, with no DeadLock problem, however, Transaction 2 did not return the latest data (updated by Transaction 1) but still display the old data. If you run transaction 2 again, the data has been updated to the latest.

V. DeadLock restriction methods

• Limit use of transactions if not absolutely necessary.

  To do this, we need to understand the meaning of using transactions and avoid abuse. It is advisable to only encapsulate the processing logic together into one transaction.
  A typical example is the classic money transfer problem: A transfers money to B, then A subtracts money before B can add money. If 1 of 2 actions fails, they will be restored before there is a transaction -> Need to use Transaction for 2 actions (order) A minus money and B plus money.

• Minimize the number of tables that need to operate in a transaction, the less the transactions that have the join tables, the lower the probability of deadlock.
• Keep transactions always with as little workload and processing time as possible, optimize queries, clean code
• When modifying multiple tables in one transactions or different rows within the same table, it is advisable to do those operations in consistent order every time.

BECAUSE. Application Retry Transaction in Laravel

As I mentioned above, Laravel supports two types of transactions. Both of these two ways can be applied with Retry . Simply run that Transaction loop if an exception occurs, up to x lần . Laravel supports teeth:

Perform retry Transaction 5 times. If you want to log each time DeadLock, or log the number of times to reTry, you can customize it as follows, the above code will be similar to:

To avoid dragging performance down, the business logic tries to leave out transactions, in transactions only executing queries. If all are left in transactions, each time reTry, the system will re-run the computation function, which takes more time. While its only purpose is to retry the query.

# Summary Here is a bit of my learning about DeadLock in MYSQL, and the application in Laravel. Hope to help everyone. Thank you everyone for taking the time to read the article, if there are any problems, don’t hesitate to ask questions under the comments section, let’s share our knowledge together, improve together, and remember to give me 1 vote. up Tks !!!

References

https://kipalog.com/posts/Mot-so-phan-tich-ve-deadlock-trong-co-so-du-lieu

https://laravel.com/docs/8.x/database#database-transactions

https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.html