Transaction and isolation levels

1. The transaction

Transactions are the smallest unit of control in concurrent database operations. Ensures that a set of database operations will either all succeed or fail.

Transactions are implemented at the engine level. Some engines support transactions, such as InnoDB, while others do not, such as MyISAM and Memory. In general, just keep these three engines in mind.

ACID

  • Atomicity: All operations in a transaction either succeed or fail, and do not end up in some intermediate stage.

  • Consistency: After the transaction ends, the integrity of the database is not damaged, that is, the database complies with the preset business rules.

  • Isolation: Allows multiple concurrent transactions to read, write, and modify data.

  • Durability: After a transaction ends, all operations of a transaction are persisted to the database.

Start the way

  • Use begin or start transaction to start the transaction, commit the transaction, and rollback the transaction.

  • Use set autocommit=0 to cancel the automatic commit. After that, any statement will start a transaction and will not commit automatically. You need to perform commit or rollback.

Using Set AutoCOMMIT can result in unexpectedly long transactions.

If you do not want to execute begin every time, you can use commit work and chain to commit the transaction, which means that the next transaction is automatically started after the transaction is committed.

Concurrent transaction problem

Concurrency problems can occur when multiple transactions are executed concurrently :(suppose there are two transactions T1 and T2)

  • Dirty reads: If T1 reads a field that has been updated by T2 but has not yet been committed, if T2 rolls back, the read before T1 is invalid.

  • Non-repeatable reads: T1 reads a field during a transaction, T2 updates the field, and the value read after T1 changes.

  • Phantom read: T1 reads some records from a table based on certain conditions, and T2 inserts new records into the table. When T1 reads again, it reads the new records as well.

2. Isolation level

Isolation levels are used to address concurrency issues for transactions.

classification

  • Read uncommitted: Changes made by a transaction can be seen by other transactions before it is committed.

  • Read Commit: After a transaction commits, its changes are seen by other transactions.

  • Repeatable read: The data seen during the execution of a transaction is always the same as the data seen when it was started.

  • Serialization: For the same row, “write” will be added “write lock”, “read lock” will be added.

Write lock is the exclusive lock, namely S lock; Read locks are shared locks, that is, X locks.

Oracle’s default isolation level is read commit.

MySQL’s default isolation level is repeatable reads.

The following discussion assumes that you are at the repeatable read isolation level.

Viewing the Isolation Level

MySQL > query isolation level

#Some versions use TX_ISOLATION
select @@global.tx_isolation;
#Some versions use transaction_ISOLATION
seelct @@global.transaction_isolation;
Copy the code

3. View

Isolation levels are achieved through views.

At the repeatable read isolation level, the view is created when the transaction is started and is used for the entire life of the transaction.

At the read commit level, views are created at the start of each SQL statement execution.

At the uncommitted level, the latest value on the record is returned directly, with no concept of view.

At the serialization level, parallel access is avoided by locking directly.

Roll back log

A rollback log is logged for each record update. By rolling back the log, you can get the value of the previous state.

MVCC

Multiple versions of the same record can exist in the system, which is called version concurrency control (MVCC).

Transactions started at different times have different views, which combine with rollback logs to achieve version concurrency control of data.

When no transaction requires these rollback logs, that is, there is no view older than this rollback log, the rollback log is cleared.

Do not use long transactions

Any rollback logs that may be used by a long transaction must be retained before committing, which can take up a lot of storage space.

In MySQL5.5 and earlier versions, rollback logs are stored in ibData files along with data dictionaries, and the file does not get smaller even if the rollback section is cleared.

Long transactions can tie up lock resources and drag down the entire library.

Query long transaction

Query long transactions whose duration exceeds 60:

select * from information_schema.innodb_trx where TIME_TO_SEC(TIMEDIFF(now(), trx_started)) > 60;
Copy the code