t-sqltransactionsadoquery-hints

ADO Transaction and READPAST

I don't know if this is the best way, if there is a better way, please post.

I have an application that read a file and insert records. The entire file is processed in one transaction.

Before a record is inserted the table needs to be checked for duplicates (note: I can't make this a table constraint since there are exceptions)

So the duplicate check is a normal select statement, but the problem is, it reads the uncomitted records from the current transaction. I have included READPAST and READCOMMITTED hints in the select statement, but that still return the record.

Any ideas?

Solution

  • the only way to implement this within the db is locking table. look at the ISOLATION LEVEL SERIALIZABLE