Sybase lock mechanism and deadlock avoidance

In the database implementation, the locking mechanism is used to control the concurrent access of the database to ensure the correctness of database access. By definition:

Locking is a concurrency control mechanism that ensures that data is consistent across transactions in the same transaction. In a multiuser environment, locking is required because several users may be using the same data at the same time. Sybase Lock Classification

According to the nature of the lock can be divided into shared locks, exclusive locks. When you read information in a database transaction, a shared lock is added to the database. An exclusive lock is added when the information is modified.

According to the size of the lock, can be divided into row locks, page locks, table locks and so on. Sybase Isolation Level

Sybase is divided into 0,1,2,3 four isolation levels.

0 Reads uncommitted, allows transactions to read uncommitted data changes, exclusive locks are released immediately after the database is written, and are not held to a transaction commit or rollback.
1 Read committed, allowing only transactions to read committed data changes, exclusive locks held to transaction commits or rolled back, but shared locks are released immediately after loading data to memory.
2 a repeatable read transaction repeats the same query, any rows read by the transaction are not updated or deleted, the exclusive and shared locks are held to the end of the transaction, and the query result set cannot be deleted and modified, but can be inserted.
3 Serializable read transactions can repeat the same query and get exactly the same result. You cannot insert any rows that will appear in the result set, and the exclusive and shared locks are held to the end of the transaction, and the query result set cannot be deleted.

You can use the SELECT @ @isolation statement to view the isolation level of the database.

Sybase databases are typically set to 1 in isolation, and it is noteworthy that using was to connect to the database through JDBC often increases the isolation level to 2. When compiling with e-sql, the isolation level is typically raised to 3. Sybase deadlock

Sybase database deadlock, that is, the transaction in the deadlock, both hold locks, but are waiting for other locks, thus forming a loop, resulting in a deadlock.

In the simplest case of deadlock, transaction t1,t2, in reverse order, can result in deadlock, as follows:

Order of execution	T1	T2
1	Exclusive lock A
2		Exclusive lock B
3	Exclusive lock B
4		Exclusive lock A

At this time, a transaction T1 holding exclusive lock A, while waiting for exclusive lock B, transaction T2 holding exclusive lock B, waiting for exclusive lock A. This is caused by T1 waiting for T2 to release exclusive locks b,t2 waiting for T1 to release exclusive lock A, forming a deadlock ring.

When multiple transactions are deadlocked, the situation is larger than the two transaction deadlocks, and there are more nodes on the ring. Its essence is still to form a waiting ring. The impact of isolation levels on deadlocks

The isolation level also has a significant impact on locking, for example,

Situation one,

Order of execution	T1	T2
1	Exclusive lock A
2		Exclusive lock B
3	Shared lock B
4		Shared lock A

When the isolation level is 0 o'clock, no deadlock occurs. Deadlocks occur when the isolation bounds are 1,2,3.

Situation II,

Order of execution	T1	T2
1	Shared lock A
2		Shared lock B
3	Exclusive lock B