JDBC Processing transactions

First, what is a business?

In the Personnel Management system, you delete a person, you need to delete the basic information of the person, but also to delete the information related to the person, such as mailbox, articles and so on, so that these database operation statements constitute a transaction!

Second, the transaction must meet 4 conditions (ACID)

• atomicity of a transaction (atomicity): A set of transactions, either successful or withdrawn.
• Consistency (consistency): After a transaction executes, the database state is aligned with other business rules. If the transaction is executed successfully, the sum of the two account balances involved in the transfer should be the same.
• Isolation (Isolation): transactions run independently of each other. The result of one transaction affects other transactions, and then other transactions are recalled. The 100% isolation of a transaction requires a sacrifice of speed.
• Persistence (Durability): after the hardware and software crashes, the INNODB data table driver uses the log file to refactor the modifications. Reliability and high speed cannot be combined, the INNODB_FLUSH_LOG_AT_TRX_COMMIT option determines when the transaction is saved to the log.

Third, MySQL in the business

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: Start transaction
• End transaction: Commit or Rollback

Execute the START transaction 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 rolling back to the start of the transaction, and all the previous actions have been undone.

1 mysql> SELECT * FROMAccount 2 +----+------+---------+ 3 | ID | NAME | Balance |  4 +----+------+---------+ 5 | 1 | ZS | 1000.00 |  6 | 2 | ls | 1000.00 |  7 | 3 | WW | 1000.00 | 8 +----+------+---------+ 9 3 rows in Set (0.00SEC) mysql> START TRANSACTION; Query OK, 0 rows affected (0.00SEC) mysql> UPDATE account SET balance=900 WHERE name = ' ZS '; Query OK, 1 row affected (0.00SEC) matched:1 changed:1 warnings:017 mysql> SELECT * FROMACCOUNT;19 +----+------+---------+20 | ID | NAME | Balance |21 +----+------+---------+22 | 1 | ZS | 900.00 |23 | 2 | ls | 1000.00 |24 | 3 | WW | 1000.00 |25 +----+------+---------+26 3 rows in Set (0.00SEC) mysql> UPDATE account SET balance=1100 WHERE name = ' ls '; Query OK, 1 row affected (0.00SEC) matched:1 changed:1 warnings:031 mysql> SELECT * FROMaccount;33 +----+------+---------+34 | ID | NAME | Balance |35 +----+------+---------+36 | 1 | ZS | 900.00 |37 | 2 | ls | 1100.00 |38 | 3 | WW | 1000.00 |39 +----+------+---------+40 3 rows in Set (0.00SEC) mysql> ROLLBACK; Query OK, 0 rows affected (0.00SEC) mysql> SELECT * FROMaccount;46 +----+------+---------+47 | ID | NAME | Balance |48 +----+------+---------+49 | 1 | ZS | 1000.00 |50 | 2 | ls | 1000.00 |51 | 3 | WW | 1000.00 |52 +----+------+---------+53 3 rows in Set (0.00SEC) mysql> START TRANSACTION; 0 Rows Affected (0.00SEC) mysql> UPDATE account SET balance=balance-100 WHERE name = ' ZS '; affected Query OK, 1 row (0.00SEC) Rows matched:1 changed:1 warnings:061 mysql> SELECT * FROMaccount;63 +----+------+---------+64 | ID | NAME | Balance |65 +----+------+---------+66 | 1 | ZS | 900.00 |67 | 2 | ls | 1000.00 |68 | 3 | WW | 1000.00 |69 +----+------+---------+70 3 rows in Set (0.00sec) mysql> UPDATE account SET balance=balance+100 WHERE name = ' ls '; 1 row affected (0.00
                          
                            sec) Changed:1 Rows matched:1 warnings:075 mysql> SELECT * from
                            account;77 +----+------+---------+78 | id | NAME | Balance |79 +----+------+---------+80 | 1 | ZS | 900.00 |81 | 2 | ls | 1100.00 |82 | 3 | WW | 1000.00 |83 +----+------+---------+84 3 rows in Set (0.00 sec) Mysql> commit; the Query OK, 0 rows affected (0.02 sec) mysql> SELECT * from account;90 +----+------+---------+91 | id | NAME | Balance |92 +----+------+---------+93 | 1 | ZS | 900.00 |94 | 2 | ls | 1100.00 |95 | 3 | WW | 1000.00 |96 +----+------+---------+97 3 rows in Set (0.00 sec)     
                           

