Welcome to pay attention to github.com/hsfxuebao, I hope to help you, if you think it can trouble to click on the Star ha

1. Overview of database transactions

1.1 Storage Engine Support

The SHOW ENGINES command is used to check what storage ENGINES MySQL currently supports and whether they support transactions.

You can see that in MySQL, only InnoDB supports transactions.

1.2 Basic Concepts

Transaction: A set of logical units of operation that change data from one state to another.

The principle of transaction processing: Ensure that all transactions are executed as a unit of work, and that this execution cannot be changed even if a failure occurs. When multiple operations are performed in a transaction, either all transactions are committed, and the changes are saved permanently; Or the database management system will discard all changes and rollback the transaction to its original state.

1.3 ACID properties of transactions

  • Atomicity

    Atomicity means that a transaction is an indivisible unit of work and either all commit or all fail and roll back. That is, either the transfer succeeds or the transfer fails, there is no intermediate state. What happens if you can’t guarantee atomicity? If the operation fails to subtract 100 yuan from account A and add 100 yuan to account B, the system will lose 100 yuan for no reason.

  • Consistency

    (Consistency is incorrect on many websites in the country, please refer to Wikipedia for details on Consistency)

    By definition, consistency refers to the transition of data from one valid state to another valid state before and after a transaction is executed. This state is semantic rather than syntactic and business-related.

    So what is a legitimate data state? A state that satisfies a predetermined constraint is called a legitimate state. More generally, this state is defined by you (e.g., satisfying real-world constraints). Satisfy this state, the data is consistent, do not satisfy this state, the data is inconsistent!

    If an operation in a transaction fails, the system automatically cancels the currently executing transaction and returns to the state before the transaction operation.

    Example 1: Account A has 200 yuan and 300 yuan is transferred out. The balance of account A is -100 yuan. You naturally see that the data is inconsistent at this point. Why? Because you define a state, the balance column has to be greater than or equal to 0.

    Example 2:200 yuan was transferred from account A to account B, 50 yuan was deducted from account A, but the balance of account B did not increase due to various accidents. You also know that the data is inconsistent at this point. Why? Because you’re defining A state where the total balance of A plus B has to be constant.

    Example 3: When we set the name field to be unique in the table, if the name in the table is not unique when the transaction is committed or rolled back, the consistency requirement of the transaction is broken.

  • Isolation

    The isolation of a transaction means that the execution of a transaction cannot be interfered by other transactions, that is, the operations and data used within a transaction are isolated from other concurrent transactions, and the concurrent transactions cannot interfere with each other.

    What if isolation is not guaranteed? So let’s say account A has $200, and account B has $0. Account A transfers money to account B twice, with an amount of 50 yuan each time, which is executed in two transactions respectively. If isolation is not guaranteed, the following situation occurs:

    UPDATE accounts SET money = money - 50 WHERE NAME = 'AA';
UPDATE accounts SET money = money + 50 WHERE NAME = 'BB';
Copy the code

  • “Durability”

    Persistence means that once a transaction is committed, its changes to the data in the database are permanent and should not be affected by subsequent operations or database failures.

    Persistence is guaranteed through transaction logging. Logs include redo logs and rollback logs. When we modify data through transactions, the database changes are first recorded in the redo log, and then the corresponding rows in the database are modified.

    The advantage of this is that even if the database system crashes, the redo log that was not updated to the database system can be found and re-executed after the database restarts, making the transaction persistent.

ACD is the four characteristics of transaction, in which atomicity is the basis, isolation is the means, consistency is the constraint condition, and durability is our goal.

Database transaction is actually a database designer for convenience, the need to ensure atomicity, isolation, consistency and persistence of one or more database operations called a transaction.

1.4 Transaction status

We now know that a transaction is an abstract concept, which actually corresponds to one or more database operations. MySQL roughly divides a transaction into several states according to the different stages of the operation:

  • Active

A transaction is said to be active when its database operation is in progress.

  • Partially committed

A transaction is said to be in a partially committed state when the last operation in the transaction completes, but the impact is not flushed to disk because the operation is in memory.

  • Failed to do STH.

When a transaction is in the active or partially committed state, it may encounter some error (database error, operating system error, or direct power outage) and cannot continue, or the execution of the current transaction may be stopped artificially.

  • Aborted

If the transaction partially executes and becomes in a failed state, you need to restore the operations in the modified transaction to the state before the transaction was executed. In other words, undo the impact of a failed transaction on the current database. We call this undo process a rollback. When the rollback operation completes, that is, the database is restored to the state before the transaction was executed, the transaction is said to be in the aborted state.

  • Committed

A partially committed transaction is said to be in the committed state after it synchronizes its modified data to disk.

A basic state transition diagram is shown below:

Visible only if the transaction is insubmitorsuspendIs the end of a transaction’s life cycle. For committed transactions, changes made to the database are permanent, and for aborted transactions, all changes made to the database are rolled back to the state before the transaction was executed.

2. How are transactions used

There are two ways to use transactions: explicit and implicit.

2.1 Explicit Transactions

Step 1: START TRANSACTION or BEGIN, which explicitly starts a TRANSACTION.

mysql> BEGIN; Mysql > START TRANSACTION;Copy the code

The START TRANSACTION statement differs from BEGIN in that it can be followed by several modifiers:

  • READ ONLY: Indicates that the current transaction is a read-only transaction, that is, database operations belonging to the transaction can ONLY READ data, not modify it.

    Supplement: only those who are not allowed to modify other transactions in the read-only transactions can also access to the data in the TABLE, for temporary TABLE (we use the CREATE TMEPORARY TABLE CREATE TABLE), because they can only be seen in the current session, so read-only transactions is the temporary TABLE can be add, delete, change operations.

  • READ WRITE: Indicates that the current transaction is a READ/WRITE transaction, that is, database operations belonging to the transaction can either READ or modify data.

  • WITH CONSISTENT SNAPSHOT: starts CONSISTENT read.

Step 2: Operations in a series of transactions (mainly DML, not DDL)

Step 3: Commit or abort the transaction (that is, roll back the transaction)

Commit transaction. When a transaction is committed, changes to the database are permanent. mysql> COMMIT;Copy the code
# 回滚事务。即撤销正在进行的所有没有提交的修改
mysql> ROLLBACK;
Copy the code
Rollback the transaction to a savepoint. mysql> ROLLBACK TO [SAVEPOINT]Copy the code

SAVEPOINT operations are as follows:

Create savepoints in the transaction for subsequent rollback against savepoints. Multiple savepoints can exist in a transaction. SAVEPOINT SAVEPOINT name;Copy the code
Erase a save point. RELEASE SAVEPOINT Name of the SAVEPOINTCopy the code

2.2 Implicit Transactions

MySQL has a system variable called autocommit:

mysql> SHOW VARIABLES LIKE 'autocommit'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | ON | +---------------+-------+ 1 row in set (0.01sec)Copy the code

Of course, if we want to turn off autosubmit, we can use one of the following two methods:

  • Explicit use ofSTART TRANSACTIONorBEGINStatement starts a transaction. This will temporarily disable auto-commit until the transaction commits or rolls back.
  • Take the system variablesautocommitIs set toOFF, like this:
SET autocommit = OFF; SET autocommit = 0;Copy the code

We write multiple statements that belong to the same transaction, until we display a COMMIT statement to COMMIT the transaction, or a ROOLLBACK statement to roll back the transaction.

Oracle does not COMMIT automatically by default. You need to write the COMMIT command. MySQL commits automatically by default

2.3 Cases of implicit submission of data

  • Data Definition Language (DDL)
    • Database objects refer to structures such as databases, tables, views, and stored procedures. When we modify a database object using statements such as CREATE, ALTER, or DROP, we implicitly commit the transaction to which the preceding statement belongs. That is:
    BEGIN; SELECT... UPDATE... A statement in a transaction... CREATE TABLE,... This statement commits the transaction to which the preceding statement belongsCopy the code
  • Implicitly use or modify tables in the mysql database
    • When we use statements such as ALTER USER, CREATE USER, DROP USER, GRANT, RENAME USER, REVOKE, and SET PASSWORD, we implicitly commit the transaction to which the preceding statement belongs.
  • Transaction control or statements about locking
    • When we START another TRANSACTION with a START TRANSACTION or BEGIN statement before a TRANSACTION has been committed or rolled back, the previous TRANSACTION is implicitly committed. That is:
    BEGIN; SELECT.·,# UPDATE,.,# UPDATE... BEGIN:# This statement implicitly commits the transaction to which the preceding statement belongsCopy the code
    • The current autoCOMMIT system variable has a value of OFF, and when we manually set it to ON, we implicitly commit the transaction to which the preceding statement belongs.
    • Locking statements that use LOCK TABLES, UNLOCK TABLES, and so on implicitly commit the transaction to which the preceding statement belongs.
  • A statement to load data
    • When the LOAD DATA statement is used to bulk import DATA into the database, the transaction to which the preceding statement belongs is also implicitly committed.
  • Some statements about MySQL replication
    • Statements such as START SLAVE, STOP SLAVE, RESET SLAVE, and CHANGE MASTER TO are implicitly committed TO the transaction TO which the preceding statement belongs.
  • Other statements
    • Use ANALYZE TABLE, CACHE INDEX, CHECK TABLE, FLUSH, LOAD INDEX INTO CACHE, OPTIMIZE TABLE, REPAIR Statements such as TABLE and RESET implicitly commit the transaction to which the preceding statement belongs.

2.4 Example 1: Commit and Rollback

By default, this transaction will be processed by MySQL.

Case 1:

CREATE TABLE user (name varchar (20), PRIMARY KEY (name)) ENGINE=InnoDB; The BEGIN. INSERT INTO user SELECT 'COMMIT'; BEGIN; INSERT INTO user SELECT 'li4'; INSERT INTO user SELECT 'li4'; ROLLBACK; SELECT * FROM user;Copy the code

Running result (1 row of data) :

mysql> commit; Query OK, 0 rows affected (0.00 SEC) mysql> BEGIN; Query OK, 0 rows affected (0.00 SEC) mysql> INSERT INTO user SELECT 'c'; Query OK, 1 rows affected (0.00 SEC) mysql> INSERT INTO user SELECT 'c'; Duplicate entry 'id 'for key 'user.PRIMARY mysql> ROLLBACK; Query OK, 0 rows affected (0.01 SEC) mysql> select * from user; + -- -- -- -- -- -- + | name + | | + -- -- -- -- -- - zhang SAN | + -- -- -- -- -- - + 1 row in the data set (0.01 seconds)Copy the code

Situation 2:

CREATE TABLE user (name varchar(20), PRIMARY KEY (name)) ENGINE=InnoDB; BEGIN; INSERT INTO user SELECT 'zhang2'; COMMIT; INSERT INTO user SELECT 'li4'; INSERT INTO user SELECT 'li4'; ROLLBACK; Mysql > SELECT * FROM user; + -- -- -- -- -- - + I name | + -- -- -- -- -- - + zhang SAN li si I I I I + -- -- -- -- -- - + 2 rows in the data set (0.01 seconds)Copy the code

Case 3:

CREATE TABLE user(name varchar(255), PRIMARY KEY (name)) ENGINE=InnoDB; SET @@completion_type = 1; BEGIN; INSERT INTO user SELECT 'COMMIT'; INSERT INTO user SELECT 'li4'; INSERT INTO user SELECT 'li4'; The ROLLBACK. SELECT * FROM user; Mysql > SELECT * FROM user; + -- -- -- -- -- - + I name | + -- -- -- -- -- - + I zhang SAN I + -- -- -- -- -- - + 1 row in the data set (0.01 seconds)Copy the code

You can see the same SQL code, except that SET@comp1et1 on_type=1 is set before the transaction begins; “, the result is the same as the first time we dealt with, only one “Zhang SAN”. Why is that?

The completion_type parameter in MySQL has three possibilities:

  • Completion =0, that’s the default. When we COMMIT, we COMMIT the TRANSACTION, and when we execute the next TRANSACTION, we START it with either START TRANSACTION or BEGIN.

  • Completion =1, in this case, when we COMMIT the transaction, we will perform a COMMIT AND CHAIN, which means that a transaction of the same isolation level will be opened after we COMMIT the transaction.

  • Completion =2, in this case COMMIT=COMMIT AND RELEASE, which means that when we COMMIT, we will automatically disconnect from the server.

When you set autocommit=0, whether you want to START TRANSACTION or BEGIN, you need to COMMIT to validate the TRANSACTION and ROLLBACK the TRANSACTION with ROLLBACK.

When we set autoCOMMIT =1, each SQL statement is automatically committed. However, if you explicitly START a TRANSACTION with START TRANSACTION or BEGIN, the TRANSACTION will only take effect at COMMIT and will only be rolled back at ROLLBACK.

3. Transaction isolation level

MySQL is a client/server architecture software. For a server, there can be several clients connected to it. After each client is connected to the server, it can be called a Session.

Each client can issue a request statement to the server in its own session, and a request statement can be part of a transaction, meaning that it is possible for the server to process multiple transactions simultaneously.

Transactions have the property of isolation. In theory, when a transaction accesses a certain data, other transactions should queue up. After the transaction commits, other transactions can continue to access the data. However, this has a significant impact on performance, and it depends on the trade-off between maintaining transaction isolation and making the server perform as well as possible when multiple transactions access the same data.

3.1 Data Preparation

We need to create a table:

CREATE TABLE student ( studentno INT, name VARCHAR(20), class varchar(20), PRIMARY KEY (studentno) )Engine=InnoDB CHARSET=utf8;
Copy the code

Then insert a row of data into the table:

INSERT INTO sc VALUES(1, '1', '1 ');Copy the code

Now the table looks like this:

3.2 Data concurrency Problems

How do we choose between transaction isolation and concurrency? Let’s take a look at some of the problems that can occur when transactions accessing the same data do not guarantee serial execution (i.e., execute one before executing another T) :

  • Dirty Write

    • For two transactions, Session A and Session B, if Session A modifies the data changed by the other uncommitted Session B, it means that A dirty write has occurred

    • Session A and Session B each start A transaction. The transaction in Session B first uses studentno? The name column of the record listed as 1 is updated to ‘li Si’, and the transaction in Session A then updates the name column to ‘li si’. The name column of the record at column 1 is updated to ‘zhang SAN’. If the transaction in Session B is later rolled back, the update in Session A will not exist, which is called dirty write. The transaction in Session A has no effect at all, because the data is updated, the transaction is committed, and the data is not changed at all. The default isolation level for a SessionA is to wait for updates to the SessionA.

  • Dirty Read

    • For two transactions, Session A and Session B, Session A reads fields that have been updated by Session B but have not yet been committed. If Session B is later rolled back, Session A’s readings are temporarily invalid.

    • Session A and Session B each start A transaction. The transaction in Session B updates the name column of the recording whose studentno is 1 to ‘studentNo’. The transaction in Session A queries the recording whose studentNo is 1. If the value of column name is read as triple and the transaction in Session B is later rolled back, then the transaction in Session A is read as non-existent dataCall it dirty reading.

  • Non-repeatable Read

    • For two transactions, Session A and Session B, Session A reads A field, and Session B updates that field. And then Session A reads the same field again, and the value is different. That means an unrepeatable read has occurred.

    • We committed several implicit transactions in Session B, all of which changed the value of the name column in the studentNo column 1. After each transaction is committed, if the transaction in Session A can see the latest value, This phenomenon is also known asUnrepeatable read.

  • Phantom read (shares)

    • For two transactions, Session A and Session B, Session A reads A field from A table, and Session B inserts some new rows into the table. Later, if Session A reads the same table again, there will be A few more rows. That means hallucination has occurred.

    • The transaction in Session A is studentno > 0. Select * from student where name = ‘name’; Then Session B commits an implicit transaction that inserts a new record into table Student; The transaction in Session A queries student for the same condition studentno > 0, and the result set contains the entry inserted by the transaction in Session B. This phenomenon is also called phantom reading. Let’s call the newly inserted recordsThe phantom records.

    Note 1:

    If Session B deletes some records that are studentNo >0 instead of inserting new records, then Session A reads fewer records that are studentNo >0. This phenomenon does not belong to phantom reading. Phantom reading forces a transaction to read a record many times according to a certain condition, and then read a record that was not read before.

    Note 2: What does that mean for a previously read record that cannot be read later? This is equivalent to an unrepeatable read for each record. Phantom reading only focuses on reading records that were not read before.

3.3 Four isolation levels in SQL

In order of severity:

Dirty write > Dirty read > Unrepeatable read > Phantom readCopy the code

Our willingness to trade off some isolation for some performance is reflected here: set some isolation levels, and the lower the isolation levels, the more problems will occur. There are four isolation levels established in the SQL standard:

  • READ UNCOMMITTED: Read uncommitted. At this isolation level, all transactions can see the execution results of other uncommitted transactions. Cannot avoid dirty read, non – repeatable read, phantom read.
  • READ COMMITTED: Read committed, which satisfies a simple definition of isolation: a transaction can only see the changes made by committed transactions. This is the default isolation level for most database systems (but not for MySQL). Dirty reads can be avoided, but non-repeatable and phantom reads still exist.
  • REPEATABLE READ: repeatable read. After transaction A reads A piece of data, transaction B modifies and submits the data. Then, transaction A reads the data again and still reads the original content. Dirty and unrepeatable reads can be avoided, but phantom reads still exist. This is the default isolation level for MySQL.
  • SERIALIZABLE: serializable, ensuring that transactions can read the same rows from a table. Insert, update, and delete operations on the table are prohibited for the duration of this transaction. All concurrency problems can be avoided, but performance is very poor. It can avoid dirty reading, unrepeatable reading and unreal reading.

