CODEWITHSUNDEEP
c#asp.netsqlsql-server-2008
kidgu
kidgu

Reputation: 413

How to avoid sql deadlock?

I am using MS-SQL 2008 and for coding am using C# in a web project.

Currently, I got stuck with sql deadlock.

I have 2 transactions, one of two is a long transaction.

When both transactions run simultaneously, deadlock occurs and short transaction will be chose to be killed automatically.

My Current solution, I am catching the exception of deadlock and redo the short transaction, it run well, just take some time.

But, I wonder that if there is any solution to avoid deadlock from the root cause?

Upvotes: 2

Views: 10712

Answers (1)

gbn
gbn

Reputation: 432190

No, you can't avoid deadlocks entirely, only mitigate them.

  • Using RCSI isolation mode. This is basically MVCC, but you have to beware that SQL Server will still use the ANSI ACID modes on occasion (DBA.SE answer). This mode stops writers blocking readers but will not help with writer-writer mutual blocks.

  • Client retry logic. The classic way

  • Try to do table updates in the same order eg T1 -> T2 always

  • Avoid long transactions: is the client round tripping a lot and keeping the transaction open for example. You can mitigate this with stored procedures, better indexes or simply tune the query

Upvotes: 6

Related Questions