T-SQL – Enforce Atomic Operations x Locks

I have a database model that works similar to a banking account (one table for operations, and a trigger to update the balance). I’m currently using SQL Server 2008 R2.

Table OPERATIONS:

VL_CREDIT decimal(10,2)
VL_DEBIT decimal(10,2)

Table BALANCE:

DT_OPERATION datetime
VL_CURRENT decimal(10,2)

Procedure INSERT_OPERATION

GET LAST BALANCE BY DATE
CHECK IF VALUE OF OPERATION > BALANCE
   IF > RETURN ERROR
   ELSE INSERT INTO OPERATION(...,....)

The issue I have is the following:

The procedure to insert the operation has to check the balance to see if there’s money available before inserting the operation, so the balance never gets negative. If there’s no balance, I return some code to tell the user the balance is not enough.

My concern is: if this procedure gets called multiple times in a row, how can I guarantee that it’s atomic?

I have some ideas, but as I am not sure which would guarantee it:

• BEGIN TRANSACTION in the OPERATION procedure
• Some sort of lock on selecting the BALANCE table, but it must hold until the end of procedure execution

Can you suggest some approach to guarantee that? Thanks in advance.

There are two different isolation levels you can use within a session that will insure that the data read at the beginning of the transaction doesn’t change until the transaction with one of the isolation levels mentionned below is committed

BOL article on Isolation Levels

Serializable

Statements cannot read data that has been modified but not yet committed by other transactions

No other transactions can modify data that has been read by the current transaction until the current transaction completes.

Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.

REPEATABLE READ

Specifies that statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the current transaction completes.

The main difference between the two, is that the serializable isolation level prevents inserting new data anywhere within the range of rows.

USE MYDATABASE
GO

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
GO
BEGIN TRANSACTION;
sp_insert_operation
COMMIT TRANSACTION

There is also the option of setting snapshot isolation at the database level, but I don’t believe that would give you the level of isolation you are looking for.

Here is the relevant documentation ALTER DATABASE look under snapshot isolation

Update on Serializable

http://blogs.msdn.com/
Serializable isolation level relies on pessimistic concurrency control. It guarantees consistency by assuming that two transactions might try to update the same data and uses locks to ensure that they do not but at a cost of reduced concurrency – one transaction must wait for the other to complete

To prevent the scenario: that you can overdraw because two debit transactions run at the same time you would have to do the following: (with this table design)

If your business logic mandates that you first do a select(as per your example)

BEGIN TRAN

 SELECT VL_CURRENT
 FROM   BALANCE
 WHERE  conditions
 WITH (xlock,rowlock)

 --DO business validation

 INSERT INTO OPERATION(columns)
 VALUES(values)

 UPDATE BALANCE TABLE

COMMIT TRAN

Key is, that you hold an exclusive lock on the balance row from the moment you read it, until you update it. That way, as long as any statement that reads the balance table with a isolation level of read commited or higher, but NOT snapshot, will simply be blocked so you can’t have a overdraw situation.

If your business logic makes it possible to just update the BALANCE table it you could try:

BEGIN TRAN
 UPDATE BALANCE
 SET VL_CURRENT=NewBalance
 WHERE accountID=@ID

--Do business logic here
--If business logic fails Rollback
--If success continue with operation insert

INSERT INTO OPERATION(columns)
     VALUES(values)

COMMIT TRAN

However, there are tricky things with indexrs combined with where clause that influence locking that could influence results and definately concurrency so it might be good to provide your table definitions including indexes. So people could assist you.