July 17, 2009 at 8:26 am
Hey all.
I have a stored procedure which will get called very frequently. This stored procedure has to get a unique integer from a table. Basically the table has only one column, with an ID...and this ID is basically the next available number. It only has one row. Everytime the SP gets called it has to read this number to return it, and then update the number by 1 on the table.
This doesnt seem to be working properly. I have tried to use a transaction and commit it, however this is the first time I have tried something like this in sql server. Any help would be appreciated. My code is as follows:
BEGIN TRAN TestTran
SELECT ID
FROM TestTable
UPDATE TestTable
SET ID = ID + 1
COMMIT TRAN TestTran
Thanks, Paul.
July 17, 2009 at 9:12 am
Invert SELECT and UPDATE: you don't lock the row just reading, you have to write to put a lock.
BEGIN TRAN TestTran
UPDATE TestTable
SET ID = ID + 1
SELECT ID - 1
FROM TestTable
COMMIT TRAN TestTran
Hope this helps
Gianluca
-- Gianluca Sartori
July 17, 2009 at 9:18 am
ah sorry, i typed it out from memory and put it back to front...I did actually have the update first as I think it only locks in teh transaction if you do an update/insert/delete.
However, when I look at another table which uses tehse numbers...I am seeing some ID's duplicated...so they are getting passed identical IDs from this SP. This is teh only SP used for it.
July 17, 2009 at 9:58 am
How many rows are in that table? If the answer is one then fine, but I think you need to be more explicit in what you are trying to do
BEGIN TRAN
-- Lock the row, nobody else can change it now
UPDATE dbo.TestTable
SET ID=ID+0
-- Get the new value
SELECT @id = ID + 1
FROM dbo.TestTable
-- Update the table with the new value
UPDATE TestTable
SET ID = @id
COMMIT TRAN
Once you do the first update the record is yours, and nobody else can read it, they will be blocked at that statement for any concurrent calls, then it gets the current value and adds one to it, then it updates the table for your new value and then commits the transaction. Then and ONLY then is the row available for other concurrent processes, you won't have anybody else readying it accidently..
CEWII
July 20, 2009 at 3:32 am
If you are porting an application from Oracle, I would strongly suggest you redesign the way keys are created.
If you really want to proceed with this idea, and do not mind potential gaps in the sequence, then make use of IDENTITY to avoid blocking problems. Something along the lines of:
SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE TABLE dbo.YourSequence
(
Sequence int IDENTITY NOT NULL
)
GO
CREATE PROCEDURE dbo.GetNextSequence
@NextSequence int OUTPUT
AS
SET NOCOUNT ON
BEGIN TRANSACTION
SAVE TRANSACTION RB
INSERT INTO dbo.YourSequence DEFAULT VALUES
SET @NextSequence = SCOPE_IDENTITY()
ROLLBACK TRANSACTION RB
COMMIT
GO
DECLARE @NextId int
EXEC dbo.GetNextSequence @NextId OUTPUT
SELECT @NextId
July 27, 2009 at 7:47 am
this is not a port from oracle or anything like that.
Basically all it is, is a table with this one row, which needs to hold a number for the next available integer. I could use an identity to solve the problem. But this seems a bit OTT, as it would do an insert and create a table with many rowqs which is unneeded. I could perhaps insert a row, then delete the oldest row to keep the table rows down, but still I think this is a bit OTT.
One row, which can only get read and updated one at a time is what I require, which was why I was trying to use the transaction route.
July 27, 2009 at 8:03 am
One row, which can only get read and updated one at a time is what I require, which was why I was trying to use the transaction route.
If you look at the code you will see that the table has zero rows so only the identity is updated. This will not hold locks for the rest of an outer transaction.
July 27, 2009 at 8:06 am
ah, so running this SP all the time will not actually create rows? only update the identity?
July 27, 2009 at 8:12 am
paul.davidson.uk (7/27/2009)
ah, so running this SP all the time will not actually create rows? only update the identity?
Correct - the rollback stops the row being added.
Rolling back to a savepoint means that any outer transaction will not be rolled back.
Having a COMMIT after the rollback decrements @@trancount by 1 so the logic of any outer transaction will remain intact.
ps If this is not a port from Oracle, the only reason I can think of for using this is if you need a sequence over a number of tables. If you just want an identifier on a particular table then make it an IDENTITY.
July 27, 2009 at 8:14 am
Technically it creates the row, updates the identity, then removes it. The identity update is outside the scope of the transaction (in case other connections need the values), so it does not roll back.
July 27, 2009 at 8:15 am
CREATE TABLE dbo.TestTable (
IDINT NOT NULL)
GO
INSERT INTO dbo.TestTable VALUES (1)
-- Use the OUTPUT clause to return the new value in a resultset
UPDATE dbo.TestTable SET ID = ID + 1
OUTPUT INSERTED.ID
GO
-- Use multiple assignment to save the new value in a variable
DECLARE @ID INT
UPDATE dbo.TestTable SET @ID = ID = ID + 1
PRINT @ID
July 27, 2009 at 8:17 am
brilliant, thanks lads..
February 1, 2010 at 2:27 am
Scott Coleman (7/27/2009)
-- Use multiple assignment to save the new value in a variableDECLARE @ID INT
UPDATE dbo.TestTable SET @ID = ID = ID + 1
PRINT @ID
How does the code work?
SET @ID = ID = ID+1 ?
the variable @ID contains the updated value of ID. how does it work?
Thanks,
KB
Thanks,
Santhosh
February 1, 2010 at 6:26 am
From Books Online topic for UPDATE (Transact-SQL):
SET @variable = column = expression sets the variable to the same value as the column. This differs from SET @variable = column, column = expression, which sets the variable to the pre-update value of the column.
February 2, 2010 at 9:01 am
We also have a table which we use to get sids from. The stored proc that gets the next sid does an application lock so that only one process at a time can run the code:
This is the simplified table definition
CREATE TABLE [dbo].[TableSIDS](
[TableSIDSSID] [int] NOT NULL,
[TableName] [varchar](40) NOT NULL,
[LastSID] [int] NOT NULL,
CONSTRAINT [PK__TableSIDS__TableSIDSSID] PRIMARY KEY NONCLUSTERED
(
[TableSIDSSID] ASC
)
)
Each row contains the sid for a different table
The stored procedure to get the next sid for a table can get multiple sids to use in a multiple insert scenario.
Name:Alloc_Sids
Author: xxx
Date:??
Purpose: Get the next SID for the requested table from the TableSIDS table.
Outputs: the next SID value
Returns: 0 = Success
-200 = Failed to get a Lock within allowed time.
-201 = invalid input.
-202 = No record for requested table in TableSIDS
OR Error updating TableSIDS.
****************************************************************************************
Revision History:
***************************************************************************************/
ALTER PROCEDURE [dbo].[Alloc_Sids](
@tableName varchar(30),
@numRequested int,
@nKeyValue int OUTPUT) AS
DECLARE @rc int;
IF (@numRequested < 1) OR (@numRequested > 1000)
RETURN -201;
-- get an application lock, max wait of one minute
EXEC @rc = sp_getapplock 'HIS_Alloc_Sids', 'Exclusive', 'Session', '60000';
IF @rc >= 0 -- then the lock succeeded.
BEGIN
IF NOT EXISTS (SELECT * FROM TableSIDS WHERE TableName = @tableName)
BEGIN
EXEC sp_releaseapplock 'HIS_Alloc_Sids', 'Session';
RETURN -202;
END
--===================================================
BEGIN TRANSACTION;
UPDATE TableSIDS SET LastSID = LastSID + @numRequested
WHERE TableName = @tableName;
IF @@ERROR != 0
BEGIN
ROLLBACK TRANSACTION;
EXEC sp_releaseapplock 'HIS_Alloc_Sids', 'Session';
RETURN -202;
END
SELECT @nKeyValue =
(SELECT LastSID - @numRequested + 1
FROM TableSIDS WHERE TableName = @tableName);
COMMIT TRANSACTION;
--===================================================
EXEC sp_releaseapplock 'HIS_Alloc_Sids', 'Session';
RETURN 0;
END -- successfully got a lock
-- if here, lock failed.
EXEC sp_releaseapplock 'HIS_Alloc_Sids', 'Session'; -- just in case
RETURN -200; -- lock failed to return within time allowed.
Hope this helps
Terri
To speak algebraically, Mr. M. is execrable, but Mr. C. is
(x+1)-ecrable.
Edgar Allan Poe
[Discussing fellow writers Cornelius Mathews and William Ellery Channing.]
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply