SQL存储过程在批量处理、高频事务场景中应用广泛，当多个会话同时调用同一存储过程操作重叠的数据范围时，很容易出现锁竞争问题，轻则导致请求响应延迟，重则引发死锁使业务中断。通过实现细粒度行级锁，可以精准锁定需要操作的目标行，避免不必要的范围锁升级，有效降低锁冲突概率。
锁竞争的常见成因
存储过程中的锁竞争通常由以下几种情况引发：
- 存储过程内未明确指定查询条件，导致执行时触发全表扫描，数据库自动升级为表级锁
- 事务持有锁的时间过长，比如先查询数据再做大量业务逻辑处理，最后才提交事务，锁释放延迟
- 多个存储过程操作相同数据集合的顺序不一致，形成循环等待锁的情况，引发死锁
- 使用了过于严格的隔离级别，比如可重复读隔离级别下会自动加更多的间隙锁，扩大锁范围
细粒度行级锁的实现原理
细粒度行级锁的核心是只锁定当前事务需要修改或读取的特定行，而不是锁定整个表或者更大的数据范围。不同数据库的行级锁实现机制略有差异，但核心逻辑都是通过行记录的事务标识来判断锁的归属，只有持有对应行锁的事务才能修改该行数据，其他事务需要等待锁释放或者根据隔离级别读取快照数据。
不同数据库的实现方案
MySQL场景实现
MySQL的InnoDB引擎默认支持行级锁，需要在存储过程中通过合理的SQL写法触发行锁，避免锁升级。首先存储过程的事务隔离级别建议设置为读已提交，减少间隙锁的使用范围。
以下是一个更新用户账户余额的存储过程示例，通过主键条件精准锁定目标行：
-- 创建更新用户余额的存储过程
DELIMITER //
CREATE PROCEDURE update_user_balance(
IN user_id INT,
IN change_amount DECIMAL(10,2),
OUT result_code INT
)
BEGIN
DECLARE current_balance DECIMAL(10,2);
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
-- 出现异常时回滚事务
ROLLBACK;
SET result_code = -1;
END;
-- 开启事务
START TRANSACTION;
-- 通过主键查询并加行级排他锁，只锁定user_id对应的单行
SELECT balance INTO current_balance FROM user_account WHERE id = user_id FOR UPDATE;
-- 校验余额是否充足
IF current_balance + change_amount < 0 THEN
ROLLBACK;
SET result_code = -2;
ELSE
-- 更新目标行数据
UPDATE user_account SET balance = current_balance + change_amount WHERE id = user_id;
-- 提交事务，释放行锁
COMMIT;
SET result_code = 0;
END IF;
END //
DELIMITER ;
上述存储过程中，FOR UPDATE语句通过主键id查询，只会锁定user_id对应的单行记录，不会锁定整个user_account表，其他会话操作不同id的用户时不会受到阻塞。
SQL Server场景实现
SQL Server的行级锁可以通过查询提示指定，避免存储过程执行时默认使用页级锁或者表级锁。可以在UPDATE、SELECT语句后添加ROWLOCK提示，强制使用行级锁。
以下是SQL Server的存储过程示例：
-- 创建更新商品库存的存储过程
CREATE PROCEDURE update_product_stock
@product_id INT,
@sell_count INT,
@result_code INT OUTPUT
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
-- 查询时指定ROWLOCK行级锁提示，只锁定目标商品行
DECLARE @current_stock INT;
SELECT @current_stock = stock_count FROM product_info WITH (ROWLOCK, UPDLOCK) WHERE id = @product_id;
IF @current_stock < @sell_count
BEGIN
ROLLBACK TRANSACTION;
SET @result_code = -2;
END
ELSE
BEGIN
UPDATE product_info WITH (ROWLOCK) SET stock_count = @current_stock - @sell_count WHERE id = @product_id;
COMMIT TRANSACTION;
SET @result_code = 0;
END
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
SET @result_code = -1;
END CATCH
END
这里WITH (ROWLOCK, UPDLOCK)提示会让查询时直接加行级更新锁，避免锁升级，UPDLOCK保证在事务提交前其他事务无法修改该行，同时不会阻塞其他行的事务操作。
优化注意事项
实现细粒度行级锁时需要注意以下几点：
- 尽量使用主键或者唯一索引作为锁定条件，非索引字段的
FOR UPDATE或者ROWLOCK可能会失效，触发表级锁 - 控制事务的执行时长，存储过程中尽量避免在事务内做非数据库操作，比如调用外部接口、大量循环计算，减少锁持有时间
- 多个存储过程操作相同表时，统一数据访问顺序，比如都按照主键从小到大的顺序操作，避免死锁
- 定期监控数据库的锁等待情况，通过
SHOW ENGINE INNODB STATUS（MySQL）或者sys.dm_tran_locks（SQL Server）查看锁竞争热点，针对性优化
细粒度行级锁虽然能减少锁竞争，但也不是锁粒度越细越好，过多的行锁会增加锁管理的开销，需要根据实际业务的并发量和数据操作特点做平衡调整。