Six methods to resolve deadlocks in applications

Some of the following methods can be used in an application to avoid deadlocks:

(1) Reasonable arrangement of table access order.

(2) As far as possible in the transaction to avoid user intervention, try to make a transaction less task, keep the transaction short and in a batch.

(3) Data access time domain Discretization method, the data access time domain Discretization method is refers to in the client/server structure, takes various control means to control to the database or the database object access time period. Mainly through the following ways: reasonable arrangement of the implementation of the background transaction time, the use of workflow for the unified management of the background affairs. Workflow in the management of tasks, on the one hand to limit the number of threads of the same type of task (often limited to 1), to prevent excessive consumption of resources; On the other hand, reasonable arrangement of different tasks to carry out timing, time, try to avoid multiple background tasks at the same time, in addition, to avoid in the foreground transaction peak time running background tasks.

(4) Data storage space discretization method. The data storage space discretization method is to take various means to scatter the data logically in one table to some discrete space, in order to improve the access performance of the table. Mainly through the following methods: First, the large table by row or column decomposition into a number of small tables; Second, decompose according to different user groups.

(5) Use the lowest possible isolation level. The level of isolation is the degree to which multiuser transactions are isolated to ensure the integrity and consistency of database data, and SQL92 defines 4 levels of isolation: uncommitted read, commit read, repeatable read, and serializable. If you choose too high a level of isolation, such as serializable, although the system can achieve better isolation and more to ensure the integrity and consistency of data, but the conflict between transactions and deadlock opportunities greatly increased, greatly affecting the system performance.

(6) Using a bound connection, a bound connection allows two or more transaction connections to share transactions and locks, and any one transaction connection is requested to request a lock, as is the case for another transaction to request a lock, so that these transactions can be allowed to share data without locking conflicts.