Long running updates and lock escalation

[Former Member]

I have a question about locking when updating tables from SAP on a MSSQL2005 database.

Once a month we have a report for updating several rows (1 mio) on a special ledger table.

This report is taken the update as one transaction because of the possibility for rolling back. It is ledger data so we want to keep the update safe.

When running this report the online users has access to the same table. The users can make INSERT into the same table without any problems.

But after 15-20 minutes runtime the whole table is locked by a table lock instead of row locks. Therefore the users goes into a "hanging" situations when trying to make INSERT into the table. The locks are held until our program has finished.

Is there any way to raise the level of row locking escalation on a MSSQL2005 database?

I have found the trace flag 1211 and 1224 but is it safe setting this on a production database?

[Former Member · Accepted solution]

Hii,,

I would suggest you to run the report when the users are not there as it is related to ledger table.

just schedule it at the low peak times.

Also the trace flags that you have mentioned can be harmful also

so i would better recommend not to use it.

Please refer to the link

http://blogs.msdn.com/sqlserverstorageengine/archive/2006/05/17/Lock-escalation.aspx

Also if u want to activate these traces I would recommend to talk to SAP before that

Rohit

[Former Member]

Check whether there is any lock in the DB level using tcode DB01. The topmost PID is responsible for the DBlock. Analyze what exactly the process is doing. Is it a standard SAP program or custom program. Kill that particular process wil release the lock.

Rgds,

SK