This is the 10th day of my participation in the August More Text Challenge. For details, see:August is more challenging
1. What is a transaction
If you have n components of something and either all of them succeed at the same time or n of them fail at the same time, you put n components into a transaction.
2. Transaction management
(1) Default transactions
An SQL statement is a transaction that by default opens the transaction and commits the transaction. In most databases, one SQL transaction is a default transaction. In Oracle, no default transaction is enabled.
(2) Manual transactions:
1) Start transaction
2) Commit: Commit means that all the SQL between the start of the transaction and the commit is considered valid, and the database is truly updated
Rollback: rollback indicates the rollback of the transaction. All SQL operations between the start of the transaction and the rollback are considered invalid database updates
3. JDBC transaction operations
The default is automatic transactions:
Execute the SQL statement: executeUpdate() —- Each execution of the executeUpdate method represents the automatic commit of the transaction
Manual transactions via JDBC’s API:
To start a transaction: conn.setautocomnmit (false);
Commit transactions: conn.com MIT ();
Rollback transactions: conn.rollback();
Note: The connnection that controls the transaction must be the same, and the connection that executes the SQL and the connnection that starts the transaction must be the same to control the transaction.
4. Transaction characteristics and isolation level
Transaction property ACID
Atomicity refers to the fact that a transaction is an indivisible unit of work in which all or none of the operations in a transaction occur.
In a transaction, the integrity of the data must be consistent before and after the transaction.
Multiple transactions. Transaction Isolation means that when multiple users concurrently access the database, the transaction of one user cannot be disturbed by the transactions of other users, and the data of multiple concurrent transactions should be isolated from each other.
Durability means that once a transaction is committed, it changes the data in the database, it’s permanent, and then even if the database fails it shouldn’t have any impact on it.
The concurrent access problem —- is caused by isolation
If isolation is not considered, the transaction has three concurrent access problems.
1) Dirty read: transaction B reads the uncommitted data of transaction A —— requires transaction B to read the committed data of transaction A
2) Non-repeatable reads: the content of the data read twice in a transaction is inconsistent —– requires that the data be consistent across multiple reads in a transaction
3) Phantom read/virtual read: the amount of data read twice in a transaction is inconsistent —– Requires the number of data read multiple times in a transaction to be consistent
The isolation level of the transaction
1) Read uncommitted data: None of these issues can be resolved
2) Read COMMITTED: can resolve dirty reads, oracle default isolation level
3) repeatable read: can resolve dirty read and non-repeatable read, mysql default isolation level
4) Serializable: can solve dirty read can not be repeated read and virtual read, equivalent to single-machine use database, generally do not set this level.