Sybase lock mechanism and Deadlock Avoidance

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

Locking is a concurrency control mechanism that ensures data consistency between the same transaction and different transactions. In a multi-user environment, the lock function is required because several users may use the same data at the same time.

Sybase lock category

Based on the lock nature, it can be divided into shared locks and exclusive locks. When reading information in a database transaction, a shared lock is added to the database. An exclusive lock is added when information is modified.

Based on the lock granularity, it can be divided into row locks, page locks, table locks, and so on.

Sybase isolation level

Sybase has four isolation levels: 0, 1, and 3.

0 read uncommitted, allow the transaction to read uncommitted data changes, exclusive locks are released immediately after the write operation on the database, and will not hold the transaction commit or rollback.
1. Read committed data and only allow the transaction to read committed data changes. exclusive locks are held for transaction commit or rollback, but the shared locks are released immediately after the data is loaded into the memory.
2. the read-only transaction can repeat the same query. No rows read by the transaction will be updated or deleted. The exclusive lock and shared lock will be held until the transaction ends, the query result set cannot be deleted or modified, but can be inserted.
3. serializable read transactions can repeat the same query and get identical results. You cannot insert any rows that will appear in the result set. The exclusive and shared locks will be held until the transaction ends. You cannot add, delete, or modify the query result set.

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

The Sybase Database is usually set to 1 for the isolation sector. It is worth noting that was is used to connect to the database through JDBC, and the isolation level is often increased to 2. When compiling with a E-SQL, the isolation level is typically elevated to 3.

Sybase deadlock

A deadlock occurs in the Sybase Database, that is, all transactions in the deadlock hold the lock, but are waiting for other locks to form a ring, resulting in a deadlock.

The simplest deadlock condition is transaction T1 and T2. the execution order is the opposite, which may cause deadlocks. The situation is as follows:

Execution sequence	T1	T2
1	Exclusive lock
2		Exclusive lock B
3	Exclusive lock B
4		Exclusive lock

At this time, transaction T1 will hold exclusive lock A, while waiting for exclusive lock B, transaction T2 holds exclusive lock B, waiting for exclusive lock. This causes T1 to wait for T2 to release exclusive lock B, T2 to wait for T1 to release exclusive lock a to form a deadlock loop.

When multiple transactions encounter deadlocks, the environment is larger than the two deadlocks, but the nodes on the ring are more. Its essence is still to form a wait ring.

Impact of isolation level on deadlocks

The isolation level also has a great impact on the lock, for example,

Scenario 1,

Execution sequence	T1	T2
1	Exclusive lock
2		Exclusive lock B
3	Shared lock B
4		Shared lock

When the isolation level is 0, no deadlock will occur. A deadlock occurs when the isolated sectors are 1, 2, and 3.

Case 2,

Execution sequence	T1	T2
1	Shared lock
2		Shared lock B
3	Exclusive lock B
4		Exclusive lock

When the isolation level is 0, 1, no deadlock occurs. When the isolated sector is, a deadlock occurs.

Case 3,

This is a deadlock problem recently found in the system. The program imports data from files to databases. Each time a record is imported, it first tries to import a record in the update method. When the record is found to be empty, then, the record is changed to be imported into the database using insert.

At the same time, the import process is completed by multiple processes. Each process imports a file and multiple processes work at the same time. However, when the program runs, the import of multiple processes has a deadlock.

By monitoring Sybase logs, it is found that the deadlock occurs during insert and the next-key lock occurs. Sybase logs are saved in the installation directory, for example, the installation directory is/Sybase/ASE-12_5, and the log file is/Sybase/ASE-12_5/install/db_name.log.

By checking the database isolation level, it is 1 and no exception is found.

Then, add the query database isolation level statement to the program to check whether the program is running at the isolation level?

After check, the isolation level is 3. That is to say, you cannot insert any rows that will appear in the result set in the transaction. The following analyzes the cause of the deadlock.

When two processes insert records to the same gap at the same time, each transaction may consist of two operations.

1. Update

2. Insert. If the update result set is empty, it is converted to insert.

During its execution, the two processes may encounter the following running conditions:

Execution sequence	T1	T2
1	Shared lock
2		Shared lock B
3	Exclusive lock B
4		Exclusive lock

For example, the database currently only has one record with the primary key of 5. At this time, t1 and t2 insert data with the primary key of 3 and 4 respectively. Because both transactions are running, T1, t2 will try to insert data before 5. First, it will generate a shared lock during update. Because the isolation level is 3, both transactions will fail during insertion. To solve this deadlock, you can explicitly set the isolation level to 1 in the program.

Sybase lock upgrade

Sybase also provides the lock upgrade function, such as upgrading a row lock to a page lock and upgrading a page lock to a row lock. You can set specific parameters.

For example, if 90% of rows on a page are locked, Sybase may upgrade these row locks to one page lock to lock the entire page. This is also an important cause of deadlock.

Sometimes, no deadlocks are generated based on the judgment.

Execution sequence	T1	T2
1	Row-level exclusive lock
2		Row-level exclusive lock B
3	Row-level exclusive lock C
4		Row-level exclusive lock d

In the above case, if there is no lock upgrade mechanism, no deadlock will occur in any case. However, when the lock upgrade mechanism is available, t1 may upgrade row-Level Lock a to page lock Pa, and T2 will upgrade row lock B to page lock Pb, the row C to be accessed by T1 is in Pb of the page, and the D to be accessed by T2 is in PA. In this case, a lock ring is formed, forming a deadlock.

 

Summary

Databases are often used in software system implementation. The deadlock problem occurs when databases are used. When a deadlock occurs, you must first analyze the cause and locate the problem. This is also the most critical step.

There are many reasons for deadlocks, but the essence must be that multiple processes are waiting for each other, and each process holds a lock, thus forming a dependency ring, therefore, the most critical issue is to find the dependency, which transactions are involved, and which locks lead to deadlocks. The deadlock problem will be solved as long as these locks are determined.