Detailed introduction to MySQL transaction related knowledge (code example)

This article brings you a detailed introduction to MySQL transaction-related knowledge (code examples). It has certain reference value. Friends in need can refer to it. I hope it will be useful to you. helped.

MySQL transactions and transaction isolation levels

MySQL transactions are mainly used to process data with large operations and high complexity. For example, in the personnel management system, if you delete a person, you will delete the basic information of the person, and also delete the information related to the person, such as mailbox, articles, etc. These database operation statements constitute a transaction (recommended course: MySQL Tutorial)

• In MySQL, only databases or tables using the Innodb database engine support transactions

• Transaction processing can be used to maintain the integrity of the database and ensure that batches of SQL statements are either all executed or not executed.

• Transactions are used to manage insert, update, and delete statements

• Generally speaking, transactions must meet four conditions: atomicity, consistency, isolation, and durability

• Atomicity: All operations in a transaction are either all executed or none are executed and will not end somewhere in the middle. If an error occurs during the execution of the transaction, it will be rolled back to the state before the transaction started

• Consistency: Before the transaction starts and after the transaction ends, the integrity of the database Sex is not ruined. This means that the data written must fully comply with all preset rules, including the accuracy and concatenation of the data, and the subsequent database can spontaneously complete the scheduled work

• Isolation Characteristics: The database allows multiple concurrent transactions to read, write and modify its data at the same time. Isolation can prevent data inconsistency due to cross execution when multiple transactions are executed concurrently. Transaction isolation is divided into different levels, including read uncommitted, read committed, repeatable read and serializable

• Persistence:Transaction processing After completion, the modification to the data is permanent and will not be lost even if the system fails.

# Under the default setting of the MySQL command line, transactions are automatic Submitted, that is, the COMMIT operation will be performed immediately after executing the SQL statement. Therefore, to explicitly start a transaction, you need to use the command BEGIN or START TRANSACTION, or execute the command SET AUTOCOMMIT=0 to disable the use of automatic submission of the current painting

Transaction control statement:

• BEGIN or START TRANSACTION; Explicitly start a transaction

• COMMIT; you can also use COMMIT WORK, both equivalent. COMMIT will commit the transaction and make all modifications to the database permanent

• ROLLBACK; you can also use ROLLBACK WORK, which are equivalent. Rollback will end the user's transaction and undo all uncommitted modifications in progress

• SAVEPOINT identifier; SAVEPOINT allows the creation of a save within the transaction point, a transaction can have multiple SAVEPOINT

• RELESE SAVEPOINT identifier;Delete the save point of a transaction. When there is no specified save point, executing this statement will Throw an exception

• ROLLBACK TO identified;Roll the transaction back to the marked point

• SET TRANSACTION ; Used to set the isolation level of the transaction. InnoDB storage engine provides transaction isolation levels of READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE

There are two main methods for MySQL transaction processing:

1. Use BEGIN, ROLLBACK, and COMMIT to implement

• BEGINStart a transaction

• ROLLBACKTransaction rollback

• COMMITTransaction confirmation

Direct SET to change MySQL Automatic submission mode:

• SET AUTOCOMMIT=0 disables automatic submission

• SET AUTOCOMMIT=1 turns on automatic submission

One of the four major characteristics of transactions: isolation

1. There is a certain degree of isolation between transaction A and transaction B

2. read uncommited Read uncommitted

• #At this isolation level, all transactions can see the execution results of other uncommitted transactions. This isolation level is rarely used in practical applications. Reading uncommitted data is called dirty data

read COMMIT

• The default isolation level of most database systems (But not MySQL). A transaction can only see changes made by committed transactions. It avoids dirty reads, but there are still problems with non-repeatable reads and phantom reads

repeatable read

• MySQL's default level; ensures that multiple instances of the same transaction will see the same data rows when reading data concurrently. Dirty reads and non-repeatable reads are avoided, but another problem will occur: phantom reads. Phantom reading means that when the user reads a certain range of data rows, another transaction inserts a new row in the range. When the user reads the data rows in the range again, a new phantom row will be found. InnoDB and Falcon storage engines solve this problem through the multi-version concurrency control (MVCC) mechanism

• The MVCC mechanism is used under the isolation level of repeatable read, and the select operation will not be updated. The version number is a snapshot read (historical version); insert, update and delete will update the version number, which is the current read (current version)

serializable

• The highest isolation level solves the phantom read problem by forcing transactions to be sorted so that they cannot conflict with each other. In short, it adds a shared lock on each data row read. At this level, it may lead to a large number of timeouts and lock competition

Set the transaction isolation level

1. Set in the my.cnf file

- READ-UNCOMMITTED
- READ-COMMITED
- REPEATABLE-READ
- SERIALIZABLE
* 例如
[mysqlId]
transaction-isolation = READ-COMMITTED

1. Dynamicly set the isolation level through commands

SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL <isolation-level>
其中isolation-level可以是：
- READ UNCOMMITTED
- READ COMMITTED
- REPEATABLE READ
- SERIALIZABLE

GLOBAL|SESSION表示事务隔离级别的作用范围：
GLOBAL：表示对所有会话有效
SESSION：表示对当前会话有效

Transaction concurrency issues

1. Dirty read: Transaction A reads the data updated by transaction B, and then B rolls back the operation, then the data read by A is dirty data

2. Non-repeatable read: transaction A reads the same data multiple times, and transaction B updates the data but does not commit it during the multiple readings of transaction A. As a result, transaction A reads the same data multiple times and the results are inconsistent

3. Phantom reading: The number of data items read before and after is inconsistent. This is because during the multiple reads of transaction A, transaction B performs insert or delete operations on the table

The above is the detailed content of Detailed introduction to MySQL transaction related knowledge (code example). For more information, please follow other related articles on the PHP Chinese website!