On Sybase database deadlock from shallow to deep

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/can apply for lock

Existing lock S U X

S∨∨x

U∨xx

xxxx

Sybase SQL Server has three types of blocking: Exclusive locks (exclusive lock, abbreviated as x locks), shared locks (share lock, abbreviation s locks), update locks (update lock, short 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.