none
Can this happen?

    Question

  • Env : SQL Server 2008 R2

    This is my SP

    BEGIN TRANSACTION;

    BEGIN TRY
        Delete...from my table(say TABLE1)

        If all records has been deleted from TABLE1

           Begin

                 Insert......TABLE1

           End

        IF @@TRANCOUNT > 0
            COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ....

        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
    END CATCH;

    Let's say there are two threads, A and B...

    A thread ran this sp..then..when B thread about to run this same sp, if the A thread was still running(before commit/or rollback), the B thread should wait until the the transaction of A thread is done...

    Since the A thread was holding that transaction until the commit/or rollback, B thread shouldn't be able to interleave...

    Is my understanding correct?

    However, the thing that I found..sometimes I noticed that I have duplicated records in TABLE1 that looks like for whatever reason, didn't work that way I was thinking ...

    Any idea?

    Thanks,

    Wednesday, July 10, 2013 7:22 PM

Answers

  • SQL Server is not a real-time system, that's not what relational databases are all about.

    If you don't want duplicate rows, a unique index/constraint is the way to go.

    If you need to coordinate multiple SPIDS/users for some reason you want some form of explicit locking, maybe app locks.

    HTH,

    Josh

    Thursday, July 11, 2013 12:17 AM

All replies

  • It depends on several things.

    To start with, what is your transaction isolation level?
    Read this if you do not know what that is:
    Transaction Isolation Level: TSQL syntax
    Transaction Isolation Level: Explanation

    Secondly, what types of locks are created. Do you get lock escalation to the table level? If you get duplicate records, does that mean you do not have a primary key? In that case your table is a heap. I should sit down and write out some possible examples, but I do not see why the second thread should not start the transaction if there is no table level lock. As long as its delete statement does not start with the rows already locked by the first process, weird things might start to happen.

    Could you give us the DDL of your table and the DDL of your proc? We might say something more conclusive.


    • Edited by Chris Sijtsma Wednesday, July 10, 2013 7:55 PM URLs changed to hyperlinks
    Wednesday, July 10, 2013 7:53 PM
  • 1. "A transaction always gets an exclusive lock on any data it modifies and holds that lock until the transaction completes, regardless of the isolation level set for that transaction"...in the msdn you linked...then does the level really matter?

    2. Let's say that I am going to use table hints for locking against the table in the delete statement..Then is this going to be enough?..or better to set the level on top or table hints for every sql statement?

    Thanks,

    Wednesday, July 10, 2013 8:17 PM
  • SQL Server is not a real-time system, that's not what relational databases are all about.

    If you don't want duplicate rows, a unique index/constraint is the way to go.

    If you need to coordinate multiple SPIDS/users for some reason you want some form of explicit locking, maybe app locks.

    HTH,

    Josh

    Thursday, July 11, 2013 12:17 AM
  • A transaction might get an exclusive lock on any date it modifies, but as long as you do not post your DDL, I cannot see of both processes actually would modify the same records. The "Delete ... from my table" might be some kind of logic that tries to delete records on a one by one basis. Also, the second process might kick in at the moment your table is empty, so the second process does not have anything to delete and subsequently will not lock any data. As long as there is no table lock, the second process is allowed to start at that moment. But what I find intriguing is the fact that you can get duplicate records at all. If you are able to spot them as being duplicates, you probably forgot to define a unique index at least, as JRStern already pointed out.

    • Edited by Chris Sijtsma Friday, July 12, 2013 8:30 PM reference to previous post added
    Friday, July 12, 2013 8:29 PM
  • In case if all the data from the table needs to be deleted, try using TRUNCATE, its a faster way with less logging.

    Restrictions on using Truncate 
    1. Are referenced by a FOREIGN KEY constraint.
    2. Participate in an indexed view.
    3. Are published by using transaction replication or merge replication.

    check this http://www.sqlserver-training.com/list-10-difference-between-truncate-delete-statement-in-sql-server/-

    Saturday, July 13, 2013 1:19 AM