Iv. JDBC Transaction

Processing transactions in JDBC is done through connection.

All operations in the same transaction are using the same connection object.

Transactions in the ①JDBC

The three methods of connection are related to transactions:

• Setautocommit (Boolean): Sets whether the transaction is auto-committed, 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) indicates that the transaction is turned on.
• Commit (): commits the end transaction.
• Rollback (): Rollback END transaction.

Code format for JDBC processing transactions:

Try{     con.setautocommit (false);//Open transaction ...      Con.commit ();//try last commit Transaction      } catch() {    con.rollback ();//ROLLBACK TRANSACTION}  

Example:

1 public class Accountdao {2 *     /* 3     * Modify the balance of the specified user 4     * */5 public     void UpdateBalance (Connection con, stri ng name,double Balance) {6         try {7             String sql = "UPDATE account SET balance=balance+?") WHERE name=? " ; 8             PreparedStatement pstmt = con.preparestatement (SQL); 9             pstmt.setdouble (1, balance); pstmt.setstring (2 pstmt.executeupdate ();}catch (Exception e) {$ throw new }16}      

1 ImportCn.itcast.jdbc.JdbcUtils; 2 ImportOrg.junit.Test; 3 ImportJava.sql.Connection; 4 ImportJava.sql.SQLException; 5 6 public class DEMO1 {7/* 8 * Demo Transfer Method 9 * All operations on connect are handled at the service layer 10 * Hide all connection operations, which requires the use of custom gadgets (Day19_1) 11 * */12 public void transferaccounts (String from,string to,doubleMoney ) {13//operation on transaction Connection con = null; try{= = jdbcutils.getconnection (); Con.setautocommit (False ), Accountdao dao = new Accountdao (), Dao.updatebalance (Con,from,-money),//to subtract the corresponding amount from (True) {21 throw new RuntimeException ("Sorry, the transfer failed"), }23 dao.updatebalance (Con,to,+money);//give to add the corresponding amount 24//COMMIT TRANSACTION  Con.commit (); the catch (Exception e) { con.rollback (), and the Catch (SQLException E1) {31} e.printstacktrace (); }33 throw new runtimeexception (e); }35 }36 @Test37 public Void fun1 () {transferaccounts ("Zs", "ls", }40}        

V. TRANSACTION ISOLATION LEVEL

1. Concurrent reading of transactions

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

2. Concurrent transaction Issues

Because of the problems caused by concurrent transactions, there are roughly 5 classes, two of which are the three types of update problems are read problems.

• Dirty Reads (dirty read): Read the uncommitted new data of another transaction, that is, the dirty data is read;
• Non-repeatable Read (unrepeatable): Inconsistent with two reads of the same record, because the record was modified by another transaction;
• Phantom Read (Phantom Read): Inconsistent with two queries on the same table because another transaction inserted a record.

3. Four isolation Levels

4 levels of Transaction isolation level, 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)

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

2, Repeatable read (Repeatable Read) (MySQL)

• Prevents dirty reads and non-repeatable reads, and cannot handle phantom reads
• Better performance than serializable

3. Read COMMITTED (Reading submitted data) (Oracle)

• Prevent dirty reading, cannot deal with non-repeatable reading and phantom reading;
• Better performance than repeatable read

4. READ UNCOMMITTED (reading uncommitted data)

• There may be anything concurrency problems and nothing is handled.
• Best Performance

Vi. 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 Isolation level repeatable READ//[4 Select 1]

Vii. JDBC Set Isolation level

con.settransactionisolation (int level): parameter optional values are as follows:

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

JDBC Processing transactions