October 25, 2018 at 3:58 pm
I was given a stored procedure that has a query which frequently blocks other processes. Here is a schematic representation of the query (I removed actual table and column names):
BEGIN
UPDATE t
SET
t.col1 = 'some value',
t.col2 = 'another value'
FROM table1 t, #temp_table2 AS tt
WITH (SERIALIZABLE)
WHERE t.col3 = tt.col3 AND tt.col1 = 1;
DELETE #temp_table2
WHERE col1 = 1
END
In short, it updates large permanent table with values from a temp-table. Number of updated records is small, usually between 1 and 5. And then it deletes records from a temp-table for same criteria.
First thing that comes to my mind is to remove SERIALIZABLE hint from the query. Secong thing is that it's written in old-style of inner join, but I am not sure if this is the culprit for blockings.
Any other ideas please?
If I will comment --WITH (SERIALIZABLE), will it have negative implications?
About testing - I could not reproduce the same conditions in a test server, and I don't have the rights to test it in production.
And another observation - I found from sys.databases (both in prod and test) that this database in READ COMMITTED SNAPSHOT isolation level. The stored procedure does not have any settings for isolation level.
Thanks
October 25, 2018 at 4:31 pm
Yes, SERIALIZABLE is the most restrictive level there is, so it could lead to blocking.
Is the main table indexed in some way on col3?
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 26, 2018 at 7:22 am
Thanks, Scott.
Yes, col3 is indexed and it is unique index.
October 26, 2018 at 8:22 am
For only 1 to 5 rows, then it should'n take long at all.
I don't see why SERIALIZABLE would be needed at all for this UPDATE. SQL will still do its normal UPDATE locking. Try commenting out SERIALIZABLE and see if that corrects the issue.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply