SQL Server 2008 R2 – DBCC DBINFO Showing Wrong Information

I would be confident you have corruption. Sounds like at least one corrupt data page. What does the contents of msdb.dbo.suspect_pages look like? Refer to http://msdn.microsoft.com/en-us/library/ms191301(v=sql.105).aspx for parsing some of that info.

There is a lot to grasp if you want to fully understand all of the intricacies of locking and isolation levels in SQL Server, and your question is essentially leading you in this direction.

All of the information is available at http://msdn.microsoft.com/en-us/library/ms173763.aspx, although it's not an easy read. An easier read is available at http://databases.about.com/od/sqlserver/a/isolationmodels.htm - pay particular attention to the "phantom reads" section.

For what you are trying to do you would need BOTH transactions running with an isolation level of "SERIALIZABLE". What this basically means is that they will run "as if" one was run after the other - that they were actually serialized.

What is happening is that BOTH the transactions decide that the same record needs to be inserted, and then BOTH try to insert that same record. The lock that is taken by the test logic is separate from the lock that is taken for the insert, which allows this to happen. SERIALIZABLE will stop this from happening with it's "Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes" clause. Another way to think of this is that the read locks from the test need to be held until the whole transaction completes. This can be specified using a HOLDLOCK query hint; but I have to say that I've not personally played with using HOLDLOCK, but just with the "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE" version.