Analyze the deadlock problem of different types of databases in detail

The forum has a lot of discussion about deadlock, in theory, R.ramakrishnan's "Database Management Systems" also has a very incisive exposition. As far as actual development is concerned, different database environments have different processing methods, different architectures can lead to different results, and the following is a brief introduction to the deadlock problem of different types of databases.

Modern DBMS or middleware, such as 1.MSSQL, DB2, and Oracle, can help us automatically resolve most deadlocks, while the rest of the deadlock is handled manually by DBAs at the database control side. In the case of an application, it is not necessary to consider the deadlock problem too much at the source code level.

2. The occurrence of deadlocks has a significant impact on the performance and throughput of the system, but deadlock is unavoidable as long as there is a large-scale concurrent access to shared data resources.

Theoretically, the best way to prevent deadlocks is to set a priority for each transaction while ensuring that lower-priority transactions do not have to wait for higher priority transactions to release shared resources, and in turn ensure that higher-priority transactions immediately acquire the appropriate resources. If a developer cannot discriminate the priority of a transaction, consider giving it a timestamp at the start of each transaction, determining the priority of the transaction based on the time stamp, similar to the FIFO queue. In this respect, the modern DBMS provides the appropriate language support. However, assuming that high priority transactions cannot commit or rollback due to a network failure, do other low-level transactions wait or be discarded? Or if the update operation in one transaction imposes multiple table-level locks and takes a lot of time, do you want to keep high priority even if the transaction is logically unreasonable?

The competition for scarce resources is normal in any situation, the causal relationship between deadlock gives me a hint, that is, can not blindly rely on the DBMS and DBA after the deadlock to resolve the deadlock problem, it must have a negative impact on the user experience. Developers need to be more involved in the design process to judge possible concurrent access issues. These issues may include:

1. Shorten the business as much as possible. When multiple long-running transactions are executed concurrently in the same db, the probability of a deadlock occurs is greater. The longer the transaction runs, the longer it holds the exclusive lock or update lock, blocking other activities and potentially causing deadlocks. Keep transactions in a batch, you can minimize the network traffic round-trip to the transaction, reduce the possible delay in completing the transaction, and release the lock. At the same time, the query update operation involving multiple tables, if more time-consuming, try not to put in a transaction within the processing, can be segmented and split. If not, try to make it run at a smaller amount of time (such as midnight or lunchtime).

2. Access data objects in the same order as possible. If all concurrent transactions access the object in the same order, the likelihood of a deadlock is reduced.

3. Avoid writing transactions that contain user interaction. Because the speed of the batch processing without user interaction is much faster than the user's manual response to the query, the transaction will hang if the user does not have timely feedback. This will severely degrade system throughput because any locks held by a transaction are released only when the transaction commits or rolls back. Even if a deadlock is not present, other transactions that access the same resource are blocked waiting for the transaction to complete.

4. Use low isolation levels. Determines whether a transaction can run at a lower isolation level. Performing a commit read allows the transaction to read data that has been read (unmodified) by another transaction without waiting for the first transaction to complete. The use of lower isolation levels (for example, commit reading) without high isolation levels (such as serializable reading) reduces the time to hold shared locks, thereby reducing lock contention.

5. Consider the optimization of the architecture and code reconfiguration to improve the overall operating efficiency of the system. For example, try not to use an inefficient computing model like EJB, or to place complex business in a compiled stored procedure.

6. Timing of the submission of the transaction by program control. If you retrieve 100,000 records at a time but change only 100 of them, you can perform 100 updates by code. Or commit in a segmented way, that is, all modifications are committed using multiple transactions, but this would make the transaction incomplete and should be used as appropriate.

7. Separate the frequently updated database from the query database. Periodically, the unchanged data is imported into the query database, so that the query and update can be separated and the deadlock probability reduced.

8. In the database schema design, note the integrity of the foreign key reference, and the foreign key index. If the primary key of the parent table is updated, the child table is locked because there is no index on the foreign key, and if a row in the parent table is deleted, the entire child table is locked.