How to prevent SQL Server lock the whole table?

  • zxmgh

    SSC Eights!

    Points: 911

    Hi,

    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?

    Thanks

     

     

  • SQL ORACLE

    One Orange Chip

    Points: 27807

    Something like the following snippet:

    CREATE TABLE #tmp (col1 INT, col2 VARCHAR(10))

    INSERT INTO #tmp WITH (rowlock) (col1, col2) VALUES (1, 'a')

    SELECT * FROM #tmp WITH (NOLOCK)

    DROP TABLE #tmp

     

  • zxmgh

    SSC Eights!

    Points: 911

    So adding WITH (ROWLOCK) to INSERT and UPDATE will tell SQL Server not to lock the whole table?

    I read http://support.microsoft.com/kb/323630 and it said SQL Server might still go table lock even WITH (ROWLOCK)

     

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    As the article states, it may.  But only to improve performance.  You'll have to test for your environement to see if lock escalations occur with a trace.  We can't do that for you...

  • zxmgh

    SSC Eights!

    Points: 911

    If there any other techniques to minimize the table lock?

    do you think I should add updlock too? WITH(UPDLOCK, ROWLOCK)

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    I'm not the expert on that matter.  I'll let someone else answer this one!

  • Rudyx - the Doctor

    SSC-Forever

    Points: 43690

    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.

    Regards
    Rudy Komacsar
    Senior Database Administrator

    "Ave Caesar! - Morituri te salutamus."

  • SQL ORACLE

    One Orange Chip

    Points: 27807

    Before running your script,

    SET TRANSACTION ISOLATION LEVEL Read uncommitted

    In your script, enforce rowlock.

  • John Mitchell-245523

    SSC Guru

    Points: 148319

    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.

    John

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply