I have the following situation
To avoid deadlocks, how should configure the 'read' transaction? I don't mind if data being imported at the time of the query is not returned. Do I need to add addtional calls in LINQ query? (ie OQL skip locked entities hint equivalent is this .Lock() method?) |
IMO, the following solution is preferrable:
Note that currently our The hierarchy of Well, we reprocess the import anyway since the file will not be removed from the list of work to do if the transaction is rolled back. So we don't have any problem in case of deadlock in importer :) Do you think it's safe to set snapshot isolation for all transactions on the domain? What happens if snapshot isolation is not available on database (ie ALTER DATABASE [DO40-Tests] SET ALLOW_SNAPSHOT_ISOLATION OFF)?
Well, it's definitely better than to work on lower isolation level to reduce the amount of locks in another case ;) Snapshot isolation is also known as MVCC; nearly any other well-known competitor of SQL Server relies on this approach for ages (although likely, they have more efficient implementation).
Frankly speaking, I don't know - an error on trying to activate it, I suppose ;) Or I misunderstood the question? What happens if snapshot isolation is not available on database ? I tested : you get an exception when you try to execute a query. That's good - IMO it's better to fail in this case. |