A database is a shared resource that can be used by multiple users. These user programs can be executed serially, with only one user program running at a time, and access to the database is performed, and other user programs must wait until the end of the user program to access the database. However, if a user program involves an input/output interchange of large amounts of data, the database system spends most of its time in idle state. Therefore, in order to make full use of database resources and to play the characteristics of database shared resources, multiple users should be allowed to access the database in parallel. However, this will result in multiple user programs concurrent access to the same data, if the concurrency operation without control may access and store incorrect data, destroy the consistency of the database, so the database management system must provide concurrency control mechanism. The concurrency control mechanism is one of the important indicators to measure the performance of a database management system.
DM uses blocking mechanisms to solve concurrency problems. It is guaranteed to have multiple running user programs at any time, but all user programs run in a completely isolated environment.
First, concurrency control of the preparatory knowledge
(i) Concurrency control overview
Concurrency control is done in transactions (transaction).
1. Unit of concurrency control-transaction
A transaction is a logical unit of work for a database, which is a user-defined set of action sequences. A transaction can be a set of SQL statements, an SQL statement, or an entire program.
The start and end of a transaction can be controlled by the user, and if the user does not explicitly define the transaction, the database system automatically divides the transaction by default.
A transaction should have 4 properties: atomicity, consistency, isolation, and persistence.
(1) atomicity
The atomicity of a transaction guarantees that the transaction contains a set of update operations that are atomic, which means that the operation is a whole and is done for the database or not at all, and cannot be partially completed. This nature can be guaranteed even after a system crash, and database recovery after a system crash will restore and revoke the impact of a system crash-active transaction on the database, thus guaranteeing the atomicity of the transaction. The system will log information about the modification operation information itself to disk before modifying any actual data on the disk. When a crash occurs, the system can record, based on these actions, what state of the transaction was at that time, in order to determine whether to revoke all modifications made by the firm, or to re-execute the modified operation.
(2) Consistency
Consistency requires that the database be transformed from one consistent state to another consistent state after the transaction execution is complete. It is a logical attribute based on consistency rules, for example, in the operation of the transfer, the account amount must be balanced, this rule is a mandatory requirement for programmers, thus, consistency and atomicity are closely related. The consistency property of a transaction requires that the transaction's consistency is still satisfied in the case of concurrent execution. It is logically not independent, it is represented by the isolation of the transaction.
(3) Isolation
Isolation means that the execution of a transaction cannot be disturbed by other transactions. That is, the operations inside a transaction and the data used are isolated from other transactions that are concurrently executing, and cannot interfere with each other concurrently. It requires that, even if there are multiple transactions executing concurrently, each successful transaction appears to be executed as a serial schedule. Another method of calling this property is serializable, which means that any interleaved operation that the system allows is equivalent to a serial dispatch. Serial scheduling means that each time a transaction is scheduled, another transaction operation cannot begin until all operations of one transaction are not completed. For performance reasons, we need to schedule staggered operations, but we also hope that the effect of these interleaved operations and a certain serial scheduling is consistent. The DM implements this mechanism by adding appropriate locks to the data access objects of the transaction, thereby rejecting concurrent operations on the same database object by other transactions.
(4) Durability
System-provided persistence guarantees that once a transaction commits, the modifications made to the database will be persistent and should not have any effect on any machine or system failure. For example, when an ATM is paying a customer a sum of money, there is no need to worry about losing a customer's withdrawal record. The persistence of a transaction guarantees that the impact of the transaction on the database is persistent, even if the system crashes. As mentioned in the atomic Nature, the system provides this assurance by making a record.
The DM does not provide a statement that explicitly defines the start of a transaction, and the first executable SQL statement (except the Connect statement) implies the beginning of the transaction, but the end of the transaction can be explicitly controlled by the user. In the DM, the following conditions end (normal, abnormal) a transaction:
(1) When the property of a connection is set to auto commit, each execution of a statement will be submitted;
(2) If the Commit/rollback statement is encountered, a transaction is submitted/rolled back;
(3) When the DDL auto-commit switch on the system opens (the default is open), the DDL statement is automatically submitted and the previous DML and DDL operations are encountered;
(4) The normal end of the program where the transaction is located and the user exits;
(5) When the system terminates abnormally;
Description: DM provides the automatic commit switch ddl_auto_commit of DDL statements in the configuration file. When this configuration item has a value of 1 (the default), all DDL statements are automatically committed, and when this configuration item has a value of 0 o'clock, all DDL statements except the CreateDatabase, Alterdatabase, and Createschema statements are not automatically committed.
The consistency in DM is transaction-based. DM is used to permanently and revoke modifications to the database by committing and rolling back, but the DM guarantees that the database will be consistent before and after each transaction, whether committed or rolled back. To improve the flexibility of transaction management, DM provides a set save point (savepoint) statement and rollback to a savepoint statement. The savepoint provides a flexible rollback in which a transaction can be rolled back to a savepoint, the operation before that SavePoint is valid, and subsequent operations are rolled back.
The transactions in the DM also have the above 4 attributes: Atomicity, consistency, isolation, and persistence.
2. Concurrency and inconsistencies in data
If there are no locks and multiple users access a database at the same time, problems may occur when their transactions use the same data at the same time, resulting in inconsistencies in the data in the database.
One of the most common examples of concurrent operations is booking operations in a train/airplane ticketing system. For example, an activity sequence in the system:
① a conductor to read out a flight ticket number of balance a, set a=16;
② B to read out the same flight ticket number of balance A, is also 16;
③ a conductor sells a ticket, modifies the ticket number balance a=a-1=15, writes a back to the database;
④ b conductor also sold a ticket, modify the ticket number balance a=a-1=15, write A back to the database.
As a result, two tickets were sold, and the balance in the database was reduced by only 1.
This situation is known as a database inconsistency. This inconsistency is caused by the concurrent operation of A and b two conductors. In the case of concurrent operation, the dispatch of the two transaction sequence of A and B is random. If you press the schedule sequence line above, the modification of a transaction is lost. This is because the 4th step in the B transaction modifies a and writes back the modifications that overwrite the a transaction.
The database inconsistencies brought by concurrent operations can be categorized into four categories: missing or overwrite updates, dirty reads, non-repeatable reads, and Phantom reads, which are just one of the concurrency problems.
<!--[If!supportlists]-->(1) <!--[endif]--> missing or overwritten updates (lost update )
A missing update issue occurs when two or more transactions select the same data and update the data based on the value originally selected. Every transaction is unaware of the existence of other transactions. The last update overrides updates made by other transactions, which results in data loss. The example of booking a flight ticket above is the concurrency problem. Transaction 1 and transaction 2 are read into the same data a=16, transaction 1 executes A-1, and the result a=15 written back, transaction 2 executes A-1, and writes the result a=15 back. The result of the transaction 2 submission overwrites the modification of the database by transaction 1 , thus making transaction 1 changes to the database missing.
(2) Dirty reading
One transaction reads the data that is written by another uncommitted parallel transaction. An unacknowledged dependency problem occurs when the second transaction chooses the row that the other transaction is updating. The data that the second transaction is reading is not yet confirmed and may be changed by the transaction that updated the row. In other words, when transaction 1 modifies a data and writes it back to disk, transaction 2 reads the same data, transaction 1 is revoked for some reason, then transaction 1 has modified the data recovery original value, transaction 2 reads the data is inconsistent with the data in the database, is incorrect data, called dirty read.
For example, in, transaction 1 modifies the C value to 200, transaction 2 reads C to 200, and transaction 1 is revoked for some reason, its modification is invalidated, C reverts to the original value 100, and transaction 2 reads the incorrect "dirty" data.
(3) Non-repeatable read (Nonrepeatable Read)
A transaction re-reads the previously read data and discovers that the data has been modified by another committed transaction. That is, when transaction 1 reads a data, transaction 2 modifies it, and when transaction 1 reads the data again, it gets a different value than the first time.
For example, in, transaction 1 reads b=100, transaction 2 reads the same data B, modifies it, and writes b=200 back to the database. Transaction 1 in order to proofread the Read value b,b is 200, inconsistent with the first read value.
(4) Phantom reading
This occurs if a transaction can change the result before the query results are committed by another transaction. This sentence can also be explained, transaction 1 read some data records from the database according to certain conditions did not submit the results of the query, transaction 2 deleted some of the records, transaction 1 again by the same conditions to read data, found some records mysteriously disappeared ; or transaction 1 when some data records are read from the database on a certain condition, the query results are not submitted, and transaction 2 inserts some records, and when transaction 1 reads the data again in the same condition, a few more records are found.
The main reason for the above four types of data inconsistency is that concurrent operations undermine the isolation of transactions. Concurrency control is the correct way to dispatch concurrent operations, so that the execution of a user's transaction is not affected by other transactions, so as to avoid the inconsistency of data.
3. Concurrency Scenario Enumeration
Combine SQL statements to enumerate the various concurrency scenarios (including situations that can cause data inconsistencies and no impact on data consistency). A indicates that a certain piece of data, B and C all represent two or more data that satisfy a certain standard, ^ means "non", ∈ means to be or contain, 1 means the first transaction, and 2 represents the second transaction.
(ii) Scheduling of concurrent operations
The scheduling of parallel operations in a computer system is random, and different schedules may produce different results, so which result is correct and which is incorrect?
If a transaction runs without other transactions running at the same time, that is, it is not disturbed by other transactions, it is possible to assume that the result of the transaction is normal or expected, so the scheduling policy that serially runs all transactions is the correct scheduling policy. Although executing a transaction serially in a different order may produce different results, it can be considered correct because the database is not placed in an inconsistent state. This leads to the following conclusion: the parallel execution of several transactions is correct when and only if the result is the same as the result of executing them serially in a sequential order. We call this parallel scheduling strategy a serializable (serializable) dispatch. Serializable (serializability) is the only criterion for the correctness of parallel transactions.
For example, there are now two transactions, each of which contains the following actions:
Transaction 1: Read b;a=b+1; write back A;
Transaction 2: Read a;b=a+1; write back B;
Suppose that the initial value of a is 10,b and the initial value is 2. The three different scheduling strategies for these two transactions are given, (a) and (b) are two different serial scheduling strategies, although the execution results are different, but they are all correctly dispatched. (c) The two transactions are interleaved, and because the result of the execution differs from the results of (a) and (b), it is the wrong dispatch. The two transactions in (d) are also interleaved, because the execution result is the same as the execution result of serial dispatch 1 (figure (a)), so it is the correct dispatch.
In order to ensure the correctness of parallel operation, the parallel control mechanism of DBMS must provide some means to ensure the scheduling is serializable.
In theory, scheduling policies that prohibit execution of other transactions at the time of execution of a transaction must be serializable, which is also the simplest scheduling strategy, but this approach is not actually feasible because it prevents users from fully sharing database resources.
At present, the DBMS generally adopts the method of blocking (pessimistic method, which is used by DM, SQL Server is also used in this method) to ensure the correctness of scheduling, that is, to ensure the serialization of parallel operation scheduling. In addition, there are other methods, such as time scale method, optimistic method and so on.
? Pessimistic concurrency control
The locking system prevents users from modifying data in a way that affects other users. If the user performs an action that causes a lock to be applied, no other user can perform an action that conflicts with the lock until the lock is released by the owner of the lock. This method is mainly used in the environment of intense data contention, and the cost of protecting data with locks when concurrency conflicts occur is lower than the cost of rollback transaction, so this method is called pessimistic concurrency control.
? Optimistic concurrency control
In optimistic concurrency control, data is not locked when the user reads the data. When you perform an update, the system checks to see if the data has changed after another user has read the data. If another user updates the data, an error occurs. In general, the user who receives the error message rolls back the transaction and starts over again. This approach is mainly used in environments where data contention is low, and occasionally the cost of rolling back a transaction exceeds the cost of locking the data when the data is read, so the method is called optimistic concurrency control.
? Time-scale concurrency control
The basic difference between markers and blocking techniques is that a blockade is a synchronous execution of a set of transactions (that is, cross-executing), which is equivalent to a single serial operation of those transactions, and the time-scale method is also used to synchronize the cross-execution of a set of transactions, but makes it equivalent to a specific serial execution of those transactions, An execution that is determined by the timing of the time scale. If a conflict occurs, it is resolved by revoking and restarting a transaction. When the transaction restarts, the new time-scale is assigned.
(iii) blockade
Blocking is a very important technique for the concurrency control of a matter. The so-called blockade is a transaction t in a data object, for example, in the mark, record, and so on before the operation of the system to make a request to lock it. After locking, the transaction T has some control over the database object, and other transactions cannot update the data object until the transaction T releases its lock.
1. Type of blockade
The DBMS typically provides a variety of data types for blocking. What kind of control a transaction has after locking a data object is determined by the type of blockade. There are two basic types of blocking: Exclusive (exclusive lock, précis-writers X Lock) and shared lock (share lock précis-writers is s lock)
An exclusive lock is also called a write lock. If transaction T adds an X lock to the data object A, only T reads and modifies a, and no other transaction can add any type of lock to a, until T releases the lock on A. This guarantees that the other transaction cannot read and modify a until the lock on the A is released by T.
Shared locks are also known as read locks. If the transaction t adds the S lock to the data object A, the other transaction can only be locked to a plus s, not the x lock, until T releases the lock on A. This guarantees that the other transaction can read a, but cannot make any modifications to a when T releases the S lock on a.
The control mode of the exclusive and shared locks can be represented by a compatible matrix.
In the blocking type compatibility matrix, the leftmost column represents the type of lock on the data object that the transaction T1 has obtained, where the horizontal line indicates that no lock is added. The top row indicates that another transaction T2 a blocking request to the same data object. T2 's blockade request can be satisfied with Y and N, where y indicates that the transaction T2 's blocking requirement is compatible with the lock that the T1 already holds, and the blocking request is satisfied. n indicates that the T2 blocking request is in conflict with the lock T1 has held, and the T2 request is denied.
2. Particle size of blockade
Both the X lock and the S lock are added to a data object. The blocked object can be either a logical unit or a physical unit. For example, in a relational database, a blocked object can be a property value, a collection of attribute values, a tuple, a relationship, an index item, an entire index, an entire database, and a logical unit, such as a page (a data page or index page), a block, and so on. Blocking objects can be large, such as locking the entire database, or it can be small, such as locking only one property value. The size of the blocked object is called the granularity of the blockade (granularity).
The granularity of the blockade is closely related to the concurrency of the system and the overhead of concurrency control. The larger the granularity of the blockade, the smaller the object that can be blocked in the system, the smaller the concurrency, but also the smaller the system overhead; Conversely, the smaller the granularity of the blockade, the higher the concurrency, but the greater the overhead.
Therefore, it is desirable to have different block sizes in a system for different transaction selection. When we choose the blocking granularity, we must consider both the blocking mechanism and the concurrency factor, and weigh the system overhead and concurrency to obtain the best results. In general, user transactions that need to handle a large number of tuples can have a relationship as a blocking unit, a user transaction for a large number of tuples that need to handle multiple relationships, a database-blocking unit, and a tuple-blocking unit to improve concurrency for a user transaction that handles a small number of tuples.
3. Blockade Agreement
The purpose of the blockade is to ensure that concurrent operations are dispatched correctly. To this end, in the use of the X-lock and S-lock of the two basic blockade, a certain granularity of data objects to lock, also need to agree on some rules, such as when to apply for X-lock or S-lock, lock time, when the release and so on. We call these rules the blockade Agreement (locking protocol). The different rules of blockade are formed, which provide certain guarantee for the correct dispatching of concurrent operation in different degree. This section describes the three-level blocking protocol that guarantees data consistency and the two-segment lock protocol that guarantees the serialization of parallel scheduling, and the next section describes the blocking protocol to avoid deadlocks.
(5) Blocking protocol to ensure data consistency-level three blocking protocol
Incorrect scheduling of concurrent operations can result in four data inconsistencies: missing or overwritten updates, dirty reads, non-repeatable reads, and fantasy reads. The three-level blockade protocol solves this problem in varying degrees, respectively.
① Level 1 Lockdown Protocol
The content of the 1-level blocking protocol is that the transaction T must have an X lock before modifying the data R until the end of the transaction is released. The end of the transaction consists of a normal end (commit) and an abnormal end (rollback).
A Level 1 blocking protocol prevents loss or overwrite of updates and guarantees that transaction T is recoverable. For example, using a Level 1 blocking protocol solves the problem of missing updates for the flight ticket example.
, transaction 1 is a plus x lock before read A is modified, and when transaction 2 is again requested for a plus x lock, it can only wait for transaction 1 to release the lock on A. Transaction 1 modifies the value A=15 writes back to the disk, after releasing the X lock on a, transaction 2 obtains an X lock on a, when he reads that a is already a transaction 1 updated value 15, then the new a value is calculated, and the result value is a=14 back to disk. This avoids the loss of the update for transaction 1.
In the Level 1 blocking protocol, if only the read data does not modify it, it does not need to be locked, so it does not guarantee repeatable read and dirty read.
② Level 2 Lockdown protocol
Level 2 Blocking Protocol content is: Level 1 blocking protocol plus transaction T before reading the data r must be the first to add S lock, after reading can release S lock.
The Level 2 Lockdown protocol prevents the loss or overwrite of updates and further prevents dirty reads. For example, the problem of dirty reads is resolved with a Level 2 blocking protocol.
|
, transaction 1 changes the C plus x lock, modifies its value, and then writes back to the disk before the change is made to C. At this point, transaction 2 requests C Plus s lock, since T1 has an X lock on C, transaction 2 can only wait for transaction 1 to release it. After the transaction 1 is revoked for some reason, C reverts to the original value of 100 and releases the X lock on C. Transaction 2 Gets the S lock on C, read c=100. This avoids the transaction 2 dirty read data.
In a Level 2 blocking protocol, the S lock can be released after reading the data, so it cannot guarantee repeatable reads.
③ Level 3 Lockdown protocol
Level 3 Blocking Protocol content is: Level 1 blocking protocol plus transaction T before reading the data must be added to the S lock, until the end of the transaction is released.
Level 3 blocking protocols further prevent non-repeatable read and fantasy reads, in addition to preventing the loss or overwriting of updates and non-dirty read data. For example, the problem of non-repeatable reads and Phantom reads is resolved using a Level 3 blocking protocol.
, transaction 1 before reading a A, a, B plus s lock, so that other transactions can only a B plus s lock, and cannot add X lock, that is, other transactions can only read a, B, and cannot modify them. So when transaction 2 applies for the X lock on B for modification B, it is rejected so that the other cannot perform the modification operation, only wait for transaction 1 to release the lock on B. Then the transaction 1 for the calculation of a second reading, a, then read the B is still 100, the sum of the result is still 150, can be repeated reading.
The main difference between the above level three protocol is what the operation requires for blocking and when to release the lock (that is, the lock time). Level three blocking protocol can be summarized as the following table.
(6) Blocking protocol to ensure the serialization of parallel dispatching--two block protocol
Serializable is the only criterion for the correctness of parallel dispatching, and the two-segment lock (two-phase locking, abbreviated 2PL) protocol is a blocking protocol to ensure the serializable of parallel scheduling.
The two-paragraph blockade agreement provides that:
① before a read or write operation on any data, the transaction must first obtain a blockade of that data, and ② after releasing a blockade, the transaction no longer obtains any other blockade.
The meaning of the so-called "two-paragraph" lock is that the transaction is divided into two phases, the first stage being the blockade, also known as the expansion phase, and the second phase releasing the blockade, also known as the contraction phase.
For example, the block sequence for transaction 1 is:
Slock A ... Slock B ... Xlock C ... Unlock B ... Unlock A ... Unlock C;
The block sequence for transaction 2 is:
Slock A ... Unlock A ... Slock B ... Xlock C ... Unlock C ... Unlock B;
Transaction 1 follows the two-block blockade agreement, while transaction 2 does not comply with the two-block blockade agreement.
It can be demonstrated that all parallel scheduling policies for these transactions are serializable if all transactions performed in parallel are subject to the two-phase lock protocol. So we come to the conclusion that all transactions that comply with the two-phase lock protocol must be correct in their parallel results.
It is necessary to note that the two-phase locking protocol is a sufficient condition for serializable scheduling, not a requirement. In a serializable schedule, all transactions must not necessarily conform to the two-block protocol. For example, in, (a) and (b) are serializable schedules, but (a) comply with the two-phase lock protocol and (b) do not comply with the two-phase lock protocol.
4. Deadlock and Live lock
Blocking technology can effectively solve the consistency problem of parallel operation, but it also brings some new problems, that is, deadlock and live lock.
(1) Live lock
If the transaction T1 blocked the data object R, the transaction T2 also requests the Block R, so T2 waits. Then T3 also asked to block R. T1 released the lock on R, the system first approved the T3 request, T2 had to wait. Then T4 also request blockade R,T3 release R on the lock, the system also approved the T4 request ..., T2 may be so forever waiting. This is the case of a live lock, as shown in.
A simple way to avoid a live lock is to use a first-come-first-serve strategy. When multiple transaction requests block the same data object, the blocking subsystem queues the transactions in the order in which they are blocked, and once the lock on that data object is released, the first transaction in the request queue is approved for the lock.
(2) Deadlock
If the transaction T1 blocked data A, the transaction T2 blocked data b. After T1 also applied to block data B, because T2 has blocked B, so T1 wait for T2 release B on the lock. Then T2 also apply for blockade a, because T1 has been blocked a,t2 can only wait for T1 release a lock on. So there is T1 waiting for T2, and T2 is waiting for T1 situation, T1 and T2 two transactions can never end, forming a deadlock. As shown in.
Deadlock problem in the operating system and general parallel processing has done in-depth research, but the database system has its own characteristics, the operating system to resolve the deadlock method is not necessarily suitable for the database system.
Currently in the database to solve the deadlock problem there are two main types of methods, a method is to take certain measures to prevent the occurrence of deadlocks, another kind of method is to allow the occurrence of deadlocks, the use of certain means of periodic diagnosis of the system has no deadlock, if there is the lifting.
Prevention of ① deadlock
In a database system, a deadlock occurs because two or more transactions have blocked some data objects, and then all request locks on data objects that have been blocked for other transactions, resulting in deadlock waits. Preventing deadlocks is actually about destroying the conditions that create deadlocks. There are usually two ways to prevent deadlocks.
A blockade act.
A blocking law requires that every transaction must be locked at once for all the data to be used, otherwise it cannot continue execution. For example, in the example, if the transaction T1 locks the data objects A and B once, the T1 can be executed and T2 wait. T1 the lock on A/b after execution, T2 continues execution. This way, there is no deadlock.
Although a blockade method can effectively prevent the occurrence of deadlocks, there are also problems. First, once will be used in the future all the data lock, it is bound to expand the scope of the blockade, thereby reducing the concurrency of the system. Second, the data in the database is constantly changing, it is not required to block the data, in the implementation process may become blocked objects, it is difficult to achieve the precise determination of each transaction to block the data objects, can only take the extension of the blockade, the transaction may be blocked during the execution of the data Objects lock all, This further reduces the degree of concurrency.
Sequential Blockade method
Sequential blocking is a pre-set block order for data objects, and all transactions perform the blockade in this order. In the above example, we stipulate that the blockade is a,b,t1 and T2 are blocked in this order, that is, T2 must first block a. When the T2 request a blockade, because T1 already blocked a,t2 can only wait. T1 release A/b on the lock, T2 continues to run. This way, there is no deadlock.
The sequential blocking method can also effectively prevent deadlocks, but there are also problems. First, the database system can be blocked data objects and many, and with the data insertion, deletion and other operations and constantly changing, to maintain such a large and varied resources of the blockade order is very difficult, high cost.
Second, requests for closure of a transaction can be dynamically determined with the execution of the transaction, and it is difficult to determine in advance which objects each transaction is to block, making it difficult to impose a blockade in the prescribed order. For example, the blocking order of the specified data objects is a,b,c,d,e. The transaction T3 initially called for the blocking of the data object B,c,e, but when it blocked the b,c, it found that it needed to block a, which destroyed the order of the blockade.
It can be seen that the strategy of preventing deadlocks in the operating system is not very well suited to the characteristics of the database, so the DBMS is more commonly used to diagnose and unlock deadlocks in solving deadlock problems.
Diagnosis and release of ② deadlock
The method of diagnosing deadlocks in a database system is similar to the operating system, and even with a transaction waiting graph, it dynamically reflects the waiting state of all transactions. The concurrency control subsystem periodically (for example, every 1 minutes) detects a transaction wait diagram, which indicates a deadlock in the system if a loop is found in the diagram. For a detailed discussion of diagnosing deadlocks, see the relevant books for your operating system.
The concurrency control subsystem of DBMS should try to remove the deadlock when it detects the existence of the system. The usual approach is to choose a transaction that deals with the minimum cost of deadlock, revoke it, release all locks held by the transaction, and enable other transactions to continue.
Second, the DM concurrency control
(i) Transaction isolation level
The isolation level of a transaction describes how exposed the behavior of a given transaction is to other concurrent execution transactions. SQL-92 A total of four isolation levels, by selecting one of the four isolation levels, the user can increase exposure to other uncommitted transactions and achieve higher concurrency. The isolation level is the degree to which a transaction must be isolated from other transactions.
The four isolation levels of SQL-92 are as follows, and DM supports all of these isolation levels:
(1) Dirty read (READ UNCOMMITTED): The lowest level of transaction isolation, transactions may be queried for uncommitted data from other transactions, and only guaranteed not to read physically corrupted data.
(2) Read submit (read COMMITTED): DM default level, guaranteed not to read dirty data.
(3) Repeatable READ (REPEATABLE Read): Guaranteed non-repeatable reading, but it is possible to read phantom data.
(4) Serializable (SERIALIZABLE): The highest level of transaction isolation, with complete isolation between transactions.
The DM allows the user to change the isolation and read-write characteristics of a transaction that is not started, and the options set will remain valid for that connection until the option is explicitly changed. Setting the transaction isolation level allows the programmer to take the risk of some integrity issues, but in exchange for greater concurrent access to the data. Each isolation level provides greater isolation than the previous isolation level, but is replaced by more restrictive locks over a longer period of time. The DM also provides a statement that sets the transaction read-only property, after which the transaction can only do query operations and the database cannot be updated.
It is important to note that the isolation level of a transaction does not affect the transaction viewing itself as a modification of the data, that is, the transaction can always view its own modifications to the data. The isolation level of a transaction needs to be set according to actual needs, and a lower isolation level can increase concurrency, but at the cost of reducing the correctness of the data. Conversely, a higher isolation level ensures that the data is correct, but can negatively affect concurrency. The isolation level required by the application determines the locking behavior used by the DM.
The following table lists four isolation levels that allow different types of phenomena
Note: Missing or overwritten updates are prohibited in all standard SQL isolation levels.
(ii) Concurrent processing
1. Data locking mechanism
DM uses data locking mechanism to solve concurrency problems. It guarantees that there can be more than one running transaction at any time, but that all transactions run in an environment that is completely isolated from one another.
The blocked objects for the DM are tables and tuples. The implementation of the blockade is automatic and manual, that is, implicit locking and explicit locking. The blockade of an implicit blocking action differs depending on the isolation level of the transaction. At the same time, DM provides users with 4 kinds of manual locking statements to adapt to user-defined application system.
In general, the DM implicit blockade is sufficient to ensure data consistency, but users can change the blocking of the table according to their needs. DM provides users with four kinds of table locks: Intent shared Lock (is:intensive SHARE), shared Lock (S:share), intent exclusive Lock (Ix:intensive EXCLUSIVE), and exclusive lock (x:exclusive). For example, under the Read Commit isolation level, the system default table lock is the IS or IX, under the two table locks, the tuple must be blocked before accessing the tuple, in order to improve the efficiency of the system, the user can manually x block the table, so that there is no need to block access to the tuple.
The blockade mechanism is designed to achieve the following:
(1) Consistency: When the user is viewing, the changed data is not fundamentally changed.
(2) Completeness: The basic structure of the database is guaranteed to accurately reflect all changes to them in the correct order.
A "lock" can be thought of as something that a process needs to prevent other processes from doing something, which "frees" the lock when the process is no longer concerned about it, and usually a lock is added to a "resource" (some object, such as a table).
The internal locking of the DM is done automatically. When a process wants to see an object but does not allow others to modify it, it gets a lock on how to share it. When a process modifies an object and prevents any other process from modifying it, it gets a lock on the update mode. An exclusive lock is obtained when a process modifies an object and prevents any other process from modifying it or blocking it in a shared manner.
2. Lock type
There are three types of locks in DM, table lock, row lock and key range lock.
Table lock
Table locks are used to block table objects, when the table is retrieved and updated, the DM will block the table object, but the DM provides users with a manual table lock statement, the user can change the blocking type of the table according to their needs. Table lock mode: Intent shared lock is, intent exclusive lock IX, shared lock S, exclusive lock X, a total of four, its compatibility matrix can be defined as the following table.
Row lock
Row lock block tuples, before accessing tuples and updating tuples, DM will be on the tuple uplink lock, the system does not provide a manual line blocking statement. There are two modes of a row lock: A shared lock (S), an exclusive lock (X), and a compatible matrix that defines the following table.
Key Range Lock
The key range lock is used on the serializable transaction, and the problem of phantom reading concurrency is mainly solved. A key-range lock overrides a single record and the range between records, preventing phantom insertions or deletions to the recordset accessed by the transaction. Key-range locks are only used for transactions that operate at the serializable isolation level.
Serializable requirements, if any of the queries are executed again at a later point in a transaction, they get the same rowset as the one obtained when the query was previously executed in the same transaction. If the row that this query attempts to fetch does not exist, the other transaction cannot insert the row until the transaction attempting to access the row is complete. If another transaction is allowed to insert the row, it appears as a phantom.
If another transaction attempts to insert a row that resides on a locked data page, page-level locking prevents phantom rows from being added and maintains serializable. However, if the row is to be added to a data page that is not locked by the first transaction, the locking mechanism should be set to prevent the row from being added.
Key-range locks work by overwriting the range between index rows and index rows (rather than locking the entire underlying table's rows). Because the second transaction needs to modify the index when it makes any row insert, update, or delete operations within that scope, the key-range lock overrides the index entry, so the second transaction is blocked until the first transaction completes.
The key-range lock is performed by the system itself, with the following conditions: (1) The Transaction isolation level is Serializable, and (2) the query results are derived from an index.
The lock will remain in effect until the end of the current transaction, and the lock is automatically dismissed by the system when the user is locked.
3. Locking type Comparison
4. SQL Statement Locking analysis
DM to all kinds of DDL and grant and other non-DML statements are broken down to add, delete, change. The following table is the DM blocking policy for various DML statements and query statements.
table:SQL statement blocking policy
Note: s* represents an instantaneous lock, which is released after the statement ends, and a range represents a key range lock.
The above table is only the system under normal processing, when the system detects the possibility of a lock escalation, the lock will be upgraded. In general, the is lock is upgraded to S lock, the IX lock is upgraded to X lock, and the line is no longer blocked.
5. Custom locking improves system efficiency
The DM also provides two function set_table_option ([DB.] [sch.]tablename, option, value], set_index_option ([DB.] IndexName, option, value) (see the Dm_sql Language Manual for the 8th chapter) for the user to define the type of locking, to enhance the system concurrency, improve system efficiency. These two functions are provided for expert users who clearly know what kind of lock a particular type is applicable to.
The function set_table_option () disables page-level locks, row-level locks, or both at the specified table, and this setting takes effect for all indexes on the table. The function set_index_option () is used to disable page-level locks, row-level locks, or both on an index.
For example, when a user only needs to modify a fixed-length field in the index, the modification does not result in a split and merge of the B-tree, and the page-level lock for that index can be disabled at that time. Also, when all users are only inserting, the user does not operate on the same tuple, and row-level locks can be disabled at this time. When the user can guarantee that the table does not increment, delete, change, but only when the query, you can also disable page-level and row-level locks on the table at this time, the highest degree of concurrency.
6. Deadlock Handling
Three ways to resolve deadlock problems: Prevent deadlocks, detect deadlocks, and avoid deadlocks. Deadlock prevention requires the user process to declare the required resources in advance or to request resources according to strict procedures, while deadlock detection should in principle allow deadlocks to occur, check at the appropriate time, and, if a deadlock occurs, try to exclude it. Compared with the prevention of deadlocks, the latter is too much to let go, resulting in frequent deadlocks. The avoidance of deadlocks is premised on transaction revocation, and when resource approval is not available, deadlock detection occurs immediately. It is not as overly conservative as the prevention of deadlocks, and not as much as the deadlock detection, because of the detection in time, by the induction method, in the approved waiting for the transaction, there is no deadlock, so the detection algorithm is relatively simple.
The DM4 system uses a method of avoiding deadlocks. A deadlock detection occurs whenever a resource requested by a firm is not immediately available, and the transaction is queued to wait for the deadlock to occur. Otherwise, DM4 is considered to produce a run-time error, and the current statement is rolled back. Using this mechanism, from the user's point of view, DM4 there is no unlocking problem.
7. Indexed and non-indexed blocking differences
Indexed and non-indexed, the DM blocking mechanism can affect the actual block range. The purpose of the index is to reduce the scanning of irrelevant data in the query. In the general isolation level, the scanned data is always blocked. Therefore, the use of indexes can reduce the number of closures, and the likelihood of conflict will be greatly reduced.
Principles of database Transaction management
