MySQL 批量更新 UPDATE 中的锁表机制

MySQL 的行锁（Row Lock）是基于索引实现的。也就是说，行锁是加在索引对应的记录上的。如果 SQL 语句执行时没有走索引，引擎将进行全表扫描（Full Table Scan），此时无法实现精确的行锁，取而代之的是锁定所有扫描过的行，其效果等同于表锁（Table Lock）。

表结构示例

以下是一张简单的用户表 SIMPLE_USER，存储引擎为 InnoDB：

CREATE TABLE SIMPLE_USER (
    ID BIGINT(20) NOT NULL AUTO_INCREMENT,
    NAME VARCHAR(32) DEFAULT NULL,
    PHONE VARCHAR(11) DEFAULT NULL,
    ADDRESS VARCHAR(32) DEFAULT NULL,
    PRIMARY KEY (ID)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

无索引场景：全表扫描导致锁表

如上面的建表语句所示，NAME 字段当前没有索引。当执行如下 UPDATE 语句时：

UPDATE SIMPLE_USER SET ADDRESS='David Road' WHERE NAME='David';

由于 WHERE 条件字段 NAME 缺乏索引，数据库需要进行全表扫描。在此过程中，数据库对该表施加的是表锁。这意味着在该 UPDATE 执行完成之前，所有对该表的其他 UPDATE 操作都将被阻塞，不允许并发执行。

有索引场景：行锁生效

若对 WHERE 条件的字段添加索引，例如本例中的 NAME 字段：

ALTER TABLE SIMPLE_USER ADD INDEX idx_name(NAME);

再次执行上面的 UPDATE 语句时，数据库能够利用索引定位到具体行。此时数据库对该表施加的是行锁，此时仅对 NAME='David' 的行的 UPDATE 是不允许的，对 NAME<>'David' 的行的 UPDATE 不受影响，从而实现了并发更新。

说明：本文所述机制基于 MySQL InnoDB 存储引擎。InnoDB 的行锁是通过给索引上的索引项加锁来实现的，因此只有当 SQL 语句走索引时，行锁才会生效，否则将锁住所有行（效果类似表锁）。