Deep parsing of JDBC transactions in Java _java

Transaction
a transaction is a logical execution unit consisting of one or more steps that compose a sequence of operations, either fully executed or all discarded. Four attributes of a transaction: atomicity (atomicity), consistency (consistency), isolation (isoiation) and persistence (durability) atomicity (atomicity): the smallest unit of execution for a transaction applies, and cannot be divided. is the smallest logical executor that cannot be divided into transactions.

Consistency (consistency): The execution of a transaction results in the need to change the database from one consistent state to another.

Isolation Line (isoiation): The execution of each transaction does not interfere with each other, and the internal operation of any transaction is isolated from the other concurrent transactions. That is, a transaction between concurrent executions cannot see the intermediate state of the other, and the concurrently executed transactions cannot interact with each other.

Persistence (Durability): Persistence is also known as persistence (persistence), meaning that once a transaction is committed, any changes made to the data are recorded in the permanent memory, usually in the physical database.

Typically, the transactions of a database involve a set of DML (data munipulation Language) statements that, when modified, will maintain a good consistency in the data, and the statements of the action table, such as inserts, modifications, deletes, and so on; a DDL (data Definition Language, data definition language) statements, which manipulate the language of data objects, have Create, alter, drop. A DCL (Data Control Language language) statement, mainly with GRANT, revoke statements. DDL and DCL Statements can have a maximum of one, because they all result in immediate commit of the transaction. When all of the database operations that the transaction contains are successfully executed, the transaction should be committed so that these changes are permanently in effect. There are two ways to commit a transaction: Show submission and autocommit. Show commits: Use Commit commit autocommit: Execute DLL or DCL, or program quits normally when any one of the database operations contained in a transaction fails, the transaction should be rolled back (rollback) to invalidate all changes made in the transaction. There are two ways to rollback a transaction: show rollback and automatic rollback. Show rollback: Use rollback to automatically rollback: System error or force exit.

Transaction concurrency handling possible issues
1. Dirty Read (Dirty Read): One transaction reads data that has not yet been committed by another transaction

2. Non-repeatable read (Non-repeatable Read): The operation of one transaction resulted in the reading of different data two times before and after another transaction

3. Phantom Read (Phantom Read): The operation of one transaction results in different data volumes of two queries before and after another transaction

Example:

Transaction A, B concurrent execution:

• When a transaction is updated, the B transaction Select reads the data not yet submitted by a, at which point a transaction rollback, the data read by B is invalid dirty data
• When the B transaction select reads the data, the a transaction update operation changes the data of the B transaction Select, at which time the B transaction reads the data again and finds that the data is different two times before and after.
• When the B transaction select reads the data, a transaction inserts or deletes a record that satisfies the select condition of a transaction, when the B transaction is again Select, the query finds a record that does not exist, or a previous record is missing.

Java JDBC Transaction mechanism

 Import java.sql.Connection; 
  Import Java.sql.DriverManager; 
  Import java.sql.PreparedStatement; 
   
   
  Import java.sql.SQLException; 
    public class Jdbctransaction {public static final String URL = ' com.mysql.jdbc.Driver '; 
    public static final String USER = "root"; 
   
    public static final String PASSWD = "123456"; 
      public static void jdbctransaction (int id) {Connection conn = null; 
      PreparedStatement pstmtupdate = null; 
      PreparedStatement pstmtquery = null; 
      String updatesql = "Update SQL"; 
   
      String querysql = "Query sql"; 
        try {class.forname ("com.mysql.jdbc.Driver"); 
   
        conn = Drivermanager.getconnection (URL, USER, PASSWD); Conn.setautocommit (FALSE); 
        Autocommit set to False//perform update operation pstmtupdate = Conn.preparestatement (Updatesql); 
   
        Pstmtupdate.executeupdate (); 
        Perform a lookup operation Pstmtquery = Conn.preparestatement (Querysql); 
Pstmtquery.executequery ();   
        Conn.commit (); 
   
        Conn.setautocommit (TRUE); 
        Pstmtupdate.close (); 
        Pstmtquery.close (); 
      Conn.close (); 
        catch (Exception e) {try {conn.rollback (); 
      catch (SQLException E1) {} e.printstacktrace (); 
          Finally {try {if (pstmtupdate!= null) {pstmtupdate.close (); 
          } if (pstmtquery!= null) {pstmtquery.close (); 
          } if (conn!= null) {conn.close (); 
 } \ Catch (SQLException E2) {}}}

JDBC Transaction support

JDBC Connection also supports things, connection the default turn on automatic submission, that is, closing things. That is, each SQL statement execution is immediately committed to the database, permanently in effect, and cannot be manipulated. Turn off automatic submission of connection and turn on things. Connection's Setautocommit method can be: Connection.setautocommit (false), and Connection.getautocommit () to get the pattern of things. When we open things, the database operations completed in the current connection are not immediately committed to the database, and the connection commit method needs to be invoked. If a statement fails to execute, you can call rollback and roll back and forth. Note: If connection encounters an unhandled SqlException exception, the system exits abnormally, and the transaction is automatically rolled back. If the program catches the exception, you need to display the rollback transaction in exception handling. Connection provides a way to set the middle savepoint of a transaction: Setsavepoint, there are 2 ways to set the middle point: SavePoint setsavepoint (): Creates an unnamed middle point in the current transaction. and returns the SavePoint object for that middle point. SavePoint setsavepoint (string name): An intermediate point with the specified name is created in the current transaction, and the SavePoint object that returns the intermediate point usually Setsavepoint (string name) to set the name of the middle point. The transaction rollback is not rolled back by the name of the middle point, but is rolled back according to the intermediate point object. Setting the name is a better way to distinguish between the intermediate point objects and the connection rollback (SavePoint savepoint) method to complete the rollback to the specified middle point.

JDBC's support for transactions is reflected in three areas:

1, the automatic submission mode (Auto-commit mode)

Connection provides a Auto-commit property to specify when a transaction ends

2, when Auto-commit is true, when the execution of each independent SQL operation completes, the transaction is automatically committed immediately, meaning that each SQL operation is a transaction

When a stand-alone SQL operation is completed, the JDBC specification is defined as this:

For data Manipulation language (DML) and data definition language (DDL), execution is considered complete when the statement is executed

3. When Auto-commit is false, each transaction must display the call commit method for submission, or the call to the rollback method for rollback. Auto-commit defaults to True

Transaction ISOLATION LEVEL (Transaction isolation levels)
JDBC defines five transaction isolation levels:

1. Transaction_none JDBC driver does not support transactions
2. Transaction_read_uncommitted allows dirty reads, non-repeatable reads, and Phantom reads
3. Transaction_read_committed prohibit dirty reads, but allow non-repeatable reads and Phantom reads
4. Transaction_repeatable_read prohibit dirty read and non repeatable read, single run Phantom read
5. Transaction_serializable prohibit dirty reads, non-repeatable reads, and Phantom reads

Save Point
JDBC defines the SavePoint interface and provides a finer-grained transaction control mechanism. When a savepoint is set, you can rollback to the state at the savepoint instead of rollback the whole transaction.
First, let's take a look at some of the major problems that existing JDBC operations can bring to us. For example, there is a business: when we modify a message to query this information, it seems to be a simple business, realize it is also very easy, but when this business is placed in a multithreaded high concurrency platform, the problem naturally appeared, For example, when we perform a modification, a thread executes the modification statement before executing the query, and then we execute the query, and the information we see may be different from the one we modified. In order to solve this problem, we must introduce the JDBC transaction mechanism, its implementation code is very simple, to show example code for everyone to refer to: