SQL Server READPAST hint

Question

I'm seeing behavior which looks like the READPAST hint is set on the database itself.

The rub: I don't think this is possible.

We have table foo (id int primary key identity, name varchar(50) not null unique);

I have several threads which do, basically

id = select id from foo where name = ?
if id == null
    insert into foo (name) values (?)
id = select id from foo where name = ?

Each thread is responsible for inserting its own name (no two threads try to insert the same name at the same time). Client is java.

READ_COMMITTED_SNAPSHOT is ON, transaction isolation is specifically set to READ COMMITTED, using Connection.setTransactionIsolation( Connection.TRANSACTION_READ_COMMITTED );

Symptom is that if one thread is inserting, the other thread can't see it's row -- even rows which were committed to the database before the application started -- and tries to insert, but gets a duplicate-key-exception from the unique index on name.

Throw me a bone here?

Answer 1

Sounds like you're not wrapping the select and insert into a transaction?

As a solution, you could:

insert into foo (col1,col2,col3) values ('a','b','c')
where not exists (select * from foo where col1 = 'a')

After this, @@rowcount will be 1 if can check if a row was inserted.

Answer 2

SELECT SCOPE_IDENTITY()

should do the trick here...

plus wrapping into a transaction like previous poster mentioned.

Answer 3

You're at the wrong isolation level. Remember what happens with the snapshot isolation level. If one transaction is making a change, no other concurrent transactions see that transaction. Period. Other transactions only will see your changes once you have committed, but only if they START after your commit. The solution to this is to use a different isolation level. Wrap your statements in a transaction and SET TRANSACTION LEVEL SERIALIZABLE. This will ensure that your other concurrent transactions work as if they were all run serially, which is what you seem to want here.

Answer 4

The moral of this story is fully explained in my blog post "You can't hold onto nothing" but the short version of this is that you want to use the HOLDLOCK hint. I use the pattern:

INSERT INTO dbo.Foo(Name)
SELECT TOP 1
    @name AS Name
FROM (SELECT 1 AS FakeColumn) AS FakeTable
WHERE NOT EXISTS (SELECT * FROM dbo.Foo WITH (HOLDLOCK)
                  WHERE Name=@name)
SELECT ID FROM dbo.Foo WHERE Name=@name