Shulou(Shulou.com)05/31 Report--

This article introduces you how to reduce deadlock to a minimum, the content is very detailed, interested friends can refer to, hope to be helpful to you.

Although deadlocks cannot be completely avoided, following specific coding conventions can minimize the chance of deadlocks. Reducing deadlocks to a minimum increases transaction throughput and reduces system overhead because there are only a few transactions:

Rollback to undo all work performed by the transaction.

Resubmitted by the application because it is rolled back during a deadlock.

The following methods help minimize deadlocks:

Access objects in the same order.

Avoid user interaction in transactions.

Keep the transaction short and in a batch.

Use a lower isolation level.

Use isolation levels based on row versioning.

Set the READ_COMMITTED_SNAPSHOT database option to ON so that committed read transactions use row versioning.

Use snapshot isolation.

Use binding to connect.

Access objects in the same order

If all concurrent transactions access objects in the same order, deadlocks are less likely to occur. For example, if two concurrent transactions first acquire a lock on the Supplier table and then acquire the lock on the Part table, the other transaction will be blocked on the Supplier table before one transaction completes. When the first transaction is committed or rolled back, the second transaction continues to execute so that deadlocks do not occur. Using stored procedures for all data modifications standardizes the access order of objects.

Avoid user interaction in transactions

Avoid writing transactions that involve user interaction, because batches without user intervention run much faster than when users have to respond to queries manually (such as responding to prompts for parameters requested by the application). For example, if a transaction is waiting for user input and the user goes to lunch or even goes home for the weekend, the user delays the completion of the transaction. This reduces the throughput of the system because any locks held by the transaction are not released until the transaction is committed or rolled back. Even if there is no deadlock, other transactions accessing the same resource are blocked before the resource-consuming transaction is completed.

Keep the transaction short and in a batch

Deadlocks usually occur when multiple long-running transactions are executed concurrently in the same database. The longer a transaction runs, the longer it will hold an exclusive or updated lock, blocking other activities and possibly causing a deadlock.

Keeping the transaction in a batch minimizes the amount of network traffic in the transaction and reduces the delay that may be encountered in completing the transaction and releasing the lock.

Use a lower isolation level

Determines whether the transaction can run at a lower isolation level. Implementing committed reads allows a transaction to read data that has been read (unmodified) by another transaction without waiting for the first transaction to complete. It takes less time to hold a shared lock using a lower isolation level (such as committed reads) than using a higher isolation level (such as serializable). This reduces lock contention.

Use isolation levels based on row versioning

If the READ_COMMITTED_SNAPSHOT database option is set to ON, transactions running under the committed read isolation level will use row versioning instead of shared locks during read operations.

Note: some applications rely on committed read-isolated locking and blocking behavior. For these applications, some changes must be made to enable this option.

Snapshot isolation also uses row versioning, which does not use shared locks during read operations. The ALLOW_SNAPSHOT_ISOLATION database option must be set to ON for transactions to run under snapshot isolation.

Implementing these isolation levels minimizes the possibility of deadlocks between read and write operations.

Use binding to connect

With bound connections, two or more connections opened by the same application can cooperate with each other. You can hold any lock acquired by a secondary connection like a lock acquired by a primary connection, and vice versa. So they don't block each other.

On how to reduce the deadlock to a minimum to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.