1. The background

Recently, there have been some activities, so the system will do some small number of paths to do some suppression, not suppression, this suppression has a strange problem, unexpectedly a section of old code deadlock problem, the log is as follows:

After seeing the log, he was on his way to check for deadlocks. Of course, if you are not familiar with locks, you can take a look at the basics of database locks in my two articles: Why developers must understand database locks: and remember a magic mysql deadlock check

2. Problem analysis

The database code is as follows:

CREATE TABLE `order_extrainfo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `orderId` int(11) NOT NULL,
  `extraInfo` text NOT NULL,
  `appProductId` int(11) NOT NULL DEFAULT '0',
  `hostAppProductId` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `orderId` (`orderId`)
) ENGINE=InnoDB AUTO_INCREMENT=17835265 DEFAULT CHARSET=utf8mb4;
Copy the code

The SQL statement in question is as follows:

INSERT INTO `order_extrainfo` (orderId, "+"extraInfo, appProductId, hostAppProductId) VALUES (? ,? ,? ,?) ON"+"DUPLICATE KEY UPDATE extraInfo = ? , appProductId = ? ."+"hostAppProductId = ?
Copy the code

Insert deadlock has not occurred before and should be caused by an ON dpulicate key update. To find the deadlock scene at the time, type: SHOW ENGINE INNODB STATUS; To view the latest deadlock log:

Transaction 1 and transaction 2 are both waiting for the gap lock to be released.

Deadlock logs are generally caused by two transactions, so it will give some confusing. in fact, most deadlock logs are caused by more than two transactions, this is no exception, this is actually a bug in mysql, bugs.mysql.com/bug.php?id=… If you’re interested, take a look.

Start replicating the scene locally from the description in the bug:

A time line session1 session2 session3
1 begin; insert into xx
2 INSERT INTO order_extrainfo (orderId, extraInfo, appProductId, hostAppProductId) VALUES (158360183,”, 0, 0) ON DUPLICATE KEY UPDATE extraInfo = ”; begin;
3 1 row in affected; INSERT INTO order_extrainfo (orderId, extraInfo, appProductId, hostAppProductId) VALUES (158360184,”, 0, 0) ON DUPLICATE KEY UPDATE extraInfo = ”; begin;
4 INSERT INTO order_extrainfo (orderId, extraInfo, appProductId, hostAppProductId) VALUES (158360185,”, 0, 0) ON DUPLICATE KEY UPDATE extraInfo = ”;
5 commit;
6 1 row in affected; Deadlock found when trying to get lock; try restarting transaction

Note that session1,2,3 are incremented for each orderId

Deadlock occurred in session3.

The sequence of session1,2,3 is very easy to occur in our high concurrency, hence the large number of deadlock errors.

2.1 lock analysis

Insert into on duplicate key (duplicate key) insert into on duplicate key (duplicate key) insert into on DUPLICATE key (duplicate key)

This is on my local computer to test, first open:

set GLOBAL innodb_status_output_locks=ON;

set GLOBAL innodb_status_output=ON;

Mysql lock count: mysql lock count: mysql lock count: mysql lock count: mysql lock count: mysql lock count: mysql lock count: mysql lock count: mysql lock count: mysql lock count: mysql lock count

First execute the first SQL:

INSERT INTO order_extrainfo (orderId, extraInfo, appProductId, hostAppProductId) VALUES (158360183,”, 0, 0) ON DUPLICATE KEY UPDATE extraInfo = ”;

Enter the show engine innodb status command to check

Insert intention here is an implicit lock, the lock added here is actually x + GAP(minus infinity to infinity GAP) + insert intention three locks

Here we are executing the second SQL,

INSERT INTO order_extrainfo (orderId, extraInfo, appProductId, hostAppProductId) VALUES (158360184,”, 0, 0) ON DUPLICATE KEY UPDATE extraInfo = ”;

Found that the insert intent lock was being blocked by a gap lock.

Similarly, if we execute the third SQL, the insert intention lock will also be blocked by the first transaction gap lock. If the gap lock of the first transaction is committed, they will first acquire the GAP lock (from the lock information, there is no gap lock when blocked), and then obtain the insert intention lock. Session2 and session3 form a circular link, which eventually leads to deadlock.

2.2 Why is there gap lock

The gap lock is used to solve illusionary problems in RR isolation level. It usually appears in delete. In bugs.mysql.com/bug.php?id=… You can see it in this bug:

“Concurrent “INSERT … ON DUPLICATE KEY UPDATE” statements run on a table with multiple unique indexes would sometimes cause events to be Written to the binary log incorrectly” “

When we concurrently use INSERT… ON DUPLICATE KEY UPDATE, if we have multiple unique indexes, there may be a binlog error, that is, the replication between the master and slave is inconsistent. For details, please refer to the link

3. How to solve it

What if you encounter this problem? We have the following ways to solve this problem:

  • Using mysql5.6, you can see that this was introduced in 5.7 and won’t happen in 5.6
  • Use the RC level, there is no gap lock at the RC isolation level — do not use insert on Duplicate key update, use normal INSERTS. We ended up using this method because ON DUPLICATE KEY UPDATE is not necessary in the code
  • Create only primary keys in database tables, no other unique indexes.
  • Insert, catch, and update
  • Use insert ignore, then determine if update Rows is 1, and then decide whether to update.

If you find this article helpful to you, your attention and forwarding will be my biggest support.