Transaction concurrency control

The previous paragraph is the time involved in the database isolation level problem, search on the internet, found that most of the ambiguity, and there are many errors. The textbook "Introduction to Database Systems" and the JDBC documentation were consulted. Now record the results of the summary for everyone to share.
1. DBMS transaction concurrency control

1.1 Issues with transactional concurrency operations Dmbs allows things to run multiple transactions, which can manipulate the same data object at the same time.        This can cause the following problems to be lost: the modification of one transaction is overwritten by another transaction.        Non-repeatable reads: The same query is executed in one transaction, and two results are different. Phantom reads: Phantom reads are not repeatable, the same query is executed in one transaction, the second result row is less than the first or more (rows are deleted or insert) dirty read: One transaction read the data is invalid, is another transaction rollback data
Therefore, it is necessary to control the concurrency of the transaction, and the main technique is the lock.
 

1.2 Lock         

A lock is a transaction that requires a corresponding lock to be added to a data object when it operates on each data object. This allows the most data object access to be restricted for other transactions.       The transaction releases this lock when the operation ends.  

1.2.1 The size of the lock           

The granularity of a lock includes a logical unit or a logical unit:

Column values (note not column), column value collection, row, table, index entry, entire index, database;

It can also be a physical unit:

< Span class= "Apple-converted-space" >< Span class= "Apple-converted-space" > data pages, index pages, blocks.         

< Span class= "Apple-converted-space" >< Span class= "Apple-converted-space" > transactions can be locked for various types of data objects, that is, multi-granularity lock. In this way, there is a tree of data objects that can be locked in the DBMS, or a multi-granularity tree. As below, level three multi-granularity tree. &NBSP;
       &NBSP; 1.2.2 Type of lock           

　　The lock is divided into 5 types, as follows          exclusive lock (x Lock):                 is also called write lock. If transaction T adds an X lock to the data object A, only T is allowed to read and modify a, and no other transaction can add any type of lock to a, until T releases the lock.          shared lock (S lock):                 is also called read lock. If transaction T adds this type lock to data object A, the transaction T allows read a but does not allow modification of a, and the other transaction can only add S lock to a, not X lock, until all s locks on a are freed.          Intent Exclusive Lock (IX Lock):                 If an IX lock is added to a data object, it indicates that his descendants in the multi-granularity tree (including direct children, children's children, etc.) have the possibility of being added X-Lock (intent).          Intent Shared Lock (is lock):                 If you add an IS lock to a data object, it means that his descendants in the multi-granularity tree (including direct children, children's children, etc.) have the possibility of being added S lock (intent).          Shared Intent Exclusive Lock (SIX):               &nbSp If a data object is added with six locks, it is added to the S lock, plus the IX lock.
The compatibility matrix for this class 5 lock is as follows, T1 has locked the data object, and T2 attempts to lock the data object. Y indicates success, and n indicates failure.
The above five types of locks in Oracle correspond to X-lock, S-lock, RX-Lock, RS-lock, SRX-lock, and lock granularity includes row-level and table-level, excluding database.
1.2.3 Lock Protocol

