On the isolation of database transaction

Write in front

Nearly two years of distributed database technology to accelerate the development, and because of the financial industry technology ecological constraints, many students around it does not have in-depth understanding, so high-performance, highly reliable system design often lack of this tool. Ivan hopes to communicate with you in a series of articles to deepen our understanding of the distributed database. This article is the first article of this series, mainly discusses the isolation in the transaction management, clarify the related concepts and key technologies, for the later elaboration of distributed database transaction management to do a cushion, let's be a forward pass it.

Body

We start with the definition, the transaction management includes four aspects of atomicity, consistency, isolation and persistence, namely acid. All the database monographs will give the definition of the four features, this article we cite the definition of Jim Gray. Jim Gray is a great God in transaction processing, many of which are quoted from his papers and monographs. To avoid the ambiguity introduced in the translation process, we use the original text directly.

Jim Gray is a master of transaction processing, and much of this article comes from his monographs and essays. In order to avoid the ambiguity introduced by translation, we refer directly to the original text here.

atomicity: Either all the changes from the transaction occur (writes, and messages sent), or none occur.
consistency: The transaction preserves the integrity of stored information.
isolation: concurrently executing transactions see the stored information as if they were running serially (o NE after another).
Durability: Once A transaction commits, the changes it made (writes and messages sent) survive any system Failures.

In the definition of the above isolation (isolation), we can find that the goal is to make the execution of concurrent transactions consistent with the serial, but in the concrete technology real

In the above-mentioned isolation (isolation) definition, we can find that the goal is to make concurrent transaction execution effect and serial consistency, but in the specific technical implementation often need to balance the concurrency capability and serialization effect, it is difficult to both. The result of the balance is that there is a phenomenon that violates the serial effect, which is abnormal phenomenon (phenomenon). Generally speaking, the elevation of isolation level is negatively correlated with the decrease in concurrency. Various databases refer to the ANSI SQL-92 standard isolation level when it comes to isolation levels, so let's take a look at its specifics.

ANSI SQL-92 Isolation Levels

ANSI SQL-92 may be the first to propose a method based on the anomaly to define the isolation level, while not binding the isolation level to the implementation mechanism, the implementation of the isolation can be based on lock (lock-based) or no lock (Lock-free), compatible with the subsequent technical development. The standard defines isolation as four levels based on three anomalies, as follows.

Dirty reads , the data items modified in the transaction (T1) are read by other transactions (T2) without being committed, and T1 does rollback operations, the data that the T2 just read does not actually exist.
non-repeatable read , T1 read data item, T2 the data in it was modified or deleted and commit succeeded. If T1 attempts to read the data again, it will get T2 modified data or the discovery data has been deleted. This T1 reads the same conditions two times in a transaction, and the result set content changes or the number of result sets is reduced.
Phantom Read , T1 uses a specific query condition to get a result set, T2 inserts new data and that data conforms to the query criteria that T2 just manipulated. T2 commit succeeds, T1 executes the same query again, resulting in an increase in the result set.

Many articles are combined with database products on the above exception phenomenon of the examples and processing mechanisms are described, this article will not repeat, interested students can refer to the end of the link [1].

The ANSI SQL-92 standard was released as early as 92, but both at the time and later were not strictly followed by major database vendors, partly because the standards were too simplistic and somewhat detached from practical applications. Jim Gray and others in 1995 published the paper "A Critique of ANSI SQL isolation Levels" (referred to in this article as critique[2]) to more comprehensive elaboration of the isolation level, can help us deepen understanding.

Critique Isolation Levels

Critique puts forward two problems in the existence of ANSI SQL-92, the first is that the anomaly phenomenon defined by natural language method is not strictly leading to the omission of some homogeneous anomalies; Secondly, some typical anomalies are not covered, resulting in a significant lack of isolation levels. As a result, three anomalies of ANSI SQL-92 (numbered as A1/A2/A3) have been extended (number P1/p2/p3), and 5 other common anomalies have been added. Only two anomalies are described here, limited by space.

Lost Update

Missing update (Lost update) is a classic database problem, because it is too important for all major databases to solve this problem, we will take a little deformation to illustrate here.

We use MySQL to demonstrate, create tables and initialize data

createtableaccountintvarchar(20)) ENGINE=InnoDB;insertintoaccountvalues(50,‘Tom‘);

T1	T2
begin;	begin;
Select balance into @bal from account where name= ' Tom ' ------------------ -- @bal =
	Select balance into @b Al from account where name= ' Tom ' ------------------- @bal = a
	Update account Set balance = @bal -40 where name = ' Tom ';
	commit;
Update account Set balance = @bal-1 WHERE name = ' Tom ';
commit;

In the above Operation T1, T2 serial execution effect is the balance of two deductions, respectively 40 and 1, the final value of 9, but the final value of the parallel to 49,t2 is lost. We can find that the essence of the lost update is that the T1 transaction reads the data, then the data is modified and submitted T2 transaction, and T1 is modified again based on the data that has expired, resulting in T2 modifications being overwritten.

Read Skew

Read Skew is an issue that is encountered at the RC level. If the data item X has a consistency constraint with Y, T1 reads x First, then T2 modifies X and Y after commit, and T1 reads y again. The T1 obtained x and y do not satisfy the original consistency constraint.

MySQL default isolation level is RR, we need to manually set to RC and initialize the data

setsessiontransactionisolationlevelreadcommitted;insertintoaccountvalues(70,‘Tom‘);insertintoaccountvalues(30,‘Kevin‘);

T1	T2
begin;	begin;
select * from account where name=’Tom’; --------------------- balance name 70 Tom
	select * from account where name=’Tom’; --------------------- balance name 70 Tom
	update account set balance = balance - 30 where name=‘Tom‘;
	update account set balance = balance + 30 where name=’Kevin’;
	commit;
select * from account where name=‘Kevin‘; --------------------- balance name 60 Kevin
commit;

Initial data The total of Tom and Kevin's accounts is 100, and two reads within the T1 transaction have an account total of 130, which clearly does not conform to the previous consistency constraints.

After adding these anomalies, critique gives a new matrix, which is more perfect than ANSI and is more fit for real database products.

The mainstream database takes into account the balance between serialization and concurrency, with the general default isolation level between RC and RR, partially providing serializable. Specifically, the isolation level of Asni SQL-92 or critique does not ensure that the isolation level is mapped directly to the actual database with the same name.

Si&mvcc

Snapshot Isolation (Si,snapshot isolation) is a common term for discussing isolation and can be interpreted in two ways, one of which is the specific isolation level that SQL Server and cockroachdb directly define. The second is an isolation mechanism for implementing the corresponding isolation level, which is commonly used in mainstream databases such as Oracle, MySQL InnoDB, PostgreSQL, and so on. Multi-version concurrency control (mvcc,multiversion concurrency controls) improves the concurrency of the system in dealing with multi-transaction access by recording the historical version of the data item. For example, avoid single-value (single-valued) storage cases where write operations are locked for read operations. MVCC and locks are important means of implementation of SI, of course, there is no lock SI implementation. The following is the SI operating process described by critique.

Transactions (recorded as T1) begin with a timestamp of start Timestamp (recorded as St), and each historical version of all data items in the database is recorded with the corresponding timestamp commit Timestamp (recorded as CT). The snapshot read by T1 is made up of all data item versions that are less than St and the most recent historical version, since these data items are only historical versions that will not be locked again by write operations, so there will be no read and write conflicts, and reads in the snapshot will never be blocked. Other transactions after the St modification, T1 is not visible. When the T1 commit instantly obtains a CT and is guaranteed to be greater than any timestamp (ST or CT) that already exists in the database at the moment, the CT will be used as the version timestamp of the data item when persisted. The T1 write operation is also reflected in the T1 snapshot, which can be read again by the read operation within the T1. When T1 commits, the modifications will be visible to those transactions that hold St greater than T1 Ct.
If there are other transactions (T2), its CT in the T1 run interval between "St,ct", and T1 to the same data items write, T1 Abort,t2 commit succeeds, this feature is known as first-committer-wins, can be guaranteed not to appear lost Update In fact, some databases will be tuned to first-write-wins to reduce the cost of conflict by advancing conflict judgments to write operations.

This process is not a specific implementation of a database, in fact, there are different databases for SI implementation There are very large differences. For example, PostgreSQL will save the historical and current versions with a timestamp distinction, while both MySQL and Oracle Save the historical version in the rollback segment. Both the RC and RR levels of MySQL use Si, and if the data for the current transaction (T1) read operation is locked by the write operation of the other transaction, the T1 turns to the rollback segment to read the snapshot data to avoid blocking the read operation. However, the snapshot definition of RC is different from the above description, and also includes the latest version of other transaction submissions during T1 execution [6].

In addition, we have an important discovery that time stamping is a key element in generating Si. In a stand-alone system, the unique timestamp is easier to implement, and for distributed systems in the case of cross-node, cross-datacenter or even cross-city deployment, how to establish a one-1: is a very complex issue, we leave the next foreshadowing will be discussed in the following feature article.

Serializable VS SSI

Si is so effective that even in the TPC-C benchmark test there is no abnormal phenomenon [5], but in fact SI does not guarantee the complete serialization effect. Critique points out that Si is not yet able to handle a5b (write Skew, writing partial order) as shown in.

Write Skew

Write Skew is also the exception under the consistency constraint, that is, two parallel transactions are based on the data set they read to cover another part of the data set, in the case of serialization two transactions in whatever order, the final will reach a consistent state, but the SI isolation level can not be achieved. "Black and white balls" are often used to illustrate the problem of write-off.

How do you achieve real serialization effects? In fact, the early databases have been fully serialized isolated (Serializable isolation) through the strict two-phase lock protocol (s2pl,strict two-phase Locking), which is the data blocking corresponding write operation for the read operation, The write operation blocks all operations, including read and write operations. If blocking causes loops to form a deadlock, a rollback operation is required. The problem with S2PL is obvious, in a competitive scenario where congestion and deadlock can cause database throughput degradation and response time to increase, so this serialization cannot be applied to the actual production environment. Until the advent of SSI, people have finally found a serialization isolation scheme with real value.

Serialization Snapshot Isolation (SSI, Serializable Snapshot isolation, also translated as a serialized snapshot) is based on an SI improvement to achieve Serializable level of isolation. SSI was presented by Michael James Cahill in his paper "Serializable Isolation for Snapshot Databases" [3] (the paper obtained the SIGMOD Best Paper Award, The article at the end of this paper provides 2009 full version [4] related information, interested students can be in-depth study). SSI retains many of the advantages of SI, especially the read does not block any operation, and the write does not block read. Transactions are still running in snapshots, but increased monitoring of read and write conflicts between transactions is used to identify dangerous structures in the transaction graph (transaction graph). When a set of concurrent transactions can produce an exception (anomaly), the system will intervene to eliminate the possibility of anomaly occurring by rolling back some of these transactions. This process may cause errors in some transactions to be rolled back (without causing anomaly transactions to be killed), but it can be ensured that anomaly[3] is eliminated.

From the theoretical model, SSI's performance is close to Si, which is much better than S2PL. In 2012, PostgreSQL implemented ssi[7 in version 9.1], and may also be the first business database to support SSI, verifying the implementation of SSI. Cockroachdb also gained inspiration from Cahill's paper, implementing SSI as its default isolation level.

With the development of technology, SI/SSI has become the main database of the isolation technology, especially the latter, without developers in the code through explicit locking to avoid exceptions, thereby reducing the probability of human error. In the relevant section of the distributed database, we will further explore the SSI implementation mechanism.

References
[transaction isolation level and lock relationships in 1]innodb, ameng,https://tech.meituan.com/innodb-lock.html
[2]h. Berenson, p. Bernstein , J. Gray, J.melton, E. o ' Neil,and P. O ' Neil. A Critique of ANSI SQL isolation levels. Inproceedings of the Sigmod International Conference on Management of Data, Pages1–10, May 1995.
[3]michael J. Cahill, Uwe R?hm, and Alan d.fekete. Serializable isolation for snapshot databases. In Sigmod ' 08:proceedings of the $ ACM SIGMOD International Conference on Management of data, pages 729–738, New York, NY, USA. Acm.
[4]michael James Cahill. Serializable isolation for Snapshot Databases. Sydney Digital theses. University of Sydney, School of Information Technologies
[5] A. Fekete, D. Liarokapis, E. O ' Neil, P.O ' Neil, andd. Shasha. Making Snapshot Isolation serializable. In ACM transactions on Database Systems, Volume (2), pages 492–528, June 2005.
[6] Kang, MySQL technology insider: InnoDB Storage engine, mechanical industry press, [7]https://wiki.postgresql.org/wiki/serializable

Talking about database transaction isolation