June 19, 2012 at 1:01 am
Hi!
I dont know if I've totally misunderstood how sql server works.
I have the following table sometable(col1 int, col2 int, creationdate datetime)
Col1 together with col2 is the primary key
I execute the following query via .net sqlclient
if not exists(select 1 from sometable where col1 = @value1 and col2 = @value2)
begin
insert into sometable (col1,col2,CreationDate) values (@value1,@value2,getdate())
end
select @@ROWCOUNT
Which in my world would execute in a single transaction and should never give an duplicate key error???
However I get the following error quite often
Violation of PRIMARY KEY constraint 'PK_sometable'. Cannot insert duplicate key in object 'dbo.sometable'
June 19, 2012 at 1:07 am
This is most likely due to updates from multiple transactions. In read committed transaction isolation level, a query won't "see" changes made by other queries before they are committed. So, if a different query has already inserted the data but not yet committed the changes, the select will tell that the data does not exists. The insert however, will wait for an appropriate lock, and when it get it, the data has already been inserted by the other query, hence the primary key violation.
To avoid this, you could either rise the transaction isolation level, force an exclusive lock when you read the data, or possibly use the merge statement instead.
June 19, 2012 at 2:20 am
Thanks for your reply!
The table is quite busy and contain 10M+ rows what would be the best for performance?
When comparing a merge version of the query and my original query the cost was 80% for the merge and 20% for my original query.
June 19, 2012 at 4:18 am
I would go for the merge statement.
And, as for the percentage: "There are three kinds of lies: lies, damned lies, and statistics." Those percentages is at the best a vague estimate, I've often found them to be way off.
Someone wisely posted here that he (or she) only traded performance for one thing, integrity. That is exactly your case. The most important is the data integrity, then you look at the performance.
June 19, 2012 at 5:12 am
Hehe =)
I don't know why my query would affect data integrity? I thought the db design (keys/constraints) was the integrity.
I'am just after a fast way to check-insert data without getting errors =)
What about table hints on the if statement? with(rowlock, holdlock)
if not exists(select 1 from sometable with(rowlock, holdlock) where col1 = @value1 and col2 = @value2)
begin
insert into sometable (col1,col2,CreationDate) values (@value1,@value2,getdate())
end
select @@ROWCOUNT
June 19, 2012 at 5:17 am
insert into sometable (col1,col2,CreationDate)
SELECT @value1,@value2,getdate()
WHERE NOT EXISTS (SELECT 1 FROM sometable WHERE col1 = @value1 AND col2 = @Value2)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 19, 2012 at 6:00 am
Nice and simple.
Is that _the_ way to do check-inserts? I've read many posts on check-inserts, everyone seems to have their own way to do them.
June 19, 2012 at 7:00 am
You mention that this is a .NET application? I would make sure at the application layer that it is doing everything possible to not insert data that it shouldn't. Are you checking for the existence from the application before even attempting an insert?
Jared
CE - Microsoft
June 19, 2012 at 7:16 am
It's not the way. It works, there are many other ways, including Ole's merge (2008 and above), if exists with elevated isolation level and locking hints, etc.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 19, 2012 at 7:22 am
Hi!
Yep its a .net application.
I'am not checking for existing records before inserting. I guessed it would be faster to just do a check-insert than first a check from application and than a check-insert to avoid duplicate error.
The table is like an achievement log for users. When users perform different actions which is mapped to an achievement they get logged to that table. Multiple actions can trigger the same achievement. So while executing the action I just want to insert a row into my achievement log if there is no row for that achievement.
June 19, 2012 at 7:25 am
Ok, so the application does not care if a row gets inserted or not? It just attempts and either succeeds or fails?
Jared
CE - Microsoft
June 19, 2012 at 7:28 am
That is correct
June 19, 2012 at 7:30 am
In this case, I would say that checking in the application is irrelevant unless it happens in a service layer on a web server, so that the application can know of other inserts happening. If the application layer check would have to query the SQL Server, that check behave exactly as the check which was already in place.
That said, yes I do agree that the application should not attempt to insert invalid data, and no I would not trust such a check in the application layer.
June 19, 2012 at 7:33 am
Hmm... Not being an expert in this... Would simply using a TRY CATCH block suffice? Simply attempt to insert and if it fails rollback and signal to the application that it has done what it is supposed to do.
Jared
CE - Microsoft
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply