471,084 Members | 1,034 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,084 software developers and data experts.

Transaction on stored precedures

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
Jul 20 '05 #1
2 5281
[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
Jul 20 '05 #2
Thanks for your help Erland
Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Sam | last post: by
2 posts views Thread by Deepak Mehta | last post: by
15 posts views Thread by Zeng | last post: by
9 posts views Thread by ucasesoftware | last post: by
1 post views Thread by cricketweb | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.