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.