Shulou(Shulou.com)06/01 Report--

Transaction is the execution unit of DBMS. It consists of a limited sequence of database operations. But not any sequence of database operations can become a transaction. Generally speaking, a transaction must meet four conditions (ACID)

①: atomicity (Autmic): transactions in execution, to achieve "don't do it, or do it all!" Which means that the transaction part is not allowed to be executed Even if the transaction cannot be completed because of a failure, the impact on the database should be eliminated during rollback!

②: Consistency: after a transaction, the state of the database is consistent with the business rules; for example, the total amount remains the same after the accounts are transferred to each other.

③: isolation (Isolation): if multiple transactions execute concurrently, they should be executed as if each transaction were executed independently!

④: Durability: transactions are persisted to the database after they are committed.

1. There are two main methods of transaction processing in MYSQL.

①: using BEGIN,ROLLBACK,COMMIT to implement

Start: a START TRANSACTION or BEGIN statement can start a new transaction

Commit: COMMIT can commit the current transaction, making the change permanent.

Rollback: ROLLBACK can roll back the current transaction and cancel its changes

②: directly use set to change the auto-commit mode of mysql

MYSQL is automatically submitted by default, that is, if you submit a QUERY, it will be executed directly!

2. Isolation level of the transaction:

The SQL standard defines four types of isolation levels, including specific rules that define which changes inside and outside the transaction are visible and which are not. Low-level isolation levels generally support higher concurrent processing and have lower system overhead.

①: Read Uncommitted (read uncommitted content)

At this isolation level, all transactions can see the execution results of other uncommitted transactions. This isolation level is rarely used in practical applications because its performance is not much better than other levels. Reading uncommitted data is also known as Dirty Read.

②: Read Committed (read submission)

This is the default isolation level for most database systems (but not the default for MySQL). It satisfies the simple definition of isolation: a transaction can only see changes that have been committed to the transaction. This isolation level also supports so-called non-repeatable reads (Nonrepeatable Read), because other instances of the same transaction may have a new commit during the instance processing, so the same select may return different results.

③: Repeatable Read (reread)

This is the default transaction isolation level for MySQL, which ensures that multiple instances of the same transaction see the same rows of data when reading data concurrently. But in theory, this leads to another thorny problem: Phantom Read. To put it simply, phantom reading means that when the user reads a range of data rows, another transaction inserts a new row in that range, and when the user reads the range of data rows, they will find a new "phantom" row. InnoDB and Falcon storage engines solve this problem through multi-version concurrency control (MVCC,Multiversion Concurrency Control) mechanisms.

④: Serializable (serializable)

This is the highest isolation level, and it solves the problem of phantom reading by forcing the ordering of transactions so that they cannot conflict with each other. In short, it adds a shared lock to each read row of data. At this level, it can lead to a lot of timeouts and lock competition.

-Note: these four isolation levels are implemented with different lock types, and problems are prone to occur if the same data is read. For example:

①: Drity Read: one transaction has updated a piece of data, and another transaction has read the same data at this time. For some reason, if the previous RollBack operates, the data read by the latter transaction will be incorrect.

②: non-repeatable read (Non-repeatable read): the data is inconsistent between two queries of a transaction, which may be due to the original data updated by a transaction inserted between the two queries.

③: Phantom Read: the number of data pens is inconsistent in two queries of a transaction. For example, one transaction queries several columns of data (Row), while another transaction inserts new columns of data at this time. The previous transaction will find several columns of data that it did not have before in the next query. The SQL standard defines four types of isolation levels, including specific rules that define which changes inside and outside the transaction are visible and which are not. Low-level isolation levels generally support higher concurrent processing and have lower system overhead.

3. Modify the default transaction isolation level of MySQL:

-Note: MySQL default transaction isolation level: REPEATABLE-READ (rereadable)

①: the syntax for modifying the transaction isolation level:

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

Note: the default behavior (without session and global) is to set the isolation level for the next (not started) transaction. If you use the GLOBAL keyword, the statement sets the default transaction level globally for all new connections created from that point (except for those that do not exist). You need SUPER permission to do this. Use the SESSION keyword to set the default transaction level for future transactions executed on the current connection. Any client is free to change the session isolation level (even in the middle of the transaction) or set the isolation level for the next transaction.

②: query the global and session transaction isolation levels with the following statement:

SELECT @ @ global.tx_isolation

SELECT @ @ session.tx_isolation

SELECT @ @ tx_isolation

③: set transaction isolation level:

-modify the my.cnf configuration file:

[mysqld]

Transaction-isolation = READ-COMMITTED

-use the command to modify:

Mysql > set global transaction isolation level read committed

Mysql > set session transaction isolation level read committed

Start transaction; / / Open transaction

Expand knowledge:

"deadlock caused by two different insert statements:"

If there are no special requirements, you can set the default isolation level of MySQL to read-committed level, which can avoid a lot of unnecessary problems; it is also a way to solve deadlocks

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.