How to solve concurrency issues? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

.NET Inside SQL Server (2005)

How to solve concurrency issues?

Author

Topic

glenfs
Starting Member

2 Posts

Posted - 2012-12-03 : 01:03:08

Hi All,

I have a log table which stores a single record and there are multiple application which accesss this table and write read/data to this. The log table basically generates sequential numbers to be used by different application.

As 2 or more application may concurrently access this table how do avoid the same sequential number is not sent to 2 or more client application? Could we row lock record? or what would be the most efficient approach?

Regards,
Darryl.

Elizabeth B. Darcy
Starting Member

39 Posts

Posted - 2012-12-03 : 07:15:00

One option you might consider is using the OUTPUT clause available in SQL 2005 and later. Of course, without knowing the details ofhow the sequential numbers are generated or stored, this is only a guess on my part.

Here is a simple example - you can capture the value that is output and send it to the client.

CREATE TABLE #tmp(id INT);

INSERT INTO #tmp 
OUTPUT INSERTED.id
VALUES (1);

UPDATE #tmp
SET id = id+1
OUTPUT INSERTED.id

DROP TABLE #tmp;

______________________________________________
-- "If a book is well written, I always find it too short"

glenfs
Starting Member

2 Posts

Posted - 2012-12-03 : 23:49:28

Thank you Elizabeth.. The Sequential number is increnmented by 1 each time an application calls the webservice.. So the row will hold the previous generated number, and when an call is made value is incremented by 1 and sent to the calling application.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources