MySQL的update语句什么时候锁行什么时候锁表?

MySQL（InnoDB引擎）中，执行`UPDATE`语句时的锁策略如下： ### 一、什么时候锁行？ 使用**索引条件**进行更新时，InnoDB只锁定涉及到的**具体行**，使用的是**行锁**。 示例： ```sql -- id为主键或有索引 UPDATE table_name SET column='value' WHERE id=100; ``` **特点**： - 锁定具体一行或多行，不影响其他行并发访问。 - 如果使用索引定位记录高效且准确，InnoDB默认都使用行锁。 ### 二、什么时候锁表？ 以下几种情况下，可能会升级为**表锁**或造成类似表锁的效果： 1. **更新语句的条件列无索引** ```sql -- name字段未建立索引 UPDATE table_name SET column='value' WHERE name='张三'; ``` - 此时InnoDB无法快速定位记录，会扫描整张表，产生范围锁（Gap锁）甚至全表锁，影响并发性。 2. **范围更新时，可能产生范围锁（Gap锁）** ```sql -- 范围更新，id有索引 UPDATE table_name SET column='value' WHERE id BETWEEN 10 AND 100; ``` - 这种情况会产生Gap锁，锁定一定范围的记录，阻塞部分范围的并发插入或修改操作，可能导致性能下降（但不是严格的表锁）。 3. **更新的行数比例非常大** - 虽然InnoDB通常不会自动将行锁升级成表锁，但如果一次更新几乎涉及全部记录，则表现类似于表锁，对并发性能有明显影响。 4. **显式表锁** - 执行`LOCK TABLES table_name WRITE;`时明确要求表锁。 ### 三、如何避免锁表？ - 条件字段添加索引，确保更新时快速精确定位记录。 - 避免大范围或模糊条件的更新操作。 ### 四、总结（简洁版） | 条件 | 锁类型 | 原因/表现 | |------|--------|-----------| | 有索引、精确条件更新 | 行锁 | 并发性能好 | | 无索引、模糊或大范围条件更新 | 范围锁或类似表锁效果 | 并发性能差 | 因此，在业务场景中，务必保证`UPDATE`的条件字段拥有索引，避免产生意外的表级锁竞争。

