SQL Server Performance

A real noodle-scratcher of a transaction problem.

Discussion in 'SQL Server 2005 General Developer Questions' started by Righteousman, Oct 29, 2007.

  1. Righteousman New Member

    Hi Guys,
    I've got a problem with regards to a race condition in one of our stored procedures that is causing duplicate rows.
    The script below does the two things:
    1. Determines the SUM of all account transactions for a particular user.
    2. If the user has not exceeded 100, perform the transaction INSERT, otherwise return an error.
    *Note: The delay in between the SELECT and the INSERT is to simulate the race condition.
    Here's what's happening: When two connections execute the query in parallel they both perform the INSERT which is totally wrong! I've changed the TRANSACTION ISOLATION LEVEL to SERIALIZABLE to prevent phantoms from being inserted, however this results in a deadlock -- even when the two users are not the same. We have hundreds of these transactions going at the same time, so a deadlock on each one of them is not acceptable.
    How can I configure this script so that if User A and User B run the script they do not block each other, but if User A runs the script twice, one of them is deadlocked and killed. I've tried the (ROWLOCK) hint, but to no avail.
    Any help would be greatly appreciated!
    Ben
    Here is a simplified script I created to simulate the problem:-- Run this initially to create the test table.
    CREATE TABLE dbo.LockTest
    (
    ID INTEGER IDENTITY(1, 1) PRIMARY KEY NOT NULL,
    UserID INTEGER NOT NULL,
    TransactionBalance INTEGER NOT NULL,
    CreatedTime DATETIME NOT NULL DEFAULT GETDATE()
    )DECLARE @UserID INTEGER
    DECLARE @Balance INTEGER
    SET
    @UserID = 7
    SET @Balance = 100-- Start of logic.
    DECLARE @CurrentBalance INTEGER
    BEGIN TRANSACTION
    -- Initially return how much is
    SELECT @CurrentBalance = SUM(a.TransactionBalance)
    FROM dbo.LockTest a (ROWLOCK)
    WHERE a.UserID = @UserID WAITFOR
    DELAY '00:00:05:000' -- wait 5 seconds. IF(@CurrentBalance >= 100)
    BEGIN
    PRINT 'You''ve already gone over your limit!'
    END
    ELSE
    BEGIN
    INSERT INTO dbo.LockTest(UserID, TransactionBalance) VALUES(@UserID, @Balance)
    END
    COMMIT
    TRAN
  2. deepu_v04 New Member

    use the following link, that describes different locking options
    http://msdn2.microsoft.com/en-us/library/ms187373.aspx
  3. Righteousman New Member

    Thank you kindly for the reply. I've gone through this page already and tried the various types of locks, but nothing does the job. I either get deadlocks between all users, or phantom records slip in.
  4. FrankKalis Moderator

    If you already have tried to add
    SELECT @CurrentBalance = SUM(a.TransactionBalance)
    FROM dbo.LockTest a (ROWLOCK, HOLDLOCK)
    WHERE a.UserID = @UserID
    to no avail, I would probably start to look into application locks with sp_getapplock.
  5. Righteousman New Member

    For anyone else who might have run into this problem, the sp_getapplock procedure worked perfectly! Thank you very much FrankKalis for your great suggestion. I'd never heard of that procedure before, which is hard to believe since it's so powerful.
  6. Kewin New Member

    Seems that problem is solved :)
    Anyways, I was playing with the idea of getting the logic into a single INSERT statement instead of doing it in several steps.
    Not sure that it fits the definition of 'single', though the logic seems to work anyway.
    The idea is to insert based on a select based on the rules for the current balance should be < 100
    Haven't done any testing on locklevels and such, though..INSERT INTO dbo.LockTest(userId, TransactionBalance)
    SELECT @UserID, @balance
    FROM (
    SELECT a.userId, SUM(a.TransactionBalance) as currentBalance
    FROM dbo.LockTest a
    WHERE a.UserID = @UserID
    GROUP BY a.UserID
    HAVING SUM(a.TransactionBalance) < 100
    ) x
    IF (@@ROWCOUNT = 0)
    BEGIN
    PRINT 'You''ve already gone over your limit!'
    END
    Anyway, just a different approach that might be fun to try
    =;o)
    /Kenneth

Share This Page