Avoiding deadlocks





In client/server mode 1С:Enterprise locks individual database table records, while in file mode it locks entire tables. It means that in client/server mode 1C:Enterprise only locks the records required by the current transaction. This improves the performance of concurrent user work (such as document input) and thus improves the performance of the entire system.

However, using the advantages of this lock mode calls for more focused design of the applied code that accesses transaction data (for instance, document posting procedures). Otherwise you can get the opposite effect: performance degradation. The degradation occurs mostly due to deadlocks during concurrent access to data from multiple 1C:Enterprise sessions.

A deadlock is a situation where transactions lock each other in such a way that their further execution is not possible. Neither of the transactions that participate in the deadlock can release the locked resources before it applies locks to all the resources that are required for its completion. Each of the transactions cannot get the resources locked by the other one. In this case you get a vicious circle. Of course a deadlock can include any number of transactions and objects. It is impossible to resolve such a situation without external intervention, and if you don't make dedicated efforts, transactions will be idle for a very long time. You can resolve this situation only by canceling one of the transactions and MS SQL Server has the tools to define such dead-end situations and to eliminate them.

While choosing a transaction to be rolled back in order to eliminate a deadlock, Microsoft SQL Server uses the following rules:

  • The number or records to be rolled back (retrieved from the transaction log)
  • The number of operations required to roll back the transaction. The server attempts to avoid rolling back transactions that are virtually finished.

Microsoft SQL Server does not retry the transaction that is rolled back, it displays an error message instead. If necessary, you can implement handling for this scenario in the client application, which might include retrying the transaction that was rolled back.

However, this approach has the following disadvantages:

  • The load on the transaction log increases, as every unsuccessful attempt adds log records (these include rollback records).
  • Deadlocks are not detected immediately. It reduces the overall performance of the system as the transactions need to wait until Microsoft SQL Server rolls back one of the transactions that participate in the deadlock.
  • Such approach generally impacts the system scalability, as increasing the load increases the number of deadlocks, and the dependency between the load and the number of deadlocks is nonlinear. Consequently, the number of waiting transactions will grow, which can decrease the performance.

In order to avoid this scenario you need to minimize the probability of deadlocks appearing.

Let's review two typical cases that can lead to deadlocks:

Resource access order

The following sequence leads to a deadlock:

  1. Transaction T1 writes the document register records, updates records in the balance table of register R1, and sets an exclusive lock to these records.
  2. Transaction T2 writes the document register records, updates records in the balance table of register R2, and sets an exclusive lock to these records.
  3. Transaction T1 attempts to write document register records and update the records in the balance table of register R2. It attempts to set an exclusive lock to these records and fails because transaction T2 set an exclusive lock to these records earlier. Transaction T1 has to wait until transaction T2 is completed and releases the lock.
  4. Transaction T2 attempts to write document register records and update the record in the balance table of register R1. It attempts to set an exclusive lock to these records and fails because transaction T1 set an exclusive lock to these records earlier. Transaction T2 has to wait until transaction T1 is completed and releases the lock.

When Microsoft SQL Server detects this, it forces a rollback of one of the transactions. The other transaction is completed successfully.

To avoid this, you need to implement standard resource access sequence for all transactions: first write register R1, then write register R2. In this case transaction T2 cannot lock the resources that are required to complete transaction T1, and you can avoid a deadlock because transaction T2 waits for completion of transaction T1. If you choose the automatic mode of writing document register records during the posting (the register record set generated during the posting is written automatically unless it is written explicitly using the Write() method), the system enforces the standard resource access sequence. If the register records are written explicitly (in the posting procedure), implementing the standard resource access sequence in all document posting procedures in order to avoid deadlocks when documents are written and posted from different 1C:Enterprise sessions simultaneously is your responsibility.

Lock escalation in operation sequences (such as reading and subsequent writing)

The following sequence leads to a deadlock:

  1. Transaction T1 runs a query to the balance table of register R1 and sets a shared lock to the records that are read in this transaction.
  2. Transaction T2 runs a query to the balance table of register R1 and sets a shared lock to the records read in this transaction. As the shared locks are compatible, the transaction is allowed to do it.
  3. Transaction T1 writes document register records and attempts to update the record in the balance table of register R1. This requires applying an exclusive lock to these records. The transaction cannot do it because transaction T2 set a shared lock to these records, which is incompatible with an exclusive lock. Transaction T1 has to wait until transaction T2 is completed and releases the lock.
  4. Transaction T2 writes document register records and attempts to update the record in the balance table of register R1. This requires applying an exclusive lock to these records. The transaction cannot do it because transaction T1 set a shared lock to these records, which is incompatible with an exclusive lock. Transaction T2 has to wait until transaction T1 is completed and releases the lock.

This process cannot end unless Microsoft SQL Server forces a rollback of one of the transactions.

You can avoid this problem if you use FOR UPDATE operator in the query to the balance table of register R1. In this case a higher-level lock (an update lock) is set to the read records. This lock type is compatible with a shared lock, and therefore transactions that read locked data can access this data without restrictions. And updating this data is not a problem, as update locks are not compatible with each other, and consequently, other transactions that read this data for update (and of course request this data with update locks) will wait until this data changes without interfering with other sessions.


Comments
0
Add comment