May 8, 2007 at 1:07 pm
I have a problem where 2 procs are deadlocking on the same table for an UPDATE; one has the KEY and the other has a PAGE file.
Both Stored Procs look like this:
UPDATE Table1 WITH (ROWLOCK)
SET ...
FROM Table1
INNER JOIN ....
What can I do to get them to get along?
May 8, 2007 at 10:16 pm
Two general approaches to avoid deadlocks are:
1. shorten you transactions
2. change the order of your transactions
In your case, you may try the first approaches and see if it helps.
May 9, 2007 at 9:12 am
I stripped all the transactions out, but thanks.
I am working on something that I think will alieviate this problem, but I have to wait for "peak time" to see if it works.
May 9, 2007 at 2:27 pm
ok I did something like this (and I have a feeling I am going to get flack for it)....
DECLARE @val bigint
SELECT @val=COUNT_BIG(*) FROM Table1 WITH (READCOMMITTED)
UPDATE Table1
SET ...
FROM Table1 WITH (NOLOCK)
INNER JOIN ....
This seems to prevent more deadlocks, BUT... I still get PAG locks
This is a 150K record table that will be moving up to 500K in the next year by my estimation. Is there anything specuial I can do with the paging that won't degregate the performance?
May 9, 2007 at 2:49 pm
It basically makes sure that any previous transactions finnish before proceeding. The "READCOMMITTED" won't allow a dirty read. So then I can do a "NOLOCK" below, because I don't care about any other processes affecting the table. The joins are just look-up tables I am using to supply the UPDATE statement. They are all using "NOLOCK."
May 10, 2007 at 8:32 am
Have you put the traces on?
DBCC TRACEON (1204, -1)
DBCC TRACEON (3605, -1)
The -1 writes the output to the SQL Server error logs. From there, you should be able to determine exactly where the problem lies.
P
May 10, 2007 at 9:32 am
that is how I determined it was Page locks.
May 11, 2007 at 8:03 am
Use an update (UPDLOCK) lock on the select rather than a (READCOMMITED)
If the count doesn't need to be perfect use this instead.
select @BeginCount=max(rowcnt) from mydb..sysindexes where id = object_id('mydb..table1')
May 11, 2007 at 8:20 am
yeah, I used UPDLOCK at first. The description Microsoft gives for this lock is very deceiving. It caused more deadlocks for me, because it would still lock the table up on a SELECT, when I am just making sure that previous transactions from calling SP's/etc had completed. This is exactly what READCOMMITTED does (which is default).
If I were to use "mydb..sysindexes," would that still acheive the same thing I am trying to accomplish above with the READCOMMITTED?
Thanks for the suggestions
May 11, 2007 at 9:01 am
What does the full UPDATE statement look like?
Does it contain a derived table?
May 11, 2007 at 9:08 am
I have about 30 update and insert statements that don't want to play nice with each other. Some derived, and others join a series of inner and left joins. I use look-ups inside the WHERE and SELECT statements on some as well. The thing is that it's not locking up on any of those objects, because I am using NOLOCK.
May 11, 2007 at 9:14 am
How many rows are being affected?
Could #temp/@temp tables help?
May 11, 2007 at 9:22 am
usually between 1 and 10 after the filtering is done.
May 11, 2007 at 9:26 am
OK, well it might seem like overkill, but could you get those into a @tmp_table, then do the update, ideally based on the Updated table's PK?
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply