Concurrency control is the key technology of DBMS
Operations on a database are performed in a transaction.
A transaction is a set of interdependent action behaviors. An operation in a transaction is an indivisible unit of work, consisting of a set of SQL statements that depend on each other in the business logic, with acid characteristics.
Atomic (atomicity): The operations contained in a transaction are considered to be a logical unit, and the operations in this logical unit are either wholly successful or fail altogether.
Consistency (consistency): only legitimate data can be written to the database, otherwise the transaction should roll it back to its original state.
Isolation (Isolation): A transaction allows multiple users to have concurrent access to the same data without destroying the correctness and integrity of the data. At the same time, the modification of parallel transactions must be independent of the modification of other parallel transactions.
Durability (persistence): After the transaction ends, the result of the transaction must be cured.
The existence of multiple transactions in a database, which is transactional concurrency, does not guarantee transactional isolation, SQL-92 defines transaction isolation levels, describes the extent to which the behavior of a given transaction exposes other concurrent execution transactions, or the degree to which a transaction must be isolated from other transactions. The isolation level is from low to High:
Read Uncommitted,read committed,repeatable read, Serializable
The higher the isolation level, the greater the assurance of data integrity and consistency, but the greater the impact on concurrency performance.
Conflicts occur when concurrent transactions operate the same data and do not fully guarantee data integrity and consistency, and the following problems may occur:
Lost Update (First class loss update): Overwrites a transaction with updated data that has been submitted by other transactions.
Dirty reads (dirty read): One transaction reads the uncommitted update data for another transaction.
Phantom reads (virtual Read): A transaction reads a newly inserted data that has been committed by another transaction.
Non-repeatable reads (non-repeatable reads): One transaction reads the updated data that has been committed by another transaction.
Second Lost Updates problem (second class loss update): This is a special case in non-repeatable reads, one transaction overwrites updated data that has been committed by another transaction.
Transaction isolation levels and issues that may occur
The key to ensure the accuracy of concurrent transactions is to make the related transaction scheduling of conflicts serializable. Theoretically, a scheduling policy that prohibits the execution of other transactions at the time of a transaction must be serializable, which is the simplest scheduling strategy, but is not actually feasible because it prevents users from sharing database resources fully. At present, the DBMS generally adopts the blocking method to ensure the correctness of the transaction, that is to ensure the serialization of parallel operation scheduling. In addition, there are some other methods, such as the time standard method, optimistic method and so on.
Pessimistic concurrency control: Locking the system prevents users from modifying data in a way that affects other users. If a user performs an action that causes a lock to be applied, the other user cannot perform the operation that conflicts with the lock until the lock's owner releases the lock. This method is mainly used in the environment of intense data contention, and the cost of protecting data with lock is lower than the cost of rollback transaction when concurrent conflict occurs, so this method is called pessimistic concurrency control.
Optimistic concurrency control: in optimistic concurrency control, users do not lock data when reading data. When you perform an update, the system checks to see if another user has changed the data after reading 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 again. This approach is mainly used in environments where data contention is low, and when the cost of occasionally rolling back transactions exceeds the cost of locking data while reading data, the method is called optimistic concurrency control.
Time-scale concurrency control: The basic difference between the time scale and the blocking technology is that the blockade is the simultaneous execution of a set of transactions (i.e., cross execution). Using it is equivalent to a serial operation of these transactions; The time scale method also uses a set of transactions to perform a cross synchronization, but makes it equivalent to a specific serial execution of these transactions, An execution determined by the timing of the time scale. If a conflict occurs, it is resolved by undoing and restarting a transaction. A transaction restarts, the new time scale is assigned.
The simple locking protocol solves the problem of concurrent control, such as read-write conflict and write-write conflict, and has a great influence on concurrent transaction. So in practice Postgresql,mysql Innodb,oracle,sql Server (can be set, default shutdown), Informix and other databases also use multiple version concurrency control (MVCC), MVCC can avoid read-write conflicts, This is done by using the lock to handle the related transaction of write-write conflicts.
The following section continues the specific database locking mechanism and the implementation of MVCC.
-----------------
Reprint please the Source:
Blog.csdn.net/beiigang
