user1751825
user1751825

Reputation: 4319

Using database snapshot vs snapshot issolation level transaction

I maintain an an MVC application which incorporates some long running batch processes for sending newsletters, generating reports etc.

I had previously encountered a lot of issues with deadlocks, where one of these long running queries might be holding a lock on a row which then needs to be updated by another process.

The solution I originally came up with, was to have a scheduled task, which creates database snapshots, like so...

CREATE DATABASE MyDatabase_snapshot_[yyyyMMddHHmmss]... AS SNAPSHOT OF MyDatabase

My application then has some logic which will find the latest available snapshot, and use this for the readonly connection for the long-running processes, or anywhere else where a read-only connection was required.

The current setup is perfectly functional, and reliable. However being dependent on that scheduled task doesn't make me happy. I can imagine, at some stage in the future, if someone else is looking after this project, this could be an easy source of confusing issues. If the database was moved to another server, for example, and the snapshot creation scheduled task wasn't setup correctly.

I've since realised I could achieve a similar result by using snapshot transaction issolation, and avoid all the extra complexity of managing the creation and cleanup of the database snapshots.

However I'm now wondering whether there may be any performance drawbacks for doing this using transactions vs continuing to use the static snapshots.

Consider the following scenario.

The system periodically sends personalised job lists to approximately 20K subscribers. For each of these subscribers it does database lookups to create the matching jobs list.

What is has been doing, is looping through the full recipient list, and for each one...

  1. Open a connection to the snapshot db
  2. Run the query to find matching jobs
  3. Close the snapshot db connection

If instead, it does the following...

  1. Open the database connection to the normal database (non-snapshot)
  2. Create a snapshot issolated transaction
  3. Run the query to find matching jobs
  4. Close the transaction
  5. Close the database connection

Does this actually translate to more work for the database server?

Specifically I'm wondering about what's involved at step #2.

Removing complexity from the application is a good thing, but not at the expense of performance. Particularly since this particular process is already quite server intensive, and takes quite a long time to run.

Upvotes: 0

Views: 128

Answers (0)

Related Questions