locked
Concurrency issue? RRS feed

  • Question

  • I have a store proc with 2 major parts in a while loop.

    The basic store proc is liked

    SET TRANSACTION ISOLATION ....

    while ( @loop >0 )

    BEGIN TRAN

    1st part, it will look for the rows that match a criteria and create a Small table, i.e. SELECT * from LargeTableA A join LargeTableB B on A.id=B.id where thisCriteria = 'found' and columnWhileLoop = @loop

    2nd part, it will update the LargeTableA based on join of SmallTable.  i.e. UPDATE SET a.colume = 'found' from LargeTableA a join SmallTable b on a.id = b.id

    COMMIT

    SET @loop= @loop -1

    end 

    For some reason, the count from Update (rows being updated) isn't the same as it executed in SSMS, without the loop, it is much shorter.

    The database is set to snapshot as well as read_committed_on.  I have tried set transaction levels to all of these:

    READ UNCOMMITTED     | READ COMMITTED -- would have been identical to result from SSMS; since SSMS by default is set READ COMMITTED.     | REPEATABLE READ     | SNAPSHOT     | SERIALIZABLE

    I have also tried. all of these table hints

      | HOLDLOCK   | PAGLOCK   | READCOMMITTED   | READCOMMITTEDLOCK   | REPEATABLEREAD   | ROWLOCK   | SERIALIZABLE   | TABLOCK   | TABLOCKX   | UPDLOCK   | XLOCK

    on both the SELECT and UPDATE statements.

    The result is still inconsistent Count on Result when executing as a store procedure.I have removed the indexes on the join and the where criteria

    The result after index removed is even worst, far fewer rows being updated.

    Someone think it is because of the update is too slow. Some think it is SAN configuration.

    How to fix this problem?


    -- IBM has jobs for USA employee to work oversea (India) and get their wages.

    Thursday, August 9, 2012 2:04 AM

All replies