According to the SQL standard, problems of different severity can occur in concurrent transactions at different isolation levels, as follows:

Isolation level Dirty read probability Non-repeatable read possibility Illusory possibility Read lock
READ UNCOMMITTED Yes Yes Yes No
READ COMMITTED No Yes Yes No
REPEATABLE READ No No Yes No
SERIALIZABLE No No No Yes

Why isn’t dirty writing involved? Because dirty writes are such a serious problem, they are not allowed to happen at any isolation level.

Different isolation levels have different phenomena, and have different locking and concurrency mechanisms. The higher the isolation level, the worse the concurrency performance of the database. The relationship between the four transaction isolation levels and concurrency performance is as follows:

3.4 MySQL supports four isolation levels

The four isolation levels specified in the SQL standard are supported differently by different database vendors. For example, Oracle supports only READ COMMITTED(the default isolation level) and SERIALIZABLE isolation level. Although MySQL supports four isolation levels, it is somewhat different from the problems allowed by the isolation levels specified in SQL standard. Under the isolation level of REPEATABLE READ, MySQL can prohibit phantom READ problems, and the reasons for phantom READ prohibition will be explained in the follow-up.

The default isolation level of MySQL is REPEATABLE READ. You can manually change the isolation level of transactions.

MySQL > SHOW VARIABLES LIKE 'tx_isolation'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | tx_isolation | The REPEATABLE - READ | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC) # MySQL 5.7.20 version later, MySQL > SHOW VARIABLES LIKE 'transaction_isolation'; +-----------------------+-----------------+ | Variable_name | Value | +-----------------------+-----------------+ | Transaction_isolation | the REPEATABLE - READ | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.02 SEC) SELECT @@transaction_ISOLATION;Copy the code

3.5 How Do I Set the Transaction Isolation Level

Change the isolation level of the transaction with the following statement:

The SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL ISOLATION LEVEL. READ UNCOMMITTED > READ COMMITTED > REPEATABLE READ > SERIALIZABLECopy the code

Or:

SET [GLOBAL | SESSION] TRANSACTION_ISOLATION = 'isolation level' # which the isolation level formats:  > READ-UNCOMMITTED > READ-COMMITTED > REPEATABLE-READ > SERIALIZABLECopy the code

About the impact of using GLOBAL or SESSION when setting up:

  • Use the GLOBAL keyword (globally affected) : 
    SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET GLOBAL TRANSACTION.ISOLATION = 'SERIALIZABLE, ';Copy the code

    则

    • The existing session is invalid
    • This applies only to sessions that occur after the statement is executed
  • Use the SESSION keyword (affected at the SESSION scope) : 
    SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET SESSION TRANSACTION.ISOLATION = 'SERIALIZABLE';Copy the code

    Is:

    • Valid for all subsequent transactions of the current session
    • If executed between transactions, it is valid for subsequent transactions
    • This statement can be executed in the middle of an already opened transaction, but does not affect the currently executing transaction

If you want to change the default isolation level of a transaction when the server starts up, you can change the value of the startup parameter transaction_ISOLATION. For example, if transaction_ISOLATION =SERIALIZABLE is specified when the server is started, the default isolation level of the transaction is changed from REPEATABLE-READ to SERIALIZABLE.

Summary: The database provides 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.

3.6 Examples of Different Isolation levels

Demo 1: Read uncommitted dirty reads

Set the isolation level to uncommitted read:

The execution flow of transactions 1 and 2 is as follows:

Demo 2: Read Committed

Set the isolation level to repeatable read and the transaction executes as follows:

Demonstration 3: Unreal reading

4. Common classifications of transactions

From the perspective of transaction theory, transactions can be divided into the following types:

  • Flat Transactions
  • Flat Transactions with Savepoints
  • Chained Transactions
  • Nested Transactions
  • Distributed Transactions

Refer to the article

Chapter 19 “MySQL Technology Insider: InnoDB Storage Engine (2nd edition)” “Database index Design and Optimization”