May 7, 2009 at 8:41 am
Rereading the posts, you were asking the wrong question, well actually you weren't asking the complete question. Please look at the following code as it more closely matches what you are attempting to do.
create table dbo.UserStats ( -- Create the test table
UserID int,
UserTime datetime,
UserData varchar(32) null
);
go
select * from dbo.UserStats; -- show that the table is empty
go
declare @user-id int,
@UserTime datetime;
select
@user-id = 1,
@UserTime = dateadd(mi,datediff(mi,0,getdate()),0); -- drop the seconds off the time
insert into dbo.UserStats (UserID,UserTime) -- insert a row of data
select
@UserTime
where
not exists(select 1 from dbo.UserStats where UserID = @user-id and UserTime = @UserTime);
go
select * from dbo.UserStats; -- show what's there
go
declare @user-id int,
@UserTime datetime;
select
@user-id = 1,
@UserTime = dateadd(mi,datediff(mi,0,getdate()),0); -- drop the seconds off the time
insert into dbo.UserStats (UserID,UserTime) -- try again, hopefully the time is the same
select
@UserTime
where
not exists(select 1 from dbo.UserStats where UserID = @user-id and UserTime = @UserTime);
go
select * from dbo.UserStats;-- show the data in the table again
go
WAITFOR DELAY '00:01'; -- Wait one minute before executing again.
declare @user-id int,
@UserTime datetime;
select
@user-id = 1,
@UserTime = dateadd(mi,datediff(mi,0,getdate()),0); -- drop the seconds off the time
insert into dbo.UserStats (UserID,UserTime) -- insert data again, this time the time will be one minute later
select
@UserTime
where
not exists(select 1 from dbo.UserStats where UserID = @user-id and UserTime = @UserTime);
go
select * from dbo.UserStats;-- show the data again
go
drop table dbo.UserStats;-- drop the table, it was a test table
go
May 7, 2009 at 12:44 pm
I didn't mention the datetime stuff in the original post, because I wanted to simplify the question. Please note that the crux of the question is about concurrency. I appreciate all the SQL variations, but what I really want to know is whether any of them guarantee that two inserts can't happen. I assume the answer is that they don't. I'm also not sure that a different isolation level exists that would help either.
Thanks for all your help in this thread!
Jamie
May 7, 2009 at 2:19 pm
That is incorrect. Look at this. You can start a transaction explicity and leave it hanging in session 1, then go to session 2 and start another transaction there. You will find that session 2 is locked out from doing the insert until session 1 commits.
Once you've confirmed that session 2 is waiting, go back and run the commented COMMIT TRAN statement in session 1. Once session 1 commits, session 2 is freed up to try the insert, but fails. You know that session 2s insert fails because @do_nothing got set to "2" in the Catch section.
After you tried that, go back and try it again setting the user in session 1 to "USERY" and the user in session 2 to "USERZ".
Obviously an implicit transaction will do it's insert in a fraction of a second, but the same locking principle applies. Let me know if you have any questions.
Bob
----------------------------------------------------------- SESSION 1
/* run this once
-- create table userStatsTest (UserID char(10) primary key)
*/
-- session 1
begin tran
--
declare @do_Nothing int
declare @userid char(10)
set @userid = 'UserX'
--
begin try -- just try to force it in there !!
insert into dbo.userStatsTest
select @user-id
end try
--
begin catch -- if you can't, do nothing
set @do_Nothing = 1
end catch
--
select * from dbo.userStatsTest
select @do_nothing
--
/* do this after starting session 2
commit tran
*/
--
/* do this after running the commit tran above and checking session 2
-- select @do_nothing AND edited to fix this "bugette"
truncate table dbo.userStatsTest
*/
--
-------------------------------------------------- SESSION 2
begin tran
declare @do_Nothing int
declare @userid char(10)
-- set @userid = 'UserY' EDITED the next day to get the userIDs right.
set @userid = 'UserX'
--
begin try -- just try to force it in there !!
insert into dbo.userStatsTest
select @user-id
end try
--
begin catch -- if you can't, do nothing
set @do_Nothing = 2
end catch
--
select * from dbo.userStatsTest
select @do_nothing -- will be null if insert was successful, = 2 if insert fails
commit tran
--
__________________________________________________
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 2:25 pm
Jamie Julius (5/7/2009)
I didn't mention the datetime stuff in the original post, because I wanted to simplify the question. Please note that the crux of the question is about concurrency. I appreciate all the SQL variations, but what I really want to know is whether any of them guarantee that two inserts can't happen. I assume the answer is that they don't. I'm also not sure that a different isolation level exists that would help either.Thanks for all your help in this thread!
Jamie
Which is why I pointed you to BOL and Locks. By understanding the different locks and how the interact you will understand how SQL Server handles concurrency.
May 7, 2009 at 2:28 pm
Jamie, are you thinking that you have to somehow code the locks yourself?
__________________________________________________
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 8, 2009 at 12:11 am
Bob Hovious (5/7/2009)
That is incorrect. Look at this. You can start a transaction explicity and leave it hanging in session 1, then go to session 2 and start another transaction there. You will find that session 2 is locked out from doing the insert until session 1 commits.Once you've confirmed that session 2 is waiting, go back and run the commented COMMIT TRAN statement in session 1. Once session 1 commits, session 2 is freed up to try the insert, but fails. You know that session 2s insert fails because @do_nothing got set to "2" in the Catch section.
After you tried that, go back and try it again setting the user in session 1 to "USERY" and the user in session 2 to "USERZ".
Obviously an implicit transaction will do it's insert in a fraction of a second, but the same locking principle applies. Let me know if you have any questions.
Bob
----------------------------------------------------------- SESSION 1
/* run this once
-- create table userStatsTest (UserID char(10) primary key)
*/
-- session 1
begin tran
--
declare @do_Nothing int
declare @userid char(10)
set @userid = 'UserX'
--
begin try -- just try to force it in there !!
insert into dbo.userStatsTest
select @user-id
end try
--
begin catch -- if you can't, do nothing
set @do_Nothing = 1
end catch
--
select * from dbo.userStatsTest
select @do_nothing
--
/* do this after starting session 2
commit tran
*/
--
/* do this after running the commit tran above and checking session 2
select @do_nothing
*/
--
-------------------------------------------------- SESSION 2
begin tran
declare @do_Nothing int
declare @userid char(10)
set @userid = 'UserY'
--
begin try -- just try to force it in there !!
insert into dbo.userStatsTest
select @user-id
end try
--
begin catch -- if you can't, do nothing
set @do_Nothing = 2
end catch
--
select * from dbo.userStatsTest
select @do_nothing -- will be null if insert was successful, = 2 if insert fails
commit tran
--
I understand that the INSERT's themselves will block. However, the original INSERT's were conditional based on SELECT's. Since I believe that the SELECT's won't block, both sessions could in theory conclude that they should both perform the INSERT. I'm trying to understand if there is a locking mechanism available to prevent this.
Hope this is clear.
May 8, 2009 at 6:57 am
Conditional or not, an INSERT is an INSERT. Look at the code I just sent you and think about it for a moment. Using the Try/Catch blocks, I am trying to insert every time!, without even checking to see if it is "safe" or not. SQL handles it. Modify the example I gave you to make it conditional, and test it until you can believe it. 🙂
By the way, if you want a good book that you can hold in your hands, pick up Inside Microsoft SQL Server 2005: T-SQL Programming by Itzak Ben-Gan and others. Chapters 9 and 10 are probably what you're looking for. Lynn is right, as usual, that all that is really required here is an understanding of locks.
__________________________________________________
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 8, 2009 at 7:58 am
This:
begin transaction
select @rows = count(*) from dbo.SomeTable where SomeID = @SomeID;
if @rows = 0
insert into dbo.SomeTable values (@SomeID)
commit transaction
WILL NOT PREVENT two sessions from entering the same value.
This (with or without and explicit transaction)
insert into dbo.SomeTable
select @SomeID
where not exists(select 1 from dbo.SomeTable where SomeID = @SomeID)
WILL achieve what you are attempting to do.
Bob has demonstrated this with his code in previous posts.
Can you explain why you feel that this won't work?
May 8, 2009 at 8:30 am
Hi Jamie
I think we talk at cross-purposes.
You are correct a SELECT will not lock the table and you can get errors if you INSERT data later in your procedure.
But if you use the "INSERT INTO SELECT WHERE NOT EXISTS" syntax the operation of the statement is an INSERT not a SELECT. The SELECT is just a sub-part of the statement so nothing to fear. 😉
Greets
Flo
May 8, 2009 at 8:36 am
Julius,
Coding this way ( with exists ... ) is NOT needed at all.
Set up a primary key and if there is a collision handle the error accordingly.
Leave locking to SQL Server, it does it very well.
* Noel
May 8, 2009 at 8:41 am
Jamie,
I think we'd still like to know why you're doing things this way, and if you don't trust SQL Server to handle concurrency somehow.
May 8, 2009 at 9:20 am
noeld (5/8/2009)
Julius,Coding this way ( with exists ... ) is NOT needed at all.
Set up a primary key and if there is a collision handle the error accordingly.
Leave locking to SQL Server, it does it very well.
I mentioned using check contraints as well in an earlier post. As you actually have a multicolumn contraint, it would probable need to be a table level contraint or unique index. Doing this will also prevent duplicate entries.
May 8, 2009 at 9:39 am
Florian Reischl (5/6/2009)
HiWhat about just a simple INSERT SELECT WHERE:
INSERT INTO dbo.UsersSELECT @userid
WHERE NOT EXISTS (SELECT TOP(1) 1 FROM dbo.Users WHERE Id = @userid)
Bob, Lynn, Flo, Steve and Bruce:
{warning, devil's advocate mode is *on*} 🙂
There are a number of scenarios where this construction does not guard against duplicates - unless the effective isolation level on the read-cursor side of the query plan is SERIALIZABLE:
INSERT INTO dbo.Users
SELECT @userid
WHERE NOT EXISTS (SELECT TOP(1) 1 FROM dbo.Users WITH (SERIALIZABLE) WHERE Id = @userid);
The simplest example is where two or more concurrent users execute the query at the READ COMMITTED isolation level when READ_COMMITTED_SNAPSHOT is in effect.
To demonstrate this, create a test database and run ALTER DATABASE SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;
Create a test table with a non-unique index:
CREATE TABLE dbo.Demo (userid INT NOT NULL);
CREATE INDEX i ON dbo.Demo (userid);
Then, in one connection, run:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED -- the default
BEGIN TRANSACTION;
INSERT dbo.Demo
SELECT 1
WHERE NOT EXISTS (SELECT 1 FROM dbo.Demo WHERE userid = 1);
...and leave the transaction open.
Run this in connection 2:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED -- the default
INSERT dbo.Demo
SELECT 1
WHERE NOT EXISTS (SELECT 1 FROM dbo.Demo WHERE userid = 1);
Note that the insert succeeds and is not blocked by connection 1
Now commit the transaction in connection 1, and from either connection run:
SELECT user_id FROM dbo.Demo;
Note that two rows exist with userid = 1
===
This behaviour is not unique to READ_COMMITTED_SNAPSHOT (which has much to recommend it) but it is the easiest to demonstrate.
In the more general case where the condition in the EXISTS clause is anything other than an equality predicate, all sorts of scenarios can cause this code to permit duplicates. These include page-splits, data modifications ahead of or behind the read-cursor scan position, and other timing-related issues.
Perhaps the easiest scenario to explain is where a range of rows is being scanned by the SELECT, say an INT column with values ranging from 1 to 100,000. At READ COMMITTED, shared locks are taken only for as long as it takes to check the row. Once the row has been checked, the lock is released. If the EXISTS clause is checking for the existence of values in the 400-600 range, and the scan has got as far as row(s) with the value 500, there is nothing to prevent another concurrent transaction inserting a row with a value of 401, and committing before the scan finishes. If no rows existed in the range 400-600 before that committed insert, the scanning query will result in unplanned-for behaviour - and difficult to debug behaviour at that.
Equally, an unrelated widening column update ahead of the scan position can cause a page split, which will move half the rows to a free page. If an allocation-order scan is being performed and the rows are moved to a page behind the scan, the rows will be missed by that scan. When I say 'scan' I include the scanning of a range rows on an index using a 'seek'. Anything that considers a number of rows is potentially vulnerable.
All these considerations become much more likely as the number of concurrent transactions increases. Aggregate expressions like COUNT, SUM, MIN, and MAX are even more vulnerable to these kinds of issues - these routinely return 'incorrect' results in high-concurrency situations, even at REPEATABLE READ. If that sounds surprising, consider that REPEATABLE READ only holds locks on rows read. Nothing prevents a new row from being inserted (and committed) into the already-scanned range, before the scan is complete.
Note that nothing is broken in SQL Server - this is the expected behaviour. It is just that not everyone appreciates the limited guarantees offered by the various isolation levels.
I think the ideas above are broadly what is bothering the OP, even if he is unable to concisely express those concerns. It is disappointing to see so many 'senior' people so ready to give the OP a hard time, almost saying that he is stupid for persisting with the question. That is my personal perception only based on the comments I have read. I have no doubt that this was not your collective intention...
Finally, yes, it is true that a unique constraint, index, or primary key will enforce uniqueness - but that does not cover all cases where the OP's question is valid, and the INSERT...NOT EXISTS construct might be used.
Cheers,
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 8, 2009 at 9:48 am
For clarity, note that Bob's example also fails if READ_COMMITTED_SNAPSHOT is on.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 8, 2009 at 9:52 am
Bob Hovious (5/7/2009)
Jamie, are you thinking that you have to somehow code the locks yourself?
Jamie,
At the same time as *not* necessarily recommending this as a solution in this case, there are times where application locks are appropriate.
Check out:
sp_getapplock
sp_releaseapplock
APPLOCK_MODE
APPLOCK_TEST
in Books Online.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 31 through 45 (of 92 total)
You must be logged in to reply to this topic. Login to reply