Java Web----Transactions

1 Create Account Table

CREATE TABLE account (ID INT PRIMARY KEY auto_increment,name VARCHAR ($), balance NUMERIC (10.2)); INSERT into account (NAME, Balance) VALUES (' Zs ', 100000), insert into account (Name,balance) VALUES (' ls ', 100000); Insert to account (Name,balance) VALUES (' ww ', 100000); SELECT * from account;

four characteristics of 2 transactions (ACID)

The four main features of a transaction are:

• Atomicity (atomicity): All operations in a transaction are non-divisible atomic units. All operations in a transaction are either successfully executed or all failed to execute.
• Consistency (consistency): After a transaction executes, the database state is aligned with other business rules. If the transaction is executed successfully or not, the sum of the two account balances that are involved in the transfer should be the same.
• Isolation (Isolation): Isolation means that in concurrent operations, separate transactions should be isolated so that transactions in each concurrency do not interfere with each other.
• Persistence (Durability): Once the transaction commits successfully, all data operations in the transaction must be persisted to the database, even if the database crashes immediately after the transaction is committed, and when the database is restarted, it must be guaranteed to recover the data through some mechanism.

3 transactions in MySQL

By default, each SQL statement executed by MySQL is a separate transaction. If you need to include multiple SQL statements in a transaction, you need to open the transaction and end the transaction.

• Open transaction:starttransaction;
• End transaction:commit or rollback.

Execute the strattransaction before executing the SQL statement, which opens a transaction (the starting point of the transaction), then executes multiple SQL statements, ends the transaction, commits the commit, that is, the impact of multiple SQL statements in the transaction is persisted to the database. or rollback, which means rollback, which rolls back to the start of the transaction, and all the previous actions have been undone!

START TRANSACTION; UPDATE account SET balance=balance-10000 WHERE id=1; UPDATE account SET balance=balance+10000 WHERE id=2; ROLLBACK; START TRANSACTION; UPDATE account SET balance=balance-10000 WHERE id=1; UPDATE account SET balance=balance+10000 WHERE id=2; COMMIT;

4 JDBC Transaction

Processing transactions in JDBC is done through connection!

all operations in the same transaction are using the sameConnectionObject! 4.1 A transaction in JDBC

The three methods of connection are related to transactions:

• Setautocommit (Boolean): Sets whether the transaction is autocommit, if True (the default is True) for autocommit, that is, each executed SQL statement is a separate transaction, and if set to false, it is equivalent to opening a transaction; Con.setautocommit (False) means open transaction!!!
• Commit (): Commit end transaction;Con.commit (); represents a COMMIT transaction
• Rollback (): Rollback END transaction. con.rollback (); represents rolling back a transaction

Code format for JDBC processing transactions:

try {

Con.setautocommit (false);//Open transaction ...

....

...

Con.commit ();//try's final commit transaction

} catch () {

Con.rollback ();//ROLLBACK TRANSACTION

}

