时间戳

首页 > 解决方案 > MSSQL 事务 - 仅在 SELECT 时插入

sql - MSSQL 事务 - 仅在 SELECT 时插入

问题描述

我正在为 MSSQL 寻找解决此问题的最佳事务/锁定解决方案。

我有下表 - 称之为“付款”

paymentId    accountId    amount
--------------------------------
1            1            100
2            1            -50
3            1            100
4            2            200 
5            2            -60

所以我们有我们的账户余额：

select [accountId], sum([amount]) as balance 
from [payments] 
group by [accountId]

accountId     balance
---------------------
1             150
2             140

我想插入新的付款，但首先以原子方式检查帐户余额是否会变为负数。

在保持低锁定状态的同时，我能做到这一点的最佳方法是什么？

这是初学者的不安全版本：

declare @accountId as int
declare @newPaymentAmount as int

declare @balance as int
set @balance = select sum([balance]) from [payments] where [accountId] = @accountId

if @balance + @newPaymentAmount > 0
  insert into [payments] ([accountId], [amount]) values (@accountId, @newPaymentAmount)
else
  throw

标签： sqlsql-servertransactions

解决方案

像这样

begin transaction 
declare @accountId as int
declare @newPaymentAmount as int

declare @balance as int
set @balance = select sum([balance]) 
               from [payments] with (updlock, serializable) 
               where [accountId] = @accountId

if @balance + @newPaymentAmount > 0
  insert into [payments] ([accountId], [amount]) values (@accountId, @newPaymentAmount)
else
  throw . . .
commit transaction

使事务 SERIALIZABLE 在技术上也可以在这里工作，但它将允许多个会话运行第一个查询，然后导致 INSERT 死锁。所以最好在第二个会话读取数据之前阻止它，这样它就可以在第一个会话完成后继续。

推荐阅读