RepeatableRead does not seem to be locking for read

I am getting开发者_JS百科 a strange problem. I have a table that has a primary key that is generated in an INSTEAD OF trigger. It's probably the worst way to implement a primary key, but they basically fetch the maximum, increment it by 1, and use this value as the key. This happens in the instead of trigger.

I have a .Net app that starts a RepeatableRead transaction, and I insert a record into this table. This works fine as long as I don't try and do more than one insert simultaneously. If I do, I receive a PK violation error. It seems to me that the inserts both get fired, the trigger fetches the same last number for each transaction, increments it by 1, and then tries to insert both records with the same new 'identifier'.

I've spoken to the DBA that set this trigger up, and he is of the opinion that RepeatableRead should stop this from happening as it apparently locks the table for reads and other transactions will wait for the locks to be released. So, in essence, my transactions will occur in serial.

So, the question is, why would I be getting a PK violation if RepeatableRead works the way the DBA described it?

RepeatableRead won't fix that, because it allows for phantom inserts, which is exactly what you have here. The second insert is wrong because it doesn't "see" the previous insert, and you have the behaviour described.

You can fix it with serializable isolation, or by doing separate transactions (the former will increase contention, the latter reduce it, but the latter may not be possible for you).

Really, the fix is to replace the instead of trigger with a property identity constraint (this can be done on an existing table, though there are difficulties, esp. if replication is being used), or failing that using a better algorithm to set the new value.

first let me express my disgust of the approach taken to generate the primary key, now as far as the REPEATABLE READ isolation, it only protects against updates, you will still be able to read and that is the problem with your implementation, no protection against inserts either

ideally I urge you to restructure the primary key generation but if that is not possible the only thing left is to use SERIALIZABLE isolation, which protects for inserts as well, however depending on how and when you determine the next key value, you might not be able to solve it either way