public void Transfer (Boolean b) {Connection con = null; PreparedStatement pstmt = null;try {con = jdbcutils.getconnection ();//Manual Commit Con.setautocommit (FALSE); String sql = "Update account set balance=balance+?" where id=? "; pstmt = con.preparestatement (sql);//Operation Pstmt.setdouble (1, -10000);p stmt.setint (2, 1);p stmt.executeupdate ();// Throws an exception in two operations if (b) {throw new Exception ();} Pstmt.setdouble (1, 10000);p Stmt.setint (2, 2);p stmt.executeupdate ();//COMMIT Transaction Con.commit ();} catch (Exception e) {//ROLLBACK TRANSACTION if (con! = null) {try {con.rollback ()} catch (SQLException ex) {}}throw new runtimeexception (e); } finally {//close jdbcutils.close (Con, pstmt);}}

4.2 Save points (Learn)

The savepoint is a JDBC3.0 thing! A rollback that requires the database server to support SavePoint mode.

Verify that the database server supports SavePoint!

Boolean B = Con.getmetadata (). supportssavepoints ();

The purpose of the savepoint is to allow the transaction to be rolled back to the specified savepoint location. When you set a savepoint in a transaction and then roll back, you can choose to roll back to the specified savepoint instead of rolling back the entire transaction! note that rolling back to the specified savepoint does not end the transaction!!! It's the end of the transaction only if you roll back the whole thing!

The connection class sets the save point and rolls back to the specified SavePoint method:

• Set save point: SavePoint Setsavepoint ();
• Rollback to the specified save point: void Rollback (savepoint).

/* * John Doe said to Zhang San, if you turn me 1W, I will give you 100W. * ========================================== * * Zhang San to Li Shihun 1W (Zhang San minus 1W, John Doe plus 1W) * Set save point! * John Doe give a three-turn 100W (John Doe minus 100W, Zhang San plus 100W) * View John Doe balance is negative, then roll back to the savepoint. * COMMIT TRANSACTION */@Testpublic void fun () {Connection con = null; PreparedStatement pstmt = null;try {con = jdbcutils.getconnection ();//Manual Commit Con.setautocommit (FALSE); String sql = "Update account set balance=balance+?" where name=? "; pstmt = con.preparestatement (sql);//Operation 1 (Zhang San minus 1W) pstmt.setdouble (1, -10000);p stmt.setstring (2, "ZS"); Pstmt.executeupdate ();//Operation 2 (John Doe plus 1W) pstmt.setdouble (1, 10000);p stmt.setstring (2, "LS");p stmt.executeupdate ();// Set Save point SavePoint sp = Con.setsavepoint ();//Operation 3 (John Doe minus 100W) pstmt.setdouble (1, -1000000);p stmt.setstring (2, "LS"); Pstmt.executeupdate ();//Operation 4 (Zhang San plus 100W) pstmt.setdouble (1, 1000000);p stmt.setstring (2, "ZS");p stmt.executeupdate () ;//Operation 5 (View John Doe balance) sql = "Select balance from account where name=?"; pstmt = con.preparestatement (sql);p stmt.setstring (1, "LS"); ResultSet rs = Pstmt.executequery (); Rs.next ();d ouble balance= rs.getdouble (1); If the John Doe balance is negative, roll back to the specified savepoint if (balance < 0) {con.rollback (SP); System.out.println ("Zhang San, you've been fooled!" ");} Commit Transaction Con.commit ();} catch (Exception e) {//ROLLBACK TRANSACTION if (con! = null) {try {con.rollback ()} catch (SQLException ex) {}}throw new runtimeexception (e); } finally {//close jdbcutils.close (Con, pstmt);}}

5 Transaction ISOLATION Level concurrent read problems for 5.1 transactions

• Dirty read: Read to another transaction uncommitted data;
• Non-REPEATABLE READ: two reads inconsistent;
• Phantom Read (Virtual Read): Read to another transaction committed data.

5.2 Concurrent Transaction issues

Because there are roughly 5 types of problems caused by concurrent transactions, two of them are update problems, and three are read problems.

• Dirty Reads (dirty read): read the uncommitted update data of another transaction, that is, the dirty data is read;
• Non-repeatable read (unrepeatable read): two reads to the same record are inconsistent because another transaction modifies the record;
• Phantom Read (Phantom Read): inconsistent with two queries on the same table because another transaction inserted a record;

Dirty Read

Transaction 1: Zhang San to John Doe transfer 100 Yuan

Transaction 2: John Doe View your account

• T1: Transaction 1: Start transaction
• T2: Transaction 1: Zhang San to John Doe transfer 100 Yuan
• T3: Transaction 2: Start transaction
• T4: Transaction 2: John Doe View your account, see the account more than 100 yuan (dirty Read)
• T5: Transaction 2: Committing a transaction
• T6: Transaction 1: ROLLBACK TRANSACTION, back to the state before the transfer

Non-REPEATABLE READ

Transaction 1: Hotel View two times Room No. 1048th status

Transaction 2: Book Room number 1048th

• T1: Transaction 1: Start transaction
• T2: Transaction 1: View number 1048th room status is idle
• T3: Transaction 2: Start transaction
• T4: Transaction 2: Booking room 1048th
• T5: Transaction 2: Committing a transaction
• T6: Transaction 1: View room 1048th again for use
• T7: Transaction 1: Committing a transaction

Two query results for the same record are inconsistent!

Phantom reading

Transaction 1: Two statistics on hotel room reservation records

Transaction 2: Add a booking room record

• T1: Transaction 1: Start transaction
• T2: Transaction 1: Statistical booking records 100
• T3: Transaction 2: Start transaction
• T4: Transaction 2: Add a booking room record
• T5: Transaction 2: Committing a transaction
• T6: Transaction 1: Re-counting the booking record to 101 records
• T7: Transaction 1: Commit

Inconsistent with two queries on the same table!

The difference between non-repeatable reads and Phantom reads:

• Non-repeatable reads are updates to another transaction;
• A phantom read is an insert that reads to another transaction (it cannot be tested into a phantom read in MySQL);

5.3 Four isolation levels

4 levels of transaction isolation, in the same data environment, using the same input, performing the same work, depending on the isolation level, can result in different results. The ability to resolve data concurrency problems at different transaction isolation levels is different.

1 SERIALIZABLE (serialization) [C1]

• No concurrency problems occur because it is a serial, non-concurrent access to the same data;
• Worst performance;

2 repeatable READ[C2] (Repeatable Read) (MySQL)

• Prevent dirty reading and non-repeatable reading, can not deal with phantom reading problem;
• Better performance than serializable

3 Read COMMITTED[C3] (Read committed data) (Oracle)

• Prevent dirty reading, do not deal with non-repeatable reading, nor deal with Phantom reading;
• Better performance than repeatable read

4 Read Uncommitted[c4] (reading uncommitted data)

• Any transactional concurrency issues may occur
• Best Performance

The default isolation level for MySQL is repeatable READ, which is a good choice!

5.4 MySQL Isolation level

The default isolation level for MySQL is repeatable read, which can be viewed using the following statement:

SELECT @ @tx_isolation

You can also set the isolation level of the current connection by using the following statement:

Set Transaction IsolationLevel [4 First 1]

5.5 JDBC Setting isolation level

con.settransactionisolation (int level)

The optional values for the parameters are as follows:

• connection.transaction_read_uncommitted;
• connection.transaction_read_committed;
• Connection.transaction_repeatable_read;
• Connection.transaction_serializable.

Transaction summary:

• features of the transaction: ACID ;
• transaction start boundary and end boundary: Start boundary ( Con.setautocommit (False) ) to end the boundary ( Con.commit () or Con.rollback () );
• isolation level of the transaction: read_uncommitted , read_committed , Repeatable_read , SERIALIZABLE . Concurrent transactions need to be considered when multiple transactions are executing concurrently.

Java Web----Transactions