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.

time Transaction1 Transaction2
T1 set autocommit=false;

begin;
T2 select * from account_info where uid = 1 for update;
T3 set autocommit=false;

begin;
T4 update account_info set current_amt = current_amt +1 where account = ‘tester’;
T5 update account_info set current_amt = current_amt +1 where account = ‘tester’;

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

trx_id trx_state trx_tables_locked trx_rows_locked trx_rows_modified trx_isolation_level
5831745 RUNNING 1 1 0 REPEATABLE READ
  • 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

trx_id trx_state trx_tables_locked trx_rows_locked trx_rows_modified trx_isolation_level
5831748 RUNNING 1 2 0 REPEATABLE READ

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.