An article explaining in detail how MySQL updates deadlocks in batches

This article brings you relevant knowledge about MySQL. It mainly talks about how MySQL updates deadlocks in batches. There are code examples. Friends who are interested can take a look below. I hope it will be helpful to everyone. .

The table structure is as follows:

CREATE TABLE `user_item` (
  `id` BIGINT(20) NOT NULL,
  `user_id` BIGINT(20) NOT NULL,
  `item_id` BIGINT(20) NOT NULL,
  `status` TINYINT(4) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_1` (`user_id`,`item_id`,`status`)) ENGINE=INNODB DEFAULT CHARSET=utf-8

The SQL statement is as follows:

update user_item set status=1 where user_id=? and item_id=?

Cause analysis:

 mysql的事务支持与存储引擎有关，MyISAM不支持事务，INNODB支持事务，更新时采用的是行级锁。这里采用的是INNODB做存储引擎，意味着会将update语句做为一个事务来处理。前面提到行级锁必须建立在索引的基础，这条更新语句用到了索引idx_1，所以这里肯定会加上行级锁。

 行级锁并不是直接锁记录，而是锁索引，如果一条SQL语句用到了主键索引，mysql会锁住主键索引；如果一条语句操作了非主键索引，mysql会先锁住非主键索引，再锁定主键索引。

This update statement The following steps will be performed:

1. Since a non-primary key index is used, you first need to obtain the row-level lock on idx_1

2. and then proceed according to the primary key Update, so you need to obtain the row-level lock on the primary key;

3. After the update is completed, submit and release all locks.

If a statement is suddenly inserted between steps 1 and 2: update user_item .....where id=? and user_id=?, this statement will first lock the primary key index, and then Lock idx_1.

A painful situation arises. One statement acquires the lock on idx_1 and waits for the lock on the primary key index; another statement acquires the lock on the primary key and waits for the lock on idx_1. This leads to a deadlock. Lock.

Solution:

1. Get the primary key

   select id from user_item where user_id=? and item_id=?

   of the record that needs to be updated first
2. Update

    update user_item set status=? where id=? and user_id=?

   one by one

3. Batch cycle repeats the first Just follow the first and second steps

Recommended learning: "MySQL Video Tutorial"

The above is the detailed content of An article explaining in detail how MySQL updates deadlocks in batches. For more information, please follow other related articles on the PHP Chinese website!