Best Isolation Level to avoid deadlocks in Sql Server

Question

I need execute un update statement over an sql server table, this table is used by another process at the same time. because that sometimes deadlocks ocurs. wich Isolation Level do you recomend for L1 and L2 to avoid deadlocks?

p1:

set transaction level L1
    delete from t1 where id=2
    select * from t1 where id=3

p1:

set transaction level L2
    delete from t1 where id=3
    select * from t1 where id=2

score 0 · Answer 1 · answered Feb 23 '14 at 15:59

I would recommend to rewrite this code to avoid the deadlocks. In your case:

p1:

set transaction level L1
    delete from t1 where id=2
    select * from t1 where id=3

p2:

set transaction level L2
    select * from t1 where id=2
    delete from t1 where id=3

Best Isolation Level to avoid deadlocks in Sql Server

1 Answers1