在 MySQL 中，`UPDATE` 语句的加锁策略主要依赖于**存储引擎**和**查询条件**： - **InnoDB** 默认使用**行级锁（Row Lock）**，但在某些情况下会升级为**表级锁（Table Lock）**。 - **MyISAM** 存储引擎默认使用**表级锁**。 ## **1. InnoDB 下的 `UPDATE` 语句锁定规则** ### **(1) 何时锁行？（行级锁，Row Lock）** ✅ **使用索引**（主键索引、唯一索引或普通索引）进行精确匹配时，InnoDB 采用 **行锁**： ```sql UPDATE employees SET salary = salary + 1000 WHERE id = 5; -- `id` 是主键 ``` - `id` 是主键索引，**只锁定 ID=5 的行**，不会锁住其他数据。 ✅ **使用范围查询时，可能会触发 Next-Key Lock（间隙锁 + 行锁）** ```sql UPDATE employees SET salary = salary + 1000 WHERE id BETWEEN 5 AND 10; ``` - **锁定范围 `[5,10]` 之间的行**，同时可能**加上间隙锁**，防止其他事务在这个范围内插入新数据。 ✅ **使用普通索引时，锁定索引覆盖的行** ```sql UPDATE employees SET salary = salary + 1000 WHERE department_id = 3; -- department_id 有索引 ``` - 如果 `department_id` 有索引，MySQL **会锁定所有符合条件的行**，但不会锁全表。 ### **(2) 何时锁表？（Table Lock）** ❌ **未使用索引，导致全表扫描** ```sql UPDATE employees SET salary = salary + 1000 WHERE name = 'Alice'; -- name 没有索引 ``` - `name` 没有索引，MySQL **只能全表扫描**，此时可能**锁住整个表**，避免并发写入导致数据不一致。 ❌ **使用 `LOCK TABLES`** ```sql LOCK TABLE employees WRITE; UPDATE employees SET salary = salary + 1000 WHERE id = 5; UNLOCK TABLES; ``` - `LOCK TABLES` **显式锁表**，所有其他事务必须等待锁释放才能访问 `employees` 表。 ❌ **MyISAM 存储引擎** ```sql UPDATE employees SET salary = salary + 1000 WHERE id = 5; ``` - **MyISAM** 只支持**表级锁**，任何 `UPDATE` 操作都会锁定整个表，直到事务完成。 ## **2. UPDATE 语句锁行 VS 锁表总结** | **情况** | **是否锁行？** | **是否锁表？** | **说明** | |---------|------------|------------|------------| | **使用索引精确匹配 (`WHERE id = 5`)** | ✅ 是 | ❌ 否 | 只锁定目标行（主键/唯一索引） | | **使用索引范围查询 (`WHERE id > 5`)** | ✅ 是 | ❌ 否 | 可能触发**Next-Key Lock**，防止幻读 | | **使用普通索引 (`WHERE department_id = 3`)** | ✅ 是 | ❌ 否 | 锁定索引覆盖的行 | | **未使用索引 (`WHERE name = 'Alice'`)** | ❌ 否 | ⚠️ 可能 | 可能导致**全表扫描，锁表** | | **显式 `LOCK TABLES`** | ❌ 否 | ✅ 是 | **强制锁表**，其他事务无法访问 | | **MyISAM 存储引擎** | ❌ 否 | ✅ 是 | **默认锁表**，任何更新都锁全表 | ## **3. InnoDB 的行锁如何优化？** ### ✅ **避免不必要的表锁** 1. **给 `WHERE` 条件字段加索引**： ```sql ALTER TABLE employees ADD INDEX idx_name (name); ``` - 避免全表扫描，减少锁表风险。 2. **用 `PRIMARY KEY` 或 `UNIQUE INDEX` 进行查询** ```sql UPDATE employees SET salary = 5000 WHERE id = 10; ``` - 主键索引保证只锁定一行，不会锁住整个表。 3. **减少范围查询，降低锁定范围** ```sql UPDATE employees SET salary = salary + 1000 WHERE id BETWEEN 5 AND 10; ``` - **范围查询可能会加 `Next-Key Lock`**，影响并发性能。 4. **避免 `FOR UPDATE` 误用** ```sql SELECT * FROM employees WHERE department_id = 3 FOR UPDATE; ``` - `FOR UPDATE` 会加锁，影响其他事务操作。 ## **4. 事务中的 `UPDATE` 加锁示例** ### **(1) 事务未提交时，行锁阻塞** ```sql -- 事务 A BEGIN; UPDATE employees SET salary = 5000 WHERE id = 1; -- 事务 A 锁住 ID=1 的行 -- 事务未提交，事务 B 需要等待 ``` ```sql -- 事务 B（并发执行） BEGIN; UPDATE employees SET salary = 6000 WHERE id = 1; -- 事务 A 未提交，事务 B 阻塞 ``` - 事务 B **必须等待事务 A 提交或回滚后才能执行**。 ### **(2) 死锁案例** ```sql -- 事务 A BEGIN; UPDATE employees SET salary = 5000 WHERE id = 1; UPDATE employees SET salary = 6000 WHERE id = 2; -- 等待事务 B ``` ```sql -- 事务 B（并发执行） BEGIN; UPDATE employees SET salary = 6000 WHERE id = 2; UPDATE employees SET salary = 5000 WHERE id = 1; -- 事务 A 已锁，发生死锁 ``` **解决方案** ✅ **确保事务操作顺序一致** ```sql UPDATE employees SET salary = 5000 WHERE id = 1; UPDATE employees SET salary = 6000 WHERE id = 2; ``` ✅ **使用 `NOWAIT` 避免死锁** ```sql UPDATE employees SET salary = 5000 WHERE id = 1 LOCK IN SHARE MODE; ``` ## **5. 总结** ### **🔹 InnoDB 下 `UPDATE` 语句加锁规则** 1. **使用索引（主键/唯一索引）** → **锁行** 2. **使用范围查询** → **可能锁住范围（Next-Key Lock）** 3. **未使用索引，全表扫描** → **可能锁表** 4. **MyISAM 存储引擎** → **默认锁表** 5. **显式 `LOCK TABLES`** → **强制锁表** ### **🔹 如何优化 `UPDATE` 语句锁机制？** ✅ **给 `WHERE` 字段加索引，避免全表扫描** ✅ **尽量使用主键索引更新数据，减少锁范围** ✅ **减少范围查询，避免 `Next-Key Lock`** ✅ **事务操作顺序要统一，避免死锁** ✅ **使用 `NOWAIT` 或 `SKIP LOCKED` 提高并发性能**