Why is this subject to deadlocks?
-
Hi,
There still are issues, but we are going to oversee the whole process.
There is plenty of room for improvements
As to EXIST or NOT TO EXIST,
if the existance is against a non-indexed column it sure will make a difference. And Jeff's standpoint of using positive logic makes sense in the form of good practice, and that it will actually make a difference isc.
" In fact, except in one very rare case, try not to use negative or non-equi logic anywhere in your code "
Curiosity kills me, What is that case?
I wondered about this:
4. SELECTS are NOT the place to use WITH ROWLOCK... UPDATES are.
Isn't a select subject to locking table/pages/rows as any other dml clause. Do you mean it is preferable to let sql take larger locks while reading iof potentially more expensive row-locks.
But surely in serializable transaction level, less data locked will help concurrency!
And also this:
"9. DO make sure that the primary key is NOT clustered for this transaction table. And DO make sure that you have the primary key on the key columns in the code. "
How strongly do you feel about this?
IOC the 3 PK cols(int) are clustered, appx 7mil rows (+steady growth).
I understand that for inserts it can cost. But the table is also read a lot.
I expect page splits only on the last pages or so of data, since the first key is "semi-increasing".
You know, if I were to rethink, I would consider having PK IDENTITY clustered, and UNIQUE on the "natural" key columns. This to make the PK narrow, and other indexes less expensive. (well there is only 1 other index right now).
I think the different setups are a tradeoff between read perf, write perf and index size.
rockmoose
You must unlearn what You have learnt -
Why? My last solution has no SELECT whatsoever...
--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)
Intro to Tally Tables and Functions -
Cluster the IDENTITY column if you wish... the natural key you are inserting to should not be clustered for the very reason you mention.
On the ROWLOCK on the SELECT... go ahead an try it but I've not had any luck with it reducing any locks. You could try the UPDLOCK there, though.
So far as the only negative case where I've seen any real performance, it's actually a double negative...
WHERE somevarcharcol NOT LIKE '%[^0-9]%' -- Test for only numeric digits (ISNUMERIC doesn't hack it for that)
You still haven't told me... does the target table have any triggers.
Also, I didn't realize that the target table was heavily read by other processes... it may be that you need to adopt staging table technology where you do only inserts to the staging table and deletes as per a pointer established at the beginning of each transfer run. How long can you wait from the time the external app spawns a record until it absolutely must be in the target table?
--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)
Intro to Tally Tables and Functions -
Oh yeah... one other question... are you trying the latest code with the UPDATE/INSERT where there are no SELECTs?
--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)
Intro to Tally Tables and Functions -
Hi guys,
I am just dropping by to give a very belated thank you to everyone on this thread.
We implemented:
UPDATE t(HOLDLOCK,ROWLOCK)
WHERE ...
IF @@ROWCOUNT = 0
BEGIN
INSERT t
WHERE NOT EXISTS(SELECT * FROM t(HOLDLOCK,ROWLOCK) WHERE ..)
END
approach.
And we are not experiencing deadlocks now.
Yes, there is a trigger on the table.
And the client app has been recoded somewhat.
Anyway a big thanks for all your help!
rockmoose
You must unlearn what You have learnt -
Thanks for taking the time to provide the belated feedback
Lot's of times, you wonder if someone died or what? 
--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)
Intro to Tally Tables and Functions -
I just got swamped, I was meaning too, and so on...
Finally searched through my gmail 🙂
Thanks for the help Jeff, it was not my intention to delay this much.
And I am not dead... yet!
Everybody is doing a great job on these forums! So keep it up!
rockmoose
You must unlearn what You have learnt
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply