Java Development Engineer (web direction)-03. Database Development-4th chapter. Transactions

The 4th chapter--Affairstransaction principle and development

Transaction transaction:

What is a transaction?

A transaction is the basic unit of concurrency control, a series of operations performed as a single logical unit of work, and the logical unit of work satisfies the acid characteristics.

i.e. Bank transfer: Start trading, Zhang San account deduction of 100 Yuan, John Doe account increase of 100 yuan, close the transaction.

Characteristics of the transaction: ACID

Atomicity atomicity: The entire transaction must be executed as a whole. (either execute all or do not do it all)

Consistency consistency: The overall capital of the entire transaction is the same

Isolation isolation:

CASE1: Jo Changsan to John Doe transfer process, Zhao Five to Zhang San transferred 200 yuan. Two trades are executed concurrently.

T1 T2

Read Zhang San balance 100;

Read Zhang San balance 100;

Transfer 100 to John Doe,

The updated Zhang San balance is 0;

Trade ended Zhao into 200,

Update Zhang San balance to 300

End of transaction

Case2: Dirty read: Zhang San transfer to others after 100 Zhang San save 200, save money after the transfer due to system reasons failure rollback.

Read an uncommitted update for a transaction

T1 T2

Read Zhang San balance 100

(transfer) Update Zhang San balance 0

Read Zhang San balance 0

T1 Rollback () (Save) Update Zhang San balance 200

T2 End (Zhang San account balance is 200)

CASE3: Non-repeatable READ: The same transaction, two reads the same numeric result is different, becomes non-repeatable read.

T1 Zhang San read his balance to 100;t2 read Zhang San balance 100;t2 Save the update to 300;T1 Zhang San read the balance to 300. T1 Two reads the Zhang San balance is non-repeatable read.

CASE4: Phantom read: The result of two reads does not contain the same row records.

T1 Read all users (Zhang San, John Doe), T2 new user Zhao Five; T1 Read all Users (3); T1/t2 end. The result of two reads in T1 is a different number of rows recorded, called Phantom Read.

The above cases need to be avoided

Isolation: Transactions are isolated from each other and cannot be affected by other transactions until a transaction is completed

Persistence Durability: Once the entire trading process is over, the transaction should be permanent, no matter what happens

Using JDBC for transaction control:

In the Connection class

. Setautocommit (): Open transaction (if False, subsequent SQL of the connection object will be processed as a transaction; If true, all subsequent SQL for the connection object will be executed as a separate statement (default true))

. Commit (): The transaction is committed, that is, the transaction takes effect and ends

. Rollback (): rollback, fallback to the state before the transaction started

i.e.

ALTER TABLE User ADDAccountint;UPDATE User SETAccount=  - WHEREId= 1;UPDATE User SETAccount= 0 WHEREId> 1;

Implement the process of ZHANGSI (1) Transfer to Lisan (2):

