Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers 2

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

share|improve this answer
    
Sorry if I hadn't made myself more clear. The issue I'm trying to prevent is noy only reading a value that is not yet commited. If I set the isolation level this way and call the insert procedure 2x at the same time (Ex: DEBIT $20), and my current balance is $20, both will read the balance as $20, and execute, and that would give me -$20 for balance. But thanks a lot for the explanation of those isolation levels! –  Tiziani Jan 17 at 15:58
1  
@Tiziani - The two aforementioned isolation levels prevent changes being made by using locks. Even if you run several insert sp's simultaneously, only one insert_procedure will have the ability to modify the data, the others will simply have to wait their turn. –  Craig Efrein Jan 17 at 16:53
add comment

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.

share|improve this answer
    
Your solution solves my issue, but just to understand it better: you changed from "tablockx" to "xlock,rowlock". the later really guarantees that no insert can be made on the table before the end of my transaction? Thanks! –  Tiziani Jan 17 at 18:24
1  
@Tiziani If my solution solves your issue remains to be seen. Please post your table definition including indexes and keys, the where clause and explain how you want to update the balance table? –  Edward Dortland Jan 17 at 19:32
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.