mysql如何使用行锁和表锁_mysql锁机制操作方法

行锁基于索引锁定单行，支持高并发；表锁锁定整表，适用于大批量操作。InnoDB默认自动加行锁，MyISAM仅支持表锁。使用SELECT ... FOR UPDATE加排他锁，SELECT ... LOCK IN SHARE MODE加共享锁；手动表锁用LOCK TABLES READ/WRITE，需UNLOCK TABLES释放。避免死锁需缩短事务、固定访问顺序，并捕获错误重试。

MySQL 的锁机制是数据库并发控制的重要手段，主要用于保证数据的一致性和完整性。在高并发场景下，合理使用行锁和表锁可以有效避免脏读、不可重复读和幻读等问题。下面介绍 MySQL 中行锁与表锁的使用方法及操作细节。

行锁（Row-Level Locking）

行锁是 InnoDB 存储引擎特有的锁机制，它只锁定当前操作涉及的数据行，其他行仍可被其他事务操作，因此并发性能较高。

使用条件： 行锁生效的前提是查询条件必须走索引（最好是主键索引），否则会退化为表锁。

常见操作方式：

• 在执行 UPDATE、DELETE 操作时，InnoDB 自动对涉及的行加排他锁（X锁）。
• 使用 SELECT ... FOR UPDATE 对查询结果集中的每一行加排他锁，常用于悲观锁场景。
• 使用 SELECT ... LOCK IN SHARE MODE 加共享锁（S锁），允许其他事务读但不能修改。

示例：

-- 事务A执行
START TRANSACTION;
SELECT * FROM users WHERE id = 1 FOR UPDATE;
-- 此时其他事务无法修改id=1的记录，直到事务提交或回滚

表锁（Table-Level Locking）

表锁是对整张表进行加锁，MyISAM 和 InnoDB 都支持表锁，但 MyISAM 只支持表锁，而 InnoDB 在特定情况下也会升级为表锁。

适用场景： 大批量数据操作、统计分析等低并发但大范围操作的场景。

手动加表锁方法：

• LOCK TABLES 表名 READ：加共享锁，其他事务可读但不可写。
• LOCK TABLES 表名 WRITE：加排他锁，其他事务不可读也不可写。
• UNLOCK TABLES：释放所有表锁。

示例：

-- 加读锁
LOCK TABLES users READ;
SELECT * FROM users; -- 可执行
INSERT INTO users VALUES(...); -- 报错，不允许写
-- 加写锁
LOCK TABLES users WRITE;
UPDATE users SET name='Tom' WHERE id=1; -- 允许
-- 其他连接任何操作都会被阻塞
UNLOCK TABLES; -- 释放锁

自动锁与手动锁的选择

InnoDB 通常在事务中自动管理行锁，无需手动干预。只有在特殊需求下才需要显式加锁。

建议：

• 尽量使用事务 + 行级锁（如 FOR UPDATE）来处理高并发写操作。
• 避免长时间持有锁，尽快提交事务以减少锁等待。
• 检查 SQL 是否命中索引，防止行锁升级为表锁。
• 监控锁等待情况：可通过 SHOW ENGINE INNODB STATUS 查看锁信息。

死锁与锁等待处理

当多个事务相互等待对方持有的锁时，可能发生死锁。InnoDB 会自动检测并回滚其中一个事务。

应对措施：

• 保持事务简短，减少锁持有时间。
• 按固定顺序访问表和行，降低死锁概率。
• 设置合理的锁等待超时时间：通过 innodb_lock_wait_timeout 参数调整。
• 应用层捕获死锁错误（错误码 1213），并实现重试逻辑。