Shulou(Shulou.com)05/31 Report--

This article introduces how to achieve the isolation level of transactions in mysql, the content is very detailed, interested friends can refer to, hope to be helpful to you.

A transaction is a set of sql statements that succeed or fail together. Transactions should also have atomicity, consistency, isolation and persistence.

1. The basic elements of a transaction (ACID)

1. Atomicity: after the transaction starts, all operations either succeed or fail, and it is impossible to be in the middle 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 broken. A transfers money to B, A deducts the money, but B does not receive the account.

3. Isolation: simultaneous transactions (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: changes made by transactions to the database are saved on disk and will not be lost.

Second, the concurrency of transactions

1. Dirty reading: transaction A reads the uncommitted write data of transaction B, and the read data is called dirty data.

2. Unrepeatable: transaction A reads the same data many times, but in the process of reading, transaction B modifies the data and commits it. Causes the same data to be read multiple times, with different results.

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

III. Four levels of transaction isolation

Transaction isolation level dirty read non-repeatable read fantasy read uncommitted read uncommitted √√√ read committed read committed × √√ repeatable read repeatable read × × √ serialized serializable * *

Get and set the isolation level of the database

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

Query using system variables

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

For mysql8, query using the following variables

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

Set 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 up

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

Fifth, illustrate the situation of each isolation level through examples

Prepare a table and a little data first.

CREATE TABLE `roomt` (`id`int (11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID', `name` varchar (32) DEFAULT' 'COMMENT' name', `money`decimal (11pc.2) DEFAULT '0.00' COMMENT' money', PRIMARY KEY (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;INSERT INTO `account` (`name`, `money`) VALUES ('Aggy`,' 500.00'); INSERT INTO `roomt` (`name`, `money`) VALUES ('Bread,' 100.00')

1. Read unsubmitted

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

Transaction B modifies the data in the table, but it is not committed, and transaction A does read the modified data. If transaction B rolls back for some reason, the data read by transaction An is dirty data.

2. Read submitted

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

If transaction B modifies the data but does not commit, then transaction A still obtains the original data, which solves the problem of dirty reading.

However, transaction B commits, and transaction An executes the last query, and the result is inconsistent with the previous query, which leads to the problem of unrepeatable reading.

3. Repeatable read

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

Transaction B modifies the data and commits it, and the results of the two queries of transaction An are consistent, which solves the problem of unrepeatable reading.

At this time, transaction A modifies the money data whose name is A.

The money with name A becomes 350 instead of 400, and repeatable readability ensures data consistency.

We re-modify the money of all accounts in transaction An equal to 200, while inserting a new piece of data in transaction B.

Transaction A still obtains two pieces of data, which solves the illusory reading problem of transaction A when adding data.

4. Serialization

Set transaction_isolation = 'SERIALIZABLE';set autocommit = 0

Transaction A queries the table, and if it is not committed, the insert statement of transaction B waits there until it times out or transaction A commits.

On the contrary, after transaction B inserts the table and does not commit, transaction A waits for the query of the table until transaction B.

At this time, the reading and writing of the table will lock the table, of course, it also has a great impact on the concurrent performance.

The higher the isolation level, the more you can ensure the integrity and consistency of the data.

VI. Mysql lock

There are two types of locks:

Internal lock: an internal lock executed internally by the mysql server to manage contention for table contents by multiple sessions.

External locks: mysql provides explicit table acquisition locks for customer sessions to prevent other sessions from accessing the table.

There are two types of internal locks:

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

2. Table-level locking: mysql uses table-level locks on 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: locks can be controlled using LOCK TABLE and UNLOCK TABLE.

READ (shared lock): multiple sessions can read data from a table without acquiring locks. In addition, multiple sessions can acquire locks on the same table, and when READ locks, no session can write data to the table. Any write operation will wait until the READ lock is released.

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

The statement that locks the table:

LOCK TABLES table_name [READ | WRITE]

The statement to unlock the table:

UNLOCK TABLES

Lock all tables in the database:

FLUSH TABLES WITH READ LOCK; on how to achieve the isolation level of transactions in mysql to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.