【mysql共享锁与排他锁】

MySQL共享锁（Shared Lock）与排他锁（Exclusive Lock）总结

1. 基本概念

• 共享锁（S锁）

  • 作用：用于读取操作（读锁）。
  • 特点：允许多个事务同时持有共享锁，但阻塞其他事务的排他锁。
  • 语法：通过 SELECT ... LOCK IN SHARE MODE 显式加锁。

• 排他锁（X锁）

  • 作用：用于写入操作（写锁）。
  • 特点：独占数据，阻塞其他事务的共享锁和排他锁。
  • 语法：通过 SELECT ... FOR UPDATE 或写操作（如 UPDATE、DELETE）隐式加锁。

2. 核心区别

3. 应用场景

• 共享锁

  • 适用于需要读取数据且不允许其他事务修改的场景（如生成报表）。
  • 示例：

    BEGIN;
    SELECT * FROM orders WHERE user_id = 100 LOCK IN SHARE MODE;
    -- 其他事务可读，但不可修改该行
    COMMIT;
    

• 排他锁

  • 适用于需要修改数据且禁止其他事务读写的场景（如更新账户余额）。
  • 示例：

    BEGIN;
    SELECT * FROM accounts WHERE id = 5 FOR UPDATE;
    UPDATE accounts SET balance = balance - 100 WHERE id = 5;
    COMMIT;
    

4. 锁的粒度

• 行级锁（InnoDB支持）
  • 仅锁定目标行，其他行不受影响，并发性能高。
• 表级锁（MyISAM默认）
  • 锁定整个表，并发性能低，但实现简单。

5. 事务隔离级别的影响

• 读未提交（Read Uncommitted）
  • 无锁机制，可能导致脏读。
• 读已提交（Read Committed）
  • 排他锁仅在修改时持有，提交后释放。
• 可重复读（Repeatable Read）
  • InnoDB默认级别，通过间隙锁+临键锁防止幻读，共享锁和排他锁范围扩大至间隙。
• 串行化（Serializable）
  • 所有SELECT隐式加共享锁，写操作加排他锁，强制事务串行。

6. 死锁与处理

• 死锁场景
  事务A持有行1的共享锁并等待行2的排他锁，事务B持有行2的共享锁并等待行1的排他锁。
• 解决方案
  • MySQL自动检测死锁，回滚代价较小的事务。
  • 优化事务逻辑，减少锁占用时间；按固定顺序访问资源。

7. 注意事项

1. 显式锁与隐式锁
   • 显式锁需手动通过 LOCK IN SHARE MODE 或 FOR UPDATE 加锁。
   • 隐式锁由DML语句（如 INSERT、UPDATE）自动加锁。
2. 锁释放时机
   • 事务提交或回滚时释放所有锁。
   • 避免长事务占用锁，影响并发性能。
3. 存储引擎差异
   • InnoDB支持行级锁和MVCC，适合高并发。
   • MyISAM仅支持表级锁，适合读多写少场景。

8. 性能优化建议

• 减少锁持有时间：事务尽量简短，避免在事务内执行耗时操作。
• 合理选择隔离级别：根据业务需求调整，如非必要避免使用串行化。
• 使用覆盖索引：减少锁的范围，避免全表扫描。
• 监控锁争用：通过 SHOW ENGINE INNODB STATUS 分析锁冲突。

总结

共享锁和排他锁是MySQL实现并发控制的核心机制：

• 共享锁确保读一致性，允许多读并行。
• 排他锁确保写安全，强制串行修改。
  结合事务隔离级别和存储引擎特性，合理使用锁机制可平衡数据一致性与系统性能。