JDBC processes transactions and jdbc transactions

JDBC processes transactions and jdbc transactions

1. What is a transaction?

In the Personnel Management System, if you delete a person, you need to delete the basic information of the person, as well as the information related to the person, such as the mailbox and articles, these database operation statements constitute a transaction!

2. transactions must meet four conditions (ACID)

• Atomicity ):A group of transactions are either successful or recalled.
• Consistency ):After the transaction is executed, the database status is consistent with that of other business rules. For example, whether the transaction is successfully executed or not, the sum of the balances of the two accounts involved in the transfer should remain unchanged.
• Isolation (Isolation ):Transactions run independently. If the results of one transaction affect other transactions, other transactions will be recalled. 100% isolation of transactions requires a sacrifice of speed.
• Durability ):After the software and hardware crash, the InnoDB data table driver uses the log file to reconstruct and modify the data table. Reliability and high speed cannot both be achieved. The innodb_flush_log_at_trx_commit option determines when to save the transaction to the log.

Iii. MySQL transactions

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

• Start transaction: start transaction
• End transaction: commit or rollback

Before executing an SQL statement, run start transaction first, which starts a transaction (the starting point of the transaction). Then, you can execute multiple SQL statements and end the transaction, commit indicates the commit, that is, the impact of multiple SQL statements in the transaction will be sustained in the database, or rollback, indicating that the transaction is rolled back to the starting point, all previous operations have been revoked.

 1 mysql> SELECT * FROM account; 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.00 sec)10 11 mysql> START TRANSACTION;12 Query OK, 0 rows affected (0.00 sec)13 14 mysql> UPDATE account SET balance=900 WHERE name = 'zs';15 Query OK, 1 row affected (0.00 sec)16 Rows matched: 1  Changed: 1  Warnings: 017 18 mysql> SELECT * FROM account;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.00 sec)27 28 mysql> UPDATE account SET balance=1100 WHERE name = 'ls';29 Query OK, 1 row affected (0.00 sec)30 Rows matched: 1  Changed: 1  Warnings: 031 32 mysql> SELECT * FROM account;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.00 sec)41 42 mysql> ROLLBACK;43 Query OK, 0 rows affected (0.00 sec)44 45 mysql> SELECT * FROM account;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.00 sec)54 55 mysql> START TRANSACTION;56 Query OK, 0 rows affected (0.00 sec)57 58 mysql> UPDATE account SET balance=balance-100 WHERE name = 'zs';59 Query OK, 1 row affected (0.00 sec)60 Rows matched: 1  Changed: 1  Warnings: 061 62 mysql> SELECT * FROM account;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.00 sec)71 72 mysql> UPDATE account SET balance=balance+100 WHERE name = 'ls';73 Query OK, 1 row affected (0.00 sec)74 Rows matched: 1  Changed: 1  Warnings: 075 76 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)85 86 mysql> commit;87 Query OK, 0 rows affected (0.02 sec)88 89 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)

4. JDBC transactions

In JDBC, transactions are processed through Connection.

All operations in the same transaction use the same Connection object.

① Transactions in JDBC

The three methods of Connection are related to transactions:

• SetAutoCommit (boolean): sets whether to automatically commit a transaction. If true (default value: true) indicates that the transaction is automatically committed, that is, each SQL statement executed is a separate transaction, if it is set to false, the transaction is enabled;Con. setAutoCommit (false) indicates that the transaction is enabled.
• Commit (): commit the end transaction.
• Rollback (): rolls back and ends the transaction.

Code Format for JDBC transaction processing:

Try {con. setAutoCommit (false); // start the transaction ...... con. commit (); // The final commit transaction of try} 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, String 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); 10 pstmt. setString (2, name); 11 pstmt.exe cuteUpdate (); 12} catch (Exception e) {13 throw new RuntimeException (e); 14} 15} 16}

1 import cn. itcast. jdbc. jdbcUtils; 2 import org. junit. test; 3 import java. SQL. connection; 4 import java. SQL. SQLException; 5 6 public class Demo1 {7/* 8 * demonstrate the transfer method 9 * all Connect operations are processed at the Service layer 10 * Hide all connection operations, this requires the use of custom gadgets (day19_1) 11 **/12 public void transferAccounts (String from, String to, double money) {13 // The transaction operation 14 Connection con = null; 15 try {16 con = JdbcUtils. getConnection (); 17 con. setAutoCommit (false); 18 AccountDao dao = new AccountDao (); 19 dao. updateBalance (con, from,-money); // subtract the corresponding amount from 20 if (true) {21 throw new RuntimeException ("sorry, transfer failed "); 22} 23 dao. updateBalance (con, to, + money); // Add the corresponding amount to 24 // submit the transaction 25 con. commit (); 26 27} catch (Exception e) {28 try {29 con. rollback (); 30} catch (SQLException e1) {31 e. printStackTrace (); 32} 33 throw new RuntimeException (e); 34} 35} 36 @ Test37 public void fun1 () {38 transferAccounts ("zs", "ls ", 100); 39} 40}

V. transaction isolation level

1. Concurrent read of transactions

• Dirty read: Read the uncommitted data of another transaction (not allowed );
• Non-repeated read: The two reads are inconsistent;
• Phantom read: reads committed data from another transaction.

2. Concurrent transactions

There are roughly five types of problems caused by concurrent transactions, two of which are update problems and three are read problems.

• Dirty read: reads dirty data when no new data is committed to another transaction;
• Unrepeatable: The two reads of the same record are inconsistent because another transaction modifies the record;
• Phantom read: The two queries to the same table are inconsistent because another transaction inserts a record.

3. Four isolation levels

There are four levels of transaction isolation levels. In the same data environment, the same input is used to perform the same work. Different isolation levels can lead to different results. Different transaction isolation levels have different capabilities to solve data concurrency problems.

1. SERIALIZABLE)

• There will be no concurrency issues, because the access to the same data is serial and non-concurrent access;
• Worst Performance

2. repeatable read (repeatable read) (MySQL)

• Prevents dirty reads and non-repeated reads, and cannot process Phantom reads
• Better performance than SERIALIZABLE

3. read committed (read committed data) (Oracle)

• Prevents dirty reads and prevents repeated and Phantom reads;
• Better performance than REPEATABLE READ

4. read uncommitted (read uncommitted data)

• There may be any transaction concurrency issues and nothing can be done.
• Best Performance

Vi. MySQL isolation level

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

SELECT @@`TX_ISOLATION`;

You can also use the following statement to set the isolation level of the current connection:

Set transaction isolation level repeatable read; // [4, select 1]

VII. Set the isolation level for JDBC

Con. setTransactionIsolation (int level): the Optional values of the parameter are as follows:

• Connection. TRANSACTION_READ_UNCOMMITTED;
• Connection. TRANSACTION_READ_COMMITTED;
• Connection. TRANSACTION_REPEATABLE_READ;
• Connection. TRANSACTION_READ_SERIALIZABLE.