Mysql single records can also cause deadlocks

In recent development, in the mysql Innodb engine, a record records events that can also cause locks.

Scene description

During the project pressure measurement, anomalies like the following were suddenly found:

com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction at sun.reflect.GeneratedConstructorAccessor247.newInstance(Unknown Source) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27) at java.lang.reflect.Constructor.newInstance(Constructor.java:513) at com.mysql.jdbc.Util.handleNewInstance(Util.java:406) at com.mysql.jdbc.Util.getInstance(Util.java:381) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1045) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)  at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060) at  com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2019) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1937) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1922)Copy the code

But when there is only one record locked in the code, there is no more than one resource.

example

First create experimental data:

DROP TABLE IF EXISTS `account_info`;
CREATE TABLE `account_info` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `account` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL.`current_amt` decimal(18.2) DEFAULT NULL,
  PRIMARY KEY (`uid`),
  UNIQUE KEY `idx_account` (`account`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

INSERT INTO `account_info` VALUES (1.'tester'.1.00);
Copy the code

Here, uid is the primary key and account is the unique index.

Mysql returns an exception from T5:

1213 - Deadlock found when trying to get lock; try restarting transaction, Time: 0.000000s
Copy the code

What? A deadlock? Locking a record also deadlocks, right?

The principle of analysis

In mysql’s InnoDB engine, row-level locking does not directly lock records, but locks indexes. If a SQL statement uses a primary key index, mysql will lock the primary key index. If a statement operates on a non-primary key index, mysql will first lock the non-primary key index and then lock the primary key index.

example

• Open a new session and execute the following statement

set autocommit=false;
begin;
select * from account_info where uid = 1 for update;
Copy the code

• Then open another session and perform the view InnoDB transaction

select * from information_schema.INNODB_TRX;
Copy the code

A summary of the contents appears, where trx_ROws_locked is 1

• After closing the previous session, open another session and execute the statement

set autocommit=false;
begin;
select * from account_info where account = 'tester' for update;
Copy the code

• Then open another session and perform the view InnoDB transaction

select * from information_schema.INNODB_TRX;
Copy the code

A summary of the content appears, where trx_ROws_locked is 2

As you can see in the above example, when a primary key index is used for a lock, only one lock occurs. When a non-primary key index is used for a lock, two locks occur. This is why a single record can cause a deadlock.