Transaction Concurrency Control

The mechanism of the transaction concurrency control lock is to prevent the interaction between transactions and control concurrent access to ensure data consistency and integrity. When a transaction or operation attempts to prevent another transaction from affecting the objects it operates on, the transaction or operation locks the object, other transactions can operate on this object only after the transaction is locked. Simple Lock types include eXclusive locks and Share locks. level-1 locks refer to transactions before they modify objects, you must first apply the X lock to this object and release the X lock until the transaction ends. If transaction T only reads the object, no locks are required. The level-1 locking protocol avoids data modification loss, but cannot avoid dirty reads and repeated reads. 2. The second-level Locking Protocol refers to the addition of the S lock before the transaction T reads the object on the basis of the first-level protocol, and the S lock is released immediately after reading the object. The second-level locking protocol avoids data modification loss and dirty reading. But cannot avoid repeated reads. 3. Level-3 locking refers to the addition of the S lock between the read objects of transaction T on the basis of the level-1 locking protocol. The S lock is released only after the transaction ends. The three-level locking protocol avoids data loss, dirty reads, and non-repeated reads. Live lock: The program waits for an indefinite period of time. The solution is to first begin with the service policy. Deadlock: Two or more transactions lock some database objects, and then lock the database objects of the other party and wait for the failure. That is, wait for the resources locked by the other party to be released. The methods to prevent deadlocks include one lock and sequential locking (this is the solution of the operating system, but not applicable to databases ). A common solution to database deadlocks is to diagnose and release the database. There are two methods: timeout method and wait method. Timeout method: If the wait time of a transaction exceeds the specified time limit, a deadlock occurs. Disadvantage: it is easy to misjudge the deadlock; time limit is not good; wait graph: The wait graph of the transaction is a directed graph. If transaction T1 waits for transaction T2, then there is a directed edge between T1 and T2 that points from T1 to T2. If a loop is generated between T1 and T2, a deadlock occurs. A common method to solve a deadlock is to manually select a transaction with the minimum cost of the deadlock, undo it, and release all the locked database objects. The lock granularity and the size of the intended Lock Object are called the lock granularity. The larger the lock granularity, the fewer objects to be locked, the smaller the selectivity, the smaller the concurrency, and the smaller the overhead. The smaller the lock granularity, the more objects to be locked. The higher the selectivity, the more concurrency, and the higher the overhead. Locking a node also means that the same lock is applied to all levels of subnodes on the node. Intention lock: if an intention lock is applied to a node, the lock will be applied to all lower-level nodes of the node (X lock and S lock ); if a lock (X lock and S lock) is applied to a node, the intention lock must be applied to all the upper-level nodes of the node. Intention Lock IS divided into three types: 1. IS Lock (Intended Share Lock refers to intention Share Lock) refers to: if an IS Lock IS applied to a node, it indicates the intention to add the S lock to all its lower-level nodes. If the S lock IS applied to a node, the IS lock must be applied to each higher-level node of the node first. 2. Intended eXclusive Lock refers to the intention to eXclusive Lock. If an IX Lock is applied to a node, it indicates that an X Lock is applied to all its lower-level nodes; if you apply an X lock to a node, you must first apply an IX lock to each of the nodes. 3. Share Intented eXclusive Lock refers to the Share intention eXclusive Lock. If the SIX Lock is applied to a node, the S Lock is applied to it and the IX Lock is added, that is, SIX = S + IX. ORACLE locks are classified DDL locks (Dictionary locks) and DML locks (Data locks ). ORACLE locks include database-level locks, table-level locks, and row-level locks. Column-level locks are not supported. Two ways to lock the database: 1. Set the database to a restricted mode; 2. Change the database to a read-only mode; to prohibit any new sessions or transactions. You can use the STARTUPRESTRICT command after SHUTDOWN the database to set it to a restricted mode when starting the database, you can also use the alter system enable restricted session or alter system disable restricted session Statement to ENABLE or DISABLE the database restriction mode when the database is opened. Connected sessions are not affected by the alter system enable restricted session Statement. Open alter database open read only in READ-ONLY mode; Table-level locks: When executing INSERT, UPDATE, DELETE, SELECT... During the for update statement, TABLE-level locks (TM-type locks) are automatically applied to the operated TABLE. You can also use the lock table statement to manually set a TABLE-Level LOCK. The purpose is to prevent modification to the table structure. Row-level locks: When executing INSERT, UPDATE, DELETE, SELECT... During the for update statement, table-level locks (TX-type locks) are automatically applied to the operated table ). Row-level locks are used to prevent other users from modifying the data of rows being operated during the data in the operation table (transactions are not committed or rolled back. Row-level locks protect data by row to prevent concurrent access to the same row. Note: To obtain the TX lock of a table, the transaction must first obtain the tmlock of the table. ORACLE uses the queue mechanism to manage locks. ORACLE locks are unrestricted and will not be automatically upgraded, which is different from MSSQL. The query statement in ORACLE does not lock data, provided that the SELECT statement without the for update clause is not included. Because ORACLE queries are successfully executed by using the pre-image before the data stored in the Undo space is locked. This method improves the concurrency and ensures that the transaction does not read dirty data. Initialization parameters of the lock: 1. DDL_WAIT_FOR_LOCKS: this parameter is a dynamic parameter used to control whether DDL statements need to wait for the lock. The default value is FALSE, indicating that the lock is not waiting (NOWAIT ). If it is TRUE, the lock is waiting. Use the alter session or alter system statement to change the value of this parameter. 2. DDL_LOCKS: this parameter is a static parameter. It is used to specify the maximum number of DML locks. The value range is 20 to an unlimited value. The default value of this parameter is 4 times that of the TRANSACTIONS initialization parameter, indicating that four locks are required for one transaction on average. Use ALTERSYSTEM... SCOPE = SPFILE statement to change the value of this parameter, and the database must be restarted. 4. ENQUEUE_RESOURCES: this parameter is a static parameter. Used to specify the number of resources that the lock manager can lock concurrently. Use alter system... SCOPE = SPFILE to change this parameter, You need to restart the database. 5. DISTRIBUTED_LOCK_TIMEOUT: this parameter is a static parameter. Used to specify the total time (in seconds) for a distributed transaction to wait for the resource to be locked. The default value is 60 seconds. Use alter system... SCOPE = SPFILE to change this parameter, you also need to restart the database. Policies and methods for solving lock contention: 1. transactions should not be run, and the COMMIT and ROLLBACK statements should be used in a timely manner during the operation. 2. Avoid using Table locks, but use ORACLE's default locking mechanism. 3. Before changing data, you can use SELECT... The for update nowait statement tentatively locks, and learns the specific situation through the returned prompt to avoid inexplicable waiting. 4. Use DDL statements during off-peak periods. Check whether a lock exists in the SYSTEM in time, investigate the cause of the lock, and use alter system kill session 'sid, serial # 'to KILL the lock as appropriate.