CODEWITHSUNDEEP
sqlsql-servertransactions
divinci
divinci

Reputation: 23119

MSSQL Transaction - INSERT only IF SELECT

I am looking for the best transaction/locking solution to this problem for MSSQL.

I have the following table - call it 'payments'

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

So we have our account balances:

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

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

I want to insert a new payment but first check in an atomic manner if the balance of the account will go into the negative.

Whats is the best way I can do this while keeping locks low?

Here is an unsafe version for starters:

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

Upvotes: 1

Views: 141

Answers (3)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 88861

Like this

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

Making the transaction SERIALIZABLE will techncially also work here, but it will allow multiple sessions to run the first query, then cause a deadlock on the INSERT. So it's better to just block the second session before it reads the data, so it can proceed after the first one is finished.

Upvotes: 1

hardikpatel
hardikpatel

Reputation: 310

SELECT * FROM dbo.payments

declare @accountId as INT=1 declare @newPaymentAmount as INT=-800

declare @balance as int set @balance = (SELECT sum(ISNULL([amount],0)) from [payments] where [accountId] = @accountId)

SELECT @balance if @balance + @newPaymentAmount > 0 insert into [payments] ([accountId], [amount]) values (@accountId, @newPaymentAmount) else RAISERROR('Insufficeint Balance',16,1)

Upvotes: 0

Ravi
Ravi

Reputation: 1172

use merge statement 

MERGE INTO payments AS TARGET 
USING (select sum([balance]) as amount, accountId from [payments] where [accountId] = @accountId group by accountId) AS SOURCE 
on(Source.accountId = Target.accountId and amount + newPaymentAmount > 0) 
WHEN MATCHED THEN 
insert into [payments] ([accountId], [amount]) values (@accountId, @newPaymentAmount);

Upvotes: 0

Related Questions