Discussion on the principle and solution of Sybase blockade

The concept of the first part of the lock

I. Type of lock
ASE has three types of blockade: Exclusive lock (exclusive lock), abbreviated as x Lock), shared lock (share lock, abbreviation s), update lock (update lock, short U lock). The compatibility matrix of these three types of locks is as follows:

X: Indicates incompatibility. ∨: Represents compatibility. ASE automatically determines the lock type. In general, the read (select) operation uses the S lock, the write (Update,insert and delete) operation uses the X lock. The U lock is built at the page level and is obtained at the start of an update operation, and the U lock is upgraded to an X lock when you want to modify the pages.

　　Second, the size of the lock
ASE supports three kinds of lock granularity: Table lock, page lock (allpage lock-lock Data page and index page, Datapage lock-lock data page), and row lock (Datarow lock-lock data row only). Generally, row locks are less restrictive (or smaller) than page locks or table locks. A row lock locks on only one data row, and the page lock locks all the rows on this page, while table locks lock the entire table. To reduce data contention and improve concurrency among users, ASE attempts to use row locks as much as possible. When ASE determines that a statement will access most pages of the entire table or table, it uses a table lock to provide a more efficient lock. The locking policy is directly constrained by the query scheme, which performs a table scan or requests a table lock if no index is available for the UPDATE or DELETE statement. If an update or DELETE statement uses an index, it starts by requesting a page lock, which, if it affects most rows, requests a table lock. When a statement accumulates a page lock that exceeds the lock elevation threshold, ASE tries to assign a table lock to the object. If it succeeds, the page lock is no longer necessary and is therefore released. Table locks also provide a way to avoid lock conflicts at the page layer. For some commands ASE automatically uses table locks.

　　Iii. Deadlock (deadlock)
Simply put, there are two user processes, each of which has a lock on a separate page or table. A deadlock occurs when each process wants to request incompatible locks on the page or table of the other process. In this case, the first process waits for another process to release the lock, but the other process will not release its own lock until the object of the first process is released.

ASE checks for deadlocks and terminates the minimum amount of CPU time in the transaction (that is, the last user to enter). ASE rolls back the transaction for the user, notifies the application with this deadlock behavior with message number 1205, and then allows other user processes to continue.

In a multiuser scenario, each user's application should check for 1205th messages for each transaction that modifies data to determine whether a deadlock is possible. Message number 1205 indicates that the user's transaction was terminated and rolled back because of a deadlock. The application must start the transaction again.

The second part finds the cause of the deadlock

The deadlock has an important effect on the performance and throughput of the system, and it is found that the deadlock of MIS is mainly because two or more threads (login) preempt the same table data resource. Causing a long time to preempt the same resource is not because the transaction we need to deal with is too complex and too long, often because we forget to commit when the front-end application operates on the database. Since the management information system locks up for a long time because we forgot to submit or improperly submitted, we can modify the program to prevent deadlock. Locating a deadlock error occurs mainly through the following three steps:
① in the event of deadlock, use Sp_who,sp_lock to obtain process and lock activity.
The ② combines the library table sysobjects with the corresponding operator information table to detect the locked library table and to lock someone else's operator.
③ according to the Locked library table and the operator's post, you can estimate the error of the program. Ask the operator to perform a specific operation at the time of the deadlock to completely locate the error location. Finally find the program and modify it.

Here are some examples of the commands applied:
Example 1, the sp_who is used to obtain information about the process of a system that is hindered by the process, sp_who gives the report of the systematic process. If the user's command is being blocked by a lock held by another process, the figure shows:

(Note: The Status column displays lock sleep.) The BLK column shows the process identity that holds the lock or these locks, that is, who is locked. The loginame column displays the login operator. With the corresponding operator information sheet, you can know who the operator is. )
Example 2, to browse the lock with Sp_lock2, to get a report on the lock maintained on the current SQL Server, the system procedure Sp_lock2 [Spid1[,spid2]],spid1,spid2 is Table master. Dbo.. The SQL Server process ID number in sysprocesses, with sp_who to obtain a locked and locked spid, which is displayed after the process is executed:

(Note: The LockType column shows the type of lock and the granularity of the blockade, and some locks have a suffix with blk indicating the state of the lock.) The prefix indicates the type of lock: sh-share lock, ex-lock or update lock, which indicates whether the lock is on the table (table or intent) or on the page. The suffix "blk" indicates that the process is blocking another process that needs to request a lock. As soon as the process of the obstacle is over, the other processes move forward. The "demand" suffix indicates that the current shared lock is released, and the process requests a mutex. The TABLE_NAME column displays the table table name. )

the third part ASE on the system tuning of the lock

First, adjust the deadlock detection interval
① shows the deadlock occurrence in the ASE log by setting the configuration parameter "Print deadlock information".
② If a deadlock phenomenon is rarely applied, you can use the parameter deadlock checking period to specify the wait time for the process before the deadlock check, which can delay the deadlock check and reduce the overhead.
The range of ③deadlock checking period is 0-2147483, the default is 500, and ASE is ready to check for deadlocks after at least 500ms of the process.

second, deadlock prevention
① to avoid the presence of deadlocks as much as possible in all transactions the tables are accessed in the same order.
Use stored procedures as much as possible to complete a transaction to ensure that the order of access to each table is consistent. Do not use the HOLDLOCK option unless you have the need for "repeatable reads."
② transactions should be reduced and should be submitted as soon as possible.
③ avoids the human input operation appearing in the transaction or exerting holdlock on the table at the same time.
④ avoids performing many data modification statements such as Insert,update,delete concurrently.

third, the database lock configuration principle
① the number of locks is not too small, if the lock is not enough, can be modified by the command sp_configure "amount of locks", NUM.
② If you need to save space, reduce maintenance, use all page lock mechanisms.
③ If you need to speed up and have enough space, use the data page lock mechanism.
④ when the lock competition over 15% is detected by monitoring, first modify the lock mechanism of the most heavily locked table and then set the data page lock as a data row lock. If more than a spiral lock is found, a separate named cache is established for the table and the named cache is partitioned.
Here are two useful examples to help readers get some system parameters for analyzing whether a parameter configuration needs to be changed.
Example 1: Perform Sp_sysmon "00:05:00" System sampling (report on data buffer statistics in 5 minutes, determine if I/o overhead is too large, etc.)
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
Sybase Adaptive Server Enterprise System Performance
===============================================================================
Server version:adaptive server ENTERPRISE/11. 9. 2. 6/1290/P/EBF 10487 ESD
Server Name:server is unnamed
Run Date:dec 27, 2005
Statistics cleared at:10:46:59
Statistics sampled at:10:51:59
Sample interval:00:05:00
(The following defaults, if you want to see more information, perform Sp_sysmon "00:05:00")
Example 2: Execute Sp_object_stats "00:05:00", 10,db1 (This command lists the first 10 tables that are highly competitive within 5 minutes)
Lock statistics for the "top" (or less) most contended objects:
Object name:db1 ... Table1 (dbid=7, objid=555305188, lockscheme=datarows):-
Row Locks Sh_row Up_row ex_row
---------- ---------- ---------- ----------
grants:969 0 50
Waits:3 0 0
deadlocks:0 0 0
wait-time:6790 MS 0 ms 0 ms
contention:0. 31% 0. 0% 0. 20g
Object name:db1 ... Table3 (dbid=7, objid=1927834080, lockscheme=allpages):-
Page Locks sh_page up_page ex_page
grants:273760 5880 324
Waits:16 0 0
deadlocks:0 0 0
wait-time:684 MS 0 ms 0 ms
contention:0. 1% 0. 0% 0. 20g
(The following defaults, if you want to see more information, perform sp_object_stats "00:05:00", 10,DB1)
It can be seen that table3 is a full page lock, table1 is data row lock, table competition in the acceptable range, there is no need to adjust the configuration.

Four, the promotion mechanism of database lock
㈠, lifting locks, several commands
In ①ase, all tables are placed in a hidden full page locking mechanism, available with commands
sp_configure "lock scheme", [allpages|datapages|datarows] View the type of lock. When a database is stored from a previous version of the server and reloaded, all tables are defined as full page-locked tables.
② When you create a new table, you can use the following syntactic format instead of this default value:
CREATE TABLE <tablename>...lock[allpages|datapages|datarows]
③ in order to change the lock type in one of the tables used, the following syntactic format can be used:
ALTER TABLE <tablename> Lock[allpages|datapages|datarows]
Several problems should be paid attention to when ㈡ and hoisting lock
Lock elevation is the same type of elevation, Share page lockàshare table lock, the prerequisite for lock elevation is that there can be no other type of lock on the table, such as a shared page lock on table A, when the number of shared page locks reaches the parameter "page lock promotion HWM" Value and no other locks on table A are promoted to table-level shared locks. Changing the lock mode in an existing table will result in the following three action consequences:
First, if a table is converted from full page locking to data locking only, or from locking data only to full page locking, the table is selected between the two types to allow for storage formatting changes. If this is a partitioned table, you must also assume that the necessary parallel levels and worker threads are already configured to execute. Second, the clustering index in the table must be recreated. Because we want to guarantee the data, this recreate can be done through "with Sorted_data" if you convert from full page locking to data locking only. However, parallel index creation is necessary when converting from a data-locking mechanism to a full-page locking method. (Note: If this is a partitioned table, then the number of parallel levels and worker threads must be configured to allow this change, otherwise this migration will fail). Finally, non-clustered indexes will be rebuilt.
Since these activities are related to the potential workload, it can be time-consuming to change from a full-page locking mechanism to only lock data or change from data locking to a full-page locking mechanism. To mark this point, there are several options: if possible, you should configure the use of parallel methods. This is required at least for the Haas (hash, hashed) creation method that performs a nonclustered index, but using partitioned tables and partitioning scans will make the system much more likely to improve if possible. After you choose to enter and create a clustered index, the task is set to Checkpoint (checkpointed). So if you have sufficient hardware resources, by allowing the checkpoint task to have more than 10 asynchronous I/O requests at any one point in time (the system defaults), tuning with DBCC can have beneficial effects. ("Maxwritedes", number) further serves as a way to reduce the cost of checkpoints by using a method of marking high phase-out levels in the associated buffer pool (cache pool), large amount of I/O operations, and allowing cleaning procedures (as well as housewives) Maintaining a particularly active state will increase the number of I/O operations for those checkpoints needing to refresh the "dirty" pages from the buffer pool, and hence the time spent on checkpoints can be significantly reduced to contribute. If the configuration is in advance, you can go to a parallel selection to use a pre-configured disk area. Therefore, setting the sp_configure "number of pre-allocated extent" to 16 will also have a significant positive effect on system performance.

v. Positioning error Place
According to the results of the sp_who and Sp_lock2 commands, combine the sysobjects and the corresponding operator's information sheet. The operator and the library table that operates on the deadlock can know about the error in the application and then execute DBCC TRACEON (3604) for further authentication. Finally find the program and fix it. There is a period of time, the author maintained a frequent deadlock in the database, see the database is not what large operations, CPU utilization is also within a reasonable range, what caused the database to deadlock frequently, the author used the following steps to the command, found the reason:
①select * FROM Master Syslogshold found that there was a process that was not released for more than 24 hours (note the SPID of the process), but what command the process executed cannot be judged. (The Syslogshold table records the oldest active transactions and queries each database for the oldest active transaction and its start time in the application.) )
②DBCC traceon (3604) opens the database switch and sets the output to the screen.
③DBCC SQLText (Process number) then we see an SQL statement that causes blocking and cannot be freed.
Through the above three steps, the author at that time to see causes blocking cannot release the SQL statement is only the ordinary SELECT statement, but to the same table repeatedly extracts the data, after the source program research discovers after the SELECT statement executes, does not commit the transaction the statement (namely commits), but this program is widely used, Causes the process blockage to increase unceasingly, eventually causes the interlock. After finding the root cause of the problem, timely tuning to the program, the deadlock phenomenon did not occur. It can also be seen that in the absence of large operations, when the process defines user_transtion process more than 60 minutes, you should check the application, adjust the unreasonable program or business process.

The fourth part summarizes

According to my personal experience of using the database for many years, I think the use of row-level locks should be fully considered for the application of high parallelism, which is essential to improve concurrency performance. Of course, there are two pros and cons to a transaction, the use of row-level locks, will also bring some of the corresponding drawbacks, such as the use of more locks, occupy more memory, in the use of row-level locks on the table frequent data deletion, insert operation over time will result in a large number of database fragmentation, database performance will decline, and affect the business. Therefore, in the use of row lock, page lock or table lock, according to the system hardware and use of a reasonable comprehensive judgment, so that the database performance to achieve optimal.