(Non-transactional:)

 Public Static voidtransfernontransaction () {Connection conn=NULL; PreparedStatement ptmt=NULL; Try{conn=drivermanager.getconnection (Db_url, user_name, PASSWORD); String SQL= "UPDATE User SET account =?" WHERE userName =? and id =?; "; //Transfer from ZHANGSI (1) to Lisan (2)PTMT =conn.preparestatement (SQL); Ptmt.setint (1, 0); Ptmt.setstring (2, "Zhangsi"); Ptmt.setint (3, 1);        Ptmt.execute (); Ptmt.setint (1, 100); Ptmt.setstring (2, "Lisan"); Ptmt.setint (3, 2);    Ptmt.execute (); } Catch(SQLException e) {e.printstacktrace (); } finally {        Try {            if(Conn! =NULL) Conn.close (); if(Ptmt! =NULL) Ptmt.close (); } Catch(SQLException e) {e.printstacktrace (); }    }}

After the execution of the first Ptmt.execute (), the database Zhangsi account=0, Lisan account=0;

There is an intermediate state that is unacceptable for the implementation of the entire business logic. If the program crashes at this point, it will be irreversible.

(transaction:)

Public Static voidtransferbytransaction () {Connection conn=NULL; PreparedStatement ptmt=NULL; Try{conn=drivermanager.getconnection (Db_url, user_name, PASSWORD); //Using Transaction mechanismConn.setautocommit (false); String SQL= "UPDATE User SET account =?" WHERE userName =? and id =?; "; Ptmt=conn.preparestatement (SQL); Ptmt.setint (1, 0); Ptmt.setstring (2, "Zhangsi"); Ptmt.setint (3, 1);        Ptmt.execute (); Ptmt.setint (1, 100); Ptmt.setstring (2, "Lisan"); Ptmt.setint (3, 2);                Ptmt.execute (); //Commit The transactionConn.commit (); } Catch(SQLException e) {//If something wrong happens, rolling back        if(Conn! =NULL) {            Try{conn.rollback (); } Catch(SQLException E1) {e1.printstacktrace ();    }} e.printstacktrace (); } finally {        Try {            if(Conn! =NULL) Conn.close (); if(Ptmt! =NULL) Ptmt.close (); } Catch(SQLException e) {e.printstacktrace (); }    }}

If the breakpoint is at the first Ptmt.execute () and the database is queried, the result is the state before the transaction executes, not the middle state.

Until the Conn.commit () method executes, all operations in the transaction are valid in the database.

Checkpoint functionality in the connection class:

. Setsavepoint (): Creates a savepoint during execution so that rollback () can be rolled back to the save point

. Rollback (SavePoint savepoint): Rollback to a checkpoint

i.e.

Public Static voidrollbacktest () {Connection conn=NULL; PreparedStatement ptmt=NULL; //Save PointSavePoint SP =NULL; Try{conn=drivermanager.getconnection (Db_url, user_name, PASSWORD); Conn.setautocommit (false); String SQL= "UPDATE User SET account =?" WHERE userName =? and id =?; "; Ptmt=conn.preparestatement (SQL); Ptmt.setint (1, 0); Ptmt.setstring (2, "Zhangsi"); Ptmt.setint (3, 1);        Ptmt.execute (); //Create a Save pointSP =Conn.setsavepoint (); Ptmt.setint (1, 100); Ptmt.setstring (2, "Lisan"); Ptmt.setint (3, 2);        Ptmt.execute (); //throw an exception manually for the purpose of testing        Throw NewSQLException (); } Catch(SQLException e) {//If something wrong happens, rolling back to the save point created before//And then transfer the Guoyi (3)        if(Conn! =NULL) {            Try{conn.rollback (SP); System.out.println ("Transfer from Zhangsi (1) to Lisan (2) failed;\n" + "Transfer to Guoyi (3) instead"); //Other OperationsPtmt.setint (1, 100); Ptmt.setstring (2, "Guoyi"); Ptmt.setint (3, 3);                Ptmt.executequery ();            Conn.commit (); } Catch(SQLException E1) {e1.printstacktrace ();    }} e.printstacktrace (); } finally {        Try {            if(Conn! =NULL) Conn.close (); if(Ptmt! =NULL) Ptmt.close (); } Catch(SQLException e) {e.printstacktrace (); }    }}

Isolation level of a transaction: 4 levels

Read uncommited: May cause dirty Reads

Read commited: cannot be dirty read, but will appear non-repeatable read

Repeat read (REPEATABLE Read): Non-repeatable reads do not occur, but Phantom reads occur

Serialization (SERIALIZABLE): Highest isolation level, no phantom reads, but strict concurrency control, serial execution results in poor database performance

n.b. 1. The higher the transaction isolation level, the poorer the database performance, but the less difficult it is for developers to program.

2. mysql default transaction ISOLATION level repeating repeatable READ

JDBC Sets the isolation level:

Connection object,

. Gettransactionisolation ();

. Settransactionisolation ();

deadlock Analysis and resolution

Java Development Engineer (web direction)-03. Database Development-4th chapter. Transactions