Can operations in a loop cause table locks? 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
begin

    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 &gt; @MinItem

end
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.