0

The current situation: We develop a program to manage multiple e-Commerce Shops. Editing an order is a task that includes many tables and therefore it's neccessary to use transactions. If the last query fails, all the previous changes must be reverted.

We have a function to alter orders into invoices. This function may alter multiple orders at once. Each alter has it's own transaction and they are processed one-by-one.

Now the problem: If 2 users use this function at the same time - on different orders - they run into a Deadlock...

DBMS: SQL Server 2008 R2 Connection Components: UniDAC v4.5.9 - TUniConnection

One solution I tried is, setting the LockMode to lmPessimistic. This reduces the Deadlocks, but they still occur...

UPDATE:

The problem was solved by using "READ_COMMITTED_SNAPSHOT" as the database Isolation Mode. This works fine for us, but won't work for all Deadlock Situations and has some sideeffects!

SvenFinke
  • 1,254
  • 3
  • 15
  • 30
  • You didn't mention your DBMS or explain in detail what components you are using to communicate with the database. Short of knowing more details and being able to recommend a simple fix in XE2, I recommend that you Google "troubleshooting deadlocks sql server 2008" (e.g., for SQL Server 2008). Resolving deadlocks has has been discussed widely and varies by DBMS and version. – James L. Jan 10 '13 at 20:36
  • We use SQL Server 2008 R2 and UniDac to connect to the database. But I guess I found a solution... The "Deadlocks" were not caused by 2 users blocking each other, but one user blocking another that tries to read/write into a talbe. The problem was solved by using "READ_COMMITTED_SNAPSHOT" as the database Isolation Mode. – SvenFinke Jan 11 '13 at 08:16

0 Answers0