0

I'm investigating a deadlock and I see in deadlock xml file from profiler that process process5332cf8 runs transaction under Snapshot isolation level (isolationlevel="snapshot (5)").

But somehow it holds IX lock on a page and wants to take another one.

What can be the reason for this (except that isolation level was changed to non-snapshot in the middle of thansaction which is not likely to happen ) ?

Am I missing something about Snapshot IL ?

PS: the full xml: http://ideone.com/yuU9td

Artur Udod
  • 4,465
  • 1
  • 29
  • 58

1 Answers1

5

Writes always take locks. IX is a write lock. As for the other processes (the two SELECTs, process process6593498 and process5cc1498) they are under read committed.

If you want to avoid contention with snapshot isolation your must use it for your read transactions first of all.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • Seems like I really missunderstood something about snapshot IL. Thank for your answer) – Artur Udod Jul 26 '13 at 15:53
  • Kind of not related to this topic, but...could you advise me then, how do I force my sql-statements to run with snapshot if I don't explicitly begin a transaction? – Artur Udod Jul 26 '13 at 16:03
  • Should I always explicitly start a transaction (even for a single statement?)? otherwise it will run under RC (default IL) – Artur Udod Jul 26 '13 at 16:04
  • Yes, you must start a transaction. The alternative is to enable read_committed_snapshot on the database which will make every standalone read under read committed (default) use the so called 'read committed snapshot isolation' (RCSI). A good read is [Implementing Snapshot or Read Committed Snapshot Isolation in SQL Server: A Guide](http://www.brentozar.com/archive/2013/01/implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide/). – Remus Rusanu Jul 26 '13 at 18:32
  • yeah, thank you. I've already read that article recently. That's a nice one. – Artur Udod Jul 26 '13 at 22:03