MySQL’s InnoDB engine has four isolation levels for transactions, mainly to ensure data consistency.

InnoDB engine provides row level locking, table locking. MyISAM provides a table lock.

a

Yes, in the InnoDB engine, REPEATABLE-READ (RR) level, if multiple transactions vie for the same resource, deadlock will occur. At the RR level, MySQL provides next-key locks. If an index row has 10,11,13,20 then the possible next-key locks include: (infinitesimal, 10] (10,11] (11,13] (13,20)

That is, when you query 12, if the data is not found, the data in the range (12,13) will be locked. Next key lock definition can go to the official concrete view, here to do a demonstration.

CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(200) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1111 DEFAULT CHARSET=utf8;
Copy the code

// Check the isolation level, show variables like'%tx_isolation%'; / / SET the ISOLATION sectors SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {the READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE} // SET autocommit = 0;Copy the code

Deadlock demo

  1. First set the isolation level to RR and do not allow transactions to commit automatically
  2. Based on the above data, query in transaction 1
Select * FROM user select * FROM userwhere id=33 for update;
Copy the code
  1. The query is also made in transaction 2
SQL > select * FROM user; SQL > select * FROM userwhere id=34 for update
Copy the code
  1. Insert data in transaction 1
Insert into user values(35,'ac', 10);Copy the code

  1. Data is also inserted in transaction 2
insert into user values(34,'ac', 10)Copy the code

  1. You can see that a deadlock has occurred

The solution

  1. Set the timeout period for deadlocks

innodb_lock_wait_timeout=500

  1. Query for the transaction thread that is currently being locked and kill it
SELECT * FROM information_schema.innodb_trx; SELECT * FROM information_schema.innodb_trx; //trx_mysql_thread_id is the result of the previous command.kill trx_mysql_thread_id
Copy the code

The last

So that’s it