SQL乐观锁是一种基于假设并发冲突概率较低的并发控制机制，它不会在读取数据时加锁，而是在更新数据时通过特定条件判断数据是否被其他事务修改过，以此保证数据一致性。这种机制在大部分业务场景中能有效减少锁带来的性能开销，是很多高并发系统的常用选择。
SQL乐观锁的常见实现方式
1. 基于版本号（version）实现
这是最常用的乐观锁实现方式，需要在数据表中新增一个version字段，每次更新数据时，先读取当前记录的version值，更新时将version加1，同时通过version字段作为更新条件，只有version和读取时一致才会更新成功。
示例表结构如下：
CREATE TABLE product (
id INT PRIMARY KEY,
name VARCHAR(50),
stock INT,
version INT DEFAULT 0
);
更新库存的SQL语句如下：
-- 先查询当前数据的version值 SELECT stock, version FROM product WHERE id = 1; -- 假设查询到的version为0，库存为100，现在要扣减10个库存 UPDATE product SET stock = stock - 10, version = version + 1 WHERE id = 1 AND version = 0;
如果执行更新时返回的影响行数为0，说明数据已经被其他事务修改过，需要重新查询数据后再次尝试更新。
2. 基于时间戳（timestamp）实现
这种实现方式和版本号思路类似，不过使用的是时间戳字段代替version字段，每次更新时判断数据的时间戳是否和读取时一致，一致则更新同时将时间戳更新为当前时间。
示例表结构：
CREATE TABLE user_info (
id INT PRIMARY KEY,
nickname VARCHAR(30),
update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
更新用户昵称的SQL语句：
-- 先查询当前记录的update_time SELECT nickname, update_time FROM user_info WHERE id = 1; -- 假设查询到的update_time为2024-05-01 10:00:00，现在要更新昵称 UPDATE user_info SET nickname = '新昵称', update_time = CURRENT_TIMESTAMP WHERE id = 1 AND update_time = '2024-05-01 10:00:00';
这种方式的缺点是不同时钟可能会有时间偏差，极端情况下可能出现冲突判断不准确的问题。
3. 基于业务字段直接判断
如果表中没有版本号或时间戳字段，也可以直接使用要更新的业务字段作为判断条件，不过这种方式只适合对数据一致性要求不高的场景。
例如扣减库存时直接用库存数量作为条件：
-- 假设当前库存是100，要扣减10个 UPDATE product SET stock = stock - 10 WHERE id = 1 AND stock = 100;
这种方式的问题是无法区分库存是被其他事务扣减还是因为其他原因减少，而且如果业务字段本身可能被其他逻辑修改，很容易出现误判。
乐观锁的适用场景
- 并发冲突概率较低的场景：比如用户个人信息修改、商品详情编辑等，同一个数据同时被多个用户修改的概率很低，使用乐观锁可以减少锁的开销，提升系统吞吐量。
- 读多写少的场景：大部分操作是查询，只有少量更新操作，乐观锁不会因为加锁阻塞查询操作，能更好地发挥系统的查询性能。
- 无法使用数据库行锁的场景：比如业务逻辑复杂，更新操作涉及多个表，使用行锁可能导致锁范围过大或者死锁，此时乐观锁是更合适的选择。
- 分布式系统场景：分布式环境下不同节点之间的数据库行锁无法生效，乐观锁基于SQL条件的判断可以跨节点生效，保证分布式环境下的数据一致性。
乐观锁的不适用场景
如果业务场景并发冲突概率很高，比如秒杀场景下的热门商品库存扣减，大量请求同时修改同一条数据，使用乐观锁会导致大量更新失败，需要频繁重试，反而会降低系统性能，这种场景更适合使用悲观锁或者分布式锁。
使用乐观锁的注意事项
首先，更新失败后需要合理设计重试逻辑，不要无限重试，避免占用过多系统资源，可以设置最大重试次数，超过次数后返回给用户操作失败的提示。
其次，要保证更新条件的原子性，所有判断和更新操作必须在同一个SQL语句中完成，不能先查询再在应用层判断然后更新，否则会出现并发问题。
最后，选择实现方式时优先使用版本号方式，时间戳和业务字段判断都存在一定的局限性，只有在无法修改表结构的情况下才考虑后两种方案。