With isolation level Read Committed without snapshot isolation enabled you generate non-exclusive locks even when just reading data. When writing you need an exclusive lock which you only get if there is no read-lock on the same item yet. So if you do parallel reading and writing of data you risk dead locks, e.g. in the following sequence T1: read A T2: read B T1: write B' over B (T1 goes to waiting state) T2: write A' over A (deadlock!) So if you experience such situations, just turn snapshot isolation on (I don't know why they don't do that by default). The result is that T1: read A T2: read B T1: write B' over B (succeeds and keeps a copy of B for transaction T2) T2: write A' over A (succeeds and keeps a copy of A for transaction T1) It's not very expensive if you usually have short transactions and it really helps. By the way: snapshot isolation is not an isolation level by itself but a concept that makes the read committed isolation level usable.
Am Dienstag, 10. Juli 2012 14:27:30 UTC+2 schrieb Throckmorton: > Does anyone have experience with snapshot isolation level on sql server? > We are getting a lot of deadlocks > with read committed. > > Thanks! > -- You received this message because you are subscribed to the Google Groups "nhusers" group. To view this discussion on the web visit https://groups.google.com/d/msg/nhusers/-/fAywqeuzj-sJ. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.
