I have a program which insert/update thousands of rows in a table (this table has millions of rows), some other programs are doing DML transactions on this table too. What we found is this program put a table lock on this table during the process so no other programs can do DML transactions on this table, even though they are process other rows of this table.
How to prevent SQL Server lock the whole table? and only locks the rows that are used by a specific program?
Possible posting the table structure and index structure may help - especially if you are updating non-primary index columns. But the bottom line is that you will probably need to run profiler to find out the DML running against the table and sift through the results.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Before you use locking hints or change the transaction isolation level, make sure you understand the implications of what you are doing.
Another road you may want to go down is having your process update only a few rows at a time (10000 maybe - you'll have to test) so that the whole table doesn't get locked. But if the update depends on data in the table not changing while it is happening, (this comes back to understanding transaction isolation levels) I'd say you have no choice but to grin and bear it... or run it out of hours.