August 9, 2006 at 12:21 pm
Considering the case of a 1 mil. rows table that is constantly accessed (online store scenario), is it possible to write an UPDATE statement that will affect lets say 700K rows but still letting users able to run SELECT statements on the table?
August 9, 2006 at 12:38 pm
the users can do a dirty read on the table while it is being updated!! of course their results are not durable after the update.
August 9, 2006 at 6:44 pm
SELECT Idetifier
INTO #ToUpdate
FROM Table
WHERE <Update Criteria>
WHILE EXISTS(select 1 from ToUpdate)
BEGIN
SET ROWCOUNT 1000
SELECT Identifier
INTO #CurrentUPDATE
FROM #ToUpdate
SET ROWCOUNT 0
UPDATE Table
SET ...
FROM #CurrentUPDATE
WHERE Table.Identifier = CurrentUPDATE.Identifier
DELETE FROM #ToUpdate
WHERE EXISTS (select 1 from #CurrentUPDATE where #ToUpdate.Identifier = #CurrentUPDATE.Identifier )
DROP TABLE #CurrentUPDATE
WAITFOR DELAY '00:00:01'
END
_____________
Code for TallyGenerator
August 10, 2006 at 3:33 am
Hi,
Sergiy's solution surely has the potential for updating 1000 rows at a time at unspecified physical locations throughout 'table'. This has the potential to lock disparate areas of the table until the transaction is complete.
If you have a clustered index and use that to order your update result set then you may at least be updating blocks of contiguous records, assuming your filter criteria also return a contiguous result set.
It might also be worthwhile adding a COMMIT after each update to release the locks created, or setting implict commits on.
You could use table hints to suggest a locking mechanism to SQL Server, e.g. WITH (ROWLOCK) or WITH (PAGLOCK) to use row or page level locking, but SQL server will override these hints and escalate the locking if it is more efficient to do so.
David
If it ain't broke, don't fix it...
August 10, 2006 at 5:55 am
David,
you're clearly wrong.
2 days ago I just updated 1,2 mil. rows on production system using this approach.
No one was locked. No one even noticed. Yes, it took whole day, but I wasn't in hurry.
I don't begin any transaction there, so I don't need to commit anything. Transaction started by UPDATE statement is committed when UPDDATE completed.
_____________
Code for TallyGenerator
August 10, 2006 at 8:02 am
Thanks guys, I thought there is an easy way to do the update, something like an update hint, that would direct the update statement to lock only one row at a time not to escallate at pages or table lock.
I tried
BEGIN TRAN
UPDATE test1 WITH (ROWLOCK) SET a = '7' WHERE a IN ('1', '2')
but when I run
SELECT * FROM test1 WHERE a = '9'
on a different connection is still blocked.
August 10, 2006 at 6:12 pm
Who gave you an idea about BEGIN TRAN?
Kill him.
UPDATE is a transaction itself.
If you would bother to read posts above you'd figure out why you SHOULD NOT use transactions.
_____________
Code for TallyGenerator
August 11, 2006 at 10:42 am
The UPDATE statement has an implicit transaction (when the connection Implicit Transaction is ON), but when you update multiple number of rows in a table, with or without explicitly starting a transaction you will still lock that table.
The process that you described earlyer I think is good because it give the users time to access the table between batches, but overall I think the table would be lock the same amount of time as with only one big update.
August 11, 2006 at 12:27 pm
As mentioned in the first response in this thread, you can enable 'dirty reads' as a possible solution.
This can be done at the statement level, using the NOLOCK hint in your select statements or at the connection level by changing the isolation level.
Using the batch method suggested by Sergiy will reduce the number of blocks that occur and significantly reduce the time that another request is blocked. However, this method can greatly increase the time it takes to complete the update. As you increase the size of your batches, the total time needed to do the update will decrease, but the amount and length of user blocking will increase.
Only you know your environment and what the priorities for your system are, so you will need to evaluate which is the best solution for you.
Good Luck!
John
August 12, 2006 at 1:01 am
I'm afraid, to enable dirty reads you need to rewrite all SP's (and ad-hoc queries from application?) accessing this table.
That's not what I'd like to do.
_____________
Code for TallyGenerator
August 13, 2006 at 8:22 pm
Why on earth are you updating 70% of a million row table that is being accessed by users on a regular basis? That's borderline insanity on the part of whoever designed the system...
So far as having to rewrite all of the procs to use the WITH (NOLOCK) hint.... not quite true... if you rename the table and create a surrogate view (named the same as the table originally was) with the appropriate transaction isolation level and using only SELECT * from one table, you will come real close to creating a "synonym" like they do in Oracle. There may be one or two places where you will get a locking clash so I'd recommend a test in a parallel environment first but it may work just fine.
If you have triggers, I wouldn't do it...
Note that the surrogate view is NOT a fix... it's a patch, a work around, and should be very temporary until you can figure out a better way to do things than updating such a large part of a large table with users in constant access...
Serqiy's method is one of the better ones but I still question the need for the massive update to begin with... somebody missed something big in the design...
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply