(sql 2008) t-sql concurrency question.

Question

(sql 2008) t-sql concurrency question.

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 1

Craig Farrell (9/19/2010)
Ah, that makes sense, you're avoiding the necessity of the double call by using the running total method that's undocumented.
Btw, if you force the tablockx, you won't get deadlocks, either. You might get a heck of a wait cycle for the proper locks if this gets called at a rediculous volume, but you'll avoid deadlocking.
That's a neat piece of code ya got there though... Look, butterflies! (Swipes it, and makes off like a thief)

Heh... thievery of code is the highest compliment one could have in this business. Thanks for the compliment.

BTW... [font="Arial Black"]the 3 part UPDATE IS, I repeat, IS documented in Books Online[/font]. Go lookup UPDATE and see. Using it as an ordered update for running totals is what isn't documented.

--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)

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 2

Heh... I'm just a step behind you on my posts...

--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)

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 3

Jeff Moden (9/19/2010)
Heh... I'm just a step behind you on my posts...

I've got the advantage, I'm west coast, so it's not beddie bye time for me yet, so I'm still running on all cylinders sitting here chowing on dinner watching Netflix. 🙂

And... yeah... found the documentation on the 3 part update. Sorry bout that, you're absolutely correct, on both parts.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Barkingdog SSCoach Points: 18670 More actions · Answer 4

Jeff,

You wrote

>>> At any rate, the WORST thing you could possibly do is use a BEGIN TRANSACTION on this problem.

Why is that the case?

In fact, my solution is exactly the same as Craig's (including the Begin Transaction...) except I used an UPDLOCK instead of his TABLOCK hint.

Barkingdog

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 5

Barkingdog (9/19/2010)
Jeff,
My solution is the same as yours except I used:
SELECT @return= NextID FROM seqNum with (UPDLOCK)
Given the table has only a single record I think the solutions are equivalent. But in a way the itnent seems clearer using TABLOCK instead of UPDLOCK.
Barkingdog

First, Craig is correct... it's his code you speak of.

Second, be a hero right now. Seriously. Plan and code NOW for what you know will happen... they're gonna add other tables that need this and they're going to add rows to your seqNum table to cover it. Take a look at the code I posted. It's fairly well bullet proof, is scalable, and you only have to do it once. 😉

--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)

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 6

Barkingdog (9/19/2010)
Jeff,
You wrote
>>> At any rate, the WORST thing you could possibly do is use a BEGIN TRANSACTION on this problem.
Why is that the case?
In fact, my solution is exactly the same as Craig's (including the Begin Transaction...) except I used an UPDLOCK instead of his TABLOCK hint.
Barkingdog

Read what I wrote about his code. The explanation for why you shouldn't use a BEGN TRANSACTION in this code is the same for your and his. And I'm not so sure that the lock you put on it is such a good thing compared to just hitting it with an update instead of a SELECT/UPDATE pair.

--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)

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 7

BarkingDog, the reason Jeff's solution is better then mine/ours is speed. His update method gets in and out in one action, whereas the select/update pair takes two. You can get higher concurrency and more use from the code, especially if you're not going to go to exclusive rowlock levels on the transaction, and you will have almost neglible blocking between multiple calls.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Barkingdog SSCoach Points: 18670 More actions · Answer 8

Gentlemen,

I must say this "simple" question led to a lot of good discussion and insights. Thanks to everyone for their help. Case closed.

Barkingdog

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 9

It's the "simple" things that kill people's code. The folks at the company I was telling you about spent hundreds of thousands of dollars on multiple consultants and 3rd party snake oilers trying to solve this very same "simple" problem prior to me getting there.

Anyway, thank you and Craig both for the great feedback. This was a good one, indeed. 🙂

--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)

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 10

Barkingdog (9/19/2010)
Case closed.

Actually, it might not be a closed case just yet. Do you know how to use the increment external to the code we wrote to handle more than one row at a time? If you run into that and it turns out to be a problem, post back.

--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)