October 7, 2010 at 4:00 am
i have a sequence_master table.. from thr i have to select a field and increment it by 1.and then i wil use it as my sequence no like 1000,1001.... while fetching this ,other user shudnt access this same field. how can i achieve it ? i am all confused with nolock and rowlock?... wil anyone please explain me this?.
October 7, 2010 at 4:10 am
It could be something like this:
CREATE PROCEDURE [getSequence]
AS
BEGIN
SET NOCOUNT ON
DECLARE @newVal int
BEGIN TRY
BEGIN TRAN
UPDATE SequenceTable
SET @newVal = Sequence = Sequence + 1
COMMIT
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
IF XACT_STATE = 1
ROLLBACK
RAISERROR (@ErrorMessage, @ErrorSeverity,@ErrorState);
END CATCH
SELECT @newVal AS NEWPROG
RETURN @newVal
END
Since this is a one-statement transaction, you could also avoid handling the transaction with begin/commit-rollback.
Hope this helps
Gianluca
-- Gianluca Sartori
October 7, 2010 at 4:20 am
in mean time if someone access the same field tht will cause inconsistency or duplication
October 7, 2010 at 4:31 am
Nobody can acces the row while you're updating it.
Don't worry about that.
-- Gianluca Sartori
October 7, 2010 at 10:30 pm
Gianluca Sartori (10/7/2010)
It could be something like this:
CREATE PROCEDURE [getSequence]
AS
BEGIN
SET NOCOUNT ON
DECLARE @newVal int
BEGIN TRY
BEGIN TRAN
UPDATE SequenceTable
SET @newVal = Sequence = Sequence + 1
COMMIT
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
IF XACT_STATE = 1
ROLLBACK
RAISERROR (@ErrorMessage, @ErrorSeverity,@ErrorState);
END CATCH
SELECT @newVal AS NEWPROG
RETURN @newVal
END
Since this is a one-statement transaction, you could also avoid handling the transaction with begin/commit-rollback.
Hope this helps
Gianluca
Man... well done. Just don't ever put something like this in a transaction... it will cause deadlocks in the "outside" world. Also, make the code handle increments other than just 1. Here's the production code I wrote a couple of years ago...
CREATE PROCEDURE dbo.GetNextID
/****************************************************************************************
Purpose:
This stored procedure is used to get a NextID for the table identified by the @KeyID
parameter. It will "reserve" a block of ID's according to the @IncrementValue parameter.
The @NextID returned is always the first ID of a reserved block of numbers. The reserved
block size defaults to 1.
Usage:
EXEC @return = dbo.GetNextID @KeyID,@IncrementValue,@NextID=@NextID OUTPUT
Outputs:
1. Returns a -1 if error and 0 if success.
2. @NextID will be a -1 if an error occured. Otherwise, it will contain the first
NextID of the requested block of NextID's.
Notes:
1. This procedure has been enhanced compared to the original...
a. The UPDATE statement sets both the @NextID variable and the NextID column in the
NextID table eliminating the need for a separate SELECT from NextID after the
UPDATE.
b. Because of (1.a) above, there is no longer a need for a transaction. If the
UPDATE didn't work, there is no need for a ROLLBACK because nothing was updated.
c. Previous error handling did not correctly return the invalid KeyID if present.
d. A test has been added to ensure a negative value for @IncrementValue was not
passed in.
e. A test to ensure that @NextID was correctly updated has been added.
f. Repairs to the previous error routines have been made so that the values returned
to @@ERROR and @@ROWCOUNT are correctly used by more than one statement.
Revisions:
REV 01 - 01 Mar 2005 - Kalpa Shah, Jeff Moden --Rewrite original
REV 02 - 06 Feb 2010 - Jeff Moden -- Removed all company references
****************************************************************************************/
--=======================================================================================
-- Define the I/O parameters used by this procedure
--=======================================================================================
--===== Declare the passed parameters
@KeyID INTEGER, --Identifies table to get the NextID for
@IncrementValue INTEGER = 1, --Number of NextIDs to "reserve"
@NextID INTEGER OUTPUT --Returns start # of block of IDs
AS
--=======================================================================================
-- Main body of procedure
--=======================================================================================
--===== Suppress auto-display of row counts for appearance and speed
SET NOCOUNT ON
--===== Declare variables local to the loop
DECLARE@MyError INTEGER --Holds @@ERROR for additional processing
DECLARE @ErrMessage VARCHAR(100) --Holds calculated error messages because RaisError
--cannot calulate messages on the fly.
DECLARE @MyRowCount INTEGER --Hold @@ROWCOUNT for additional processing
--===== Preset @NextID to an error condition
SET @NextID = -1 --Defaults don't work consistently on OUTPUT parameters
--===== If the increment is not greater than zero, raise and error and exit immediately
IF @IncrementValue <= 0
BEGIN --Start of error processing
--===== Process errors (RaisError cannot do calcs for error message)
SET @ErrMessage = 'The NextID row could not be updated. '
+ 'Increment was set to '
+ CONVERT(VARCHAR(11),@IncrementValue) + '.'
RAISERROR (@ErrMessage,1,1)
RETURN -1 --Returns an error indication to calling procedure
END --End of error processing
--===== Update the correct NextID row according to the KeyID passed in.
-- Sets @NextID and the column to the previous value + the increment
-- simultaneously so we don't need to read from the NextID table to
-- get the value of @NextID in the following steps.
UPDATE dbo.NextID WITH (UPDLOCK)
SET @NextID = NextID = NextID + @IncrementValue
WHERE KeyID = @KeyID
-- Get the error value and rowcount
SELECT @MyError = @@ERROR, @MyRowCount = @@ROWCOUNT
--===== Check for errors, a rowcount of 1, and a non-default value for @NextID
IF @MyError <> 0 --An error did occur
OR @MyRowCount <> 1 --The row was not updated
OR @NextID = -1 --A new value for @NextID was not returned
BEGIN --Start of error processing
--===== Process errors (RaisError cannot do calcs for error message)
IF @MyError <> 0 --Error occured
SET @ErrMessage = 'The NextID row could not be updated.'
ELSE --1 row or @NextID was not updated
SET @ErrMessage = 'The NextID row could not be updated. KeyID '
+ CONVERT(VARCHAR(11),@KeyID)
+ ' may not exist.'
RAISERROR (@ErrMessage,1,1)
RETURN -1 --Returns an error indication to calling procedure
END --End of error processing
--===== Calculate and return the first number in the block of reserved NextID's
-- to the @NextID output parameter
SELECT @NextID = @NextID - @IncrementValue
--===== Return a "success" indication to the calling procedure
RETURN 0
GO
--Jeff Moden
Change is inevitable... Change for the better is not.
October 8, 2010 at 1:03 am
Jeff Moden (10/7/2010)
Man... well done. Just don't ever put something like this in a transaction... it will cause deadlocks in the "outside" world. Also, make the code handle increments other than just 1. Here's the production code I wrote a couple of years ago...
Wow, great as usual, Jeff!
Let me ask you a couple of things:
1) Why deadlocks? I thought that deadlocks could happen only when two or more processes acquire locks on a resource and try to acquire a new lock on resources already locked by one of the processes they're blocking. I don't see how this could happen with the code above. I'm not trying to defend my code (wrote it on the fly, could be dead wrong), I'm just trying to learn something new.
2) Why the UPDLOCK hint in the UPDATE statement? Could a single table UPDATE acquire locks other than that?
-- Gianluca Sartori
October 8, 2010 at 3:12 am
Gianluca Sartori (10/8/2010)
Jeff Moden (10/7/2010)
Man... well done. Just don't ever put something like this in a transaction... it will cause deadlocks in the "outside" world. Also, make the code handle increments other than just 1. Here's the production code I wrote a couple of years ago...Wow, great as usual, Jeff!
Let me ask you a couple of things:
1) Why deadlocks? I thought that deadlocks could happen only when two or more processes acquire locks on a resource and try to acquire a new lock on resources already locked by one of the processes they're blocking. I don't see how this could happen with the code above. I'm not trying to defend my code (wrote it on the fly, could be dead wrong), I'm just trying to learn something new.
2) Why the UPDLOCK hint in the UPDATE statement? Could a single table UPDATE acquire locks other than that?
If you put the request for a NEXTID inside yet another transaction, especially a long winded transaction, it will occasionally deadlock (according to what we saw at the old company). So far as the UPDLOCK goes, that was the suggestion of the DBA that suggested using the 3 part update for this. I'm not sure why I never asked her why on that and I've not taken it apart (although I really should) in a study.
What I do know is that the day we implemented this bad boy, the deadlocks on the system dropped from an average of 640 per day to almost zero for a week at a time. The old 3rd party method used a SELECT with an UPDATE which we both wrapped in a single transaction.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 8, 2010 at 3:32 am
Jeff Moden (10/8/2010)
If you put the request for a NEXTID inside yet another transaction, especially a long winded transaction, it will occasionally deadlock (according to what we saw at the old company).
That makes sense. That's why I had to use an autonomous transaction through CLR to update the sequence here in my prod DB. Gaps in the sequence are not a problem, so going with autonomous transaction is a legitimate solution.
Jeff Moden (10/8/2010)
So far as the UPDLOCK goes, that was the suggestion of the DBA that suggested using the 3 part update for this. I'm not sure why I never asked her why on that and I've not taken it apart (although I really should) in a study.
Thanks for the tip. I'll have to investigate it deeper. BOL doesn't say much indeed, but it's in my research list now.:-)
Jeff Moden (10/8/2010)
What I do know is that the day we implemented this bad boy, the deadlocks on the system dropped from an average of 640 per day to almost zero for a week at a time. The old 3rd party method used a SELECT with an UPDATE which we both wrapped in a single transaction.
Yup. That's exactly what I had here before changing the procedure.
Thanks for clarifying, Jeff.
-- Gianluca Sartori
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply