Problem description

There was a deadlock problem caused by insert in the online consumer today. Here is a DEMO to reproduce the whole process of the 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)
Copy the code

Table data:

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

The code in the transaction is to update 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
Copy the code

Transaction 1 starts the transaction and updates a record that does not exist (a, [20,50] will be gapped).

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
Copy the code

Transaction 2 starts the transaction and updates a record that does not exist.

Transaction 1:

mysql> insert into test_table values(30.30.1);
mysql> waiting...
Copy the code

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
Copy the code

Transaction 2 also inserts a record, which results in an error, deadlock, and rollback of 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)
Copy the code

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

Problem analysis

1. A gap lock will be added to the range a, [20,50] in transaction 1 and transaction 2, respectively, when an update operation is performed and the records of the operation do not exist.

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

Insert in transaction 1 is blocked because transaction 2 has a gap lock on range a, [20,50].

3. An INSERT was also performed in transaction 2, which was also blocked by a gap lock in transaction 1. At this time, transaction 1 is waiting for transaction 2 to release the gap lock, and transaction 2 is also waiting for transaction 1 to release the gap lock, which constitutes a deadlock. Therefore, transaction 2 reports an error “deadlock” and rolls back the transaction.

4. After transaction 2 is rolled back, the gap lock of transaction 2 is released, the INSERT operation of transaction 1 is executed successfully, and the commit of transaction 1 is successful.

The solution

Two solutions were considered:

Plan a

Insert a record before a transaction. Select a record before each transaction. If a record does not exist, insert an empty record into the transaction.

Disadvantages: The select operation is performed once more, which may affect interface performance. Therefore, you need to perform a pressure test again.

Scheme 2

Reduce the mysql transaction isolation level from RR to RC.

Disadvantages: there are magic reading problems

Overall, since this cluster is only used for the storage of pipelining and statistics, solution two is used: lower transaction isolation level.

Knowledge extension

Does InnoDB have a phantom read problem with RR isolation level?

Before I answer this question, I’ll assume you know that database isolation levels are defined for “current reads.”

First let’s take a look at the 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.

If an RR query can use a unique index or a unique query condition, then only a row lock is added. If a range query is performed, then a gap lock or a next-key lock is added to the range.

InnoDB RR isolation range will add GAP, there is no magic read.

summary

  • The transaction should not be too long, otherwise it is prone to lock wait, deadlock and other problems
  • Insert operations are best kept out of transactions, which can cause deadlock problems (mutual waits).