Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2003
    Location
    California
    Posts
    8

    Unhappy Unanswered: Sql Server 2k Lock Escalation

    Hello All, Do you know of any issues with locks after SP3a installation. I'm getting lock errors. This errors started happening after SP3a installation. The query below is the one resulting the lock error. It usually deletes around 800,000 records, but since the deletion is done in a batch of 500 records, I don't see why the lock is occurring. The lock configuration of the server is the default(min=5000,max=2147483647,config_value=0, run_value=0). The server has dual CPU with 3gig of RAM. Any input on this will be highly appreciated. Thanks. I tryied running this query with rowlock hint hoping it won't esclate into a table lock. But while running this I was monitoring sql acitvity using profiler and there was a lot of esclation going on. Can anyone help me on how I can avoid lock esclation.

    SELECT TOP 500 * FROM tbl1
    WHILE @@ROWCOUNT > 0
    BEGIN
    SET ROWCOUNT 500
    delete tbl1 WHERE mon_date < GETDATE() - 16
    END

    >>>>>>
    The SQL Server cannot obtain a LOCK resource at this time.
    Rerun your statement when there are fewer active users or
    ask the system administrator to check the SQL Server lock
    and memory configuration.
    [SQLSTATE HY000] (Error 1204). The step failed
    <<<<<<

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    Just a comment - doesn't make any difference but I usually do this like

    SET ROWCOUNT 500
    SELECT 1
    WHILE @@ROWCOUNT > 0
    BEGIN
    delete tbl1 WHERE mon_date < GETDATE() - 16
    END
    SET ROWCOUNT 0

    I presume you don't have a transaction somewhere which is forcing the locks to be held?

    Maybe something is rebuilding statistics automatically.

    Have a look at the query plan
    Try it with rowcount 1 to see what happens.

  3. #3
    Join Date
    Aug 2003
    Location
    California
    Posts
    8
    Thanks Nigel, I created an index on the column refrenced on the where clause and by also using rowlock hint , the query run without and problem. There was no lock esclation or deadlock. It seemd like the index took care of it. Its realy wierd.

    Originally posted by nigelrivett
    Just a comment - doesn't make any difference but I usually do this like

    SET ROWCOUNT 500
    SELECT 1
    WHILE @@ROWCOUNT > 0
    BEGIN
    delete tbl1 WHERE mon_date < GETDATE() - 16
    END
    SET ROWCOUNT 0

    I presume you don't have a transaction somewhere which is forcing the locks to be held?

    Maybe something is rebuilding statistics automatically.

    Have a look at the query plan
    Try it with rowcount 1 to see what happens.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •