MySQL--The transaction isolation level and the steps for transaction submission in Java

The SQL standard defines four isolation levels, with improper use of different levels of isolation, with dirty reads, non-repeatable reads, and Phantom reads, and the presence of isolation levels is a balance between transaction efficiency and security.

• Problems that occur with different isolation levels

Dirty Reads (drity read): A transaction has updated one copy of the data, another transaction reads the same data at this time, for some reason, the previous rollback operation, the latter will read the data is not correct.
Non-repeatable read (non-repeatable Read): Data inconsistency in two queries for a transaction, which may be the original data that was inserted in the middle of a transaction update during the two query process.
Phantom Read (Phantom Read): In a transaction two times the number of data pens inconsistent, for example, one transaction queried several columns (row) of data, while another transaction at this time inserted a new column of data, the previous transaction in the next query, you will find that there are a few columns of data that it did not previously.

The difference between non-repeatable reads and Phantom reads is that the data in the same column is changed, and the latter is how many columns in the table change.

• Isolation levels and issues addressed

READ UNCOMMITTED (Read UNCOMMITTED content)
At this isolation level, all transactions can see the execution results of other uncommitted transactions. This isolation level is rarely used in real-world applications because it has no better performance than other levels. Reading uncommitted data is also known as Dirty reading (Dirty read).

Read Committed (read submit content)
This is the default isolation level for most database systems (but not MySQL default). It satisfies the simple definition of isolation: A transaction can only see changes that have been submitted to the firm. This isolation level also supports so-called non-repeatable reads (nonrepeatable read), because other instances of the same transaction may have new commits during the instance processing, so the same select may return different results.

Repeatable Read (can be reread)
This is the default transaction isolation level for MySQL, which ensures that multiple instances of the same transaction will see the same rows of data while concurrently reading the data. In theory, however, this can lead to another tricky problem: Phantom Reading (Phantom read). To put it simply, Phantom reads when a user reads a range of data rows, another transaction inserts a new row within that range, and when the user reads the data row of that range, a new phantom row is found. The InnoDB and Falcon storage engines address this issue through a multi-version concurrency control (mvcc,multiversion Concurrency control) mechanism.

Serializable (Serializable)
This is the highest isolation level, which solves the Phantom reading problem by forcing transactions to sort, making it impossible to conflict with one another. In short, it is a shared lock on every data row read. At this level, a large number of timeouts and lock competitions can result.
　　

• Scenarios and ideas for using transactions in Java

Some special processing of the database, such as multi-step processing (Business requirements intermediate steps can not be interrupted), we need to unify a series of operations as a whole, or all succeed, or all do not execute. At this point, you need to set up the transaction in your code, set a series of actions to a transactional commit, and roll back when an exception occurs, depending on your business needs. The idea is that the auto-commit is set to false first, then the business processing, the final overall commit, the catch exception set the rollback operation. Here's a demonstration with org.apache.commons.dbutils.DbUtils:

        /*** Management of things*/Connection Conn=NULL; Try{conn=dbutiltest.getconnection (); Conn.setautocommit (false); Qr2.update (Conn,"Delete from Chiq3huanjson");  for(Jsonobject jsonobject:list) {qr2.update (conn,"INSERT into Chiq3huanjson (Name,code,logicnumber,pushbatch) VALUES (?,?,?,?)", Jsonobject.get ("Name"), Jsonobject.get ("code"), Jsonobject.get ("Logicnumber")), UUID);            } dbutils.commitandclose (conn); Log.info (UUID+ "Data request and overwrite deposit complete"); } Catch(Exception e) {dbutils.rollbackandclosequietly (conn); Throw Newruntimeexception (); }

MySQL--The transaction isolation level and the steps for transaction submission in Java