Geek asks and answers

RepeatableRead doesn't seem to block reading

I have a strange problem. I have a table with a primary key that is generated in an INSTEAD OF trigger. This is probably the worst way to implement the primary key, but they basically extract the maximum, increase it by 1, and use this value as the key. This happens instead of a trigger.

I have a .Net application that starts a RepeatableRead transaction, and I am inserting a record into this table. This works great if I'm not trying to make multiple tabs at the same time. If so, then I get a PC violation error. It seems to me that the inserts both start, the trigger retrieves the same last number for each transaction, increments it by 1, and then tries to insert both records with the same new "identifier".

I spoke with the database administrator who installed this trigger, and he believes that RepeatableRead should stop this, as it seems to be locking the table for reading, and other transactions will wait for the locks to exit. Thus, in essence, my transactions will occur in serial mode.

So the question is, why should I get a PK violation if RepeatableRead works like the DBA described it?

+3
source share
2 answers

RepeatableRead will not fix this because it allows phantom inserts, which is exactly what you are here for. The second insert is incorrect because it does not “see” the previous insert, and you describe the described behavior.

( , , ).

, ( , , ) .

+1

, , , REPEATABLE READ, , , ,

, , SERIALIZABLE, , , , ,

+1

More articles:


All Articles