How to avoid sql deadlock?

Question

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

I am currently stuck in sql deadlock.

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

When both transactions are performed simultaneously, a deadlock occurs and the short transaction will be automatically deleted.

My current solution, I caught the deadlock exception and redid the short transaction, it works well, just take the time.

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

+2

c # sql asp.net sql-server-2008

kidgu Jun 11 '13 at 7:55

source share

1 answer

gbn · Accepted Answer · 2013-06-11T08:25:07+0000

No, you cannot completely eliminate the locks, just reduce them.

Using RCSI mode. This is basically MVCC , but you should be careful that SQL Server will still use ACSI ACID modes (DBA.SE response). This mode stops writers blocking readers, but does not help with the mutual blocks of a writer-writer.
The logic of client repetition. Classic way
Try updating the tables in the same order, for example T1 -> T2 always
Avoid lengthy transactions: the client round disconnects a lot and, for example, closes the transaction. You can reduce this with stored procedures, improve indexes, or simply tune the query