December 7, 2004 at 5:10 pm
I'm fairly new to using SQL Server and figure there's probably a more elegant way to write this store procedure. What I need is a stored procedure that can be accessed by many test systems that will send their data to a central server. I'd like to be able to arbitrarily set the starting value of the sessionID.
GlobalSessionID is a one column, one row table used to generate unique session ID's.
The first select statement locks the table for the duration of the transacdtion. The update statement increments the ID. The second select statement returns the value; the caller needs to fetch the value after executing the stored procedure.
If anyone has suggestions on how this "should" be done or if there is a simpler solution I'd appreciate your thoughts.
Thanks for the help.
Fred
=================================================
CREATE PROCEDURE dbo.PROC_GetSessionID
AS
DECLARE @dummy as bigint
BEGIN TRANSACTION
select @dummy = SessionID from GlobalSessionID with (TABLOCK, HOLDLOCK)
UPDATE GlobalSessionID set SessionID =
(SELECT SessionID + 1 FROM GlobalSessionID)
SELECT SessionID
FROM GlobalSessionID
COMMIT TRANSACTION
GO
December 7, 2004 at 11:56 pm
A third party company that we had the great misfortune of hiring, did something similar (nearly identical) in our database... it caused an average of 620 deadlocks a day. The normal goal for deadlocks is, of course, 0. Obviously, we fixed the bloody thing...
If you don't want to use 36 character SessionID's that could be created by the use of NEWID(), then try this...
Create your session id table like this...
Then, create a proc that looks like this...
Every record inserted will create a new auto-numbering sequential SessionID in the record created. Selecting the SCOPE_IDENTITY will return that number regardless of any triggers firing or other tables being modified. It is session sensitive and requires no table locking except during the microsecond to do the insert.
Yes, the table will grow so you may want to consider using DECIMAL(19,0) (takes same number of bytes as DECIMAL(10,0)) or BIGINT for the datatype of the SessionID. You may want to also consider deleting earlier records from the table on a regular basis but, before you do, add a datetime column to the table and default it to GETDATE() to create a historical record of when each SessionID was created (has helped me twice with FBI investigations).
--Jeff Moden
Change is inevitable... Change for the better is not.
December 8, 2004 at 10:39 am
Jeff,
Thanks for the help. A colleague of mine wasn't too excited about maintaining the table, having to clean up rows that have been inserted. A 36 char ID is too big since we'll have lots of rows.
Do you think this may pose deadlock problems? This should be the only stored procedure or even script for that matter that would ever access this table.
Any other ideas?
I do appreciate your help.
Fred
December 8, 2004 at 1:15 pm
Another clean way of doing it would be to use a Base-36 number (zero to Z). Use a function to create it and default the value of the column to the function.
10 characters will handle almost 4,000,000,000,000 rows of data.
December 8, 2004 at 5:12 pm
In 7 years, this never deadlocked and it always retrieves a unique next number. This is just an exerpt of a function, but it finds a unique @iBatch (integer batch sequence #) in a table called batch. The key technique is to get the next iBatch:
SELECT @iBatch = 1 +
COALESCE((SELECT MAX(iBatch) FROM Batch),0)
Assuming you have a unique constraint on the iBatch #, when you add your new record, you check to see if it worked
INSERT Batch (jBatchPeriod, iBatch, MachineId, idUser, jStart, idJob, Lock, BatchTicketMicr)
VALUES (@jBatchPeriod, @iBatch, @szMachineId, @idUser, @jStart, @idJob, @jstart, @szBatchTicketMicr)
IF @@ERROR = 0 BEGIN -- hey it worked, so we can return the identity column of the record added
SET @idBatch = @@IDENTITY
BREAK
END
IF @@ERROR <> 2601 -- I think this was "constraint violated" or something.
BREAK -- real bad error, trying again probably wont work
If it failed, wait a couple ms and then try again. Failure would only occur when some other process called this stored proc simultaneously.
SET @cLoop = RAND() * 50
WHILE @cLoop > 0
SET @cLoop = @cLoop - 1
SET @iTry = @iTry + 1
END -- loop
Here is the code block taken all at once:
SET @iTry = 0
-- loop to find unused iBatch
WHILE @iTry < 5 BEGIN
SELECT @iBatch = 1 +
COALESCE((SELECT MAX(iBatch) FROM Batch),0)
INSERT Batch (jBatchPeriod, iBatch, MachineId, idUser, jStart, idJob, Lock, BatchTicketMicr)
VALUES (@jBatchPeriod, @iBatch, @szMachineId, @idUser, @jStart, @idJob, @jstart, @szBatchTicketMicr)
IF @@ERROR = 0 BEGIN
SET @idBatch = @@IDENTITY
BREAK
END
IF @@ERROR <> 2601
BREAK
SET @cLoop = RAND() * 50
WHILE @cLoop > 0
SET @cLoop = @cLoop - 1
SET @iTry = @iTry + 1
END
-- we are done
IF @@ERROR <> 0
RAISERROR ('ERROR - Could not begin a new batch.', 16, 1)
ELSE
SELECT * FROM Batch WHERE idBatch = @idBatch
December 9, 2004 at 2:04 am
Following sample generates uniqueid but does not store in a table.
DECLARE @IntTransactionID bigint, --Set @IntTransactionID@dteCurrentDate datetime
SET @dteCurrentDate = GETDATE()
--Genreate unique transaction id
SET @IntTransactionID = CAST(CAST(@@SPID AS VARCHAR(10)) +
CAST(DatePart(dd,@dteCurrentDate)AS VARCHAR(2)) +
CAST(DatePart(mm,@dteCurrentDate) AS VARCHAR(2)) +
CAST(DatePart(YY,@dteCurrentDate) AS VARCHAR(4)) +
CAST(DatePart(hh,@dteCurrentDate) AS VARCHAR(2)) +
CAST(DatePart(mi,@dteCurrentDate) AS VARCHAR(2)) +
CAST(DatePart(ss,@dteCurrentDate) AS VARCHAR(2)) +
CAST(DatePart(MS,@dteCurrentDate) AS VARCHAR(3)) AS BIGINT)
December 9, 2004 at 6:16 am
> Do you think this may pose deadlock problems? This should be the only stored procedure or even script for that matter that would ever access this table.
Fred,
Yeah, your method could cause a deadlock depending on how it is used and how many people try to hit it and whether or not you use BEGIN TRANSACTION with the stuff that calls it. On the other hand, you may never see a deadlock if you never call it from a long running transaction because your method runs very very fast. I guess you could try it to see.
The advantage of the method I presented is the table doesn't take much space, can be used for history, and will never deadlock. Since it doesn't take much space, you really don't need to do the maintenance of deletions. If the boss really does want it to stay short, a very simple scheduled job could be set up.
The MAX+1 idea is a good one but you wanted to use a single record so it won't work for you. Besides, if the table grew go each Session ID, you wouldn't need MAX+1... you could use an Identity field, instead.
The date conversion above is a great idea but, be warned, although highly unlikely because of the millisecond listing, it is still possible to get a dupe if you have a lot of people asking for a Session ID at the same time.
And, to reiterate, if you use the NEW(ID) method, although 36 characters long, you will never get a dupe and, since you don't need the Session ID table, you will never have a deadlock. Since the 36 characters are hexidecimal in nature, you could do a conversion (kind of like the date method above) to decimal in each section of the ID and concatenate the pieces together to make a smaller more managable Sessiond ID.
Anyway, lot's of folks gave some pretty darned good answers above... I guess all you need to do now is make a choice.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 9, 2004 at 9:06 pm
Jeff,
THe date approach as suggested above also takes SPID into consideration, which is suppose to be unique for any number of concurrent connected users to the database. Hence at any given point of time no two users with the same SPID can access the database. Therefore the ID generated suppose to be unique. Please do correct me if I am wrong.
Thanks
nskr72
December 10, 2004 at 7:10 am
Thanks nskr72... I missed that.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply