Sybase database Deadlock Countermeasures

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. This article describes a method for dealing with this deadlock. Sybase blockade principle

Data sharing and data consistency are irreconcilable contradictions, and concurrency control is necessary to achieve data sharing and data coherence. The task of concurrency control is to avoid data inconsistencies caused by shared conflicts. The method of Sybase SQL Server concurrency control is the locking mechanism (locking).

Type of lock

The lock that can be applied

Existing lock S U X s∨∨xu∨xxxxxxsybase SQL Server has three types of blockade: Exclusive lock (exclusive lock, abbreviated as X Lock), shared lock (share lock, abbreviation S locks), update lock (Updates lock, Referred to as U-Lock). The compatibility matrix of these three types of locks is as follows:

X: Indicates incompatibility. ∨: Represents compatibility.

Sybase SQL Server 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.

The strength of the lock

SQL Server has two-level locks: page locks and table locks. Typically, page locks are less restrictive (or smaller) than table locks. Page locks lock all the rows on this page, while table locks lock the entire table. To reduce data contention and improve concurrency among users, SQL Server tries to use page locks as much as possible.

When SQL Server decides 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. Once a statement accumulates a page lock that exceeds the lock elevation threshold, SQL Server 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 SQL Server automatically uses table locks.

Status of the lock

There are three states of SQL Server lock:

1 Intent Lock (intend)-is a table-level lock that represents the intention to obtain an S or X lock on a data page. Intent locks prevent other transactions from acquiring exclusive locks on the table of the data page.

2) blocking (blocking, denoted Blk)-it indicates the current state of the lock process, and a lock with a blk suffix indicates that the process is currently blocking another process that needs to be locked, and only if the process completes, other processes are available.

3 demand Lock (demand)-Indicates that the process is attempting to get an exclusive lock at this time. It prevents excessive s locks on this table or page, and she says a transaction is the next one to lock the table and the page.

A requirement lock is an internal process, so it is not visible with sp_lock.

Dead Lock Deadlock

Simply put, there are two user processes, each of which has a lock on a separate page or table, and 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.

SQL Server checks for deadlocks and terminates the minimum amount of CPU time in a transaction (that is, the last user who enters). SQL Server rolls back the user's transaction, 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 1025 indicates that the user's transaction was terminated and rolled back because of a deadlock. The application must start the transaction again.

Find the reason for the deadlock

Since the management information system deadlock for a long time because of our submission or improper submission, then we can modify the program to prevent deadlock. Locating a deadlock error occurs mainly through the following three steps:

1 When the deadlock occurs, use Sp_who,sp_lock to obtain the activity of the process and the lock.

2) combined with the library table sysobjects and the corresponding operator information table to detect the locked library table and lock someone else's operator.

3 According to the Locked library table and the operator's post, you can estimate the program about error. 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.

Use sp_who to get information about blocked processes

The system process sp_who the report of the system process. If the user's command is being blocked by a lock held by another process:

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.

Fid spid status loginame origname blk dbname cmd

0 1 Lock Sleep lm LM-Qjyd SELECT

0 2 sleeping null NULL 0 master network HANDLER

0 3 sleeping null NULL 0 master network HANDLER

......

Navigating the lock with sp_lock

To get a report on the locks maintained on the current SQL Server, use the system procedure Sp_lock [Spid1[,spid2]],spid1,spid2 is the SQL Server process ID number in table master.dbo.sysprocesses, with SP _who can be locked with a locked spid:

The LockType column shows the type of lock and the granularity of the blockade, and some of the lock suffixes also have blk indicating the state of the lock. The prefix indicates the type of lock: sh-shared 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_id column displays the ID number of the table and, combined with sysobjects, detects the blocked table name.

The screen appears after the process is executed

Fid Spid locktype table_id page row dbname Class context

0 1 sh_intent 678293476 0 0 qjyd Non Cursor lockfam dur

0 1 Sh_page 678293476 31764 0 qjyd Non Cursor Lock

0 ex_intent 9767092 0 0 qjyd Non Cursor lockfam dur

......

Locate Error Place

According to the results of the sp_who and sp_lock commands, combine the sysobjects and the corresponding operator information table. The operator and the library table that operates on the deadlock can know about the error in the application and ask the operator what to do in the deadlock to further authenticate. Finally find the program and fix it.