Transaction ACID, transactionacid

Transaction ACID (reproduced), transactionacid
TransactionSource: Huang Yong

TransactionThat is, the so-called transactions. A general understanding is one thing. When we were young, our parents taught us how to do things from beginning to end. The same is true for transactions 0 and 2. If you do not do this, you will not do it. That is to say, a transaction must be an inseparable whole, just like the atom we learned in chemistry, which is the smallest unit of matter. As a result, people will summarize the first feature of transactions:Atomicity). It's no secret to me.

Especially in the field of databases, transactions are a very important concept. Apart from atomicity, transactions also have an extremely important feature:Consistency). That is to say, after the database operation is completed, the data will not be damaged. For example, if you transfer money from account A to account B, it is impossible for account A to deduct the money, but account B does not add the money. If this happens, you will be very angry. What about diao!

When we write an update statement and submit it to the database in an instant, someone else may also submit a delete statement to the database. Maybe we all operate on the same record. As you can imagine, if we don't control it a little, it will cause a lot of trouble. We must ensure that database operations are "isolated" (sometimes between threads) without interfering with each other. This is:Isolation).

It is very difficult to realize that there is no interference between operations. Therefore, database experts who work on soy sauce every day start to use their brains. "We need to develop a specification, let every database vendor support our specifications! ", This specification is:ThingsService Isolation level (TransactionIsolation Level ).It is really not easy to define such an awesome specification. In fact, there are four levels:

Never translate. It's just a code. From top to bottom, the level is getting higher and higher, the concurrency is getting worse, and the security is getting higher and higher, and vice versa.

When we execute an insert statement, the database must ensure that a piece of data is permanently stored in the disk. This is also a feature of the transaction. It is:Durability).

To sum up, the above mentioned four features of transactions are mentioned. The first letter of their English words is ACID, which is the legendary "ACID feature of transactions "!

It's really amazing! These four features are the cornerstone of transaction management and must be thoroughly understood. In addition, it should be clear who is the boss among the four guys?

In fact, it is clear that Atomicity is the foundation, isolation is the means, persistence is the purpose, and the true boss is consistency. If the data is inconsistent, it is equivalent to "the rivers and lakes are messy, and the hooligans wear bras ". Therefore, the three younger siblings are following the "consistency" boss and serve him wholeheartedly.

In fact, the most difficult thing to understand is not consistency, but isolation. Because it is an important means to ensure consistency, it is a tool, it cannot have a half-difference pool, otherwise the consequences are at your own risk! No wonder experts in the database industry have to study the so-called transaction isolation level. In fact, the four levels are defined to solve the problems caused by high concurrency of data. What are the problems?

First, let's look at "Dirty reading". When I see the word "dirty", I think of disgusting and dirty. How can the data be dirty? This is what we often call "junk data. For example, two transactions are executed concurrently (that is, competition ). Take a look at the following table and you will understand what I'm talking about:

Time	Transaction A (deposit)	Transaction B (withdrawal)
T1	Start transaction
T2		Start transaction
T3		Query balance (1000 RMB)
T4		Retrieve 1000 RMB (balance 0 RMB)
T5	Query balance (0 RMB)
T6		Cancel the transaction (the balance is restored to 1000 RMB)
T7	Deposit 500 yuan (balance 500 yuan)
T8	Commit transactions

The balance should be RMB 1500! Please refer to the T5 time point. Transaction A's query balance is 0 yuan at this time. This data is dirty data, which is caused by transaction B. Obviously, the transaction is not isolated and infiltrated, it's messy.

Therefore, dirty reading is very undesirable and must be solved! Isolation between transactions is the final principle.

How can we explain the 2nd records that cannot be read repeatedly? A similar example is used to describe:

Time	Transaction A (deposit)	Transaction B (withdrawal)
T1	Start transaction
T2		Start transaction
T3		Query balance (1000 RMB)
T4	Query balance (1000 RMB)
T5		Retrieve 1000 RMB (balance 0 RMB)
T6		Commit transactions
T7	Query balance (0 RMB)

In fact, except for two queries, transaction A did not do anything, and the result of the money was changed from 1000 to 0, which is repeated reading. As you can imagine, this is what others did, not what I did. In fact, this is also reasonable. After all, transaction B committed the transaction, and the database made the result persistent, so transaction A's re-reading naturally changed.

This phenomenon is basically understandable, but it is not allowed in some abnormal scenarios. After all, this phenomenon is also caused by the absence of isolation between transactions, but we seem to be able to ignore this problem.

The last one is Phantom read. I am going! Isn't Phantom a "ghost or ghost? When I saw this word, I was really stunned by my little brother. No wonder this is about to be translated into "phantom reading". It cannot be translated into "ghost reading" or "ghost reading. In fact, it means that the ghost is reading, not reading, or figuring out why. It changes, dizzy, and dizzy. Let's use an example:

Time	Transaction A (count total deposits)	Transaction B (deposit)
T1	Start transaction
T2		Start transaction
T3	Total deposits (10000 RMB)
T4		Deposit 100 RMB
T5		Commit transactions
T6	Total deposits (10100 RMB)

The bank staff can see different results every time they count the total deposits. However, this is indeed quite normal, and the total number of deposits has increased. It must be because someone is saving money at this time. However, if the banking system is designed in this way, it will be over. This is also caused by the absence of isolation of transactions, but it seems normal for most application systems. It is understandable and acceptable. The disgusting systems in the bank have very strict requirements. During statistics, they may even isolate all other operations, this isolation level is very high (it is estimated that it will reach the SERIALIZABLE level ).

To sum up, the above mentioned problems related to data reading caused by transaction concurrency are described in one sentence:

The first is a strong boycott, and the last two are not considered in most cases.

This is why there must be a transaction isolation level. It isolates different transactions like a wall. The following table shows how different transaction isolation levels can handle transaction concurrency:

Transaction isolation level	Dirty read	Non-repeated read	Phantom read
READ_UNCOMMITTED	Allow	Allow	Allow
READ_COMMITTED	Disable	Allow	Allow
REPEATABLE_READ	Disable	Disable	Allow
SERIALIZABLE	Disable	Disable	Disable

Based on your actual needs, refer to this table and finally determine the transaction isolation level. It is no longer difficult.

JDBC also provides these four transaction isolation levels, but the default transaction isolation level is different for different database products. The default transaction isolation level of the well-known MySQL database is "0" 2READ_COMMITTED. Oracle, SQL Server, DB2, and so on all have their own default values. I think READ_COMMITTED can solve the vast majority of problems. For more information, see the specific analysis.

If you are not clear about the default transaction isolation level of other databases, you can use the following code:

MySQL

12	DatabaseMetaData meta = DBUtil. getDataSource (). getConnection (). getMetaData (); int defaultIsolation = meta. getdefadefatransactionisolation ();

 

Tip: You can view all isolation levels in the java. SQL. Connection class.

We know that JDBC is only a bridge between Java programs and databases. How does the database isolate transactions? In fact, it is the "Lock" thing. When data is inserted, the table is locked. This is called the "Lock table". When data is updated, the row is locked. This is called the "Lock row ". Of course, this is beyond the scope of our discussion today, so leave some space for our DBA, so that he will not be able to write well.

In addition to the transaction isolation level solution provided by JDBC, what other solutions can be used to improve the transaction management function?

Let's take a look at the Spring solution. In fact, it is a supplement or extension to JDBC. It provides a very important function:Transaction Propagation Behavior (TransactionPropagation Behavior).

It was really awesome. Spring provided seven kinds of transaction propagation behaviors at once. Once these seven behaviors appeared, they really caught my eye!

After reading the 0-2 Spring reference manual, I was dizzy. What is it doing?

The first thing to be clear is, where does the transaction come from? Where to spread it? The answer is, from method A to method B. Spring only solves the problem of transaction propagation between methods, which is more often, for example:

There are four special cases. Let's use my Style to give you an analysis:

Assume that the transaction is propagated from method A to Method B. You need to face method B and ask yourself A question:

Method A has A transaction?

Seeing my above explanation, do my friends already feel the feeling of being connected to the second pulse of Ren du? Read it several times and try to understand that it is your own thing.

Note that "0" 2PROPAGATION_NESTED should not be spoofed by its name, Nested (Nested). So when method A calls Method B, this transaction Propagation Behavior is used on Method B. If you do that, you will be wrong. Because you mistakenly think that 0 2PROPAGATION_NESTED is prepared for nested method calls, in fact, the default 0 2PROPAGATION_REQUIRED can help you do what you want to do.

Spring brings us transaction Propagation Behavior, which is indeed a very powerful and practical function. In addition, some small additional functions are provided, such:

Finally, we recommend that you use Spring's annotation-type transaction configuration instead of XML-type transaction configuration. Because the annotations are so elegant, of course all of this depends on your own situation.

Use the following in the Spring configuration file:

MySQL

123	... <Tx: annotation-driven/>...

 

Use the following methods to handle transactions:

MySQL

1234	Transactionalpublic void xxx (){...}

 

You can set the transaction isolation level, transaction Propagation Behavior, transaction timeout, and whether to read-only transactions in the 0 @ Transactional annotation.