JDBC: Handling the isolation level of transactions & transactions

I. Database transactions

In the database, the so-called transaction refers to a set of logical operations units that transform the data from one state to another.
To ensure consistency of data in the database, data manipulation should be a discrete group of logical units: when it is complete, the data consistency can be maintained, and when a part of the operation fails, the entire transaction should be treated as an error, all operations from the starting point should be rolled back to the start state.
Transaction operations: First define the start of a transaction, and then modify the data, if committed, these changes are permanently saved, if fallback (ROLLBACK), the database management system will discard all the modifications made to the start of the transaction state.

Second, acid (acid) properties of the transaction
1. Atomic (atomicity) atomicity means that a transaction is an inseparable unit of work, and the operations in the transaction either occur or do not occur.
2. The consistency (consistency) transaction must transform the database from one consistent state to another.
3. The isolation of the isolation (isolation) transaction is that the execution of one transaction cannot be disturbed by other transactions, that is, the operations inside a transaction and the data used are isolated from other transactions that are concurrent, and cannot interfere with each other in the execution of the transaction. (I made this mistake a few days ago)
4. Persistence (durability) persistence refers to the fact that once a transaction is committed, it changes the data in the database to be permanent, and the subsequent operations and database failures should not have any effect on it.

Third, JDBC things processing

Transactions: Refers to the set of operations that make up a single logical unit of work
Transactions: Ensure that all transactions are executed as a unit of work, even if there is a failure, this execution cannot be changed. When multiple operations are performed in a transaction, either all transactions are committed (commit), or the entire transaction is rolled back (rollback) to its original state
When a connection object is created, the transaction is automatically committed by default: each time an SQL statement is executed, if the execution succeeds, it is automatically committed to the database and cannot be rolled back

In order for multiple SQL statements to be executed as a transaction:
Call the Setautocommit (false) of the Connection object; To cancel the automatic commit transaction
After all the SQL statements have been executed successfully, a commit () is called; Method commits a transaction
When an exception occurs, call rollback (); method to roll back a transaction
If Connection is not turned off at this point, it needs to resume its autocommit status

public class Transcationtest {/** * ID1 gives ID2 500 money * About transactions: * 1. If multiple operations, each using their own separate connection, there is no guarantee that the transaction example test1 Demo * 2. Steps: * 1) before the transaction begins, Cancels the default auto-commit setautocommit (false) for connection; * 2) If the operation of the transaction is successful, then commit the transaction * 3) otherwise roll back in the Try-catch block * try {* * * CONN.SETAUTOCOMMIT (FALSE); * ... * conn.commit (); *}catch{* ... * conn.rollback (); * */@Test public void Test2 () {Connection conn = null;try {conn = Jdbc_tools.getconnection ();//system.out.println (CONN.G Etautocommit ());//1) Cancel Auto-commit conn.setautocommit (false); String sql = "UPDATE rent Set Money =" + "money-500 where id =?"; /2) If the operation of the transaction succeeds, then commit the transaction update (CONN,SQL, 1);//int i = 1/0; sql = "UPDATE rent Set Money =" + "money + where id =?"; Update (Conn,sql, 2); Conn.commit ();} catch (Exception e) {e.printstacktrace ();//3) Otherwise rollback in Try-catch block try {conn.rollback ();} catch (SQLException E1) { E1.printstacktrace ();}} FINALLY{JDBC_TOOLS.RELASESOURCE (conn, null);}} public static void Update (Connection conn,string sql,object...objs) {PreparedStatement ps =null;try {PS = CoNn.preparestatement (SQL), for (int i = 0;i<objs.length;i++) {ps.setobject (i+1, objs[i]);} Ps.executeupdate ();} catch (Exception e) {e.printstacktrace ();} Finally{jdbc_tools.relasesource (NULL, PS);}} @Testpublic void Test1 () {String sql = "UPDATE rent Set Money =" + "money-500 where id =?";D Ao.update (SQL, 1); int i = 1/0; Once an exception occurs, ID1 is reduced by 500, but ID2 's money does not increase by sql = "UPDATE rent set", "+" + "WHERE id =?";D Ao.update (SQL, 2);}}

third, the isolation level of the database

For multiple transactions running concurrently, when these transactions access the same data in the database, if the necessary isolation mechanism is not taken, it can cause various concurrency problems:
Dirty reads : For two things T1, T2, T1 read the fields that have been T2 updated but have not yet been committed . Then, if T2 rolls back, the content read by T1 is temporary and invalid.
Non-repeatable READ: For two things T1, T2, T1 read a field, and T2 updated the field. After that, T1 reads the same field again, and the value is different.
Phantom reading : For two things T1, T2, T1 reads a field from a table, and then T2 inserts some new rows into the table. Then, if T1 reads the same table again, it will have a few more rows.
Database transaction Isolation: The database system must have the ability to isolate and run each transaction concurrently, so that they do not affect each other and avoid various concurrency problems.
The degree to which a transaction is isolated from other transactions is called the isolation level. The database specifies a variety of transaction isolation levels, which correspond to different levels of interference, the higher the isolation level, the better the consistency of data, but the weaker the concurrency

4 transaction isolation levels provided by the database:

2 Kinds of transaction isolation levels supported by Oracle: READ commited, SERIALIZABLE. The default transaction isolation level for Oracle is: READ commited
Mysql supports the transaction isolation level in 4. The default transaction isolation level for Mysql is: Repeatable READ

Demonstrate

/** * ID1 give ID2 500 money * About Transaction: * 1. If multiple operations, each using their own separate connection, cannot guarantee that the transaction example test1 Demo * 2. Steps: * 1) Cancel the default auto-commit of connection before the transaction starts Setauto Commit (FALSE); * 2) If the operation of the transaction is successful, then commit the transaction * 3) otherwise roll back in the Try-catch block * try {* * * CONN.SETAUTOCOMMIT (FALSE); * ... * conn.commit (); *}catch{* ... * conn.rollback (); * */@Test public void Test2 () {Connection conn = null;try {conn = Jdbc_tools.getconnection ();//system.out.println (CONN.G Etautocommit ());//1) Cancel Auto-commit conn.setautocommit (false); String sql = "UPDATE rent Set Money =" + "money-500 where id =?"; /2) If the operation of the transaction succeeds, then commit the transaction update (CONN,SQL, 1);//int i = 1/0; sql = "UPDATE rent Set Money =" + "money + where id =?"; Update (Conn,sql, 2); Conn.commit ();} catch (Exception e) {e.printstacktrace ();//3) Otherwise rollback in Try-catch block try {conn.rollback ();} catch (SQLException E1) { E1.printstacktrace ();}} FINALLY{JDBC_TOOLS.RELASESOURCE (conn, null);}} public static void Update (Connection conn,string sql,object...objs) {PreparedStatement ps =null;try {PS = Conn.preparestatement (SQL); for (iNT i = 0;i<objs.length;i++) {ps.setobject (i+1, objs[i]);} Ps.executeupdate ();} catch (Exception e) {e.printstacktrace ();} Finally{jdbc_tools.relasesource (NULL, PS);}} @Testpublic void Test1 () {String sql = "UPDATE rent Set Money =" + "money-500 where id =?";D Ao.update (SQL, 1); int i = 1/0; Once an exception occurs, ID1 is reduced by 500, but ID2 's money does not increase by sql = "UPDATE rent set", "+" + "WHERE id =?";D Ao.update (SQL, 2);}

Setting the isolation Level

public static <E> E getforvalue (String sql) {//1. Get the result set, the result is only one row of connection conn = null; PreparedStatement PS = null; ResultSet rs = null;try {//1. Get database Connection conn = Jdbc_tools.getconnection ();//system.out.println ( Conn.gettransactionisolation ()); Conn.settransactionisolation (connection.transaction_read_committed);//2. Gets the PreparedStatement object PS = conn.preparestatement (sql);//2. Obtained results rs = ps.executequery (); if (Rs.next ()) {return (E) rs.getobject (1);}} catch (Exception e) {e.printstacktrace ();} Finally{jdbc_tools.relasesource (Rs,conn, PS);} return null;}

Setting the isolation level in MYSQL

A separate database connection is obtained for each MySQL program that is started. Each database connection has a global variable @ @tx_isolation that represents the current transaction isolation level. MySQL default Isolation level is repeatable Read
To view the current isolation level: SELECT @ @tx_isolation;
To set the isolation level for the current MySQL connection:
Set TRANSACTION ISOLATION Level Read Committed;
Set the global isolation level of the database system:
Set global transaction ISOLATION level Read Committed;

JDBC: Handling the isolation level of transactions & transactions