August 5, 2008 at 7:13 am
I have a stored procedure that is only going to be used very rarely by high level administrators which
clones a site and its data and categories so that another site can use it or the site can be rebranded. This means categories and other settings are copied, worked with and once finished the original values are updated to use the new IDs.
The stored procedure has many different UPDATES,SELECTS and INSERTS within in and is contained within a TRY/CATCH and a transaction so that if any part of it fails the whole thing is rolled back.
On one update statement within the proc I am updating a table that holds millions of rows so that old category IDs are switched to use the new category IDs. Usually when I do updates like this on large tables I use a top or set rowcount X to limit the number of rows affected to try and reduce locking
SELECT @Rows = 1, @TotalRows = 0
WHILE @Rows > 0
BEGIN
-- SET ROWCOUNT 500-- on 2k
UPDATETOP(500) DC
SET DC.CategoryFK = tmp.NewCategoryPK
FROMDATA_CV_CATEGORIES as DC
JOIN @TMP_CAT as tmp
ONtmp.OrigCategoryPK = DC.CategoryFK
WHEREDC.CategoryTypeFK = @OrigCategoryTypePK
SELECT @Rows = @@ROWCOUNT, @TotalRows = @TotalRows + @Rows
END
However what I want to know is whether there is any point doing this as I'm not too clued up how the locking works within a transaction. Because the code is within a transaction will a lock be put on the whole table until the commit/rollback anyway and therefore should I just as well do the UPDATE in one hit rather than stages?
Can someone give me a step through of what would happen in this situation.
Thanks in advance for any help or advice given.
August 5, 2008 at 7:43 am
A reason to break the updates into chunks using TOP/ROWCOUNT is to prevent an exclusive table locks. There are other advantages to do with transaction logging, but I think you have correctly worked out you dont get these because the whole procedure is in a transaction.
Nothing will be written to the DB until the final COMMIT.
Any updated row will therefore be unavailable (depending on your isolation level) until you either COMMIT or ROLLBACK.
I think you can have an isolation level which will allow reading on uncommitted transactions :exclamation: - but its probably not what you want. Certainly nothing else will be able to update the rows.
I think the TOP/ROWCOUNT method has the slight advantage of putting off an exclusive table lock until the number of updated rows passes some interal limit where SQL Server decides it will lock the table.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply