Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
Can you post an example of the loop you are using, and the DML statements being issued? –  datagod Sep 10 '12 at 3:20
    
@datagod added in a dumbed down example of what the script does –  dann.dev Sep 10 '12 at 3:30
1  
Some insights may be gained here stackoverflow.com/questions/2248011/… –  adam f Sep 10 '12 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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