Recommended transaction parameters for a big read only query?

I have the following situation

A background thread is creating (big) entities from an external source (file). The transaction may be long (~10s) with big files and can not be reduced, because we need complete, complex, and consistent business objects. Those business objects will likely not be modified later. To sum it up, I have a thread writing data in tables in a 'write once' mode.
At the same time the user can query already imported data. The LINQ queries can be complex, but are always read only.

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?)

dataobjects.net transaction linq

asked Sep 22 '10 at 03:51

olorin
358●87●87●92

2 Answers:

oldest newest most voted

IMO, the following solution is preferrable:

Use snapshot isolation. This ensures writer thread (trannsaction) won't be blocked (or deadlocked) by readers.
Implement correct deadlock handling (transaction reprocessing) for data import - just to be sure it won't fail in any case.

Note that currently our [Transactional] aspect isn't capable of automatic deadlock handling, but shortly this feature will be available. On the other hand, it's pretty easy to implement this in some particular case - just re-run the transaction (up to N times, with a random restart delay), if you got ReprocessableException on the previous run. DO will take care about cached state invalidation.

The hierarchy of ReprocessableException explains the cases when it gets thrown.

answered Sep 22 '10 at 06:18

Alex Yakunin
2971●4●4●12

edited Sep 22 '10 at 06:20

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 :)

(Sep 22 '10 at 06:34) olorin

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)?

(Sep 22 '10 at 10:52) olorin

Do you think it's safe to set snapshot isolation for all transactions on the domain?

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).

What happens if snapshot isolation is not available on database

Frankly speaking, I don't know - an error on trying to activate it, I suppose ;) Or I misunderstood the question?

(Sep 22 '10 at 14:16) Alex Yakunin

What happens if snapshot isolation is not available on database ?

I tested : you get an exception when you try to execute a query.

(Sep 23 '10 at 03:45) olorin

That's good - IMO it's better to fail in this case.

(Sep 23 '10 at 16:06) Alex Yakunin

I would suggest you to use read_committed_snapshot isolation. It will use row-level locks, so you will not get any deadlocks/table locks for large read queries. http://stackoverflow.com/questions/51969/how-to-detect-readcommittedsnapshot-is-enabled

answered Sep 28 '10 at 02:48

xumix
425●75●76●82

AFAIK, S-locks aren't applied and aren't taken into account on SNAPSHOT.

References:

(Sep 28 '10 at 15:59) Alex Yakunin

But anyway, read_committed_snapshot is lighter

(Sep 29 '10 at 09:00) xumix

Yep, surely ;) All depends on scenario they have - e.g. if they read almost nothing (~= don't fear inconsistencies like appearance of newly added objects), that's ok.

(Sep 29 '10 at 13:14) Alex Yakunin

Your answer

Please start posting your answer anonymously - your answer will be saved within the current session and published after you log in or create a new account. Please try to give a substantial answer, for discussions, please use comments and please do remember to vote (after you log in)!

toggle preview

community wiki

Subscription:

Once you sign in you will be able to subscribe for any updates here

Tags:

DataObjects.Net ×574
linq ×57
transaction ×23

Asked: Sep 22 '10 at 03:51

Seen: 6,541 times

Last updated: Sep 29 '10 at 13:14

Subscription:

Related questions