Learning Notes for database transactions

Understanding of the transaction itself

1. A transaction is a set of atomic SQL queries that are either completely successful or fail completely for queries within a transaction.

2.mysql The default transaction is automatically committed, that is, autocommit=true, which means that an SQL query is a transaction.

3. For multiple statements, use start transaction, and commit (rollback) to wrap multiple statements into a larger transaction unit.

4. If you set Autocommit=false, the statement will not be committed until you use rollback, or commit.

5. Characteristics of the transaction acid (atomicity, consistency, isolation, persistence)

Atomicity: Requires a transaction to be indivisible, the inside statement either all succeeds, or all fails, and does not see the middle state.

Consistency: The personal feeling is that the constraint relationship between the database data itself and the data will not change before or after the transaction.

Isolation: When a transaction is committed, his modifications to the record are not visible to other transactions. To prevent excessive locks, MySQL is implemented through MVCC.

Persistence: Transaction-to-record modification, which is permanently saved to disk at commit time. MySQL in order to improve transaction efficiency, the storage engine typically modifies only the memory data while modifying the data, logging the modification operation to the transaction log, and then asynchronously brushing back to the disk.

Understanding of the Transaction isolation level

Read uncommited

READ UNCOMMITTED, the lowest level of the database, and the transaction reads uncommitted data from other transactions. This transaction level only guarantees that the disk is not faulty. The resulting problem is dirty reading.

The age=4 record in the left-hand transaction was updated but not committed, the right-hand transaction was read to the uncommitted record using uncommited read, and the original value was read using a different transaction level.

Read Committed

Unlike read uncommitted, transactions under this level cannot read records that are not committed by other transactions. This is reflected in the previous picture. Although Read committed can guarantee that a transaction is read to a record submitted by someone else, it still exists in the same transaction, with the same record being read two times and the value changed. That is, there are other transactions in the two select that update the record. This inconsistent situation becomes "non-repeatable read"

In the right session under Read committed start a transaction, the first query age=4 the record value is Xiaotong, and then the left to the age=4 record to myname, the right side of the transaction again query, found that the record has changed its name. This is the inconsistency between repeated reads of a record in a transaction, called "non-repeatable read"

repeatable Read (MySQL default level)

REPEATABLE read. In the case of Read committed, Repeatable read is an inconsistent transaction level within a transaction that repeatedly queries the consolidated records.

If you set the right transaction level to repeatable read, no matter how other transactions change the record value during the transaction, the right transaction always matches the query result for the consolidated record.

Serializable (Sequence)

A transaction level with the highest isolation level, which requires serial execution between different transactions, ensures that there is no interference between transactions, but such queries are inefficient, low throughput, and need to be used with caution. At the level of a sequence, there is no phantom read, that is, in one transaction, a batch of transactions is updated, and a new record is inserted into a transaction to satisfy the condition. This update transaction will find that the update operation is still not updated after the commit, which is called "Phantom read".

Transaction-related Common SQL

Querying the transaction isolation level of the current session

SELECT @ @tx_isolation

To view the global transaction isolation level

SELECT @ @global. tx_isolation

This is the transaction level of the session

Set session (global) transaction ISOLATION LEVEL READ UNCOMMITTED

(read committed,repeatable read,serializable)

Learning Notes for database transactions