CODEWITHSUNDEEP
c#linqsql-server-2008
Renae
Renae

Reputation: 442

nolock/TransactionScope vs snapshot isolation

Should snapshot isolation be used instead of nolock/TransactionScope? Snapshot seems like a db setting that applies to the database as whole? Is that right and does that mean I don't need to specifically code for it?

If I update related tables and then SubmitChanges(), will linq always update the tables in the same order?

Thanks

Upvotes: 0

Views: 1680

Answers (1)

Alexey Raga
Alexey Raga

Reputation: 7535

First, NoLock (and ReadUncommitted) are extremely dangerous. Think, you are using transactions mostly because you want to have your data to be consistent. By using NoLock you allow dirty-reads of your data. Say, it is very possible in your application that you read half-updated or half-inserted data. Your application (or users) will make decisions based on inconsistent data.

If you go to the business and simply ask if the data your application works on can be inconsistent, I believe the answer will be strictly NO. So just don't take this path, we've been there, it ends up nowhere.

Now about the order. As far as I understand, it is theoretically not guaranteed whether it is going to be always the same order or not, but practically it probably will be (as ORMs usually have just one algorithm to enumerate entities and discover changes). But it doesn't really help because even it enumerates the entities (in order to find what needs to be saved) in the same order, the number of entity types can be different. Say, it is A and D in one scenario and A, B, D in another and A, C, D in another one. Now it may depend on relationships between these entities. Say, C depends on D, so the actual order will actually be A, D, C, not A, C, D (and even here it is not specified that it is not going to be D, C, A). So relaying on this order is not an option. The only thing you can do to ensure the order is to call .SaveChanges() after each step which is nasty.

Yes, you can use Snapshot isolation level (there are two, basically any of them, try Snapshot Read Committed). It will dramatically decrease the amount of deadlocks in your system, but this method has its own disadvantages.

In order to solve the issue more or less properly I would recommend you to clearly define transaction boundaries within your system. Which part of the system "owns" which data? Which data must be changed consistently with other data? Then you will be able to say "only these transactions are allowed" and "only this specific part of my system is allowed to touch this specific data".

It is hard, especially in the beginning when you have a big database that is read and written chaotically, everything from everywhere. But it leads to more stable, controllable and maintainable system.

BTW, Udi Dahan has an interesting blog post about this: Inconsistent data, poor performance, or SOA – pick one

Upvotes: 2

Related Questions