


An article explaining the principles of transactions and MVCC in MySQL in detail
This article will take you to understand the transactions in MySQL and introduce the principle of MVCC. I hope it can help you!
#01 What is a transaction?
Database transaction refers to a set of data operations. The operations within the transaction are either all successful or all failed. Nothing is done. In fact, it is not that nothing is done. It is possible that some of it is done, but as long as If one step fails, all operations must be rolled back, which is a bit of a non-stop operation.
In MySQL, transaction support is implemented at the engine layer. MySQL is a system that supports multiple engines, but not all engines support transactions. For example, MySQL's native MyISAM engine does not support transactions, which is one of the important reasons why MyISAM was replaced by InnoDB.
1.1 Four major characteristics
- Atomicity: After the transaction starts, all operations are either completed or completed If you don’t do it, you won’t be stuck in the middle. If an error occurs during transaction execution, it will be rolled back to the state before the transaction started, and all operations will be as if they did not happen. That is to say, affairs are an indivisible whole, just like atoms learned in chemistry, which are the basic units of matter.
- Consistency: Before and after the transaction starts and ends, the integrity constraints of the database are not violated. For example, when A transfers money to B, it is impossible for A to deduct the money but B not to receive it.
- Isolation: Only one transaction is allowed to request the same data at the same time, and there is no interference between different transactions. For example, A is withdrawing money from a bank card. B cannot transfer money to this card before A's withdrawal process is completed.
- Durability (Durability): After the transaction is completed, all updates to the database by the transaction will be saved to the database and cannot be rolled back.
1.2 Isolation level
Among the four major characteristics of SQL transactions, atomicity, consistency, and durability are all relatively easy to understand. But the isolation level of transactions is indeed difficult. Today we will mainly talk about the isolation of MySQL transactions.
SQL standard transaction isolation from low to high levels are: read uncommitted (read uncommitted), read committed (read committed), repeatable read (repeatable read) and serializable (serializable) ). The higher the level, the lower the efficiency.
- Read uncommitted: When a transaction has not been committed, the changes it makes can be seen by other transactions.
- Read commit: After a transaction is committed, the changes it makes will be seen by other transactions.
- Repeatable read: The data seen during the execution of a transaction is always consistent with the data seen when the transaction is started. Of course, under the repeatable read isolation level, uncommitted changes are also invisible to other transactions.
- Serialization: As the name implies, for the same row of records, "write" will add a "write lock", and "read" will add a "read lock". When a read-write lock conflict occurs, the transaction accessed later must wait for the completion of the previous transaction before it can continue to execute. So all data under this isolation level is the most stable, but the performance is also the worst.
1.3 Solved concurrency issues
SQL transaction isolation level is designed to solve concurrency issues to the greatest extent:
- 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
- Non-repeatable read: Transaction A has more When transaction A reads the same data multiple times, transaction B updates and submits the data, resulting in inconsistent results when transaction A reads the same data multiple times.
- Phantom reading: System administrator A changed the grades of all students in the database from specific scores to ABCDE grades, but system administrator B inserted a record of specific scores at this time. When system administrator A After the modification is completed, I find that there is still one record that has not been modified, as if I have hallucinated. This is called phantom reading.
SQL Different transaction isolation levels can solve different concurrency problems, as shown in the following table: Only the serialized isolation level solves all three problems, and the other 3 Each isolation level has flaws.
Transaction isolation level | Dirty read | Non-repeatable read | Phantom read |
---|---|---|---|
Read Uncommitted | Possible | Possible | Possible |
Read Committed | Impossible | Possible | Possible |
Repeatable Read | Impossible | impossible | possible |
impossible | impossible | impossible |
The above is the detailed content of An article explaining the principles of transactions and MVCC in MySQL in detail. For more information, please follow other related articles on the PHP Chinese website!

MySQL functions can be used for data processing and calculation. 1. Basic usage includes string processing, date calculation and mathematical operations. 2. Advanced usage involves combining multiple functions to implement complex operations. 3. Performance optimization requires avoiding the use of functions in the WHERE clause and using GROUPBY and temporary tables.

Efficient methods for batch inserting data in MySQL include: 1. Using INSERTINTO...VALUES syntax, 2. Using LOADDATAINFILE command, 3. Using transaction processing, 4. Adjust batch size, 5. Disable indexing, 6. Using INSERTIGNORE or INSERT...ONDUPLICATEKEYUPDATE, these methods can significantly improve database operation efficiency.

In MySQL, add fields using ALTERTABLEtable_nameADDCOLUMNnew_columnVARCHAR(255)AFTERexisting_column, delete fields using ALTERTABLEtable_nameDROPCOLUMNcolumn_to_drop. When adding fields, you need to specify a location to optimize query performance and data structure; before deleting fields, you need to confirm that the operation is irreversible; modifying table structure using online DDL, backup data, test environment, and low-load time periods is performance optimization and best practice.

Use the EXPLAIN command to analyze the execution plan of MySQL queries. 1. The EXPLAIN command displays the execution plan of the query to help find performance bottlenecks. 2. The execution plan includes fields such as id, select_type, table, type, possible_keys, key, key_len, ref, rows and Extra. 3. According to the execution plan, you can optimize queries by adding indexes, avoiding full table scans, optimizing JOIN operations, and using overlay indexes.

Subqueries can improve the efficiency of MySQL query. 1) Subquery simplifies complex query logic, such as filtering data and calculating aggregated values. 2) MySQL optimizer may convert subqueries to JOIN operations to improve performance. 3) Using EXISTS instead of IN can avoid multiple rows returning errors. 4) Optimization strategies include avoiding related subqueries, using EXISTS, index optimization, and avoiding subquery nesting.

Methods for configuring character sets and collations in MySQL include: 1. Setting the character sets and collations at the server level: SETNAMES'utf8'; SETCHARACTERSETutf8; SETCOLLATION_CONNECTION='utf8_general_ci'; 2. Create a database that uses specific character sets and collations: CREATEDATABASEexample_dbCHARACTERSETutf8COLLATEutf8_general_ci; 3. Specify character sets and collations when creating a table: CREATETABLEexample_table(idINT

To safely and thoroughly uninstall MySQL and clean all residual files, follow the following steps: 1. Stop MySQL service; 2. Uninstall MySQL packages; 3. Clean configuration files and data directories; 4. Verify that the uninstallation is thorough.

Renaming a database in MySQL requires indirect methods. The steps are as follows: 1. Create a new database; 2. Use mysqldump to export the old database; 3. Import the data into the new database; 4. Delete the old database.


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

SAP NetWeaver Server Adapter for Eclipse
Integrate Eclipse with SAP NetWeaver application server.

mPDF
mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Dreamweaver Mac version
Visual web development tools

EditPlus Chinese cracked version
Small size, syntax highlighting, does not support code prompt function
