catalogue
2, Operating transaction commands in MySQL database
2.1. Write the test SQL script as follows:
3.2. Set transaction rollback point
4, Four characteristics of transaction (ACID)
5, Isolation level of transaction
5.1 possible problems caused by transaction isolation
5.2. Transaction isolation setting statement
5.3. Use MySQL database to demonstrate concurrency problems under different isolation levels
1, Concept of transaction
A transaction is a logical set of operations. The units that make up this set of operations are either all successful or all unsuccessful
For example, A-B transfer corresponds to the following two sql statements
update from account set money=money+100 where name='B';
update from account set money=money-100 where name='A';
2, Operating transaction commands in MySQL database
2.1. Write the test SQL script as follows:
1 /*Create account table*/
2 create table account(
3 id int primary key auto_increment,
4 name varchar(40),
5 money float
6 );
7
8 /*Insert test data*/
9 insert into account(name,money) values('A',1000);
10 insert into account(name,money) values('B',1000);
11 insert into account(name,money) values('C',1000);Let's simulate the business scenario of A-B transfer in MySQL database
2.1. Start transaction
Use "start transaction" to start the transaction of MySQL database, as shown below:
First, we simulate the transfer failure scenario in the database. First, we execute the update statement to reduce user A's money by 100 yuan, as shown in the following figure:
Then we close the dos command line window of the current operation, so that the database transaction of the update statement just executed is not committed, so our modification to user A is not real. The next time we query user A's money, it will still be the previous 1000, as shown in the following figure:
2.2. Commit
Let's simulate the successful transfer of A-B in the database
After we manually commit the database transaction, the business operation of A-B transferring 100 yuan is really successful. There is 100 less in account a and 100 more in account B.
2.3. Rollback
Manually roll back the transaction to invalidate all operations, so that the data will return to the original state!
3, Using transactions in JDBC
When the JDBC program obtains a Connection object from the database, by default, the Connection object will automatically submit the SQL statements sent on it to the database. If you want to turn off this default commit method and let multiple SQL execute in one transaction, you can use the following JDBC control transaction statements
- Connection.setAutoCommit(false);// Start transaction
- Connection.rollback();// Rollback transaction
- Connection.commit();// Commit transaction
3.1. JDBC transaction example
Demonstrate the bank transfer case in JDBC code, so that the following transfer operations can be performed in the same transaction
"update account set money=money-100 where name='A'"
update account set money=money+100 where name='B'
The code is as follows:
package me.gacl.demo;
2
3 import java.sql.Connection;
4 import java.sql.PreparedStatement;
5 import java.sql.ResultSet;
6 import java.sql.SQLException;
7 import me.gacl.utils.JdbcUtils;
8 import org.junit.Test;
9
10 /**
11 * @ClassName: TransactionDemo1
12 * @Description:
13 * JDBC Use transactions in to simulate transfers
14 create table account(
15 id int primary key auto_increment,
16 name varchar(40),
17 money float
18 );
19 insert into account(name,money) values('A',1000);
20 insert into account(name,money) values('B',1000);
21 insert into account(name,money) values('C',1000);
22 * @author: Lonely Wolf
23 * @date: 2014-9-22 11:16:17 PM
24 *
25 */
26 public class TransactionDemo1 {
27
28 /**
29 * @Method: testTransaction1
30 * @Description: Simulate the business scenario when the transfer is successful
31 * @Anthor:Lonely Wolf
32 *
33 */
34 @Test
35 public void testTransaction1(){
36 Connection conn = null;
37 PreparedStatement st = null;
38 ResultSet rs = null;
39
40 try{
41 conn = JdbcUtils.getConnection();
42 conn.setAutoCommit(false);//Notify the database to start a transaction
43 String sql1 = "update account set money=money-100 where name='A'";
44 st = conn.prepareStatement(sql1);
45 st.executeUpdate();
46 String sql2 = "update account set money=money+100 where name='B'";
47 st = conn.prepareStatement(sql2);
48 st.executeUpdate();
49 conn.commit();//After the above two SQL Update statements are executed successfully, the database will be notified to commit the transaction
50 System.out.println("success!!!"); //log4j
51 }catch (Exception e) {
52 e.printStackTrace();
53 }finally{
54 JdbcUtils.release(conn, st, rs);
55 }
56 }
57
58 /**
59 * @Method: testTransaction1
60 * @Description: An exception occurs during the simulated transfer process, resulting in some SQL execution failures, and the database will automatically roll back the transaction
61 * @Anthor:Lonely Wolf
62 *
63 */
64 @Test
65 public void testTransaction2(){
66 Connection conn = null;
67 PreparedStatement st = null;
68 ResultSet rs = null;
69
70 try{
71 conn = JdbcUtils.getConnection();
72 conn.setAutoCommit(false);//Notify the database to start a transaction
73 String sql1 = "update account set money=money-100 where name='A'";
74 st = conn.prepareStatement(sql1);
75 st.executeUpdate();
76 //Use this code to simulate that an exception occurs in the program after SQL1 is executed, resulting in the subsequent SQL can not be executed normally and the transaction can not be committed normally. At this time, the database will automatically execute the rollback operation
77 int x = 1/0;
78 String sql2 = "update account set money=money+100 where name='B'";
79 st = conn.prepareStatement(sql2);
80 st.executeUpdate();
81 conn.commit();//After the above two SQL Update statements are executed successfully, the database will be notified to commit the transaction
82 System.out.println("success!!!");
83 }catch (Exception e) {
84 e.printStackTrace();
85 }finally{
86 JdbcUtils.release(conn, st, rs);
87 }
88 }
89
90 /**
91 * @Method: testTransaction1
92 * @Description: When an exception occurs during the simulated transfer process, which causes some SQL execution to fail, manually notify the database to roll back the transaction
93 * @Anthor:Lonely Wolf
94 *
95 */
96 @Test
97 public void testTransaction3(){
98 Connection conn = null;
99 PreparedStatement st = null;
100 ResultSet rs = null;
101
102 try{
103 conn = JdbcUtils.getConnection();
104 conn.setAutoCommit(false);//Notify the database to start a transaction
105 String sql1 = "update account set money=money-100 where name='A'";
106 st = conn.prepareStatement(sql1);
107 st.executeUpdate();
108 //Use this code to simulate that an exception occurs in the program after SQL1 is executed, resulting in the failure of subsequent SQL execution and transaction submission
109 int x = 1/0;
110 String sql2 = "update account set money=money+100 where name='B'";
111 st = conn.prepareStatement(sql2);
112 st.executeUpdate();
113 conn.commit();//After the above two SQL Update statements are executed successfully, the database will be notified to commit the transaction
114 System.out.println("success!!!");
115 }catch (Exception e) {
116 try {
117 //After an exception is caught, manually notify the database to roll back the transaction
118 conn.rollback();
119 } catch (SQLException e1) {
120 e1.printStackTrace();
121 }
122 e.printStackTrace();
123 }finally{
124 JdbcUtils.release(conn, st, rs);
125 }
126 }
127 }3.2. Set transaction rollback point
In development, sometimes it is necessary to manually set the transaction rollback point. In JDBC, use the following statement to set the transaction rollback point
Savepoint sp = conn.setSavepoint();
Conn.rollback(sp);
Conn.commit();// After rollback, the database must be notified to commit the transaction
4, Four characteristics of transaction (ACID)
4.1 Atomicity
Atomicity means that a transaction is an inseparable work unit, and all operations in the transaction either succeed or fail. For example, all SQL statements in the same transaction are executed successfully or fail
4.2 Consistency
The concept of transaction consistency on the official website is that transactions must change the database from one consistency state to another. Take the transfer as an example. If A transfers to B, the sum of the two users' money before the transfer is 2000, then after A transfers to B, no matter how the two accounts are transferred, the sum of user A's money and user B's money is 2000, which is the consistency of the transaction.
4.3 Isolation
Transaction isolation is that when multiple users access the database concurrently, the transactions opened by the database for each user cannot be disturbed by the operation data of other transactions. Multiple concurrent transactions should be isolated from each other.
4. Durability
Persistence means that once a transaction is committed, its changes to the data in the database are permanent. Then, even if the database fails, it should not have any impact on it
The most troublesome of the four characteristics of transactions is isolation. The following focuses on the isolation level of transactions
5, Isolation level of transaction
When multiple threads start their own transactions to operate the data in the database, the database system shall be responsible for isolating operations to ensure the accuracy of each thread in obtaining data.
5.1 possible problems caused by transaction isolation
If the transaction does not consider isolation, the following problems may arise:
1. Dirty reading
Dirty read means that one transaction reads uncommitted data from another transaction.
This is very dangerous. Suppose a transfers 100 yuan to B, and the corresponding sql statement is as follows
1.update account set money=money+100 where name='B';
2.update account set money=money-100 where name='A';
When the first sql has been executed and the second sql has not been executed (when A has not submitted), if B queries his account at this time, he will find that he has an extra 100 yuan. If A rolls back after B leaves, B will lose 100 yuan.
2. Non repeatable reading
Non repeatable reading refers to reading a row of data in a table in a transaction, and the reading results are different multiple times.
For example, if the bank wants to query the balance of account A, the first query of account A is 200 yuan. At this time, account A stores 100 yuan in the account and submits it. The bank then makes another query. At this time, account A is 300 yuan. If the bank's two queries are inconsistent, it may be very confused and do not know which query is accurate.
The difference between non repeatable reading and dirty reading is that dirty reading is to read the uncommitted dirty data of the previous transaction, and non repeatable reading is to re read the committed data of the previous transaction.
Many people think that this situation is right. There is no need to be confused. Of course, the latter shall prevail. We can consider such a situation. For example, the bank program needs to output the query results to the computer screen and write them to the file respectively. The results are inconsistent in two queries for the output destination in a transaction, resulting in inconsistent results in the file and screen, and the bank staff do not know which shall prevail.
3. Virtual reading (unreal reading)
Virtual reading (phantom reading) refers to reading the data inserted by other transactions in one transaction, resulting in inconsistent reading.
If C's deposit of 100 yuan is not submitted, then the bank makes a report to make statistics. The total amount of all users in the account table is 500 yuan, and then C submits it. At this time, the bank finds that the account is 600 yuan, resulting in false reading, which will also make the bank confused. Which one shall prevail.
5.2. Transaction isolation setting statement
MySQL database defines four isolation levels:
- Serializable: it can avoid dirty reading, non repeatable reading and virtual reading.
- Repeatable read: it can avoid dirty reading and non repeatable reading.
- Read committed: to avoid dirty reading.
- Read uncommitted: the lowest level. None of the above conditions can be guaranteed.
mysql database query current transaction isolation level: select @@tx_isolation
For example:
The default transaction isolation level of mysql database is repeatable read
Set transaction isolation level for mysql database: set transaction isolation level name
For example:
5.3. Use MySQL database to demonstrate concurrency problems under different isolation levels
Open two windows at the same time to simulate two users accessing the database concurrently
1. When the isolation level of a transaction is set to read uncommitted, dirty reads, non repeatable reads, and virtual reads are triggered
A window
set transaction isolation level read uncommitted;-- Set the database isolation level of user A to read uncommitted
start transaction;-- Open transaction
select * from account;-- Query the existing money in account A and go to window B for operation
select * from account -- it is found that a has an extra 100 yuan. At this time, a reads the uncommitted data of B (dirty reading)
B window
start transaction;-- Open transaction
update account set money=money+100 where name='A';-- Do not submit, go to window a for query
2. When the isolation level of a transaction is set to read committed, non repeatable reads and virtual reads will be triggered, but dirty reads are avoided
A window
set transaction isolation level read committed;
start transaction;
select * from account;-- Find that account a is 1000 yuan, go to window b
select * from account;-- It is found that there are 100 more accounts in A. at this time, a reads the data submitted by other transactions. The results read from account a twice are different (non repeatable)
B window
start transaction;
update account set money=money+100 where name='aaa';
commit;-- Go to window a
3. When the transaction isolation level is set to repeatable read(mysql default level), virtual reads will be triggered, but dirty reads and non repeatable reads are avoided
A window
set transaction isolation level repeatable read;
start transaction;
select * from account;-- If 4 records are found in the table, go to window b
select * from account;-- It may be found that there are 5 records in the table. At this time, a reads the data inserted by another transaction (virtual read)
B window
start transaction;
insert into account(name,money) values('ggg',1000);
commit;-- Go to window a
4. When the isolation level of transactions is set to Serializable, all problems are avoided
A window
set transaction isolation level Serializable;
start transaction;
select * from account;-- Go to b window
B window
start transaction;
insert into account(name,money) values('ggg',1000);-- It is found that it cannot be inserted. It can only be inserted after a ends the transaction
