What is a transaction?
Database transaction refers to a series of operations performed as a single logical unit of work, either completely or not.
To put it simply: a transaction is to bind a pile of SQL statements (usually add, delete and modify operations) together for execution. Either all of them are executed successfully or all of them fail. That is, only when all of them are executed successfully will they be successful, otherwise they will return to the state before the execution of this pile of SQL statements.
Taking bank transfer as an example, Zhang San transfers 100 yuan to Li Si's account, which requires at least two SQL statements:
- Deduct 100 yuan from Zhang San's account;
update Account table set money=money-100 where name='Zhang San';
- Add 100 yuan to Li Si's account.
update Account table set money=money+100 where name='Li Si';
If the program is interrupted after the first SQL statement is executed successfully and before the second SQL statement is executed (an exception may be thrown, or for some other reason), Li Si's account does not add 100 yuan, while Zhang San subtracts 100 yuan. This is certainly not allowed in real life.
If a transaction is added in the transfer process, all SQL statements executed in the whole transfer process will be in one transaction, and all operations in the transaction will either succeed or fail. There can be no half success.
In other words, if it is successful to subtract 100 yuan from Zhang San's account, the operation of adding 100 yuan to Li Si's account must also be successful; Otherwise, it will fail to subtract 100 yuan from Zhang San and add 100 yuan to Li Si.
Four characteristics of transaction
The four characteristics of transactions (ACID) are:
(1) Atomicity: all operations in a transaction are indivisible atomic units. All operations in the transaction either succeed or fail.
(2) Consistency: after the transaction is executed, the database state is consistent with other business rules. For example, the sum of the amounts of the two accounts involved in the transfer should remain unchanged before and after the transaction, regardless of whether the transaction is successful or not.
Zhang San: 1000 1000-500=500 1000 Li Si: 1000 1000+500=1500 1000
(3) Isolation: isolation means that in concurrent operations, different transactions should be isolated so that transactions in each concurrency will not interfere with each other. In other words, when viewing data updates in an in-process transaction, the state of the data is either the state before another transaction modifies it or the state after another transaction modifies it. The transaction will not view the data in the intermediate state. For example, in transaction A, view Zhang San's account amount in another transaction B (Zhang San's account amount is being modified). To view Zhang San's account amount before transaction B, or Zhang San's account amount after transaction B.
Transaction 1: query A,B Sum of account amount (1000)+1000,500+1500,1000+1000) Transaction 2: A Transfer to B 500 element A - 500 = 500 B + 500 = 1500
(4) Durability: once the transaction is committed successfully, all data operations in the transaction must be persisted to the database. Even if the database crashes immediately after the transaction is committed, it must be able to recover the data through some mechanism when the database is restarted.
Open transaction---A to B Transfer 500 yuan A: 1000 - 500 = 500 (succeed) Record in log,Transaction succeeded,A Account amount updated to 500 B: 1000 + 500 = 1500 (succeed) Record in log,Transaction succeeded,B Account amount updated to 1500 End transaction---RollBACK /Submit
Transactions in MySQL
By default, each SQL statement executed by MySQL is a separate transaction. Because the bottom layer will automatically start the transaction before executing the SQL statement. After the SQL statement is executed, the transaction will be ended immediately!
If you need to include multiple SQL statements in a transaction, you need to manually start and end the transaction.
-
Start transaction: start transaction;
-
End transaction: commit or rollback.
Before executing SQL statements, execute strat transaction first, which starts a transaction (the starting point of the transaction). Then you can execute multiple SQL statements. Finally, to end the transaction, commit means commit, that is, the impact of multiple SQL statements in the transaction will be persisted to the database. Or rollback, which means rollback, that is, rollback to the starting point of the transaction. All previous operations have been undone!
Here is an example of A transfer from account A to account B
Prepare data:
-- 1,Create database jt_db database(If it does not exist, it will be created)
create database if not exists jt_db charset utf8;
use jt_db; -- choice jt_db database
-- 2,stay jt_db Create in Library acc surface(Bank account statement),Required id(Primary key),name(full name),money(Account amount)
drop table if exists acc;
create table acc(
id int primary key auto_increment,
name varchar(50),
money double
);
-- 3,to acc In the table, Insert 2 records
insert into acc values(null,'A',1000);
insert into acc values(null,'B',1000);
-- query acc All records in the table
select * from acc;
The following shows the effects of rolling back a transaction, committing a transaction, and interrupting operations after a transaction is started and a series of SQL are executed.
– rollback:
-- query acc In the account table A and B Amount of select * from acc; -- Open transaction start transaction; -- Start transfer, A Account minus 100 yuan update acc set money=money-100 where name='A'; -- query acc In the account table A and B Amount of select * from acc; -- B The account is increased by 100 yuan update acc set money=money+100 where name='B'; -- query acc In the account table A and B Amount of select * from acc; -- Rollback transaction rollback; -- Query again acc In the account table A and B Amount of select * from acc;
– commit: do the above operation again, and finally replace rollback with commit, that is, commit the transaction
commit;
– interrupt operation: do the above operation again, and finally replace rollback with quit, that is, interrupt operation
quit;
Transaction concurrent read problem
Multiple transactions operate on the same data at the same time, which is called transaction concurrency.
If necessary isolation measures are not taken during transaction concurrency, it may lead to various concurrency problems and damage the integrity of data. Among these problems, there are three kinds of reading problems: dirty reading, unrepeatable reading and unreal reading.
(1) dirty read: in one transaction, read the data of another transaction that has not committed the update, that is, read the dirty data;
For example, in one transaction, A transfers 100 yuan to B but does not submit the transaction. In another transaction, B queries the account amount and finds the updated data that A does not submit. We call it dirty reading.
Tip: you need to set the transaction isolation level of the database to the lowest to see dirty reads Transaction 1: start transaction; A - 100 = 900; B + 100 = 1100; (No transaction committed) Transaction 2: start transaction; query B Account amount 1100, This process is called dirty reading, 1100 It's just dirty data
(2) unrepeatable read: the two read results of the same record are inconsistent because another transaction modified the record during the two queries (for modification operations)
For example, in transaction 1, the amount of account A is queried twice before and after. Between the two queries, another transaction 2 modifies the amount of account A (and also commits the transaction). This may lead to inconsistent results in transaction 1. This is non repeatable.
Transaction 1: start transaction--- First read A Account amount: 1000 Second reading A Account amount: 900 Transaction 2: start transaction--- A account - 100 = 900; Commit transaction---
(3) phantom read: the results of two queries on the same table are inconsistent, because during the two queries, another transaction has been inserted or deleted (for insertion or deletion);
Transaction 1: start transaction--- select * from acc where id=3;//There is no record with id 3 insert into acc value(3,'C',2000); select * from acc where id=3;//There is a record with id 3 Transaction 2: start transaction--- insert into acc value(3,'C',2000); Commit transaction---
Note: mysql does not allow dirty reads and non repeatable reads by default, so you need to set mysql to allow dirty reads and non repeatable reads before the following demonstration.
set tx_isolation='read-uncommitted'; -- set up mysql Transaction isolation level
1. Dirty read example:
-- In window 1, start the transaction and execute A to B Transfer 100 yuan set tx_isolation='read-uncommitted'; -- Dirty reading, unrepeatable reading and phantom reading are allowed use jt_db; -- choice jt_db library start transaction; -- Open transaction update acc set money=money-100 where name='A'; update acc set money=money+100 where name='B'; -- In window 2, open the transaction and query B Account amount set tx_isolation='read-uncommitted'; -- Dirty reading, unrepeatable reading and phantom reading are allowed use jt_db; -- choice jt_db library start transaction; -- Open transaction select * from acc where name='B'; -- Dirty data -- Switch to window 1, roll back the transaction and cancel the transfer operation. rollback; -- Rollback transaction -- Switch to window 2 and query B Account amount select * from acc where name='B';
In window 2, B sees that his account has increased by 100 yuan (at this time, the data A operation transaction has not been submitted). This situation is called "dirty reading".
2. Examples of non repeatable reads:
-- In window 1, open the transaction and query A Amount of account set tx_isolation='read-uncommitted'; -- Dirty reading, unrepeatable reading and phantom reading are allowed use jt_db; -- choice jt_db library start transaction; -- Open transaction select * from acc where name='A'; -- In window 2, open the transaction and query A Account amount minus 100 set tx_isolation='read-uncommitted'; -- Dirty reading, unrepeatable reading and phantom reading are allowed use jt_db; -- choice jt_db library start transaction; -- Open transaction update acc set money=money-100 where name='A'; -- A Account minus 100 select * from acc where name='A'; commit; -- Commit transaction -- Switch to window 1 and query again A The amount of the account. select * from acc where name='A'; -- Inconsistent query results
In window 1, the query results of the same data (amount of account A) before and after are inconsistent because another transaction has modified the amount of account A between the two queries. This situation is "no repetition"
3. Unreal reading example:
-- In window 1, open the transaction and query whether the account table exists id=3 Your account set tx_isolation='read-uncommitted'; -- Dirty reading, unrepeatable reading and phantom reading are allowed use jt_db; -- choice jt_db library start transaction; -- Open transaction select * from acc where id=3; -- In window 2, the transaction is opened and a entry is inserted into the account table id Record for 3 and commit the transaction. -- set up mysql Dirty reading, non repeatability and phantom reading are allowed set tx_isolation='read-uncommitted'; use jt_db; -- choice jt_db library start transaction; -- Open transaction insert into acc values(3, 'C', 1000); commit; -- Commit transaction -- Switch to window 1, because there is no query in window 1 above id It is a record of 3, so it can be inserted id Record for 3. insert into acc values(3, 'C', 1000); -- Insertion will fail!
In window 1, no record with id 3 is queried, so the record with id 3 will be inserted next. However, when the insertion has not been performed, the record with id 3 is inserted into another transaction and the transaction is committed, so the insertion operation in window 1 will fail.
Explore the reasons and find that there is another record with id 3 in the account table (it feels like an illusion). This situation is called "unreal reading"
The above are three common concurrent read problems in transaction concurrency. How to prevent these problems?
You can prevent this by setting the transaction isolation level.
Transaction isolation level
The transaction isolation level is divided into four levels. Under the same data environment, performing the same operation on the data and setting different isolation levels may lead to different results. Different transaction isolation levels can solve different data concurrency problems.
set tx_isolation='read-uncommitted';
1. READ UNCOMMITTED
The security is the worst, and any transaction concurrency problems may occur (such as dirty reading, non repeated reading, phantom reading, etc.)
But the performance is the best (not used!!)
2. READ COMMITTED (Oracle default)
Poor safety
Good performance
Can prevent dirty reading, but can not prevent non repeatable reading, nor can it prevent phantom reading;
3. REPEATABLE READ (MySQL default)
High security
Poor performance
It can prevent dirty reading and non repeatable reading, but it can not prevent phantom reading;
4. SERIALIZABLE
It has the highest security and will not have any concurrency problems, because its access to the same data is serial and non concurrent;
The worst performance; (not used!!)
The default isolation level of MySQL is REPEATABLE READ, which can prevent dirty reads and non repeatable reads
Set isolation level (understand)
0. MySQL queries the current transaction isolation level
select @@tx_isolation;
1. MySQL set transaction isolation level (understand)
(1) set tx_isolation='read-uncommitted';
The security is the worst, prone to dirty reading, non repeatable reading and phantom reading, but the performance is the highest
(2) set tx_isolation='read-committed';
General security, can prevent dirty reading, can not prevent non repeatable reading and unreal reading
(3) set tx_isolation='repeatable-read';
Good security, can prevent dirty reading and non repeatable reading, but can not prevent phantom reading
(4) set tx_isolation='serialiable';
The security is the best, which can prevent all transaction concurrency problems, but the performance is the worst.
2. JDBC set transaction isolation domain
Set the transaction isolation level through the method provided by Connection in JDBC:
Connection.setTransactionIsolation(int level)
The optional parameters are as follows:
Connection.TRANSACTION_READ_UNCOMMITTED 1(Read uncommitted data) Connection.TRANSACTION_READ_COMMITTED 2(Read submitted data) Connection.TRANSACTION_REPEATABLE_READ 4((repeatable) Connection.TRANSACTION_SERIALIZABLE 8((serialization) Connection.TRANSACTION_NONE 0((do not use transactions)
Tip: generally, it is not necessary to modify the transaction isolation level during development
3. Transfer example in JDBC
Tip: automatic transaction submission is the default in JDBC, so you need to turn off automatic submission and manually submit transactions instead
In other words, after auto commit is turned off, the transaction will be started automatically, but you need to manually commit or rollback after execution!!
(1) Execute the following procedure. If there is no abnormality in the execution of the procedure, the transfer is successful! Account A subtracts 100 yuan and account B increases 100 yuan.
(2) Release the code in steps 4 and 5, execute the program again, throw exceptions in the transfer process, and the transfer fails! Since the transaction is rolled back, the amounts of accounts A and B remain unchanged.
public static void main(String[] args) throws SQLException {
Connection conn = null;
Statement stat = null;
ResultSet rs = null;
try {
//1. Get connection
conn = JdbcUtil.getConn();
//2. Turn off JDBC auto commit transaction (transaction is turned on by default)
conn.setAutoCommit(false);
//3. Get the transmitter
stat = conn.createStatement();
/* ***** A Transfer 100 yuan to B***** */
//4.A account minus 100 yuan
String sql = "update acc set money=money-100 where name='A'";
stat.executeUpdate(sql);
//int i = 1/0; // Let the program throw an exception and interrupt the transfer operation
//5.B account plus 100 yuan
sql = "update acc set money=money+100 where name='B'";
stat.executeUpdate(sql);
//6. Manually commit transactions
conn.commit();
System.out.println("Transfer succeeded! Commit transaction...");
} catch (Exception e) {
e.printStackTrace();
//Once one of the operations fails, it will be rolled back, making both operations unsuccessful
conn.rollback();
System.out.println("Execution failed! Rollback transaction...");
} finally{
JdbcUtil.close(conn, stat, rs);
}
}