Post reply

Is a lock held on a table throughout the whole while loop?

  • August 1, 2016 at 4:31 am

    #328528

    Quick question, if I have some logic like the following:

    select a = 1;

    While a < 100000

    Begin

    Insert into table_a....

    Delete from table_b where....

    Select a = a +1;

    End

    Will the locks on the insert and deletes held on table_a and table_b be held for the duration of the while loop? I.e. if the process above took 10 minutes to run, could row/table locks be held on table_a and table_b for the whole 10 minutes or would new locks be held for each insert or delete iteration, which would allow other statements to not be blocked by this process? There is no explicit begin transaction....commit statements being issued here.

    More information probably is required, but curious to understand if while loops are seen as one transaction as a whole, or each individual statement being executed is a transaction.

    Thanks.

  • August 1, 2016 at 4:40 am

    #1892896

    If there's no explicit transaction, then the locks are released at the end of each statement.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 1, 2016 at 5:15 am

    #1892900

    Perfect, thank you.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply