Deadlock problem
Death lock is a very important topic. During the use of transactions and locks, deadlocks are an inevitable phenomenon. In both cases, a deadlock can occur. The first case is that when two transactions are locked, two separate objects are locked, and each transaction requires a lock on another transaction-locked object, so every transaction must wait for another transaction release possession. Lock, at this time, a deadlock occurs. This deadlock is the most typical deadlock form. There are two transactions A and B in the same time. Transaction A has two operations: lock table part and request access table Supplier; transaction B also has two operations: Lock Table Supplier and request Access Table Part. As a result, a deadlock occurs between transaction A and transaction B, and its schematic process is shown in Figure 10.
Figure 10 dead lock schematic
The second case of deadlock is that when in a database, there are several long running transactions perform parallel operations, and when the query analyzer processes a very complex query, such as connection queries, then due to cannot be controlled The order of order is likely to have a deadlock.
When a deadlock occurs, the system can automatically detect and then the deadlock is ended by automatically canceling one of the transactions. In two transactions of the deadlock, the priority is determined based on the length of the transaction time as the rules. The transaction for processing time has a high priority, and the transaction with short processing time has a lower priority. When a conflict occurs, the priority is high, and the priority low-level transaction is canceled.
Session-level lock and table-level lock
Generally, the lock is automatically provided by the system. However, in some cases, the user can customize the lock. That is to say, the user can customize the session level lock and the table-level lock.
The customization of the session-level lock includes two aspects, transaction isolated grade and lock timeout limit. Transaction Isolate Level Protection Specifies transaction, the transaction Isolated Level Allows Isolated Levels to all transactions in a session. When setting the isolation level, the default lock behavior is specified for all the statements in the session. Use some of the following options when specifying a transaction isolation level. The read committed option guide the system when reading the data, uses a shared lock. Under this option, you can't experience the dirty reading, that is, you can't see the data being modified. However, the data can be changed before the end of the transaction, so it can be generated not read data or illusion data. The Read Uncommitted option guide does not use shared locks, nor does it use to lock. Under this option, you can see the data being modified, you can experience the dirty reading. This option is the lowest level setting. The REPEATABLE READ Option Guidance The system places a lock on all data used to prevent other users from modifying data, but illusion reads can occur. The Serializable option guide The system places the lock on the data until the transaction is complete, the user can modify or insert data. This option is the highest level of restriction. In addition, the time waiting for access to access can also be customized, which requires setting the lock timeout limit. When the lock timeout is set, the transaction is automatically canceled if the transaction wait for the transaction exceeds the lock timeout.
The custom table-level lock refers to the behavior of the table-level lock by specifying one or more options for the table. In fact, custom table-level locks is to use a way to optimize hidden. Optimization hide refers to the ability to add content options after the FROM clause, and improve system identification operations. Table-class lock has many options. Rowlock Options Guidance The system uses a row-level lock instead of a page-level lock or a table-level lock, which is the default option of the system. PAGLOCK Options Guidance The system uses a page level lock. Tablock Options Guidance The system uses a table-level lock or a chart lock or page lock, and uses a shared lock, thus allowing other transaction to read data in the table, but cannot modify the data in the table. Tablockx Options Guidance The system uses a row lock to prevent other transactions from reading or modifying any of the data in the table. NOLOCK Options Guidance The system does not use the lock, neither the shared lock, nor does it lock it. In this case, you can experience the dirty reading. The HOLDLOCK option guide The system holds the shared lock until the end of the transaction, not immediately released when there is any other lock request. UPDLOCK Options Guide Transactions When the data in the table is read, use the modified page-level lock instead of the shared lock. This lock always occupies the end of the statement or transaction. in conclusion
Network technology is the trend of information technology development. Multi-user, multi-transaction, scalable, replication, data warehouse, etc. are all in order to adapt to database development directions of network technology. As an important database technology, the transaction is a fundamental role in protecting database recoverability and multi-user, multi-transaction. A business is a unit of work that may include a statement, or may include a hundred statement, and all of these statements, either, either cancel. During database backup and recovery, transactions also have an important role, you can use logs for transaction log backup, incremental backup, without having to perform full backups for time, energy, and backup media every time. The lock is a means of achieving multi-user, multi-transaction and other concurrent processing methods. There are many types and resources of the lock. The lock is automatically supplied by the system, and the user can customize some custom. In SQL Server 7.0, an obvious feature is to use a row lock. Using a row-level lock causes changes in data storage engines, transaction management methods.
