March 5, 2009 at 3:05 pm
I need to sequentially number events created by users with an incremental counter. In the events table, I have a unique key on the combination of OrgId, TypeId, and OrgEventCounter. Are there any “best practices” on how best to handle the unlikely possibility that 2 users will try to save at the same time with the same OrgEventCounter?
I could trap for :
Msg 2627, Level 14, State 1, Line 9
Violation of UNIQUE KEY constraint 'IX_DocDataEvent'. Cannot insert duplicate key in object 'dbo.DocDataEvent'.
and loop until it works, but it seems there should be a more elegant way.
Another thought using recusion (cringe)- but given the low likelihood that this scenario will even be encountered, would the following be reasonable?
/*
Let's say that this code lives in a stored proc called
uspEventInsert (@OrgId, @TypeId)
*/
BEGIN TRY
DECLARE @OrgEventCounter int
SET @OrgEventCounter =
(SELECT MAX(OrgEventCounter)
FROM dbo.Event
WHERE OrganizationID = @OrgId AND TypeID = @TypeId) + 1
INSERT dbo.Event
(OrganizationID, TypeID, OrgEventCounter)
VALUES (OrgId, TypeId, @OrgEventCounter)
END TRY
BEGIN CATCH
IF (ERROR_NUMBER() = 2627)
EXEC uspEventInsert @OrgId, @TypeId
END CATCH
🙂
March 5, 2009 at 3:13 pm
Hi Stef
There are various suggestions in the other thread. What are you missing?
Greets
Flo
March 5, 2009 at 3:27 pm
The couple of replies focused on using an IDENTITY field, which i can't do since the counter value is different per Org/Type. So is the solution I present above reasonable? Something about it feels questionable but it is simple and effective, at least in the testing I ran with it.
March 5, 2009 at 3:27 pm
March 5, 2009 at 3:34 pm
I'm sorry Bruce, I guess I did a bad job of posting the problem to begin with. I have no trigger, so your suggestion is:
-- if there is no trigger on your Event table, then use the following
-- logic to get the next counter
--
update dbo.Event
set @OrgEventCounter = OrgEventCounter = OrgEventCounter + 1
where OrganizationID = 2325 and
TypeID = 1
The OrgEventCounter is not in a separate table, it's only stored as a field in the Event record. The only thing i'm trying to avoid is 2 users saving an event at the very same moment with the same OrgEventId.
March 5, 2009 at 3:38 pm
The only thing i'm trying to avoid is 2 users saving an event at the very same moment with the same OrgEventId.
Can you just create a Unique Constraint ?
* Noel
March 5, 2009 at 3:41 pm
Hello Stef
Here a complete example how to use a id table:
-- Event table
IF (OBJECT_ID('tempdb..#event') IS NOT NULL)
DROP TABLE #event
GO
CREATE TABLE #event (org_id INT, type_id INT, id INT)
GO
-- Id table
IF (OBJECT_ID('tempdb..#id_tab') IS NOT NULL)
DROP TABLE #id_tab
GO
CREATE TABLE #id_tab (table_name SYSNAME, next_id INT)
GO
INSERT INTO #id_tab VALUES ('Event', 1)
GO
-- ID procedure
IF (OBJECT_ID('tempdb..#usp_get_id') IS NOT NULL)
DROP PROCEDURE #usp_get_id
GO
CREATE PROCEDURE #usp_get_id
@table_name SYSNAME,
@id INT = NULL OUTPUT
AS
BEGIN TRANSACTION
UPDATE #id_tab SET next_id = next_id + 1 WHERE table_name = @table_name
SELECT @id = next_id - 1 FROM #id_tab
COMMIT TRANSACTION
RETURN @id
GO
-- insert event procedure
IF (OBJECT_ID('tempdb..#usp_insert_event') IS NOT NULL)
DROP PROCEDURE #usp_insert_event
GO
CREATE PROCEDURE #usp_insert_event
@org_id INT,
@type_id INT
AS
DECLARE @id INT
EXECUTE #usp_get_id 'Event', @id OUTPUT
INSERT INTO #event VALUES (@org_id, @type_id, @id)
GO
-- Sample values
EXECUTE #usp_insert_event 1, 2
EXECUTE #usp_insert_event 1, 2
EXECUTE #usp_insert_event 2, 2
EXECUTE #usp_insert_event 2, 2
GO
-- Result
SELECT * FROM #event
Greets
Flo
March 5, 2009 at 3:43 pm
Yes, I do have a unique constraint on OrgId + TypeId + OrgEventCounter. But I'm just trying to handle it gracefully. I don't want the save attempt fail- I just need to try again with the next available counter.
March 5, 2009 at 4:17 pm
florian.reischl (3/5/2009)
BEGIN TRANSACTIONUPDATE #id_tab SET next_id = next_id + 1 WHERE table_name = @table_name
SELECT @id = next_id - 1 FROM #id_tab
COMMIT TRANSACTION
Heh... yeah... we had code like that.... caused an average of 640 deadlocks per day. I'll try to come back tonight and show you how we fixed that problem.
By the way... You're not running "CostGuard" are you, Flo?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 5, 2009 at 5:15 pm
Hi Jeff
This was only a simple sample. Sorry for performance problems due lockins on the id-table 😉
In real implementation wi work with a temporary table for scripts and procedures to maintain caches of new ids. The client applications we use other ways to get a bunch of new ids (e.g. 100) for the client in one step and work with this. So locks should be really rarely.
But
What is "CostGuard"?? I don't know this.
Greets
Flo
March 5, 2009 at 5:58 pm
"CostGuard" is a telephone accounting system. It had similar code to the NEXT_ID example you posted and it was, in fact, called "NEXT_ID".
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply