DeadLock SQL server withTransactionScope ReadCommited
I encounter problems of deadlock on my business layer during buying reservation scenario (sequential call to method to read, insert and update reservation data).
Each method of service partially fills up reservation data. Some methods are embedded in a TransactionsScope in ReadCommited mode. Scope transactions are not chained. SQL statements are sent via ADO.NET without using SqlTransaction, on a SQL server 2012 database Normally, each Insert / Update / Delete order made on the tables in these services is restricted by a reservation identifier (which means that two parallel reservation paths can not change the same data).
In the facts : I run a piece of code that runs 50 full reservation course in parralel (Parralel.Invoke). Notice that in a reservation course, everything is sequential, nothing is launched in parallel. After a short time I receive a deadlock. According to the launch of this piece of code, the deadlock does not always occur (rarely) in the same sql request I plugged in the sql server profiler which tells me many graphs of errors but it is quite complicated to understand In an example of deadlock I have for example: - a select query participating in the deadlock, the select returns the last x reservations without any particular filters (thus potentially "blocked" if an uncommitted update order exists on one of these reservations) - a select request participating in the deadlock, the select returns the last x reservations made by a customer (theoretically unique by reservation route, so must not cross the rest) - a "victim" update request where the deadlock is launch, which updated a field in the reservation table on a specific reservation. I would have liked to receive the parameters sent in the query to make sure if the data received or updated crossed but I can not find it in the profiler (I filtered by all that concerns the locks)
I also see that if I have an update command on a row in a non-complete transaction, a select on the table that would bring the row back to timeout (if I leave a breakpoint before the transaction.complete of course) . Is this normal? I understood that yes but it was not hyper clear. Note that my transactions are not hyperlong, of the order of 2 3 seconds max. Because actually I can have in parallel an update order on a row and another thread that makes a select on this row. But I expect in this case that the select remains in wait and as soon as the transaction of the update is commited ca returns the result.
In short I do not have too many ideas, I take any advices,
thanks !
Julien
链接地址: http://www.djcxy.com/p/32868.html上一篇: 正确的方式采取排他锁