Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

What is the isolation level of MySQL locks from things?

2025-01-28 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)06/01 Report--

Editor to share with you what is the isolation level of MySQL locks and things. I hope you will gain a lot after reading this article. Let's discuss it together.

Preface MySQL Index underlying data structure and algorithm MySQL performance Optimization principles-previous MySQL performance Optimization-practice 1MySQL performance Optimization-practice 2

Earlier, we talked about the underlying data structure and algorithm of MySQL database, and some content of MySQL performance optimization. Let's talk about the lock and transaction isolation level of MySQL, which is divided into two parts. This article focuses on the row lock and transaction isolation level of MySQL.

Lock definition

Lock is a mechanism by which a computer coordinates multiple processes or threads to access a resource concurrently.

In the database, in addition to the contention of traditional computing resources (such as CPU, RAM, Imax O, etc.), data is also a resource that needs to be shared by users. How to ensure the consistency and effectiveness of data concurrent access is a problem that must be solved in all databases, and lock conflicts are also an important factor affecting the performance of database concurrent access.

Lock classification is divided into optimistic lock (realized by version comparison) and pessimistic lock in terms of performance; from the type of database operation: read lock and write lock (both belong to pessimistic lock) read lock (shared lock): for the same data, multiple reads can be performed at the same time without affecting each other; write locks (exclusive locks): it blocks other write locks and read locks before the current write operation is completed. The granularity of database operation is divided into table lock and row lock.

For an in-depth understanding of locks, you can see "understanding locks in Java."

MySQL's lock.

Row lock (Record Locks)

Gap lock (Gap Locks)

Key lock (Next-key Locks)

Shared lock / exclusive lock (Shared and Exclusive Locks)

Intention shared lock / intention exclusive lock (Intention Shared and Exclusive Locks)

Insert intention lock (Insert Intention Locks)

Self-increasing lock (Auto-inc Locks)

Prediction lock, which is mainly used to store the spatial index of spatial data.

Watch lock

Lock the whole table for each operation.

The overhead is small and the locking is fast; there is no deadlock; the lock granularity is large, the probability of lock conflict is the highest, and the concurrency is the lowest. Basic operation

Sample table, as follows:

# create table SQLCREATE TABLE mylock (id INT (11) NOT NULL AUTO_INCREMENT, NAME VARCHAR (20) DEFAULT NULL, PRIMARY KEY (id)) ENGINE = MyISAM DEFAULT CHARSET = utf8;# insert data INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('1YONAME`) VALUES); INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES (' 2cycles journal b`); INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES INSERT Into`test`.`mylock` (`id`, `NAME`) VALUES ('4perpendicular code'); copy code manually add table lock lock table table name read (write), table name 2 read (write); copy code view added lock show open tables; copy code delete table lock unlock tables; copy code case analysis-add lock LOCK TABLE mylock read; copy code

The table can be read by current session and other seesion

Inserting or updating locked tables in the current session will report an error, while other session inserts or updates will wait.

Case study-write lock LOCK TABLE mylock WRITE; copy code

At present, there is no problem with the addition, deletion, modification and query of the table by session, and all operations on the table by other session will be blocked.

Case conclusion

MyISAM automatically adds read locks to all tables involved before executing the query statement (SELECT), and automatically adds write locks to the tables involved before adding, deleting, modifying and querying operations.

Read operations on MyISAM tables (with read locks) do not block read requests from other processes on the same table, but block write requests to the same table. The write operations of other processes are performed only when the read lock is released. Write operations on MyISAM tables (with write locks) block other processes' read and write operations on the same table, and read and write operations of other processes are performed only when the write lock is released.

Summary: read locks block writes, but not reads, while write locks block both reads and writes.

Row lock

Each operation locks a row of data.

High overhead, slow locking, deadlock will occur; locking granularity is the smallest, the probability of lock conflict is the lowest, and the degree of concurrency is the highest.

The biggest difference between InnoDB and MyISAM:

Support transactions (TRANSACTION) support row-level locks support transaction transactions (Transaction) and their ACID attributes

A transaction is a logical processing unit composed of a set of SQL statements. A transaction has the following four attributes, which are usually referred to as the ACID attributes of a transaction.

Atomicity: a transaction is an atomic operation unit that either performs all or none of the changes to the data. Consistent: data must be in a consistent state at the beginning and completion of a transaction. This means that all relevant data rules must be applied to the modification of the transaction to maintain the integrity of the data; at the end of the transaction, all internal data structures (such as B+ tree indexes or bidirectional linked lists) must also be correct. Lsolation: the database system provides an isolation mechanism to ensure that transactions are executed in an "independent" environment that is not affected by external concurrent operations. This means that the intermediate state during the transaction is not visible to the outside, and vice versa. Durable: after a transaction is completed, its modification of the data is permanent, even in the event of a system failure. Problems caused by concurrent transactions lost updates (Lost Update)

When two or more transactions select the same row and then update the row value based on the initially selected value, the problem of missing updates occurs because each transaction is unaware of the existence of other transactions, and the final update overrides the updates made by other transactions.

Dirty reading (Dirty Reads)

A transaction is modifying a record, and the data of this record is in an inconsistent state before the transaction is completed and committed; then another transaction reads the same record, if uncontrolled, the second transaction reads these "dirty" data and makes further processing accordingly, resulting in uncommitted data dependencies. This phenomenon is vividly called "dirty reading".

Summary: transaction A reads the data that has been modified but not yet committed by transaction B, and operates on the basis of this data. At this point, if transaction B rolls back, the data read by transaction An is invalid and does not meet the consistency requirements.

Non-repeatable (Non-Repeatable Reads)

At some time after reading some data, a transaction reads the previously read data again, only to find that the data it reads has been changed, or that some records have been deleted. This phenomenon is called "unrepeatable reading".

Summary: transaction A reads the modified data that has been committed by transaction B, which does not conform to isolation.

Fantasy reading (Phantom Reads)

A transaction re-reads previously retrieved data according to the same query conditions, only to find that other transactions insert new data that meets its query criteria. This phenomenon is called "phantom reading".

Summary: transaction A reads the new data committed by transaction B, which does not conform to isolation.

Transaction isolation level

"dirty reading", "non-repeatable reading" and "phantom reading" are all problems of database read consistency, which must be solved by the database to provide a certain transaction isolation mechanism.

The stricter the transaction isolation of the database, the less the side effects, but the greater the cost, because transaction isolation is essentially to make transactions "serialized" to a certain extent, which is obviously contradictory to "concurrency".

At the same time, different applications have different requirements for read consistency and transaction isolation. For example, many applications are not sensitive to "unrepeatable reading" and "phantom reading", which may be more related to the ability of concurrent data access.

View the transaction isolation level of the current database

Show variables like 'tx_isolation'; copy code

Set transaction isolation level

Set tx_isolation='REPEATABLE-READ'; copy code

The database version is 5.7and the isolation level is Repeatable-Read (repeatable). Different database versions and isolation levels have a great impact on the execution results of statements. So you need to specify the version and isolation level.

Row locks and isolation level case study transaction control statements BEGIN or START TRANSACTION; explicitly open a transaction; COMMIT; can also use COMMIT WORK, but the two are equivalent. COMMIT commits the transaction and makes any changes that have been made to the database permanent; ROLLBACK; can use ROLLBACK WORK, but they are equivalent. Rollback ends the user's transaction and undoes any uncommitted changes in progress; SAVEPOINT identifier;SAVEPOINT allows you to create a SavePoint in a transaction, and multiple SAVEPOINT;RELEASE SAVEPOINT identifier; in a transaction can delete the SavePoint of a transaction, and executing this statement throws an exception when there is no SavePoint specified; ROLLBACK TO identifier; rolls back the transaction to the marker point; SET TRANSACTION; is used to set the isolation level of the transaction. The isolation levels provided by the InnoDB storage engine for transactions are READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE. Transaction processing method

There are two main methods of MYSQL transaction processing:

Use BEGIN, ROLLBACK, COMMIT to implement BEGIN to start a transaction ROLLBACK transaction rollback COMMIT transaction confirmation directly use SET to change the autocommit mode of MySQL: SET AUTOCOMMIT=0 forbids autocommit SET AUTOCOMMIT= 1`` enables autocommit

Sample table, as follows:

CREATE TABLE `user` (`id` INT (11) NOT NULL AUTO_INCREMENT, `name` VARCHAR (255) DEFAULT NULL, `balance` INT (11) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE = INNODB DEFAULT CHARSET = utf8;INSERT INTO `test`.user` (`name`, `balance`) VALUES ('zhangsan','450'); INSERT INTO `test`.user` (`name`, `balance`) VALUES (' lisi', '16000'); INSERT INTO `test`.user` (`name`, `balance`) VALUES (' wangwu','2400'); copy code line lock demonstration

One session turns on transaction updates and does not commit, another seesion updates the same record will block, and update different records u will block.

Read unsubmitted

(1) Open a client A, set the current transaction mode to read uncommitted (read uncommitted), and query the initialization value of the table user

Set tx_isolation='read-uncommitted'; copy code

(2) before the transaction of client A commits, open another client B and update the table user

(3) at this time, although the transaction of client B has not been committed, the updated data of B can be queried at client A.

(4) once the transaction of client B is rolled back for some reason, all operations will be undone, then the data queried by client An is actually dirty data.

(5) execute the update statement update user set balance = balance-50 where id = 1 on client A; isn't it strange that the balance of zhangsan is 400 instead of 350? the data is inconsistent. It would be naive of you to think so. In the application, we will use 400-50 sessions 350 without knowing that other sessions have been rolled back. To solve this problem, we can use the isolation level of read committed.

Read submitted

(1) Open a client A, set the current transaction mode to read committed (read committed), and query all records of table user

Set tx_isolation='read-committed'; copy code

(2) before the transaction of client A commits, open another client B and update the table user

(3) at this time, the transaction of client B has not been committed, and client A cannot query the updated data of B, which solves the problem of dirty reading.

(4) transaction submission of client B

(5) client An executes the same query as the previous step, and the result is inconsistent with the previous step, that is, the problem of unrepeatable reading occurs.

Repeatable read

(1) Open a client A, set the current transaction mode to repeatable read, and query all records of the table user.

Set tx_isolation='repeatable-read'; copy code

(2) before the transaction of client A commits, open another client B, update the table user and commit.

(3) all the records of the table user are queried in client A, and the query results in step (1) are always the same, and there is no problem of non-repeatable reading.

(4) in client A, then execute update user set balance = balance-50 where id = 1, balance does not change to 400-50 = 350. the balance value of zhangsan is calculated using 350in step (2), so it is 300. the consistency of the data has not been broken. The MVCC (multi-version concurrency control) mechanism is used under the repeatable isolation level. The select operation does not update the version number, but is a snapshot read (historical version); insert, update, and delete update the version number, which is the current read (current version).

Let's talk about MVCC in the next part.

(5) reopen client B, insert a new piece of data and submit it.

(6) in client A, all records of table user are queried, and no new data is found, so there is no phantom reading.

(7) verify that phantom reading executes update user set balance = 8888 where id = 4; on client A, and can be updated successfully, and the new data added by client B can be queried again.

Serialization

(1) Open a client A, set the current transaction mode to serializable, and query the initial value of the table user

Set tx_isolation='serializable'; copy code

(2) Open a client B, and set the current transaction mode to serializable, insert a record and report an error, and the table is locked and failed to insert. When the transaction isolation level in MySQL is serializable, the table will be locked, so there will be no illusory reading. The concurrency of this isolation level is very low, and it is rarely used in development.

Case conclusion

Because InnoDB storage engine implements row-level locking, although the performance loss caused by the implementation of locking mechanism may be higher than that of table-level locking, it is much better than MyISAM's table-level locking in terms of overall concurrency processing capacity. When the system concurrency is the highest, the overall performance of InnoDB will have obvious advantages compared with MyISAM.

However, InnoDB's row-level locking also has its fragile side, and when we use it incorrectly, it may not only make the overall performance of InnoDB not better than MyISAM, but may even be worse.

Row lock analysis

Analyze row lock contention on the system by checking the innodb_row_lock state variable:

Show status like 'innodb_row_lock%'; copy code

The description of each state quantity is as follows:

Innodb_row_lock_current_waits: number of locks currently waiting Innodb_row_lock_time: total length of time from system startup to now Innodb_row_lock_time_avg: average time spent waiting each time Innodb_row_lock_time_max: longest time spent waiting from system startup to now Innodb_row_lock_waits: total number of times the system has waited since it started

For these five state variables, the more important ones are:

Innodb_row_lock_time_avg (average waiting time) Innodb_row_lock_waits (total waiting times) Innodb_row_lock_time (total waiting time)

Especially when the waiting times are high and the waiting time is not small, we need to analyze why there is so much waiting in the system, and then start to make an optimization plan according to the analysis results.

Deadlock set tx_isolation='repeatable-read'; copy code Session_1 execution: select * from user where id=1 for update;Session_2 execution: select * from user where id=2 for update;Session_1 execution: select * from user where id=2 for update;Session_2 execution: select * from user where id=1 for update; copy code

View recent deadlock log information:

Show engine innodb status\ G; copy the code

In most cases, mysql can automatically detect deadlocks and roll back the transaction that caused the deadlock, but in some cases mysql cannot automatically detect deadlocks

It is recommended that all data retrieval should be done through indexes as far as possible to avoid upgrading non-indexed row locks to table locks; reasonably design indexes to minimize the scope of locks; minimize the scope of retrieval conditions and avoid interlocking; control transaction size as far as possible, reduce the amount of locking resources and time length, put sql involving transaction locking at the end of transactions as far as possible, and isolate transactions at a low level as far as possible. The default level of question and answer MySQL is repeatable-read. Is there any way to solve the problem of fantasy reading mom?

Gap lock (Gap Lock) can solve the phantom reading problem in some cases. It is a locking mechanism introduced by Innodb to solve the phantom reading problem under repeatable read commit. To avoid illusion, you can use a gap lock to execute update user set name = 'hjh' where id > 10 and id 2 and id under Session_1

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.

Views: 300

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report