　　When you lock a data object, you need to contract some rules, such as the appropriate application, lock-in time, and when to release. These rules are called lock protocols. What protocols are followed by the DBMS depends on the specific situation. Here are some protocols:          Basic Lock Protocol: When a transaction T locks on data object A, if a lock already exists on a and T is incompatible with the lock, then T treats Until the existence of A is released.          first-level lock protocol: transaction T must be X-locked before modifying the data object A, knowing that the transaction ends before releasing.          Two-level lock protocol: On the basis of the first-level lock protocol, plus the transaction t before reading the data object A must be added S lock, after reading can release S lock.          three-level lock protocol: On the basis of the first-level lock protocol, add transaction T to the data object A before reading it must be locked, until the end of the transaction is released.          Multi-Granularity lock protocol: Each node in a multi-granularity tree is individually locked (explicit lock). Locking a node means that all descendant nodes are also locked with the same type of lock (implicit lock, which does not actually lock on descendant nodes, just logically descended nodes have the same type of lock, so it is called an implicit lock. ）。 The effect of explicit and implicit locks is the same, so checking for lock compatibility not only checks for an explicit lock but also an implicit lock. So transaction T locks the data object A and needs to check for explicit locks on its ancestors (both direct and indirect all upper nodes) (to check if an implicit lock for a is incompatible with an explicit lock to be added), check its explicit lock (check that an already existing explicit lock is incompatible with the explicit lock to be added), Check the explicit lock of its descendants (check if the implicit lock to be added to the descendant is incompatible with the explicit lock that the descendant already exists), and if an incompatible lock is found, T waits.          Intent Lock Protocol: When you lock any node, you must first add an intent lock to the ancestor (including all the direct and indirect upper nodes), in order from top to bottom. After the release node is locked, the intent lock on its ancestor is released, releasing the order from the bottom up. The meaning of the intent lock is that if you add an intent lock to a node, it means that the descendant nodes of the node are being locked. So the transaction T to lock the data object A, you need to check aAll locks on ancestors (check if the locks on the ancestor are compatible with intent locks to be added to ancestors), check all locks on a (check if the lock on a is compatible with the lock to which he wants to add), and if an incompatible lock is found, T waits.        Multi-granularity lock protocol, when the node is locked, it is necessary to check the explicit lock of the sub-node, which brings a lot of performance overhead when the sub-nodes are many. The intent lock is an improvement on this issue, which improves performance by turning the lock between multi-granularity trees from top to bottom. Oracle uses the intent lock protocol.
2.DBMS Transaction ISOLATION level

2.1 Description of the transaction isolation levelWhat we discussed in the previous section is the content of the DBMS implementing transactional concurrency control, which is a problem within the DBMS. So how do you intervene from outside the DBMS inside the concurrency control?When a transaction writes a data object, the DBMS operates with an X-lock and its associated lock (for example, ix), which is fixed and cannot be intervened by the outside world. When a transaction reads a data object, which data objects should be visible to this transaction? For example, other transactions have been written yet uncommitted, other transactions have been committed, and so on. This should be optional, which means the outside world can intervene.        The DBMS lets the transaction select which data objects to read through the transaction isolation level. It is possible to understand that writing is strictly operational and does not allow errors to occur, so its concurrency control is the most stringent and is not optional. While reading is a loose operation, allowing non-repeatable read, Phantom Read, dirty read problems, so its concurrency control is divided into multiple levels, you can choose the appropriate level to change the concurrency and allow errors.
2.2 Transaction ISOLATION level typesThe Ansi/iso SQ92 standard defines the following transaction isolation levels:2.2.1 Read UNCOMMITTED transactions can read other transactional uncommitted data objects.         Potential problems: Dirty read, non-repeatable read, Phantom Read DBMS lock operation: No lock, direct read arbitrary data object.            2.2.2 The Read Committed transaction can read other transactional committed data objects, and after reading this data object can be read and written by other transactions.         Possible problems: Non-repeatable read, Phantom Read DBMS lock operation (using Intent Lock Protocol): Plus S lock, ancestor Plus is lock, after reading immediately release S lock and ancestors of is lock. 2.2.3 Repeat read (Repeatabe read):A transaction can read other transactional committed data objects, and after reading this data object cannot be re-written by other transactions but can be read until the transaction ends. Possible problems: Phantom Read DBMS lock operation (with intent Lock Protocol): Plus S lock, ancestor Plus is lock.         The is lock that releases s locks and ancestors after the end of the transaction. 2.2.4 Serialization (Seriaizabe): Before a transaction is read, the data objects submitted by other transactions are visible to this transaction, and the entire multi-granularity tree is read-only and cannot be written until the end of the transaction. So he severely reduced the concurrency of transactions, especially when the multi-granularity tree was deep, which is why Oracle did not use the database as a multi-granularity root. Transactions are fully isolated at this level, just as all transactions are executed sequentially.Possible problems: No DBMS lock operation (with intent Lock Protocol): Plus S lock, ancestor plus six lock, release s lock and Ancestor's six lock after transaction ends.
2.3 Use of isolation levelsThe isolation level is for transactions. However, the default and DBMS global default transaction isolation levels can also be configured for the session (connection for JDBC). This allows the default transaction isolation level to be used when creating transactions without specifying them.
3. JDBC TransactionExecutes a transaction through connection in JDBC. A connection can execute multiple transactions. A transaction can execute multiple SQL statements.
3.1 Opening of the transactionIn connection, if there is no transaction, a transaction is opened automatically before the SQL statement is executed. If the transaction already exists, use its Execute SQL statement directly.
3.2 End of the transactionAfter the SQL statement is complete, call connection's commit or Rollback method to end the current transaction. Automatically opens a new transaction before executing the SQL statement.
3.3 Setting the isolation level of a transaction The isolation level of a transaction cannot be set separately in JDBC, only indirectly through the session (corresponding connection), through the connection Settransactionisolation method. After this method is called, if the current transaction is present and the transaction isolation level of the connection is changed, then the current transaction is committed immediately, and then the transaction that is opened will take the transaction isolation level.
3.4 SQL statements have different types of completion time SQL statements , and their SQL statements finish at different times.    The following DML, DDL statement: SQL statement execution completes, then the SQL statement is completed.    SELECT statement: The SQL statement is completed after the query statement execution completes and returns resultset when the Close method of resultset is called. Multi-result statement: When the SQL statement returns multiple results (such as executing a stored procedure, batch), the completion time of the SQL statement is the largest of the results.
the 3.5Connection autocommit mode Connection has a schema called autocommit, which calls connection's Commit method or Rollback method immediately after SQL is completed in this mode. This enables each SQL to execute in a separate transaction. Auto-commit mode mode is turned on.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Transactional concurrency control