May 6, 2009 at 4:45 pm
And with that, Bruce, you can also remove the TOP 1 as shown below:
create table dbo.UserStats (UserID int);
insert into dbo.UserStats
select 1 union all select 2 union all select 3 union all select 4;
select
*
from
dbo.UserStats;
declare @user-id int;
set @user-id = 1;
insert into dbo.UserStats
select
where
not exists(select 1 from dbo.UserStats us1 where us1.UserID = @user-id);
select
*
from
dbo.UserStats;
set @user-id = 2;
insert into dbo.UserStats
select
where
not exists(select 1 from dbo.UserStats us1 where us1.UserID = @user-id);
select
*
from
dbo.UserStats;
set @user-id = 3;
insert into dbo.UserStats
select
where
not exists(select 1 from dbo.UserStats us1 where us1.UserID = @user-id);
select * from dbo.UserStats;
set @user-id = 4;
insert into dbo.UserStats
select
where
not exists(select 1 from dbo.UserStats us1 where us1.UserID = @user-id);
select * from dbo.UserStats;
set @user-id = 5;
insert into dbo.UserStats
select
where
not exists(select 1 from dbo.UserStats us1 where us1.UserID = @user-id);
select * from dbo.UserStats;
drop table dbo.UserStats;
May 6, 2009 at 4:48 pm
Thank you guys. I really thought I'm a bit slow on the uptake 😛
May 6, 2009 at 5:04 pm
Lynn Pettis (5/6/2009)
And with that, Bruce, you can also remove the TOP 1 as shown.
[font="Verdana"]Yep, you're right, the top 1 is no longer necessary. No Flo, you're not slow... you're bang on target as usual. :-D[/font]
May 6, 2009 at 5:28 pm
And now it also works for an empty table. Just ran a test with the code here at home.
May 6, 2009 at 10:24 pm
Just for fun... Don't ask if you can insert before you can insert. In situations where you actually expect the insert to succeed most of the time, this might even be a little more efficient. (In case Jeff looks in, I haven't tested it yet. It's late and I'm at home without my laptop.)
declare @do_Nothing int
--
begin try -- just try to force it in there !!
insert into dbo.UserStats
select @user-id
end try
--
begin catch -- if you can't, do nothing
set @do_Nothing = 1
end catch
--
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
May 7, 2009 at 12:55 am
Thanks to all of you who looked into my question!
I gather that the consensus is to combine everything into a single statement like this:
insert into dbo.UserStats
select
where
not exists(select 1 from dbo.UserStats us1 where us1.UserID = @user-id);
Cool!
Can someone point me in the direction of literature that would explain why that enforces the necessary locks to guarantee that in a highly concurrent environment, I won't end up with two insertions?
Thanks,
Jamie
May 7, 2009 at 1:11 am
Steve Jones - Editor (5/6/2009)
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?
Just to make sure I understand. You're saying that I can INSERT a single row and @@ROWCOUNT will give me 2, because concurrently some other session also inserted a row? Isn't @@ROWCOUNT per session?
As for what I'm doing:
UserStats is a table to maintain statistics about which users have accessed the system during a specific one-minute time slice. So each record in the UserStats table is unique in terms of the user in question (UserID) AND the one-minute time slice (e.g., "2009-05-06 08:17:00").
For example, if a user accesses the system (say, UserID 17 at 2009-05-06 08:17:34), I attribute this to the one-minute time slice of "2009-05-06 08:17:00" (i.e., throw away the seconds).
Then I want to add a record to UserStats with UserID 17 and TimeSlice "2009-05-06 08:17:00" ONLY if such a record doesn't already exist in the table.
Hope this is clear.
Jamie
May 7, 2009 at 6:12 am
Best place to start is in Books On-Line, locking.
May 7, 2009 at 6:22 am
Thanks. I'm familiar with Books Online. It seems to me that the proposed SQL would be considered an implicit transaction. It would be run under the default isolation mode of "Read committed". I don't think this is enough to prevent two concurrent sessions from both inserting the same UserID. If they each do the SELECT part at the same time, they will both conclude that the UserID is not in the table. I would be happy to be proved wrong about this.
May 7, 2009 at 6:28 am
mjarsaniya (5/7/2009)
--USE THIS ONEBEGIN TRANSACTION
SELECT * FROM UserStats WHERE UserID = @user-id
IF NOT EXISTS (SELECT * FROM UserStats WHERE UserID = @user-id)
BEGIN
INSERT INTO UserStats (UserID) VALUES (@UserID)
END
COMMIT TRANSACTION
Err... nope. This was the initial statement which has the problem that the SELECT will not cause a lock.
Greets
Flo
May 7, 2009 at 6:33 am
jamiejulius (5/7/2009)
Thanks. I'm familiar with Books Online. It seems to me that the proposed SQL would be considered an implicit transaction. It would be run under the default isolation mode of "Read committed". I don't think this is enough to prevent two concurrent sessions from both inserting the same UserID. If they each do the SELECT part at the same time, they will both conclude that the UserID is not in the table. I would be happy to be proved wrong about this.
Then let's start with this, what are the different types of locks? When the INSERT starts, what type of lock is SQL Server going to issue? When you issue a BEGIN TRANSACTION, SQL Server isn't going to issue any locks at that point, it simply marks the start of an explicit transaction in the transaction log.
Again, read BOL and if you still have questions, we'd be glad to help you further your understanding.
May 7, 2009 at 6:53 am
jamiejulius (5/7/2009)
Thanks. I'm familiar with Books Online. It seems to me that the proposed SQL would be considered an implicit transaction. It would be run under the default isolation mode of "Read committed". I don't think this is enough to prevent two concurrent sessions from both inserting the same UserID. If they each do the SELECT part at the same time, they will both conclude that the UserID is not in the table. I would be happy to be proved wrong about this.
And, going further here, if the value being inserted needs to be unique (ie no duplicate values) then a unique constraint should ne declared on that column in the table. That would prevent duplicate values from being inserted to the table.
May 7, 2009 at 7:04 am
And then I guess I would need to handle the specific "duplicate" error as a non-error.
May 7, 2009 at 7:39 am
Actually you would typically "retry" a couple times if you get the duplicate error.
The chances of there being two inserts at the same time is extremely small, so likely you would get an insert on the 2nd or 3rd time if there was a collission.
The other thing is what data are you passing in here that can't be duplicated? If you are generating your UserID in the application or by user entry, SQL can automatically handle integer generation.
Viewing 15 posts - 16 through 30 (of 92 total)
You must be logged in to reply to this topic. Login to reply