Database Transactions and Locks (v)

Data | database


Deadlock Problem





deadlock is a very important topic. Deadlock is an unavoidable phenomenon during the use of transactions and locks. In both cases, deadlocks can occur. In the first case, when two transactions lock out two separate objects, each transaction requires a lock on an object that is locked by another transaction, so each transaction must wait for another transaction to release the lock, and a deadlock occurs. This deadlock is the most typical form of deadlock. With two transactions A and b at the same time, transaction A has two operations: lock the table part and request Access table supplier; transaction B also has two operations: locking the table supplier and requesting access to the table part. As a result, a deadlock occurs between transaction A and transaction B, and the schematic process is shown in Figure 10.














Figure 10 Deadlock schematic





deadlock in the second case, when in a database, there are a number of long-running transactions to perform parallel operations, when the Query Analyzer processing a very complex query such as the connection query, then because the order of processing can not be controlled, the deadlock phenomenon may occur.





When a deadlock occurs, the system can automatically detect it, and then end the deadlock by automatically canceling one of the transactions. In the two transactions in which deadlocks occur, their priority is determined by the length of the transaction time as a rule. A transaction with a long processing time has a higher priority, and a transaction with a shorter processing time has a lower priority level. In the event of a conflict, a high priority transaction is retained, and the lower priority transaction is canceled.





session-level and table-level locks





Generally, locks are provided automatically by the system. However, in some cases, the user can also customize the lock. This means that the user can customize session-level and table-level locks.





The customization of Session-level locks consists of two aspects, transaction isolation level and lock timeout limit. The transaction orphan level protects the specified transaction, which allows the isolation level to be set for all transactions in a session. When an orphaned level is set, the default locking behavior is specified for all statements in the session. Some of the following options are used when specifying transaction isolation levels. The Read Committed option instructs the system to use shared locks when reading data. Under this option, you cannot experience dirty reading, that is, you cannot see the data being modified. However, you can change the data before the transaction ends, so you can produce unreadable or illusory data. The READ uncommitted option instructs the system to use neither shared locks nor exclusive locks. Under this option, you can see the data that is being modified, and feel the dirty reading. This option is the least restrictive setting. The REPEATABLE read option directs the system to place locks on all data used by the query, preventing other users from modifying the data, but hallucinations can occur. The serializable option directs the system to place locks on the data until the transaction is completed before users can modify or insert data. This option is the most restrictive setting. In addition, the time that the transaction waits for access can be customized, which requires setting the lock timeout limit. When the lock timeout is set, the transaction is automatically canceled if the transaction waits longer than the lock timeout.





Custom table-level locks are the behavior of setting table-level locks by specifying one or more options for a table. In fact, customizing table-level locks is a way to optimize hiding. Optimization concealment is the ability to improve the system's discerning operations by attaching the relevant content options to the FROM clause. There are many options for customizing table-level locks. The Rowlock option directs the system to use row-level locks instead of page-level locks or table-level locks, which is the default option for the system. The Paglock option directs the system to use page-level locks. The TABLOCK option instructs the system to use a table-level lock instead of a finer row-level or page-level lock on the locked object, and uses a shared lock, allowing other transactions to read data from the table, but not the data in the table. The TABLOCKX option directs the system to use exclusive locks to prevent other transactions from reading or modifying any data in the table. The NOLOCK option instructs the system not to use locks, neither shared locks nor exclusive locks. In this case, you can experience dirty reading. The HOLDLOCK option directs the system to occupy a shared lock until the end of the transaction, rather than releasing it immediately if there are other lock requests. The Updlock option instructs the transaction to use modified page-level locks instead of shared locks when reading data in the table. This lock is occupied until the statement or the end of the transaction.





Conclusion





Network technology is the trend of the development of information technology. Multi-user, multi-transaction, scalability, replication, data Warehouse and so on are all to adapt to the network Technology database development direction. As a basic concept of database technology, transaction has the basic function in protecting the recoverability of database and multi-user and multi transaction. A transaction is the work of a unit, which may include a statement or 100 statements, all of which are either completed or canceled. Transactions also have an important role to play during database backup and recovery, using logs for transaction log backups, incremental backups, without having to perform a full backup of time, effort, and backup media every time. The lock is a means to realize the concurrent processing such as multi-user and multi transaction. There are a number of types and resources for locks. Locks are provided automatically by the system and can be customized by the user. In SQL Server 7.0, an obvious feature is the use of row-level locks. The use of row-level locks causes changes in the data storage engine, transaction management, and so on.