Search code examples
sqlsql-server-2005isolation-leveldatabase-deadlocks

Best Isolation Level to avoid deadlocks using an UPDATE sentence in Sql Server 2005

I need execute an update statement over an sql server table. This table is used by another process at the same time. Because that sometimes deadlocks occurs, which Isolation Level do you recommend to avoid or minimize these deadlocks?

Solution 
  • READ UNCOMMITTED

    But that allows the process to read the data before a transaction has committed, what is known as a dirty read. Further Reading

    You may prefer to turn on row versioning, the update creates a new version of the row and any other select statements use the old version until this one has committed. To do this turn on READ_COMMITTED_SNAPSHOT mode. There is more info here. There is an overhead involved maintaining the versions of the rows but it removes UPDATE/SELECT deadlocks.