Problem Description

Today's online consumer has a deadlock caused by insert. Here we use a DEMO to repeat the whole process of case and make a detailed analysis.

The table structure is as follows:

mysql> show create table test_table;
| Table      | Create Table                                                                                
| test_table | CREATE TABLE `test_table` (
  `id` int NOT NULL AUTO_INCREMENT,
  `a` int NOT NULL,
  `b` int DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `test_table_a_uindex` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

1 row in set (0.00 sec)

Table data:

mysql> select * from test_table;
+----+----+------+
| id | a  | b    |
+----+----+------+
|  1 |  1 |    1 |
| 20 | 20 |   20 |
| 50 | 50 |   50 |
+----+----+------+
3 rows in set (0.02 sec)

The code in the transaction is update d first, and then insert if the record does not exist.

Transaction 1:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update test_table set b = 1 where a = 30;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

Transaction 1 opens the transaction and update s a record that does not exist (gaps are locked on a, [20,50]).

Transaction 2:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update test_table set b = 1 where a = 31;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

Transaction 2 opens the transaction and update s a record that does not exist (at this point a, [20,50 with gap lock]).

Transaction 1:

mysql> insert into test_table values(30,30,1);
mysql> waiting...

Transaction 1 inserts a record and is blocked...

Transaction 2:

ysql> insert into test_table values(31,31,1);
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Transaction 2 also inserts a record, at which point errors, deadlocks, and rolls back the transaction.

Transaction 1:

mysql> insert into test_table values(30,30,1);
Query OK, 1 row affected (12.58 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

After transaction 2 rolled back, transaction 1 was inserted successfully and the transaction was committed successfully.

problem analysis

1. Transaction 1 and transaction 2 update each other, and the record of the operation does not exist. In this case, transaction 1 and transaction 2 add a gap lock to the a, [20,50] range, respectively.

Note: Gap locks are compatible with gap locks (shared locks)

2.An insert operation was performed in transaction 1, at which point the insert operation in transaction 1 was blocked because transaction 2 added a gap lock to the a, [20,50] range.

3. An insert operation was also performed in transaction 2, which was also blocked by a gap lock on transaction 1. At this time, transaction 1 releases a gap lock on transaction 2, while transaction 2 releases a gap lock on transaction 1, which constitutes a deadlock, so transaction 2 errors a deadlock and rolls back the transaction.

4. After transaction 2 rolled back, the gap lock of transaction 2 was released, the insert operation of transaction 1 was performed successfully, and transaction 1 was committed successfully.

Solution

Two solutions were considered:

Scheme One

Insert operations in a transaction before the transaction. select before each transaction begins. If the record does not exist to insert an empty record, you only need to perform an update operation in the transaction.

Disadvantages: An additional select operation may affect the performance of the interface, requiring re-pressure judgment.

Option 2

Reduce mysql transaction isolation level from RR to RC.

Disadvantages: hallucination problem

Taken together, since this cluster is only used for the storage of streaming water and statistics, scenario two is adopted: lowering transaction isolation.

Knowledge Expansion

Is there a magic reading problem with RR isolation level in InnoDB?

Before answering this question, let me assume you know that database isolation levels are defined for "current reads".

Let's start with an official InnoDB document:

For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE, and DELETE statements, locking depends on whether the statement uses a unique index with a unique search condition, or a range-type search condition. For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it. For other search conditions, InnoDB locks the index range scanned, using gap locks or next-key locks to block insertions by other sessions into the gaps covered by the range.

Roughly, at the RR level, if a query condition can use the upper unique index, or is a unique query condition, only row locks are added, and if it is a range query, gap locks or next-key locks (row locks + gap locks) are added to the range.

InnoDB's R isolation boundaries add GAP to the range and do not have magic reads.

Summary

• Transactions should not be too long, otherwise lock waiting, deadlock, etc. may occur
• insert operations are best not placed in transactions, otherwise deadlock problems (waiting for each other) can arise.