Transaction on stored precedures

[metehan]

Hi all
question regarding how transactions work in SQL Server...

stored procedure below that generates unique IDs for messages we
send to the backend It looks like sometimes, the number generated is
identical for a messages that were sent simultaneously. Since this
seems like a concurrency issue, am I correct in thinking that using
SQL
transactions within the stored procedure code will solve this problem?

The stored procedure is outlined below, the code in red is what I
think should go in to solve the concurrency problem.

CREATE PROCEDURE [dbo].[praCCGetNewCDMIndex]
@newdate int
AS

SET NOCOUNT ON

DECLARE @lastdate int
DECLARE @lastindex int
DECLARE @newindex int

BEGIN TRAN CDMIndex

SELECT @lastdate = N_JULIANDATE FROM tbl_CC_CDMIndexes
SELECT @lastindex = N_INDEX FROM tbl_CC_CDMIndexes
IF @lastdate = @newdate
SET @newindex = @lastindex + 1
ELSE
SET @newindex = 1

UPDATE tbl_CC_CDMIndexes
SET N_JULIANDATE = @newdate,
N_INDEX = @newindex
SELECT N_INDEX FROM tbl_CC_CDMIndexes

COMMIT TRAN CDMIndex

SET NOCOUNT OFF
GO

[Erland Sommarskog]

[posted and mailed, please reply in news]

metehan (me*******@Hotmail.com) writes:

stored procedure below that generates unique IDs for messages we
send to the backend It looks like sometimes, the number generated is
identical for a messages that were sent simultaneously. Since this
seems like a concurrency issue, am I correct in thinking that using
SQL transactions within the stored procedure code will solve this problem?
Yes, handled properly.
The stored procedure is outlined below, the code in red is what I
think should go in to solve the concurrency problem.
Not only news is a text-only medium. It's in black-and-white too.

Anyway, I have a suggestion for improvements to your procedure:
SELECT @lastdate = N_JULIANDATE FROM tbl_CC_CDMIndexes
SELECT @lastindex = N_INDEX FROM tbl_CC_CDMIndexes
Rewrite this as:

SELECT @lastdate = N_JULIANDATE, @lastindex = N_INDEX
FROM tbl_CC_CDMIndexes WITH (UPDLOCK)

If you don't have the locking hint, two processes may access the
table at the same time and get the same ID. Both will then try to
update the table with the same value. This is because the default
locking mode in SQL Server is to release the lock on row once you
have read it. Great in many situations, but not here.

UPDLOCK instructs SQL Server to hold the lock to the end of the
transaction. Furthermore, while UPDLOCK is only a read-lock, only
one process at the time can have a UPDLOCK, so the process that
comes in as #2, while be blocked until the other process have
committed.

UPDATE tbl_CC_CDMIndexes
SET N_JULIANDATE = @newdate,
N_INDEX = @newindex

You should also be aware of that an error in an UPDATE statement does
not automatically cause a rollback, but you need to have proper error
handling. This is unfortunately quite a tedious job to implement. I
have an article on this at
http://www.algonet.se/~sommar/error-handling-II.html.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[metehan]

Thanks for your help Erland