CODEWITHSUNDEEP
sql-server-2008cursorlocking
mslliviu
mslliviu

Reputation: 1138

Sql cursor locking

I would like to help me with a question for which I couldn't understand the documentation from msdn. If I have the following sql code:

declare C1 cursor for select * from tableA
open C1
fetch next from C1 into @a, @b..
while @@fetch_status = 0
 .. do something very time consuming for each row
 fetch next from C1 into @a, @b..
end
close c1

When is tableA locked for updating? during cursor declare? during Open, during fetch? or from open to close?

Thank you

Upvotes: 2

Views: 7576

Answers (1)

marc_s
marc_s

Reputation: 754428

The row(s) that you're updating will be locked exclusively while the UPDATE statement runs. Or if you all the statements inside a single transaction, then these rows will be exclusively locked until the transaction commits (or is rolled back)

By default, SQL Server uses row-level locking. But if you make more than 5000 updates in a single transaction, SQL Server might do a lock escalation and lock the entire table in question to avoid having to handle too many individual locks.

Upvotes: 1

Related Questions