I know a bit about locking etc, that when a transaction is working on a table, if it is altering a certain amount of data it might try and lock the entire table (I'm sure that is over simplified, but I don't think it's important in this case, feel free to correct me).

I want to know whether single row operations within a while loop could cause the same kind of lock on a table? The operations are mostly inserts, each creating a single. Can this be optimized in the background to lock the entire table?

If it can't lock the whole table, can it have the same type of effect, monopolizing the resources or operations on that table until it is completed?

We are using MS SQL Server 2008r2, and a spring batch application is calling stored procedures in the database. The while loop in question is inside that stored procedure which commits as an entire transaction (not per loop or anything like that).

EDIT: Below is a cut down version of the loop (it is a production application):

select @MinItem = min(ItemId) from Item

while @MinItem is not null

    declare @x, @y, @z

    select @x = i.x
           @y = i.y
           @z = i.z
    from Info i
    where ItemId = @MinId

    insert into Sales (a,b,c)
    select x,y,z

    --two more inserts like above but to different tables, followed 
    --by an update to another table

    select @MinItem = min(ItemId) from Item
    where ItemId > @MinItem

  • Can you post an example of the loop you are using, and the DML statements being issued?
    – datagod
    Sep 10, 2012 at 3:20
  • @datagod added in a dumbed down example of what the script does
    – dann.dev
    Sep 10, 2012 at 3:30
  • 1
    Some insights may be gained here stackoverflow.com/questions/2248011/…
    – adam f
    Sep 10, 2012 at 6:39

1 Answer 1


If you have wrapped the transaction block at the around of the insert command it would be only locking that record at a time, but if you have defined the whole loop in the transaction block, all involved records will be locked until the operation is finished.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.