How to prevent SQL Server lock the whole table?

Question

Post reply

How to prevent SQL Server lock the whole table?

zxmgh

SSC Eights!

Points: 911
More actions
November 6, 2006 at 1:39 pm

#61008

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

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

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

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 1

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 More actions · Answer 2

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 More actions · Answer 3

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 More actions · Answer 4

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 More actions · Answer 5

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

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 6

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."

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 7

Before running your script,

SET TRANSACTION ISOLATION LEVEL Read uncommitted

In your script, enforce rowlock.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 8

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