This article uses examples to describe MySQL transactions, isolation levels, and lock usage. Share with you for your reference, the details are as follows:

A transaction is a group of SQL statements that succeed or fail together. Transactions should also have atomicity, consistency, isolation, and durability.

1. Basic elements of transactions (ACID)

1. Atomicity: After the transaction starts, all operations are either completely successful or completely failed. There is no possibility of being in an intermediate state. The transaction is an indivisible whole, just like an atom.

2. Consistency: Before and after the transaction starts and ends, the integrity constraints of the database are not violated. A transfers money to B, A deducts the money, but B does not receive it.

3. Isolation: Transactions occurring simultaneously (concurrent transactions) should not cause the database to be in an inconsistent state. Each transaction is executed independently and does not affect the existence of other transactions.

4. Persistence: All changes made by transactions to the database are saved on disk and will not be lost.

2. Concurrency issues of transactions

1. Dirty read: Transaction A reads the uncommitted written data of transaction B. The read data is called dirty data.

2. Non-repeatable read: Transaction A reads the same data multiple times, but during the reading process, transaction B modifies the data and commits it. This results in different results when the same data is read multiple times.

3. Phantom read: Transaction A modifies all data rows in the table, such as setting status = 1, but at the same time, transaction B inserts a new row of data into the table with status = 0. For the user operating transaction A, there is still a record in the table that has not been modified, just like an illusion.

3. Four levels of transaction isolation

4. Get and set database isolation level

SHOW VARIABLES LIKE '%isolation%';
SHOW GLOBAL VARIABLES LIKE '%isolation%';

Use system variables to query

SELECT @@GLOBAL.tx_isolation;
SELECT @@SESSION.tx_isolation;
SELECT @@tx_isolation;

For mysql8, use the following variables to query

SELECT @@GLOBAL.transaction_isolation;
SELECT @@SESSION.transaction_isolation;
SELECT @@transaction_isolation;

Setting the isolation level

SET GLOBAL tx_isolation = 'isolation level';
SET SESSION tx_isolation = 'isolation level';
SET @@tx_isolation = 'Isolation level';

For mysql8, use the following statement to set

SET GLOBAL transaction_isolation = 'isolation level';
SET SESSION transaction_isolation = 'isolation level';
SET @@transaction_isolation = 'Isolation level';

5. Explain the isolation levels through examples

First prepare a table and some data.

CREATE TABLE `account` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
 `name` varchar(32) DEFAULT '' COMMENT 'name',
 `money` decimal(11,2) DEFAULT '0.00' COMMENT 'Money',
 PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `account` (`name`, `money`) VALUES ('A', '500.00');
INSERT INTO `account` (`name`, `money`) VALUES ('B', '100.00');

1. Read uncommitted

set transaction_isolation = 'READ-UNCOMMITTED';
set autocommit = 0;

Transaction B modified the data in the table but did not commit it. Transaction A did read the modified data. If transaction B is rolled back for some reason, the data read by transaction A is dirty data.

2. Read Submitted

set transaction_isolation = 'READ-COMMITTED';
set autocommit = 0;

If transaction B modifies the data but does not commit it, transaction A still obtains the original data, thus solving the dirty read problem.

However, when transaction B is committed, transaction A executes the last query, and the result is inconsistent with the previous query, which causes the problem of non-repeatable read.

3. Repeatable reading

set transaction_isolation = 'REPEATABLE-READ';
set autocommit = 0;

Transaction B modified the data and committed it. The results of the two queries by transaction A were consistent, solving the problem of non-repeatable reads.

At this time, transaction A modifies the money data named A.

The money of the file named A becomes 350 instead of 400. Repeatable read ensures data consistency.

We modify the money of all accounts to 200 in transaction A and insert a new data in transaction B.

Transaction A still obtains two pieces of data, which solves the phantom read problem that occurs in transaction A when new data is added.

4. Serialization

set transaction_isolation = 'SERIALIZABLE';
set autocommit = 0;

Transaction A queries the table. If it is not committed, the insert statement of transaction B will wait there until it times out or transaction A is committed.

On the contrary, if transaction B does not commit after inserting data into the table, transaction A will wait for the query on the table until transaction B commits.

At this time, both reading and writing the table will lock the table, which of course has a greater impact on concurrent performance.

The higher the isolation level, the better the data integrity and consistency can be guaranteed.

6. MySQL lock

There are two types of locks:

Internal locks: Internal locks performed internally by the MySQL server to manage contention for table contents by multiple sessions.

External locks: MySQL provides client sessions with the ability to explicitly acquire table locks to prevent other sessions from accessing the table.

Internal locks can be of two types:

1. Row-level locks: Row-level locks are fine-grained, and only the accessed rows are locked, which allows multiple sessions to perform write access simultaneously.

2. Table-level locks: MySQL uses table-level locks for MyISAM, Memory, and Merge tables, allowing only one session to update the table at a time, which makes these storage engines more suitable for read-based operations.

External locks: Locking can be controlled using LOCK TABLE and UNLOCK TABLE.

READ (Shared lock): Multiple sessions can read data from a table without acquiring a lock. In addition, multiple sessions can obtain a lock on the same table. When a READ lock is held, no session can write data to the table. Any write operations will wait until the READ lock is released.

WRITE (exclusive lock): When a table is locked by WRITE, no session other than the session holding the lock can read or write data unless the WRITE lock is released.

Lock table statement:

LOCK TABLES table_name [READ | WRITE];

Unlock table statement:

UNLOCK TABLES;

Lock all tables in the database:

FLUSH TABLES WITH READ LOCK;

Readers who are interested in more MySQL-related content can check out the following topics on this site: "Summary of MySQL Index Operation Skills", "Summary of MySQL Common Functions", "Summary of MySQL Log Operation Skills", "Summary of MySQL Transaction Operation Skills", "Summary of MySQL Stored Procedure Skills" and "Summary of MySQL Database Lock-Related Skills".

I hope this article will be helpful to everyone's MySQL database design.