How to solve the conflict between INSERT and DELETE
-
Hi, I have an appliction which run the following two SQLs in the order:
1.INSERT INTO tab1 WITH (ROWLOCK)
2.DELETE from tab1 WITH (ROWLOCK)
This appliction is executed by hundreds of users at the same time. Normally these SQLs only takes less than 1 seoncd. Sometimes I saw significantly delay on these SQLs - around 10 seconds. It seems that they are blocking each other (when updating clustered index?), is there a way to solve this conflict?
Thanks
-
Use Transactions is better then rowloc
begin tran
1.INSERT INTO tab1
2.DELETE from tab1
commit tran
regards
john
-
Do you really execute the DELETE with no WHERE clause? That simply can't be good/right.
And if it is, then you certainly don't want rowlock hint specified.Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
I believe adding the transaction increases the chances of a deadlock... better to let it wait than to form deadlocks.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply