"best" way to implement multi-user query
-
G'Day all,
This is a greatly simplified version of a common, real world problem. The actual select statement is significantly more complex. I know of at least three ways to solve the issue. Extreme performance is mandatory, as this proc is potentially called hundreds of times per second. This is almost a textbook problem, but interestingly enough, the textbooks differ on the right solution.
PROBLEM: The following proc is not multi-user safe as written. Two users making a request at the same time can receive the same result. Periodically, two users call the proc at exactly the same time and receive the same value which is a really, really bad thing. The table size is not huge - perhaps on the order of 300,000 to 500,000 rows.
So much for the problem statement. I would like your thoughts on the "best" way to make it multi-user safe. Please remember to include "why" your suggestion is the best one. Your thoughts?
CREATE TABLE tblName (RowID int,
Color VARCHAR(10),
RowIsUsed INT DEFAULT 0)
GO
INSERT INTO tblName (RowID, Color)
SELECT 1, 'RED'
UNION
SELECT 2, 'ORANGE'
UNION
SELECT 3, 'YELLOW'
UNION
SELECT 4, 'GREEN'
UNION
SELECT 5, 'BLUE'
UNION
SELECT 6, 'INDIGO'
UNION
SELECT 7, 'VIOLET'
GO
CREATE PROCEDURE GetColor
@OutRowID INT OUTPUT
AS
BEGIN
SET NOCOUNT ON
DECLARE @Error INT,
@RowCount INT
SET @Error = 0
SELECT @OutRowID = (SELECT TOP 1 RowID FROM tblName WHERE RowIsUsed = 0)
SELECT @Error = @@ERROR
IF @Error <> 0 RETURN @Error
IF @OutRowID IS NULL RETURN -1
UPDATE tblName SET RowIsUsed = 1 WHERE RowID = @OutRowID
SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT
IF @Error <> 0 OR @RowCount <> 1 OR @RowCount IS NULL
RETURN -2
END
GO
DECLARE @MyRowID VARCHAR(10)
EXEC GetColor @MyRowID OUTPUT
SELECT @MyRowID
EXEC GetColor @MyRowID OUTPUT
SELECT @MyRowID
UPDATE tblName SET RowIsUsed = 0
GO
-
I believe what you are needing is the "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE" at the beginning of your Stored Procedure. By doing it this way, you are preventing anyone from reading the data whilst there are updates or inserts to the dataset. Remember that concurrency is at an all time low when you use this Isolation Level!
Have a look at BOL for syntax & Info.
-
how about using transactions to get isolation ?
Offcourse everything has a price !
CREATE PROCEDURE GetColor
@OutRowID INT OUTPUT
AS
BEGIN
-- print statements added for debugging
SET NOCOUNT ON
declare @trancount int
set @trancount = @@trancount
print @trancount
begin tran
DECLARE @Error INT,
@RowCount INT
SET @Error = 0
SELECT @OutRowID = (SELECT TOP 1 RowID FROM tblName with (holdlock) WHERE RowIsUsed = 0)
SELECT @Error = @@ERROR
IF @Error <> 0
begin
print 'A-A'
print @@trancount
if @trancount <> @@trancount
begin
commit tran -- rollback would also have impact to the
-- calling application
end
RETURN @Error
end
IF @OutRowID IS NULL
begin
print 'B-B'
print @@trancount
if @trancount <> @@trancount
begin
commit tran -- rollback would also have impact to the
-- calling application
end
RETURN -1
end
UPDATE tblName SET RowIsUsed = 1 WHERE RowID = @OutRowID
SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT
print 'C-C'
print @@trancount
if @trancount <> @@trancount
begin
print 'final commit in this proc'
commit tran -- rollback would also have impact to the
-- calling application
end
IF @Error <> 0 OR @RowCount <> 1 OR @RowCount IS NULL
begin
print 'D-D'
print @@trancount
RETURN -2
end
END
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
i guess what u r trying to ask, tht 2 people have the same records and each one updates records, then there wud be a probelm is int?
wht u actually asked is 2 people geting the same resultselt, ya let them get it , its not a problem
u can handle this thing at the client level, see u can add a column in a table which will store the latest updated data & time. then while saving the data
u can check the column value in the table with the resultset value in that column (for any change)
if there is a diference in it,
u will come to know that this row has been editited by some 1 else and u can stop the update for a particular row or inform the user of the changes made,or do the needful thing.
see if u want to boost the perfomance, u can improve the perfomance by using command object in ado and compiling and storing the compiled query in the clients memory.
thats possible in Ado
seting the isolation level to serilizable is not a very
advisiable way to do it, becoz that will lock the entire table, and the users will get a hangup system, and they will come shouting to u.
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
-
Thanks all for the good thinking so far. Comments follow:
Sukhio: Suppose that instead of colors, the actual data is a bank account number being assigned to a new customer. Having two different customers get the same account number would be a really, really bad thing. The proc must guarantee that one and only one user receives a specific result.
Trigger: The transaction approach is safe, but performance is terrible.
Alzdba: Same comment as trigger.
One tweak to the proc will help to ensure that only one person gets the row.
UPDATE tblName
SET RowIsUsed = 1
WHERE RowID = @OutRowID
AND RowIsUsed <> 1
This will cause the update to fail for the second caller and return an error to the calling routine. There is an error handler in the calling routine that will continue trying to get a result when errors occur. The simultaneous read happens rarely, and this may be "good enough".
Again, I open it up to the community for comments.
Thanks for your thoughts, and have a great day!
Wayne
-
This is a classic problem what method to use the flag or the LockedTable
For scalability purposes I would recommend you use the Locks Table instead of the Flag. For an explanation see this.
* Noel -
u should have mentiond thats they no 2 persons will get the same resultset
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply