January 2, 2009 at 1:08 pm
I need some help with an identity-type column in a table. I have a order table that has an order number field, the order number is a incrementing value but I can't use an identity column because the order number sequence is different for each customer. So I added another table that hold the "current number" for each customer, and have a stored procedure that does a select for N, then updates the table with the N+1. But there is a problem with locks/deadlocks when two threads try to add a order at the same time. Also, the logic just seems wrong since two threads could execute the select and get the same number before trying to lock for the update.
Any thoughts?
January 2, 2009 at 1:59 pm
I would leave the identity column to do what it does, using it as the "internal key" in relations, and have a "surrogate" or external user key in the form of your orderNumber. Assign the order number "after the fact", during whatever setting allows you the least amount of locking.
The blocking you're seeing is often why user-generated identifiers are so darned inefficient. Deadlocks and blocking tend to be the trademark for alphanumeric and/or custom numbering schemes.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 2, 2009 at 2:14 pm
The approach that I have used for this kind of sub-sequence number generator is like this:
INSERT Into ChildTable(...)
Select @ParentID
, 1+Coalesce((Select MAX(ChildID) From ChildTable c2
Where c2.ParentID = @ParentID), 0)
, {other columns...}
If you get a unique key conflict error on the (ParentID + generate ChildID), then just retry it (once).
If you do not keep the transaction open for very long, then the race condition window is pretty small. And if it does happen, one retry is usually enough to get by it.
If you still have problems with this then, either something is wrong with your table/index setup or you contention rate is too high and I suspect that you will need a radically different approach.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 2, 2009 at 2:46 pm
I've recently advised to create a number-bucket table for this kind of stuff.
Preparing a series of numbers for a key-identifier (i.e.customer in your case) and marking it 'free/taken'.
providing a sproc/function (uses read_past hint) that gets a free number from the series, if the number of frees for a key-identifier goes below x, add a new set of free numbers for the key-identifier.
Once keys are "taken", they get removed by a background cleanup process.
Be sure to support it with good indexing.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data and code to get the best help
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Who am I ? Sometimes this is me but most of the time this is me
January 2, 2009 at 3:07 pm
mweber26 (1/2/2009)
I need some help with an identity-type column in a table. I have a order table that has an order number field, the order number is a incrementing value but I can't use an identity column because the order number sequence is different for each customer. So I added another table that hold the "current number" for each customer, and have a stored procedure that does a select for N, then updates the table with the N+1. But there is a problem with locks/deadlocks when two threads try to add a order at the same time. Also, the logic just seems wrong since two threads could execute the select and get the same number before trying to lock for the update.Any thoughts?
Man, I feel for ya. My old company did a similar thing with some 3rd part software and they had an average of 640 deadlocks per day with spikes to 4000 per day! It was a bloody nightmare, but once we isolated it, it was easy to fix.
As you've already found out, it's the sequence table update code (the GetNextID procedure, if you will) that's causing all the problems. Chances are, it does a Select and an UPDATE and might even have a {gasp!} hard-coded BEGIN TRAN to "keep the Select and the Update" together.
Rather than continue to bore you to tears with what you already know, please consider the following and "warp" it a bit (change "table" to "customer") to meet your own needs...
CREATE PROCEDURE dbo.GetNextKey
@TableName SYSNAME,
@Increment INT = 1,
@NextKey INT OUTPUT
AS
IF @Increment > 0
BEGIN
UPDATE dbo.GetNextKey
SET @NextKey = NextKey = NextKey + @Increment
WHERE TableName = @TableName
SELECT @NextKey = @NextKey - @Increment
RETURN
END
SELECT @NextKey = NULL
RETURN
That's a heck of a SET statement, huh? Don't worry... it's document in Books Online... just not too many people even read about it never mind think about how to use it.
[font="Arial Black"]UPDATE [/font]
[ TOP ( expression ) [ PERCENT ] ]
{ | rowset_function_limited
[ WITH ( [ ...n ] ) ]
}
SET
{ column_name = { expression | DEFAULT | NULL }
| { udt_column_name.{ { property_name = expression
| field_name = expression }
| method_name ( argument [ ,...n ] )
}
}
| column_name { .WRITE ( expression , @Offset , @Length ) }
| @variable = expression
| [font="Arial Black"]@variable = column = expression [/font][ ,...n ]
} [ ,...n ]
[ ]
[ FROM{ } [ ,...n ] ]
[ WHERE {
| { [ CURRENT OF
{ { [ GLOBAL ] cursor_name }
| cursor_variable_name
}
]
}
}
]
[ OPTION ( [ ,...n ] ) ]
[ ; ]
--Jeff Moden
Change is inevitable... Change for the better is not.
January 2, 2009 at 3:08 pm
Oh... almost forgot... the code I posted allows you to reserve more than one ID using an "increment". If you can't figure out how to use that for more than 1 ID, post back and I'll run you through it. Helps avoid RBAR... 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
January 6, 2009 at 7:43 am
Wow -- that is interesting. I got a recommendation to use an SQL server application lock, it seemed to me like a little overkill for this operation, but it did work. I will have to try the UPDATE with SET.
January 6, 2009 at 11:50 am
The solution I'd go with would be to assign a standard identity column to the orders table, and then use a view with the Row_Number function in it for the incrementing order numbers by account.
Something like:
create view dbo.OrderIDs
as
select
ID as BaseID,
row_number() over (partition by AccountID order by OrderDate, ID) as OrderID
from
dbo.Orders;
Then just join to that view when you want the incrementing order number by account. All tables that reference orders would use the standard ID from the Orders table for their FKs. Customer views, etc., could use the OrderID as a presentation item.
Would that do what you need? It makes for a very simple structure to code, and doesn't involve any special locks.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply