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.
VL_CREDIT decimal(10,2) VL_DEBIT decimal(10,2)
DT_OPERATION datetime VL_CURRENT decimal(10,2)
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 TRANSACTIONin the
- Some sort of lock on selecting the
BALANCEtable, 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
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.
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
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.