Cause analysis and solution for deadlock of database table __ Database

In the database application system of online transaction processing (OLTP), the concurrency of multi-user and multi-task is one of the most important technical indexes. In order to improve concurrency, most of the RDBMS are now using lock technology. However, due to the complexity of the real environment, the use of lock technology has inevitably produced a deadlock problem. Therefore, how to use lock technology reasonably and effectively, minimizing deadlock is the key to develop online transaction processing system.
First, the cause of the deadlock
In the online transaction processing system, there are two main reasons for the crash. On the one hand, because of multi-user, multitasking concurrency and transactional integrity requirements, when multiple transactions are simultaneously accessed by multiple resources, if the two sides have locked a portion of the resource but also need each other's locked resources, it is not possible to fully obtain the required resources within a limited time, will be in an infinite waiting state, Thus causing its deadlock on resource requirements.
On the other hand, the database system has different methods to implement the locking mechanism, and the database systems can also produce special deadlock situations. For example, in Sybase SQL Server 11, a lock method with a minimum lock of 2K per page is not a row-level lock. If a table has a small number of records and the length of the record is short (that is, the record density is high, such as the System Configuration table or system parameter table in the application system is such a table), the frequency of access is high, it is easy to create a deadlock on the page.

Two, prone to deadlock in several cases are as follows:
1, different stored procedures, triggers, dynamic SQL statements in a different order at the same time access to more than one table;
2. Add frequently recorded tables during the exchange, but use non-clustered indexes on the table (non-clustered);
3, the table of less records, and a single record is shorter, the frequency of access is higher;
4, the whole table is accessed high frequency (such as code comparison of the query, etc.).

Three or more deadlock scenarios corresponding to the solution

1, in the system implementation should be specified in all stored procedures, triggers, dynamic SQL statement segments, the operation of multiple tables always use the same order. Such as:

There are two stored procedures proc1, PROC2, all of which require access to three tables Zltab, Z2tab, and Z3tab, and if Proc1 accesses in the order of Zltab, Z2tab, and Z3tab, PROC2 should also access the three tables in the order listed above.

2, to add records frequently during the exchange of tables, using the cluster index (clustered) to reduce the number of users to add records to the table on the last page, the end of the table to create hotspots, resulting in deadlock. This type of table is the current account of the flow table, which is characterized by the need to append a large number of records at the end of the table, and the added records do not do or less delete operations.

3, the number of records in a single table is not too much, and during the exchange of select or updata more frequent tables can be used to set the maximum row per page, reduce the data stored in the table density, simulate row-level locks, reduce the occurrence of deadlock on the table. This kind of table is the information is multifarious and the record strip number is few table. Such as:

System Configuration table or system parameter table. Add the following statement when defining the table: with max_rows_per_page=1

4. In stored procedures, triggers, dynamic SQL statement segments, if a select operation on some of the entire table is more frequent, you may have deadlocks with other users accessing the table on the table. Non-critical statements that check whether an account exists, but the field being checked will not be updated during inspection, can be resolved by using the AT isolation READ UNCOMMITTED clause in the SELECT command. This method actually reduces the lock level of the SELECT statement to the entire table and increases the concurrency of other users ' operations on the table. The effect of this method is especially significant when the system is running at high load.

such as: SELECT * FROM titles at isolation READ UNCOMMITTED                order number of serial numbers Builder field, you can perform the Updata Flow Number field +1, and then execute the Select Fetch flow number method.