Why do it?

Once concurrency on a database reaches a certain level the chances of deadlocks increase. Whenever a deadlock occurs there will be at least one transaction that is a victim.

Deadlocks also have a negative effect on performance.

What about protection of data

Without going into detail (see further reading below) READ_COMMITTED_SNAPSHOT provides the same level of protection against dirty reads as the current SQLServer default (READ_COMMITTED).

The payload of SNAPSHOT is increased resource usage. However compared to the resources lost from creation of LOCKS this doesnt seem to be a big issue.

I have deadlocks and SQLServer 2000, what do I do?

In this situation you are a little snookered!
The only strategy is to determine which queries are being locked/being selected as dead lock victims and reduce the lock protection on them by adding WITH(NOLOCK) to the table selection.
e.g.

SELECT * FROM Articles WITH(NOLOCK)

Example a Deadlock error message

Transaction (Process ID 98) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

How to enable it

To enable READ_COMMITTED_SNAPSHOT you must have the only active connection to the database you want to enable it on.

  1. Log everyone off the database server
  2. Restart the MSSQL service.
  3. Open Developer Studio – you now have the only active connection to the database
  4. Run: “ALTER DATABASE MyDatabase SET ALLOW_SNAPSHOT_ISOLATION ON”
  5. Run: “ALTER DATABASE MyDatabase SET READ_COMMITTED_SNAPSHOT ON”
  6. Complete

Note: If either of the queries in points 4 or 5 take a long time to complete (more than a few seconds) it is likely you do not have the only active connection. Check in the activity monitor for information on active sessions.

Things to be aware of

When enabled ALLOW_SNAPSHOT_ISOLATION keeps an archive of all changed values in the tempdb (it will automatically clear these up after it is finished with them). You should check the location of the tempdb and ensure there is available space on that drive.

Further Reading

http://msdn.microsoft.com/en-us/library/tcbchxcb(VS.80).aspx

Share
  • Digg
  • Twitter
  • Facebook
  • Identi.ca