MySQL transaction and transaction isolation level

This is the 23rd day of my participation in the August More Text Challenge

The transaction

A transaction is a unit of execution consisting of a set of SQL statements that either all or none of them execute.

Four properties of a transaction: ACID

• Atomicity refers to the indivisibility of a transaction, in which all operations of a transaction are either performed continuously or not at all.

• A consistent consistency transaction must transform a database from one consistent state to another. For example, the total amount before the transfer is 2000, and after the transfer is still 2000.

• Isolation Isolation prevents the execution of a transaction from being interfered by other transactions. That is, the operations and data used in a transaction are isolated from other concurrent transactions. Concurrent transactions cannot interfere with each other.

• Durability Durability Once a transaction is committed, its changes to data in the database are permanent and subsequent operations and database failures should not affect them in any way.

Case: Liu Bei transferred 100 yuan to Guan Yu

Initial amount:

• Liu bei1000
• Guan yu1000

Suppose liu Bei’s ID is 1 and Guan Yu’s ID is 2.

UPDATE account SET balance = balance - 100 WHERE id = 1; UPDATE accountSET balance = balance + 100 WHERE id = 2;
Copy the code

Data inconsistencies occur if the second SQL sentence is not executed after the first SQL sentence is executed because of network failure or other reasons. Liu Bei’s money was only 900 yuan, but Guan Yu’s was still 1000 yuan.

You can use the mechanism of transactions to avoid this situation. Simply place two SQL statements in the same transaction:

BEGIN;
UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;
COMMIT;
Copy the code

The BEGIN statement marks the start of a transaction, and the COMMIT statement is used to COMMIT the transaction. During this time, if a failure occurs, MySQL automatically rolls back the transaction and the account is restored to its initial state. If you want to ROLLBACK a transaction manually due to service requirements during the transaction execution, you can perform ROLLBACK before committing.

After starting a transaction with BEGIN, either COMMIT or ROLLBACK.

Isolation level

Transaction isolation level: The degree to which a transaction is isolated from other transactions.

The database specifies multiple transaction isolation levels, which correspond to different levels of interference. The higher the isolation level, the better the data consistency, but the weaker the concurrency.

Four transaction isolation levels:

• Read uncommittedREAD UNCOMMITTED: allows a transaction to read changes that have not been committed by another transaction. Problems with dirty reads, unrepeatable reads, and phantom reads can occur.
• Reading has been submittedREAD COMMITTED: Only allow transactions to read changes that have been committed by other transactions to avoid dirty reads, but non-repeatable reads and phantom read problems can still occur.
• Repeatable readREPEATABLE READ: Ensures that a transaction can read the same value from a field multiple times. By preventing other transactions from updating the field during the duration of the transaction, you can avoid dirty and unrepeatable reads, but the phantom problem still exists.
• serializableSERIALIZABLE: Ensures that a transaction can read the same row from a table. Ban on other transactions in the transaction duration, on the table to perform insert, update, and delete operations, all of the [[202012072244 database concurrency issues: dirty read, not repeatable read, phantom | concurrency issues]] can be avoided, but the performance is very low.

Oracle supports two transaction isolation levels:

• READ COMMITTEDRead already submitted, this tooOracleDefault transaction isolation level.
• SERIALIZABLESerialization.

MySQL supports four transaction isolation levels, with the default being REPEATABLE READ.

Comparison of concurrency problems that isolation levels can solve: