Concurrency issue? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Concurrency issue?

Author

Topic

t.perrin
Starting Member

5 Posts

Posted - 2013-08-08 : 09:17:20

Hi everyone,

I use the following script in a stored procedure called a hundred times per second:


/* table with column id which is primary key */
INSERT INTO TABLE(id)
  SELECT @new_id
  WHERE @new_id IS NOT NULL
  AND NOT EXISTS (
    SELECT NULL
    FROM TABLE
    WHERE id = @new_id)

Sometimes (not for each call), I have the exception "violation primary key". How is it possible that the clause "where not exists" is checked because @new_is does not exist then that the insert is cancelled because @new_id is found??!! I don't understand!!!
Is it a concurrency issue? what can I do to avoid that? (of course I can manage the exception with try/catch or just ignore the exception, but I want to understand the issue and to know the "geniune" solution

Thanks a lot, Thomas

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-08 : 09:41:52

why select NULL inside NOT EXISTS?
try


INSERT INTO TABLE(id)
  SELECT @new_id
  WHERE @new_id IS NOT NULL
  AND NOT EXISTS (
    SELECT 1
    FROM TABLE
    WHERE id = @new_id)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-08 : 10:06:31

SELECT NULL seems to behave the same way as SELECT 1 - see the example below. Even so, I would prefer SELECT 1 or SELECT *, just for clarity.

My conjecture is that because READ COMMITTED is the default isolation level, SQL Server thinks that it is allowed to change the data after you have read it, but before the new data is inserted. If that is the case, you might try specifying isolation level of REPEATBLE READ http://technet.microsoft.com/en-us/library/ms173763.aspx

This is just a conjecture. Let us see if someone else can provide some more insights into the problem.

CREATE TABLE #tmp(id int);
INSERT INTO #tmp VALUES (1);

IF NOT EXISTS(SELECT NULL FROM #tmp WHERE id = 2) SELECT '2 does not exist in table';
IF NOT EXISTS(SELECT NULL FROM #tmp WHERE id = 1) SELECT '1 does not exist in table';
DROP TABLE #tmp;

-- output
-- (No column name)
-- 2 does not exist in table

t.perrin
Starting Member

5 Posts

Posted - 2013-08-08 : 10:27:50

Hello Visakh,
James is right, it is a select in a "not exists" clause, so we can put whateveer we want. Select null seems to be faster

t.perrin
Starting Member

5 Posts

Posted - 2013-08-08 : 10:30:18

hello James K,
I already try all the different isolation level, I still have insertion of an existing primary key...
What do you think about using WITH(TABLOCKX)

INSERT INTO TABLE(id)
SELECT @new_id
WHERE @new_id IS NOT NULL
AND NOT EXISTS (
SELECT NULL
FROM TABLE WITH(TABLOCKX)
WHERE id = @new_id)

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-08 : 10:48:40

TABLOCKX should certainly prevent the problem, but then again, if isolation level is the problem, I would have expected repeatable read to prevent the problem as well.

The problem with TABLOCKX is that your performance will be in the tank. It won't even let anyone else read from that table when you are holding on to that lock. And, if you are inserting into it hundred times per second, the readers will really notice the poor performance.

I am a bit surprised that your existence check of SELECT NULL performs faster than SELECT 1. Just can't wrap my mind around how that can be.

t.perrin
Starting Member

5 Posts

Posted - 2013-08-08 : 11:17:33

No you are right, select null is the same than select 1 (or any constant), i was comparing it to select a column in a table.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-08 : 16:41:03

quote:
Originally posted by t.perrin

No you are right, select null is the same than select 1 (or any constant), i was comparing it to select a column in a table.

That makes sense - when you specify a column name, SQL Server will have to check the permissions on the column, retrieve the data in that column etc. A constant does not require that, and so would be faster.

Did you resolve your original problem using TABLOCKX?

t.perrin
Starting Member

5 Posts

Posted - 2013-08-09 : 09:41:54

for now I haven't seen the problem yet. But it happend only a few times a day, so I will wait a whole week without any error before to say it resolved it.
I also still have to make tests about performance of that solution

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources