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 code

The 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