Table lock escalation even with ROWLOCK hint

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Table lock escalation even with ROWLOCK hint

Author

Topic

spromtet
Starting Member

7 Posts

Posted - 2003-06-05 : 16:35:07

I have to run an update statement on a fairly large table every 15 minutes. The update affects anywhere from 10-50% of the rows every 15 minutes.

Even using the ROWLOCK hint, SQL Server 2000 still escalates the locks to a table lock. An application that reads the table uses nolock for reads so there's no problem there. I'm just concerned about users trying to update one of those rows.

For SQL 6.5, there used to be lock threshold value. This has been removed.

Any ideas?

Edited by - spromtet on 06/05/2003 16:36:17

Edited by - spromtet on 06/05/2003 16:36:38

Edited by - spromtet on 06/05/2003 16:37:25

Edited by - spromtet on 06/05/2003 16:50:01

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-05 : 16:47:28

Let me get this straight, you're potentially updating a 50% of a OLTP table every 15 minutes?

With what and why?

Log's Ahooooooyyyyyyy

do you dump the logs every 15 minutes too?

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-06-05 : 16:54:01

Instead of just updating 10-50% of the table, do the updates in batches by setting ROWCOUNT. For instance, we have a stored procedure that purges data that is older than 14 days. But we do the DELETEs in batches. For your scenario, I have set ROWCOUNT to 500 whereas we set ours to 20000.

What this code does is only delete 500 records at a time until there are no more records to delete (based upon the WHERE statement). You should be able to modify this code for your UPDATE statement.


SET ROWCOUNT 500

DECLARE @count INT

SELECT @count = COUNT(*)
FROM Table1
WEHRE DateColumn < (getdate() - 14)

WHILE @count > 0
BEGIN
  DELETE FROM Table1
  WHERE DateColumn < (getdate() - 14)

  SELECT @count = COUNT(*)
  FROM Table1
  WHERE DateColumn < (getdate() - 14)
END

SET ROWCOUNT 0

Tara

Edited by - tduggan on 06/05/2003 16:55:53

spromtet
Starting Member

7 Posts

Posted - 2003-06-05 : 16:56:34

The large update occurs because of an integration problem. We're getting the data from another database. We're using DTS to copy the data over to a staging table. The DTS package massages that data in the staging table. Then we do an update on the main table with the data from the staging table.

The main table is not updated that often by the client application.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources