Updates are blocking.Tips welcome

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 2005 Forums

SQL Server Administration (2005)

Updates are blocking.Tips welcome

Author

Topic

oliviers
Starting Member

7 Posts

Posted - 2007-11-26 : 10:04:40

Hello,

We come accross situations where people are running big updates on the database (i.e. 50.000 updates).
Our problem is that those big updates are blocking other user updates.

Thanks to snapshot isolation, users can query (select) the database with no lock.
We rebuilt the indexes setting that the indexes used by the update procedure would not use page locks and only row locks.
We set that the database would update the statistics asynchronously.
Now we are still facing blockings and we would like to optimize the database to avoid those blockings.
What else could we check? Any tips regarding the way to avoid that problem is really welcome.

Thanks,

Olivier

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-11-26 : 10:46:26

Doing selects witn a nolock will cause dirty reads since you are getting back data that is updated soon after you select it. So your results from your select with a nolock may or may not be correct / current. You could recode your updates to only update one or a few rows at a time (with looping) rather than all 50,000 records at once. But again your selects may not be current.

oliviers
Starting Member

7 Posts

Posted - 2007-11-26 : 10:58:30

That's the purpose of snapshot isolation and users do agree with that.
The point is that users are updating computation results while others are selecting other data. The chance is near zero to have users who will select data being updated in the same time.

What we basically do is :
- Reset a range of rows to 0 (UPDATE SET x=0 WHERE xxxxxxxxxxxxxxxx)
- Bulk load data from text file to a temp table
- Update the main data table from the temp table.

Updates are blocking for some big subsidiaries.

Looking at the new (from SP2) Index Usage Statistics report, I see some indexes that are heavily range scanned, that report a lot of row locks but I'm unable to figure out how to avoid such page or table locks (I guess that when other users are blocked, a page or table lock is set...).

Olivier

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-11-26 : 11:00:13

How long do the large updates take to run? How often are large updates being run?

CODO ERGO SUM

oliviers
Starting Member

7 Posts

Posted - 2007-11-26 : 11:13:15

During a one month period, the Db is under heavy 'upload' activity during 5 days.
We're talking about a financial reporting tool, fiancial analysts are thus expecting a fast, reliable application.

Such big updates can be run 10 to 15 times a day.
Reset to 0 takes 30 seconds to run, update of data takes around 20 seconds to run. Thus around 45 seconds when everyone wanting to update data is blocked.

What makes our system 'nice' to our users is that it allows them to compute, see the result, not to be so happy with the result, modify the source data, start to compute again, etc...

The problem is that those blocking will always appear when you don't expect them and, of course, when the CEO is waiting for his reports :(

Olivier

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-11-26 : 11:18:03

run the updates in the batches of 1000 or 5000, to get fewer lock escalations from row to page to table.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-11-26 : 11:23:50

Recode the updates to make sure that the actual update is doing a lookup only by primary key: Run an initial selection to find the rows to be updated, and save the primary key values of those rows, along with the values to updated with into a temp table, and then run the update from the temp table. Also, make sure you are not updating rows that will not actually change. The rows will only be locked during the actual update, not while the selection is running. If the update still takes a long time to run, then update in small batches, like 5000 rows so that the updates take less time.


-- Sample two step update

declare @MyTable_UD_temp table 
	(
	MyTablePK int not null primary key clustered,
	MyTable_Col1_NewValue varchar(20)
	)

insert into @MyTable_UD_temp
select
	a.MyTablePK,
	MyTable_Col1_NewValue = 'New Value'
from
	MYTable a
where
	a.MyTable_Col <> 'New Value' or a.MyTable_Col is null

update MyTable
set 
	MyTable_Col1 = b.MyTable_Col1_NewValue
from
	MyTable
	join
	@MyTable_UD_temp a
	on MyTable.MyTablePK = a.MyTablePK

CODO ERGO SUM

oliviers
Starting Member

7 Posts

Posted - 2007-11-26 : 11:24:07

What's the best/easy way to achieve that?
That would, indeed, be nice if I could commit the update every 10000 rows.
A stored proc is doing all the stuff (update to 0 where xxxx and update to data from temp table). Are there some options I can set to commit every x record?

Thanks,

Olivier

oliviers
Starting Member

7 Posts

Posted - 2007-11-26 : 11:28:46

quote:
Originally posted by Michael Valentine Jones

Recode the updates to make sure that the actual update is doing a lookup only by primary key: Run an initial selection to find the rows to be updated, and save the primary key values of those rows, along with the values to updated with into a temp table, and then run the update from the temp table. Also, make sure you are not updating rows that will not actually change. The rows will only be locked during the actual update, not while the selection is running. If the update still takes a long time to run, then update in small batches, like 5000 rows so that the updates take less time.

-- Sample two step update

declare @MyTable_UD_temp table
(
MyTablePK int not null primary key clustered,
MyTable_Col1_NewValue varchar(20)
)

insert into @MyTable_UD_temp
select
a.MyTablePK,
MyTable_Col1_NewValue = 'New Value'
from
MYTable a
where
a.MyTable_Col <> 'New Value' or a.MyTable_Col is null

update MyTable
set
MyTable_Col1 = b.MyTable_Col1_NewValue
from
MyTable
join
@MyTable_UD_temp a
on MyTable.MyTablePK = a.MyTablePK

CODO ERGO SUM

We'll do some checks that way.
Thanks,

Olivier

oliviers
Starting Member

7 Posts

Posted - 2007-11-27 : 05:32:35

quote:
Originally posted by Michael Valentine Jones

Recode the updates to make sure that the actual update is doing a lookup only by primary key: Run an initial selection to find the rows to be updated, and save the primary key values of those rows, along with the values to updated with into a temp table, and then run the update from the temp table. Also, make sure you are not updating rows that will not actually change. The rows will only be locked during the actual update, not while the selection is running. If the update still takes a long time to run, then update in small batches, like 5000 rows so that the updates take less time.

That solution gave me the same performances (that are not bad) and the same locking behavior.

What puzzles me is that the index is built to use only row locking.
In one way or another, the lock is escalated to table lock as any single update on the table is blocked till the end of the mass update.

-- Sample two step update

declare @MyTable_UD_temp table
(
MyTablePK int not null primary key clustered,
MyTable_Col1_NewValue varchar(20)
)

insert into @MyTable_UD_temp
select
a.MyTablePK,
MyTable_Col1_NewValue = 'New Value'
from
MYTable a
where
a.MyTable_Col <> 'New Value' or a.MyTable_Col is null

update MyTable
set
MyTable_Col1 = b.MyTable_Col1_NewValue
from
MyTable
join
@MyTable_UD_temp a
on MyTable.MyTablePK = a.MyTablePK

CODO ERGO SUM

Olivier

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources