Sword refers to architect series-innodb storage engine, spring transaction and cache

Transactions are different from locks. Transactions have ACID properties:

Atomicity: Persistence: Redo log Redo logs to ensure the atomicity and durability of transactions,
Consistency: Undo log is used to ensure transactional consistency
Isolation: A transaction sees the results of other transactions, such as Phantom reads, during the operation. Locks are a mechanism for resolving isolation. The isolation level of a transaction is implemented through a locking mechanism.

The transaction isolation level for the database is (in the case of multiple transactions concurrency):

1. READ UNCOMMITTED

#首先, modify the isolation level set tx_isolation= ' read-uncommitted '; select @ @tx_isolation; +------------------+| @ @tx_isolation |+------------------+| read-uncommitted |+------------------+ #事务A: Start a transaction start Transaction;select * FROM tx;+------+------+| ID |    Num |+------+------+|    1 |    1 | |    2 |    2 | |    3 |  3 |+------+------+ #事务B: Initiates a transaction (then two transactions intersect) executes the UPDATE statement in transaction B without committing the start transaction;update TX set num=10 where Id=1;select * From tx;+------+------+| ID |    Num |+------+------+|   1 |    10 | |    2 |    2 | |    3 | 3 |+------+------+ #事务A: So at this point does transaction a see the updated data? SELECT * FROM tx;+------+------+| ID |    Num |+------+------+|   1 |   10 | ---> Can see!    It means that we have read the data that transaction B has not yet submitted |    2 |    2 | |    3 | 3 |+------+------+ #事务B: Transaction b rollback, still uncommitted rollback;select * FROM tx;+------+------+| ID |    Num |+------+------+|    1 |    1 | |    2 |    2 | |    3 | 3 |+------+------+ #事务A: What you see in transaction A is also B data not submitted SELECT * FROM tx;+------+------+| ID |    Num |+------+------+|    1 |      1 | ---> Dirty reads mean I'm in this business (a), and transaction B, thoughWithout committing but it any one of the data changes I can see!|    2 |    2 | |    3 | 3 |+------+------+

　

2. Read Committed

#首先修改隔离级别set tx_isolation= ' read-committed '; select @ @tx_isolation; +----------------+| @ @tx_isolation |+----------------+| read-committed |+----------------+ #事务A: Start a transaction start Transaction;select * FROM tx;+------+------+| ID |    Num |+------+------+|    1 |    1 | |    2 |    2 | |    3 | 3 |+------+------+ #事务B: A transaction is also started (then two transactions are crossed) and the data is updated in this transaction, and the start transaction;update TX set num=10 where Id=1;select * from is not committed tx;+------+------+| ID |    Num |+------+------+|   1 |    10 | |    2 |    2 | |    3 | 3 |+------+------+ #事务A: Can we see the data change in transaction A at this time? SELECT * FROM TX; ------------->+------+------+ | | ID |                num |    |+------+------+                ||    1 |    1 |---> Can't see! | |    2 |                2 |    ||    3 |                3 |        |+------+------+ |--> The same SELECT statement, but the result is different | #事务B: What if transaction B is committed?                        |commit;                               | | #事务A: |select * from TX; ------------->+------+------+| ID |    Num |+------+------+|   1 |    ---> Because transaction B has already been submitted, we see data changes in a |    2 |    2 | |    3 | 3 |+------+------+

　　

3. Repeatable READ

#首先, change the isolation level set tx_isolation= ' Repeatable-read '; select @ @tx_isolation; +-----------------+| @ @tx_isolation  |+-----------------+| Repeatable-read |+-----------------+ #事务A: Start a transaction start Transaction;select * FROM tx;+------+------+| ID   | num  |+------+------+|    1 |    1 | |    2 |    2 | |    3 |    3 |+------+------+ #事务B: Open a new transaction (then the two transactions intersect) update the data in transaction B and submit the start Transaction;update TX set num=10 where Id=1;select * from t x;+------+------+| ID   | num  |+------+------+|    1 |   Ten | |    2 |    2 | |    3 |    3 |+------+------+commit; #事务A: Even if transaction B is already committed, can a see the data change? SELECT * FROM tx;+------+------+| ID   | num  |+------+------+|    1 |    1 | ---> Can not be seen! (This level 2 is different, also indicates that level 3 resolves non-repeatable read issues) |    2 |    2 | |    3 |    3 |+------+------+ #事务A: Only if transaction A is committed, can it see data changes Commit;select * from tx;+------+------+| ID   | num  |+------+------+|    1 |   Ten | |    2 |    2 | |    3 |    3 |+------+------+

　　

4, Serializable

#首先修改隔离界别set tx_isolation= ' serializable '; select @ @tx_isolation; +----------------+| @ @tx_isolation |+----------------+| SERIALIZABLE   |+----------------+ #事务A: Starts a new transaction start transaction; #事务B: This cross-transaction is the start of the data that cannot be changed before a commit Transaction;insert TX VALUES (' 4 ', ' 4 '); ERROR 1205 (HY000): Lock wait timeout exceeded; Try restarting Transactionupdate TX set num=10 where id=1; ERROR 1205 (HY000): Lock wait timeout exceeded; Try restarting transaction

　　

To summarize:

√: May appear x: does not appear

Transaction ISOLATION level	dirty read    transaction 1 updated the record, but no commit, transaction 2 reads the updated row, then the transaction T1 Rollback, and now the T2 read is invalid. Violation of isolation caused by the addition of row lock implementation	non-repeatable read    transaction 1 read records, transaction 2 updated records and commits, transaction 1 read again can see the transaction 2 modified record (Modify batch update or delete) need to add row lock for implementation	Phantom Read     transaction 1 reads records when transaction 2 adds a record and commits, and transaction 1 reads again to see the new record for transaction 2. Table locks need to be added for implementation. The InnoDB storage engine solves the problem by using the multi-version concurrency control (mvcc,multiversion Concurrency) mechanism
Read uncommitted& nbsp;	Yes	Yes	√
Read Co mmitted	x	√	√
Repeatable Read	x	x	√
Serializable	x	x< /strong>	x

