May 6, 2009 at 8:16 am
Hi,
I've written the following stored procedure:
BEGIN TRANSACTION
SELECT * FROM UserStats WHERE UserID = @user-id
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO UserStats (UserID) VALUES (@UserID)
END
COMMIT TRANSACTION
It essentially wants to insert a record into a table ONLY if a similar record is not already there. UserID is some field in the table (not the key).
As written it does not support concurrency, as two processes could both get a rowcount of 0 from the SELECT and thus decide to INSERT the new record.
I have considered adding SET TRANSACTION ISOLATION LEVEL SERIALIZABLE to the beginning, but reading the documentation of this level, I'm not convinced that this would actually provide the mutual exclusiveness required for what I need.
I'd be happy to hear views on this code and alternatives to what seems to be a task needed often.
Thanks,
Jamie
May 6, 2009 at 8:37 am
Why did you write it this way? I'm curious because you might have a good reason, or you might be thinking about this wrong.
If you reversed the statements, and looked for @@rowcount = 1, you'd achieve what you want. If you had @@rowcount= 2, you could roll back.
Note that this doesn't prevent 2 people from rolling back, it just reduces the chances.
If you have an index on this field, you can prevent duplicates without using this code, although you'd then need to handle errors. Of course, you need to handle errors anyway 🙂
May 6, 2009 at 9:00 am
Thanks for your ideas!
I'm not sure I follow the idea of reversing the order. Won't rowcount always return 1 after an INSERT? (i.e., One row affected).
Note that most of the time the record will already be there, so..
1. If I reverse the order, most of the time I'll be rolling back. Could that be a performance problem?
2. Likewise for generating an error on the disallowed second INSERT.
Jamie
May 6, 2009 at 1:01 pm
Hi
What about just a simple INSERT SELECT WHERE:
DECLARE @users TABLE (Id INT)
DECLARE @userid INT
-- New user
SELECT @userid = 1
INSERT INTO @users
SELECT @userid
WHERE NOT EXISTS (SELECT TOP(1) 1 FROM @users WHERE Id = @userid)
-- Existing User
SELECT @userid = 1
INSERT INTO @users
SELECT @userid
WHERE NOT EXISTS (SELECT TOP(1) 1 FROM @users WHERE Id = @userid)
SELECT *
FROM @users
Greets
Flo
May 6, 2009 at 3:00 pm
It doesn't always return 1. If you were to collide on concurrency, both insert a Userid = 1, then you'd get 2, then you'd know there's a collision.
However, can you answer the questions about why you're doing or worrying about this? What is the thing you're trying to achieve? Is there unique index on this field?
May 6, 2009 at 3:42 pm
[font="Verdana"]Strange. I would have done that in the following way:
begin transaction;
if not exists (
select1
fromdbo.UserStats
whereUserID = @user-id
) begin
insert into dbo.UserStats(UserID)
values (@UserID);
end; -- if
commit transaction;
Unless you particularly want the side effect of selecting the row?
[/font]
May 6, 2009 at 3:50 pm
Bruce,
That is how I'd do it as well, but does that guarantee there can't be a collision? Could not two people potentially run the SELECT and IF NOT EXISTS at the same time? The transaction in that select shouldn't lock anything.
May 6, 2009 at 4:00 pm
Original code:
BEGIN TRANSACTION
SELECT * FROM UserStats WHERE UserID = @user-id
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO UserStats (UserID) VALUES (@UserID)
END
COMMIT TRANSACTION
What I would do:
insert into UserStats
select
from
UserStats us
where
us.UserID @user-id;
As long as the table isn't empty, this will work.
May 6, 2009 at 4:09 pm
Lynn, please correct me if I'm wrong but the original code would only once insert the @userid if does not yet exist. Your code seems to create a new row for every row with another UserId.
I still think a simple INSERT SELECT WHERE NOT EXISTS should work correct and transaction safe.
May 6, 2009 at 4:15 pm
Florian Reischl (5/6/2009)
Lynn, please correct me if I'm wrong but the original code would only once insert the @userid if does not yet exist. Your code seems to create a new row for every row with another UserId.I still think a simple INSERT SELECT WHERE NOT EXISTS should work correct and transaction safe.
Nope. The variable @user-id only has one value. If the value already exists in the table, it won't be inserted.
May 6, 2009 at 4:20 pm
Sure, the variable has only one value, but your SELECT might return many:
DECLARE @UserStats TABLE (UserId INT)
INSERT INTO @UserStats
SELECT 1
UNION ALL SELECT 2
DECLARE @userid INT
SELECT
@userid = 3
insert into @UserStats
select
from
@UserStats us
where
us.UserID @user-id;
SELECT * FROM @UserStats
Greets
Flo
May 6, 2009 at 4:21 pm
[font="Verdana"]I think Lynn has the right approach in doing the check and the insert in one statement, but he's not quite got the syntax right. Here's a quick test frame I came up with that works, and does the check and insert as one atomic operation.
if object_id('dbo.UserStats') is not null drop table dbo.UserStats;
create table dbo.UserStats(UserID int not null primary key);
insert into dbo.UserStats
select 1 union all select 2 union all select 3;
select * from dbo.UserStats;
declare @user-id int;
/*
insert into dbo.UserStats
select@user-id
fromdbo.UserStats us
whereus.UserID @user-id;
Msg 2627, Level 14, State 1, Line 11
Violation of PRIMARY KEY constraint 'PK__UserStats__07D70320'. Cannot insert duplicate key in object 'dbo.UserStats'.
The statement has been terminated.
*/
set @user-id = 2;
insert into dbo.UserStats
select top 1
wherenot exists (
select1
fromdbo.UserStats
whereUserID = @user-id
);
select * from dbo.UserStats;
set @user-id = 4;
insert into dbo.UserStats
select top 1
wherenot exists (
select1
fromUserStats us2
whereUserID = @user-id
);
select * from dbo.UserStats;
drop table dbo.UserStats;
Edited because Flo had a better idea. I think he and I were posting code at much the same time. I have tested this code and it works as expected.
[/font]
May 6, 2009 at 4:29 pm
Good catch Bruce. I wasn't testing with enough values. Mind isn't quite on it at the moment.
May 6, 2009 at 4:30 pm
Hi Bruce
This works correct, but why access the table twice? My first post should also work without accessing UserStats twice:
SELECT @userid = 2
INSERT INTO dbo.UserStats
SELECT @userid
WHERE NOT EXISTS (SELECT TOP(1) 1 FROM dbo.UserStats WHERE UserID = @userid)
SELECT @userid = 5
INSERT INTO dbo.UserStats
SELECT @userid
WHERE NOT EXISTS (SELECT TOP(1) 1 FROM dbo.UserStats WHERE UserID = @userid)
May 6, 2009 at 4:40 pm
Florian Reischl (5/6/2009)
Hi BruceThis works correct, but why access the table twice?
[font="Verdana"]Ooops. My Oracle days are showing... keep forgetting that you can do a select without having a table! So you're quite correct Flo... you don't need the table in the select. I'll go back and amend my post to remove the sys.columns.[/font]
Viewing 15 posts - 1 through 15 (of 92 total)
You must be logged in to reply to this topic. Login to reply