>I have a problem and I am seeking a little help. I have an application that's trying to do 13000 Sql Updates in a transaction to 2 tables. It runs fine for about 2000 or so updates and then hangs. When I check the server for file locks, I notice that there are 2000 or so locks on each file. I cannot identify what's causing this and I am wondering if there is a lock limit on the server that maybe an issue here? Thanks in advance for any help.
Can you do this in one update stament or you want to run these 13000 in a transaction one by one?
Just yesterday I was reading a blog on this exact topic
http://aboutsqlserver.com/2011/05/12/locking-in-microsoft-sql-server-part-3-blocking-in-the-system/ (I read first 2 in the series and will read the rest soon) - these are really great and simple to understand blogs about isolation levels and lock types in SQL Server.
If it's not broken, fix it until it is.
My Blog