473,371 Members | 1,566 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,371 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 5350
[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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

16
by: noah | last post by:
Does PHP have a feature to associate Cookie sessions with a persistent database connection that will allow a single transaction across multiple HTTP requests? Here is how I imagine my process: I...
2
by: Sam | last post by:
Guys, I have a question regarding oracle. I have a stored procedure executing a number of statements including delete , select and lots of inserts in one big procedure and main transaction I...
1
by: Rittercorp | last post by:
I am debugging an app which blocks many processes in a SQL7 server DB. The app log writes every transaction "open" and "close". The weird thing is : when the app logfile says the transaction is...
2
by: Deepak Mehta | last post by:
i have to update two tables from ASP pages with same data but i want that both of them should be updated at one time. If either of them is not updated then my transaction should roll back.I want...
15
by: Zeng | last post by:
Hi, The bigger my C# web-application gets, the more places I need to put in the tedious retrying block of code to make sure operations that can run into database deadlocks are re-run (retried)...
2
by: Leon | last post by:
How can I code a stored procedure "within my codebehind page" that execute two or more stored procedures within a transaction? if so how would I pass values to each parameter? i.e. Begin Tran...
9
by: ucasesoftware | last post by:
i need to use this : Private Shared Sub Demo1() Dim db As SqlConnection = New SqlConnection("connstringhere") Dim transaction As SqlTransaction db.Open transaction = db.BeginTransaction Try...
6
by: Crash | last post by:
C# VS 2003 ..Net Framework V1.1 SP1 SQL Server 2000 SP3 Enterprise Library June 2005 I'm working with some code {not of my creation} that performs the following sequence of actions: - Open...
1
by: cricketweb | last post by:
I have a stored procedure that calls another stored procedure with the first stored procedure opening a transaction: BEGIN SET XACT_ABORT ON BEGIN TRANSACTION does various updates/inserts
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

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.