In-depth analysis of Java Web Item31 -- JDBC (MySQL) Transaction Management

In-depth analysis of Java Web Item31 -- JDBC (MySQL) Transaction Management
I. Concepts of transactions

　　A transaction refers to a logical group of operations that constitute each unit of this group of operations. If the transaction is successful, the transaction fails.
For example, A--B transfer corresponds to the following two SQL statements
　　

update from account set money=money+100 where name='B';update from account set money=money-100 where name='A';

Ii. MySQL database operation transaction commands

1. Write the test SQL script as follows:

/* Create account table */create table account (id int primary key auto_increment, name varchar (40), money float);/* insert Test Data */insert into account (name, money) values ('A', 1000); insert into account (name, money) values ('B', 1000); insert into account (name, money) values ('C', 1000 );

Below we simulate A--B transfer in MySQL database this business scenario

2.1 start transaction)

Use start transaction to start MySQL database transactions, as shown below:

　　

First, we simulate the transfer failure scenario in the database. First, execute the update statement to reduce the money of user A by 100 yuan, as shown in:

　　

Then, we close the doscommand line window for the current operation. As a result, the transaction of the database that just executed the update statement is not committed, therefore, the modification to user A is not A real modification. The next time we query user A's money, it is still the previous 1000, as shown in:

　　

2.2 commit a transaction)

Below we simulate a successful A--B transfer in the database scenario

　　

After we manually commit (commit) The database transaction, the A--B transfers 100 yuan of this business operation is really successful, A account less than 100, B account more than 100.

2.3. rollback)

　　

By manually rolling back the transaction, all operations will be invalidated, so that the data will return to the initial state!

Iii. Use transactions in JDBC

When the Jdbc program obtains a Connection object from the database, the Connection object will automatically submit the SQL statement sent to the database by default. To disable this default commit method and run multiple SQL statements in one transaction, you can use the following JDBC statements to control the transaction.

Connection. setAutoCommit (false); // start transaction Connection. rollback (); // rollback Connection. commit (); // commit a transaction (commit)

3.1. Example of JDBC transactions

The JDBC Code demonstrates the bank transfer case so that the following transfer operations are executed in the same transaction

　　"update account set money=money-100 where name='A'"　　update account set money=money+100 where name='B'

The Code is as follows:

Package me. gacl. demo; import java. SQL. connection; import java. SQL. preparedStatement; import java. SQL. resultSet; import java. SQL. SQLException; import me. gacl. utils. jdbcUtils; import org. junit. test;/*** @ ClassName: TransactionDemo1 * @ Description: * use a transaction in JDBC to create table account (id int primary key auto_increment, name varchar (40 ), money float); insert into account (name, money) values ('A', 1000); inser T into account (name, money) values ('B', 1000); insert into account (name, money) values ('C', 1000); * @ author: solitary wolf * @ date: 11:16:17 **/public class TransactionDemo1 {/*** @ Method: testTransaction1 * @ Description: business scenario when the transfer is successful * @ Anthor: lone Wolf **/@ Test public void testTransaction1 () {Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try {conn = JdbcUtils. getConnecti On (); conn. setAutoCommit (false); // notifies the database to start A transaction (start transaction) String sql1 = "update account set money = money-100 where name = 'A'"; st = conn. prepareStatement (sql1); st.exe cuteUpdate (); String sql2 = "update account set money = money + 100 where name = 'B'"; st = conn. prepareStatement (sql2); st.exe cuteUpdate (); conn. commit (); // after the above two SQL statements have successfully executed the Update statement, the database is notified to commit the transaction (commit) System. out. println ("Successful !!! "); // Log4j} catch (Exception e) {e. printStackTrace ();} finally {JdbcUtils. release (conn, st, rs) ;}}/*** @ Method: testTransaction1 * @ Description: an exception occurs during the simulated transfer process, causing some SQL statements to automatically roll back the transaction * @ Anthor: Lone Wolf **/@ Test public void testTransaction2 () {Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try {conn = JdbcUtils. getConnection (); conn. setAutoCommit (false); // notifies the database to start a transaction (start Transaction) String sql1 = "update account set money = money-100 where name = 'A'"; st = conn. prepareStatement (sql1); st.exe cuteUpdate (); // use this code to simulate that after SQL1 is executed, an exception occurs in the program and the subsequent SQL statement cannot be executed normally, and the transaction cannot be submitted normally, in this case, the database automatically performs the rollback operation int x = 1/0; String sql2 = "update account set money = money + 100 where name = 'B'"; st = conn. prepareStatement (sql2); st.exe cuteUpdate (); conn. commit (); // after the above two SQL statements have successfully executed the Update statement, the database is notified to commit the transaction (commit) System. o Ut. println ("Successful !!! ");} Catch (Exception e) {e. printStackTrace ();} finally {JdbcUtils. release (conn, st, rs) ;}}/*** @ Method: testTransaction1 * @ Description: when some SQL statements fail to be executed due to an exception during the simulated transfer process, the database is manually notified to roll back the transaction * @ Anthor: Lone Wolf **/@ Test public void testTransaction3 () {Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try {conn = JdbcUtils. getConnection (); conn. setAutoCommit (false); // notifies the database to start a transaction (start transa Ction) String sql1 = "update account set money = money-100 where name = 'A'"; st = conn. prepareStatement (sql1); st.exe cuteUpdate (); // use this code to simulate that after SQL1 is executed, an exception occurs in the program and the subsequent SQL statements cannot be executed normally, the transaction cannot be normally committed either. int x = 1/0; String sql2 = "update account set money = money + 100 where name = 'B'"; st = conn. prepareStatement (sql2); st.exe cuteUpdate (); conn. commit (); // after the above two SQL statements have successfully executed the Update statement, the database is notified to commit the transaction (commit) System. out. println ("Successful !!! ");} Catch (Exception e) {try {// manually notifies the database to perform the rollback transaction operation conn after an Exception is caught. rollback ();} catch (SQLException e1) {e1.printStackTrace ();} e. printStackTrace ();} finally {JdbcUtils. release (conn, st, rs );}}}

3.2 SET transaction rollback points

During development, you may need to manually set the transaction rollback point. In JDBC, use the following statement to set the transaction rollback point.

Savepoint sp = conn. setSavepoint (); conn. rollback (sp); conn. commit (); // The database must be notified to commit transactions after rollback.

Example of setting transaction rollback points:

Package me. gacl. demo; import java. SQL. connection; import java. SQL. preparedStatement; import java. SQL. resultSet; import java. SQL. SQLException; import java. SQL. savepoint; import me. gacl. utils. jdbcUtils; import org. junit. test;/*** @ ClassName: TransactionDemo1 * @ Description: * use a transaction in JDBC to create table account (id int primary key auto_increment, name varchar (40 ), money float); insert into account (name, money) values ('A', 1000); insert into account (name, money) values ('B', 1000 ); insert into account (name, money) values ('C', 1000); * @ author: lone wolf * @ date: 11:16:17 **/public class TransactionDemo2 {/*** @ Method: testTransaction1 * @ Description: business scenario when the transfer is successful * @ Anthor: lone Wolf **/@ Test public void testTransaction1 () {Connection conn = null; PreparedStatement st = null; ResultSet rs = null; Savepoint sp = null; try {conn = JdbcUtils. getConnection (); conn. setAutoCommit (false); // notifies the database to start A transaction (start transaction) String sql1 = "update account set money = money-100 where name = 'A'"; st = conn. prepareStatement (sql1); st.exe cuteUpdate (); // sets the transaction rollback point sp = conn. setSavepoint (); String sql2 = "update account set money = money + 100 where name = 'B'"; st = conn. prepareStatement (sql2); st.exe cuteUpdate (); // an exception occurs when the program is executed here. The execution of the subsequent sql3 statement will interrupt int x = 1/0; string sql3 = "update account set money = money + 100 where name = 'C'"; st = conn. prepareStatement (sql3); st.exe cuteUpdate (); conn. commit ();} catch (Exception e) {try {/*** we sent three update statements to the database. * sql3 statements cannot be executed due to program exceptions, database transactions cannot be committed normally. * because the SET transaction rollback point is after the SQL 1 Statement is executed normally and before the SQL 2 Statement is executed normally, * When you notify the database to roll back the transaction, the update operation performed by sql1 is not rolled back * It is rolled back to the update operation executed by sql2. that is to say, in the above three update statements, the Modification Operation of sql1 takes effect * sql2 modification operation does not take effect due to transaction rollback, sql3 has no chance to execute due to program exceptions */conn. rollback (sp); // rollback to the set transaction rollback point // rollback remember to notify the database to submit the transaction conn. commit ();} catch (SQLException e1) {e1.printStackTrace ();} e. printStackTrace ();} finally {JdbcUtils. release (conn, st, rs );}}}

Iv. Four major ACID features of transactions) 4.1. Atomicity)

　　Atomicity means that a transaction is an inseparable unit of work. All operations in a transaction either succeed or fail.For example, if the SQL statement in the same transaction is either successfully executed or all SQL statements fail to be executed

4.2. Consistency)

The concept of transaction consistency on the official website is:The transaction must change the database from a consistent state to another consistent state.Taking the transfer as an example, A transfers money to B. Assume that the total amount of money for the two users is 2000 before the transfer, no matter how the two accounts transfer money to B, the sum of user A's money and user B's money is 2000, which is the transaction consistency.

4.3 Isolation)

The isolation of transactions is the transactions opened by the database for each user when multiple users access the database concurrently. The transactions cannot be disturbed by the operation data of other transactions. multiple concurrent transactions must be isolated from each other.

4.4. Durability)

Durability means that once a transaction is committed, its changes to the data in the database are permanent. Then, even if the database fails, it should not have any impact on it.

Isolation is the most troublesome of transactions. The following describes the isolation level of transactions.

5. transaction isolation level

When multiple threads start their respective transaction operations on data in the database, the database system is responsible for isolation operations to ensure the accuracy of each thread when obtaining data.

5.1. problems that may arise if the transaction does not consider isolation

If the transaction does not consider isolation, the following problems may occur:

　　1. Dirty read

　　　　　Dirty read refers to a transaction that reads the uncommitted data of another transaction..

This is very dangerous. Suppose A transfers 100 yuan to B, the corresponding SQL statement is as follows:

update account set money=money+100 where name='B';    update account set money=money-100  where name='A';

When 1st SQL statements are executed and 2nd SQL statements are not executed (when A is not submitted), if B queries his account, it will find that he has an additional 100 yuan. If B is rolled back after A and so on, B will lose 100 yuan.

　　2. Non-repeated read

　　Non-repeated reading refers to reading a row of data in a table in a transaction, and reading results are different for multiple times.
For example, if A bank wants to query the balance of account A, the first query of Account A is 200 yuan. At this time, A has committed 100 yuan to the account memory, and the bank then queries again, at this time, account A is 300 yuan. The Bank may be confused when the two queries are inconsistent.
　　
　　The difference between non-repeated reads and dirty reads is that dirty reads read reads the dirty data not committed by the previous transaction, and non-repeated reads reread the data committed by the previous transaction.
　　
Many people think this is the case, so there is no need to be confused. Of course, this is the case later. We can consider such a situation. For example, a bank program needs to output the query results to a computer screen and write them to a file respectively. The results are for the output destination in a transaction, the two queries are inconsistent. As a result, the files and the results on the screen are inconsistent. The bank staff do not know which one should prevail.

　　3. Virtual read (phantom read)

Virtual read refers to reading data inserted by other transactions in a transaction, resulting in inconsistent reading before and after.
If deposit C is not submitted for 100 yuan, the Bank will make a report to count the total amount of all users in the account table as 500 yuan, and then submit the statement. Then, the bank will find that the account is 600 yuan, the Bank will also be overwhelmed by the result of virtual reads, whichever prevails.

5.2 transaction isolation setting statement

The MySQL database defines four isolation levels:

Serializable: prevents dirty reads, repeated reads, and virtual reads. Repeatable read (Repeatable read): prevents dirty reads and non-repeated reads. Read committed (Read committed): prevents dirty reads.

Read uncommitted (Read uncommitted): the lowest level. None of the above conditions can be guaranteed.

Mysql database query current transaction isolation level:select @@tx_isolation

For example:

　　

The default transaction isolation level of the mysql database is:Repeatable read(Repeatable)

Mysql database sets the transaction isolation level:set transaction isolation levelIsolation level name

For example: <喎?http: www.bkjia.com kf ware vc " target="_blank" class="keylink"> VcD4NCjxwPqGhoaE8aW1nIGFsdD0 = "here write picture description" src = "http://www.bkjia.com/uploads/allimg/151225/04294K0T-6.png" title = "\"/>

5.3 use the MySQL database to demonstrate concurrency issues at different isolation levels

At the same time, two windows are opened to simulate concurrent accesses to the database by two users.

1. When the transaction isolation level is set to read uncommitted, dirty reads, non-repeated reads, and virtual reads are triggered.

Window
　　　　

Set transaction isolation level read uncommitted; -- set the database isolation level of user A to Read uncommitted (read uncommitted) start transaction; -- enable transaction select * from account; -- query the current money in account A and go to window B to operate select * from account -- it is found that a has an additional 100 yuan. At this time, A reads data not submitted by account B (dirty read)

Window B
　　　　

Start transaction; -- enable transaction update account set money = money + 100 where name = 'a'; -- do not submit, go to window A for query

2. When the transaction isolation level is set to read committed, non-repeated read and virtual read will be triggered, but dirty read is avoided.

Window

Set transaction isolation level read committed; start transaction; select * from account; -- found that account a is 1000 yuan, go to the B window select * from account; -- found that account a is 100 more than account, at this time, a reads the data submitted by another transaction and reads the data of account a twice as different results (non-repeated reads)

Window B
　　　　

Start transaction; update account set money = money + 100 where name = 'aaa'; commit; -- go to window

3. When the transaction isolation level is set to repeatable read (mysql Default level), virtual reads are triggered, but dirty reads and repeated reads are avoided.

Window
　　　　

Set transaction isolation level repeatable read; start transaction; select * from account; -- four records are found in the table and transferred to select * from account in window B; -- it may be found that the table has five records. At this time, a reads the data inserted into another transaction (Virtual read)

Window B
　　　　

Start transaction; insert into account (name, money) values ('ggg ', 1000); commit; -- go to window

4. When the transaction isolation level is set to Serializable, all problems will be avoided.

Window
　　　　

Set transaction isolation level Serializable; start transaction; select * from account; -- go to B window

Window B
　　　　

Start transaction; insert into account (name, money) values ('ggg ', 1000); -- it is found that the transaction cannot be inserted and can only be inserted after a ends.