Transactions are an important feature of relational data, but few people have a comprehensive understanding of transactions. This article will cover all aspects of transactions.
The concept of transactions
What is a transaction
A database transaction (transaction) is a logical unit of DBMS execution consisting of a limited sequence of database operations.
Concurrency issues
The five types of concurrency problems are:
- The first type is missing updates
- The second type of lost updates
- Dirty read
- Unrepeatable read
- Phantom read
Here are some examples.
Lost Update
A transaction overwriting an update committed by another transaction is called a lost update.
The first type of lost updates:
| time | The draw money transactions | Deposit money transactions |
|---|---|---|
| T1 | Start the transaction | |
| T2 | Start the transaction | |
| T3 | Query account balance is 10,000 | |
| T4 | Query account balance is 10,000 | |
| T5 | Deposit 1,000 and modify the balance to 11,000 | |
| T6 | Commit the transaction | |
| T7 | Take out 1,000 and change the balance to 9,000 | |
| T8 | Undo the transaction and roll back the balance to 10,000 |
The final balance is 1,000 less.
The second type of lost updates:
| time | The draw money transactions | Deposit money transactions |
|---|---|---|
| T1 | Start the transaction | |
| T2 | Start the transaction | |
| T3 | Query account balance is 10,000 | |
| T4 | Query account balance is 10,000 | |
| T5 | Take out 1,000 and change the balance to 9,000 | |
| T6 | Commit the transaction | |
| T7 | Deposit 1,000 and modify the balance to 11,000 | |
| T8 | Commit the transaction |
The final balance was 1,000 too high.
Dirty Read
A transaction that reads data that has not been committed by another transaction is called a dirty read.
For example, if transaction A modifies A row but does not commit it, transaction B reads the data modified by transaction A, and then transaction A for some reason rolls back, then the data read by transaction B is dirty.
| time | The draw money transactions | Deposit money transactions |
|---|---|---|
| T1 | Start the transaction | |
| T2 | Start the transaction | |
| T3 | Query account balance is 10,000 | |
| T4 | Take out 1,000 and change the balance to 9,000 | |
| T5 | Query account balance is 9,000 (dirty read) | |
| T6 | Undo the transaction and roll back the balance to 10,000 | |
| T7 | Deposit 1,000 and modify the balance to 11,000 | |
| T8 | Commit the transaction |
NonRepeatable Read
One transaction reads the data before another transaction commits and the updated data that has been committed.
Transaction A and TRANSACTION B execute concurrently, transaction A queries the data, then transaction B updates the data, and when transaction A queries the data again, it finds that the data has changed.
Non-repeatable reads often occur during update and DELETE operations.
| time | The draw money transactions | Deposit money transactions |
|---|---|---|
| T1 | Start the transaction | |
| T2 | Start the transaction | |
| T3 | Query account balance is 10,000 | |
| T4 | Query account balance is 10,000 | |
| T5 | Take out 1,000 and change the balance to 9,000 | |
| T6 | Commit the transaction | |
| T7 | The account balance is 9,000 | |
| T8 | The results of two queries for the same transaction are different |
Phantom Read
A transaction makes two queries during operation, and the result of the second query contains or is missing data that was not present in the first query.
For example, transaction A and TRANSACTION B execute concurrently, transaction A queries data, transaction B inserts or deletes data, and transaction A re-queries and finds data in the result set that was not there before, or the data that was there disappears, as if there is an illusion.
Phantom reads often occur during insert operations.
| time | The draw money transactions | Deposit money transactions |
|---|---|---|
| T1 | Start the transaction | |
| T2 | Start the transaction | |
| T3 | The number of queried accounts is 100,000 | |
| T4 | Register a new account | |
| T5 | Commit the transaction | |
| T6 | The number of accounts to be queried is 100,001 | |
| T7 | The results of two queries for the same transaction are different |
Characteristics of transactions
Transactions have four properties, known as ACID, which are:
- Durability (Durability)
- Isolation (Isolation)
- Consistency (Consistency)
- Atomicity (Atomicity)
atomic
All operations in a transaction either complete or do not complete and do not end somewhere in between.
If a transaction fails during execution, it will be rolled back to the state before the transaction began, as if the transaction had never been executed.
consistency
The integrity of the database is not compromised before and after a transaction.
That is, the state of the database satisfies all integrity constraints before and after a transaction.
Isolation,
The ability of a database to allow multiple concurrent transactions to read, write, and modify its data at the same time. Isolation prevents data inconsistencies due to cross-execution when multiple transactions are executed concurrently.
Transaction isolation can be divided into different levels, including Read uncommitted, Read Committed, Repeatable Read, and Serializable.
persistence
After a transaction, changes to the data are permanent and will not be lost even if the system fails.
The isolation level of the transaction
The four isolation levels of a transaction are mentioned in the context of transaction isolation:
- Read Uncommitted
- Read Committed
- Repeatable Read
- Serializable
The isolation level of transactions is also related to the concurrency issues mentioned in 5 above. A higher isolation level ensures data integrity and consistency, but has a greater impact on concurrency performance.
Isolation levels and concurrency issues:
| Isolation level | Dirty read | Unrepeatable read | Phantom read |
|---|---|---|---|
| Uncommitted read | may | may | may |
| Has been submitted to read | Can’t be | may | may |
| Repeatable read | Can’t be | Can’t be | may |
| serializable | Can’t be | Can’t be | Can’t be |
For most applications, setting the isolation level of the database system to Read Committed is a priority. It can avoid update loss, dirty read, and has good concurrency performance. Although it can cause concurrent problems such as unrepeatable reads and phantom reads, it can be controlled by applications using pessimistic or optimistic locking in the rare cases where such problems may occur.
MySQL InnoDB default isolation level is Repeatable Read. By default, databases such as Oracle are Read Committed, that is, only Committed data can be Read.
Read Uncommitted
Read transactions do not block other read and write transactions. Uncommitted write transactions block other write transactions but do not block read transactions.
This isolation level prevents update loss, but does not prevent dirty reads, unrepeatable reads, or phantom reads.
Read Committed
Read transactions allow other read and write transactions. Uncommitted write transactions prohibit other read and write transactions.
Read uncommitted can prevent update loss and dirty reads, but cannot prevent unrepeatable reads and phantom reads.
Repeatable Read
On the premise of operating on the same row of data, a read transaction forbids other write transactions but does not block the read transaction. An uncommitted write transaction forbids other read and write transactions.
This isolation level prevents lost updates, dirty reads, and unrepeatable reads, but not phantom reads.
Serializable
Provides strict transaction isolation, which requires that transactions be serialized and executed one after another, not concurrently.
This isolation level prevents lost updates, dirty reads, unrepeatable reads, and phantom reads.
If transaction serialization is not possible through row-level locking alone, other mechanisms must be used to ensure that newly inserted data is not accessed by the transaction that just performed the query.
The transaction operations
BEGIN
BEGIN or START TRANSACTION: Explicitly starts a TRANSACTION.
COMMIT
COMMIT or COMMIT WORK: Commits the transaction and makes permanent any changes that have been made to the database.
ROLLBACK
ROLLBACK or ROLLBACK WORK: ROLLBACK and undo all uncommitted changes that are being made.
SAVEPOINT
SAVEPOINT Identifier: SAVEPOINT allows you to create a SAVEPOINT in a transaction, and you can have multiple Savepoints in a transaction.
RELEASE SAVEPOINT Identifier: Removes a transaction SAVEPOINT. Execution of this statement throws an exception when no SAVEPOINT is specified.
ROLLBACK
ROLLBACK: Rolls back the transaction.
ROLLBACK TO identifier: ROLLBACK the transaction TO the savepoint.
TRANSACTION
SET TRANSACTION: Sets the isolation level of a TRANSACTION.
InnoDB storage engine provides transaction isolation levels of READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE.
AUTOCOMMIT
Select @@autoCOMMIT: View automatic transaction commit Settings.
Set autoCOMMIT =0: set transaction not tocommit automatically.
Set autoCOMMIT =1: Set automatic transaction commit.
-- View automatic transaction commit Settings
select @@autocommit;
-- Set transactions not to commit automatically
set autocommit=0;
-- Set automatic transaction commit
set autocommit=1;
Copy the codeThe transaction log
The transaction mechanism implementation relies heavily on transaction log files.
The transaction log is a separate file from the database file. It stores all changes made to the database and all records inserts, updates, deletions, commits, rollbacks, and database schema changes. Transaction logs are also called roll forward logs or redo logs. Transaction logs are an important component of backup and recovery.
The database lock
Locking is an important topic for database transactions, which we will not cover here for space reasons, but I will provide an article on it.
The related resources
MySQL > MySQL
- MySQL index and query optimization
- Install the MySQL decompressed version in Windows
- MySQL primary key Auto Increment usage
- Introduction to MySQL Database storage engine