SQL中的锁机制

1. 锁

为什么要加锁：

加锁是为了保证数据的一致性，防止并发冲突。这个思想在程序开发领域中同样很重要。在程序开发中也会存在多线程同步的问题。当多个线程并发访问某个数据的时候，尤其是针对一些敏感的数据（比如订单、金额等），我们就需要保证这个数据在任何时刻最多只有一个线程在进行访问，保证数据的完整性和一致性。

1.1. 锁的分类方式

1. 按锁粒度划分

锁类型	粒度	并发性	开销	死锁可能性	特点
行锁	小	高	大	容易	锁单行记录
页锁	中	一般	中	可能	锁定一个页，页中多行
表锁	大	低	小	少	锁整个表

• 附加粒度：区锁、数据库锁（不同引擎支持不同锁粒度）
• 锁升级机制：当锁资源占用超过阈值，系统将小粒度锁升级为大粒度锁以节省资源（如行锁升级为表锁）

2. 按数据库管理角度划分

锁类型	别名	行为
共享锁（S锁）	读锁	允许并发读，不允许写
排它锁（X锁）	写锁	排他访问，其他事务不能读写
意向锁		提前声明下级资源有锁，辅助加锁判断

加锁命令：

• 表级共享锁：LOCK TABLE table_name READ;
• 表级排它锁：LOCK TABLE table_name WRITE;
• 解锁：UNLOCK TABLE;
• 行级共享锁：SELECT ... LOCK IN SHARE MODE
• 行级排它锁：SELECT ... FOR UPDATE

意向锁的作用：

• 用于快速判断是否可以加表级锁，无需扫描所有记录
• 类型包括：意向共享锁（IS锁）、意向排它锁（IX锁）

1.2. 死锁

死锁的概念：

死锁（Deadlock）是指在并发系统中，两个或多个事务因争夺资源而互相等待，导致所有事务都无法继续执行的现象。

死锁产生的四个必要条件（Coffman条件）：

1. 互斥条件：资源一次只能被一个事务占用。
2. 请求与保持条件：一个事务已经持有资源，同时又申请其他资源。
3. 不剥夺条件：事务持有的资源在使用完之前，不能被强制剥夺。
4. 循环等待条件：两个或多个事务之间形成资源等待环路。

为什么共享锁也可能导致死锁？

多个事务同时持有共享锁时，尝试升级为排它锁可能发生资源互相等待，导致死锁

死锁处理机制：

• 数据库检测循环等待
• 选出牺牲事务，回滚重试

降低死锁概率的方式：

1. 如果事务涉及多个表，操作比较复杂，那么可以尽量一次锁定所有的资源，而不是逐步来获取，这样可以减少死锁发生的概率；
2. 如果事务需要更新数据表中的大部分数据，数据表又比较大，这时可以采用锁升级的方式，比如将行级锁升级为表级锁，从而减少死锁产生的概率；
3. 不同事务并发读写多张数据表，可以约定访问表的顺序，采用相同的顺序降低死锁发生的概率。

1.3. 从程序员的角度对锁进行划分

乐观锁和悲观锁并不是锁，而是锁的设计思想。

1. 悲观锁（Pessimistic Locking）

• 特点：认为并发冲突可能发生，操作前加锁
• 实现：直接使用数据库锁机制，如 SELECT ... FOR UPDATE
• 适用场景：高冲突、高一致性要求

2. 乐观锁（Optimistic Locking）

• 特点：认为冲突较少，不主动加锁，提交时校验
• 实现方式：

版本号机制：

• • 增加 version 字段
  • 更新语句中包含 WHERE version = ?
  • 若版本不一致则更新失败

时间戳机制：

• • 使用 update_time 字段进行乐观校验

• 适用场景：读多写少场景，性能优于悲观锁

两种锁的使用场景：

1. 乐观锁适合读操作多的场景，相对来说写的操作比较少。它的优点在于程序实现，不存在死锁问题，不过适用场景也会相对乐观，因为它阻止不了除了程序以外的数据库操作。
2. 悲观锁适合写操作多的场景，因为写的操作具有排它性。采用悲观锁的方式，可以在数据库层面阻止其他事务对该数据的操作权限，防止读 - 写和写 - 写的冲突。