Note: (1) to distinguish between non-repeatable reading and the difference between Phantom read one is to update the record, the other is to read the new record (2) different database storage engine is not strictly according to the standards, such as INNODB default repeatable Read isolation level can be done to avoid the problem of phantom reading (using the Next-key-lock lock algorithm). The InnoDB and Falcon storage engines address this issue through a multi-version concurrency control (mvcc,multiversion Concurrency control) mechanism.

Corresponds to 5 transaction isolation levels in spring (specified by Lsolation property values)

1. Default transaction ISOLATION level. Using the default transaction isolation level for the database

2, read_uncommitted read UNCOMMITTED, one transaction can operate another uncommitted transaction, cannot avoid dirty read, non-repeatable read, Phantom read, the lowest isolation level, the highest concurrency performance

3, read_committed (dirty read) Most databases default transaction isolation level. Read Committed, one transaction can not operate another uncommitted transaction, can prevent dirty read, cannot avoid non-repeatable read, Phantom read

4, Repeatable_read (non-repeatable read) InnoDB The default transaction isolation level. Be able to avoid dirty reading, non-repeatable reading, cannot avoid phantom reading

5. Serializable (Phantom Read) The InnoDB storage engine can have distributed XA transaction support at this level. Highest isolation level, lowest resource consumption, highest cost, ability to prevent dirty reads, non-repeatable reads, Phantom reads

The transaction in spring is completely based on the transaction of the database, and if the database engine uses the MyISAM engine, the spring transaction does not actually work. In addition, the transaction-related characteristics that spring provides to the developer are the propagation behavior of the transaction, as follows:

Transaction propagation Behavior Type	Description
Propagation_required	If there is no current transaction, create a new transaction, if one is already present, join the transaction. This is the most common choice (spring default transaction propagation behavior)
Propagation_supports	Supports the current transaction, if no transaction is currently in use and is executed in a non-transactional manner
Propagation_mandatory (Managed)	Throws an exception with the current transaction, if there is no current transaction
Propagation_requireds_new	New transaction, suspending the current transaction if a transaction is currently present
propagation_not_supported	Performs an operation in a non-transactional manner, suspending the current transaction if a transaction is currently present
Propagation_never	Executes in a non-transactional manner, throwing an exception if a transaction is currently present
propagation_nested	Executes within a nested transaction if the transaction is currently present. If there is currently no transaction, perform an operation similar to propagation_required, that is, create a new transaction

Spring controls how the current transaction propagates to the target service interface method of the nested call through the transaction propagation behavior.

Spring can configure the properties of a transaction, but the isolation level, read-Write transaction properties, time-out and rollback settings are all given to JDBC, and only the transactional propagation behavior is truly self-fulfilling. So when does the propagation of a transaction occur?

public class Forumservice {private UserService userservice, @Transactional (propagation = propagation.required) public void Addtopic () {//Add topicthis.updatetopic (); Userservice.addcredits ();} @Transactional (propagation = propagation.required) public void Updatetopic () {//Add topic}public void Setuserservice ( UserService userservice) {this.userservice = UserService;}}

Take a look at the Addcredits () method in UserService, as follows:

public class UserService {@Transactional (propagation = propagation.requires_new) public void Addcredits () {}}

Then test under:

Forumservice.addtopic ();

After the debug mode of the Spring4 log is turned on, the output is as follows:

　

 -Returning cached instance of singleton Bean ' Txmanager '-Creating new transaction with name [Com.baobaotao.service.For Umservice.addtopic]: Propagation_required,isolation_default; "-Acquired Connection [[email protected]] for JDBC transaction-switching jdbc Connection [[email protected] ] to manual commit-suspending-transaction, creating new transaction with Name [Com.baobaotao.service.UserService . Addcredits]-Acquired Connection [[email protected]] for JDBC transaction-switching JDBC Connection [[email  Protected]] to manual commit-initiating transaction commit-committing JDBC transaction on Connection [[Email prot  Ected]]-releasing JDBC Connection [[email protected]] after transaction-returning JDBC Connection to DataSource- Resuming suspended transaction after completion of inner transaction-initiating transaction commit-committing JDBC TR Ansaction on Connection [[email protected]]-releasing JDBC Connection [[email&Nbsp;protected]] After transaction-returning JDBC Connection to DataSource 

Clearly see the call to the Addcredis () method when a new transaction is created, and when the Addcredits () method is called in this method, because the transaction propagation behavior of this method is progation_required_new, the current thread is suspended, A new thread is created. But for the This.updatetopic () method call, the

The transaction for the method is still propagation_required, so it executes in the current thread transaction.

In the use of transactions we need to avoid deadlocks as much as possible, to minimize blocking, according to the different database design and performance requirements of the required isolation level is the most appropriate. Special attention needs to be paid to the specific following:

A, the transaction operation process to be as small as possible, can split the transaction to separate

B, the transaction operation process should not have interaction (System interaction, interface invocation), because the interaction waits, the transaction does not end, may lock a lot of resources

C, the transaction operation procedure to access the object in the same order. (Avoid the deadlock situation to produce)

D, improve the efficiency of each statement in the transaction, using indexes and other methods to improve the efficiency of each statement can effectively reduce the execution time of the whole transaction.

E, the query can use a lower isolation level, especially when the report query, you can choose the lowest isolation level (READ UNCOMMITTED).

Sword refers to architect series-innodb storage engine, spring transaction and cache