January 17, 2011 at 9:32 am
I'm trying to make sure that no one steps on the other's job number. This is my code:
SELECT @Run_id =
MAX(Run_id)
FROM
dbo.Jobs ;
SET @Run_id = @Run_id + 1 ;
INSERT INTO dbo.Jobs
( Run_id )
VALUES
( @Run_id ) ;
My concern is that another job will get the same job number, if started between the read and the write.
What I want is to lock the record from the read until the write.
Is that possible?
*******************
What I lack in youth, I make up for in immaturity!
January 17, 2011 at 9:51 am
No need for that. Just do the INSERT in one statement so no one can get in between.
declare @MyTable (Run_id int not null )
INSERT INTO dbo.Jobs
( Run_id )
OUTPUT
INSERTED.Run_id
INTO
@MyTable
SELECT
MAX(Run_id)+1
FROM
dbo.Jobs;
select @Run_id = run_id from @MyTable
Using an IDENTITY column for Run_id would be a better solution, and you application would scale up better.
January 17, 2011 at 10:04 am
Thanks!
*******************
What I lack in youth, I make up for in immaturity!
January 24, 2011 at 7:02 am
Well, even with the OUTPUT statement, I'm still having a timing issue. I think I need to do this as an Identity column. The problem is that I have a bunch of records already in there. Can I change to an identity column and retain my existing numbers?
*******************
What I lack in youth, I make up for in immaturity!
January 24, 2011 at 7:31 am
To stop the race condition you will need to apply a key range lock on Jobs by making the select serializable.
(If this still gives problems add the update lock as well.)
ie The middle bit of Michael's script would look like:
INSERT INTO dbo.Jobs
( Run_id )
OUTPUT
INSERTED.Run_id
INTO
@MyTable
SELECT
MAX(Run_id)+1
FROM
dbo.Jobs WITH (SERIALIZABLE);
--dbo.Jobs WITH (UPDLOCK, SERIALIZABLE);
As Michael mentioned, IDENTITY would be a better solution.
Changing Run_id to an IDENTITY would be fiddly by script as you would need to copy the table.
I think if you add an IDENTITY in management studio it will do this for you.
January 24, 2011 at 7:38 am
You can get a new unique identity with NEWID() function.
Regards,
Iulian
January 24, 2011 at 7:42 am
I was able to change the key to an Identity key, retaining my numbers, by using the designer. Kewl.
*******************
What I lack in youth, I make up for in immaturity!
January 25, 2011 at 7:46 am
Hmm... now when I try to insert a record, I get this:
Msg 50000, Level 16, State 2, Procedure PEC_Jobs, Line 1676
Error 515 "Cannot insert the value NULL into column 'run_id', table 'dbo.PEC_Jobs'; column does not allow nulls. UPDATE fails." raised in myProc 758
I thought it should insert the value automatically?
*******************
What I lack in youth, I make up for in immaturity!
January 25, 2011 at 8:03 am
Have you checked that the identity value is already set for the Run_id field, can be seen in SSMS --> modify table --> select Run_id column --> downside screen .?
you can check your max value thats current en put this one in as new startnumber for the identity
DECLARE @NewStartSeedValue INT
SELECT @NewStartSeedValue= max(Run_id ) from dbo.Jobs
DBCC CHECKIDENT (dbo.Jobs, reseed, @NewStartSeedValue)
Wkr,
Eddy
January 25, 2011 at 8:10 am
Thanks. I'm all set.
*******************
What I lack in youth, I make up for in immaturity!
January 25, 2011 at 8:13 am
Your welcome,
Glad to hear and tnx for the feedback
Eddy
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply