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 > @MinItem
end