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.