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

This article mainly gives you a brief introduction to the principle and usage of the mysql lock mechanism. You can check the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here. Let's go straight to the topic. I hope this article on the principle and usage of the mysql lock mechanism can bring you some practical help. Mysql uses many of these locking mechanisms, such as row locks, table locks, read locks, write locks, etc., all of which are locked before doing an operation. These locks are generally called pessimistic Pessimistic Lock.

InnoDB lock

There are two biggest differences between InnoDB and MyISAM:

One is to support transactions (TRANSACTION)

Second, the row-level lock is adopted. There are many differences between row-level locks and table-level locks. In addition, the introduction of transactions also brings some new problems.

1. Transaction and its ACID attribute

A transaction is a logical processing unit composed of a set of SQL statements. A transaction has four attributes, usually called the ACID attribute of a transaction.

1. Actomicity: a transaction is an atomic operation unit in which all or none of the changes to the data are performed.

2. Consistent: the data must be in a consistent state at the beginning and completion of the transaction. This means that all relevant data rules must be applied to the modification of the transaction to control integrity; at the end of the transaction, all internal data structures (such as B-tree indexes or bidirectional linked lists) must also be correct.

3. Isolation: the database system provides a certain 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.

4. Durable: after the transaction is completed, the modification of the data is permanent and can be maintained even if there is a system failure.

2. Problems caused by concurrent transactions

Compared with serial processing, concurrent transaction processing can greatly increase the utilization of database resources and improve the transaction throughput of the database system, thus supporting more users. However, concurrent transaction processing can also bring some problems, including the following situations.

1. Update loss (Lost Update): when two or more transactions select the same row and then update the row based on the initially selected value, the problem of missing updates occurs because each transaction is unaware of the existence of other transactions-the last update overwrites updates made by other transactions. For example, two editors made an electronic copy of the same document. Each editor changes its copy independently, and then saves the changed copy, thus overwriting the original document. The editor who finally saved a copy of his changes overrides the changes made by another editor. This problem can be avoided if another editor cannot access the same file before one editor completes and commits the transaction.

2, 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 committed; at this time, another transaction also 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".

3. Non-Repeatable Reads: a transaction has changed in reading some data, or some records have been deleted! This phenomenon is called "unrepeatable".

4. Phantom Reads: a transaction re-reads the previously retrieved data according to the same query conditions, but finds that other transactions insert new data that meets its query conditions. This phenomenon is called "illusory reading".

3. Transaction isolation level

In the problems caused by concurrent transactions, "update loss" should usually be completely avoided. However, preventing the loss of updates can not be solved by the database transaction controller alone, and the application needs to add the necessary locks to the data to be updated. Therefore, it should be the responsibility of the application to prevent the loss of updates.

In fact, "dirty reading", "unrepeatable 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. Database can be divided into the following two ways to achieve transaction isolation.

1. One is to lock the data before reading it to prevent other transactions from modifying the data.

2. The other is to generate a consistent data snapshot (Snapshot) of a data request point in time without any lock, and use this snapshot to provide a certain level of consistent reading (statement level or transaction level). From the user's point of view, it seems that a database can provide multiple versions of the same data, so this technology is called data multi-version concurrency control (MultiVersion Concurrency Control, referred to as MVCC or MCC), and is often called multi-version database.

In MVCC concurrency control, read operations can be divided into two categories: snapshot read (snapshot read) and current read (current read). Snapshot reading, read the visible version of the record (it may be the historical version), without locking. The current read is the latest version of the record, and the record returned by the current read will be locked to ensure that other transactions will not modify the record concurrently.

Which reads are snapshot reads in a system that supports MVCC concurrency control? Which operations are currently read? Take MySQL InnoDB as an example:

Snapshot read: simple select operation, belongs to snapshot read, unlocked. (of course, there are exceptions.)

Select * from table where?

Current read: special read operation, insert / update / delete operation, belongs to the current read and needs to be locked.

The following statements belong to the current read, reading the latest version of the record. Moreover, after reading, you also need to ensure that other concurrent transactions cannot modify the current record and lock the read record. Among them, except for the first statement, S lock (shared lock) is added to the read record, and X lock (exclusive lock) is added to all other operations.

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 insensitive to "unrepeatable reading" and "phantom reading" and may be more concerned about the ability to access data concurrently.

In order to solve the contradiction between "isolation" and "concurrency", ISO/ANSI SQL92 defines four transaction isolation levels. The isolation degree of each level is different and the allowed side effects are also different. Applications can balance the contradiction between "isolation" and "concurrency" by selecting different isolation levels according to their own business logic requirements. The following table provides a good overview of the characteristics of these four isolation levels.

Get InonoD row lock contention

You can analyze row lock contention on the system by checking the InnoDB_row_lock state variable:

Mysql > show status like 'innodb_row_lock%'

If you find that lock contention is serious, such as high values of InnoDB_row_lock_waits and InnoDB_row_lock_time_avg, you can further observe the tables and data rows that have lock conflicts by setting InnoDB Monitors, and analyze the causes of lock contention.

Row Lock Mode and locking method of InnoDB

InnoDB implements the following two types of row locks.

Shared lock (s): also known as read lock. Allows one transaction to read a row, preventing other transactions from acquiring exclusive locks for the same dataset. If transaction T adds an S lock to the data object A, transaction T can read A but cannot modify A, and other transactions can only add S lock to An instead of X lock until T releases the S lock on A. This ensures that other transactions can read A, but no modifications can be made to A until T releases the S lock on A.

Exclusive lock (X): also known as write lock. Allows transaction update data with exclusive locks to be acquired, preventing other transactions from acquiring the same dataset shared read lock and exclusive write lock. If transaction T adds an X lock to the data object A, transaction T can read An or modify A, and other transactions can no longer lock A until T releases the lock on A.

For shared locks, it may be easy to understand that multiple transactions can only read data and cannot change data.

There may be some differences in our understanding of exclusive locks. I made a mistake at the beginning, thinking that after an exclusive lock locks a row of data, other transactions cannot read and modify that row of data, but this is not the case. An exclusive lock means that after a transaction adds an exclusive lock to a row of data, no other transaction can add another lock to it. Mysql InnoDB engine default modify data statement: update,delete,insert will automatically add exclusive locks to the data involved, select statements will not add any lock type by default, if you add exclusive locks, you can use select. For update statement, with shared lock, you can use select. Lock in share mode statement. Therefore, data rows with exclusive locks cannot be modified in other transactions, nor can they be queried through for update and lock in share mode locks, but directly through select. From... Query data because a normal query does not have any locking mechanism.

In addition, in order to allow row locks and table locks to coexist and implement a multi-granularity locking mechanism, InnoDB also has two internally used Intention Locks locks, both of which are table locks.

Intention shared lock (IS): a transaction is intended to give a shared lock to a data row, and the transaction must acquire the IS lock of the table before adding a shared lock to the data row. Intention exclusive lock (IX): the transaction intends to add an exclusive lock to a data row, and the transaction must acquire the IX lock of the table before adding an exclusive lock to the data row.

InnoDB row lock mode compatibility list:

If the lock mode of a transaction request is compatible with the current lock, InnoDB grants the requested lock to the transaction; conversely, if the two are not compatible, the transaction waits for the lock to be released.

The intention lock is automatically added by InnoDB without user intervention. For UPDATE, DELETE, and INSERT statements, InnoDB automatically adds exclusive locks (X) to the dataset involved; for normal SELECT statements, InnoDB does not add any locks.

A transaction can explicitly add a shared or exclusive lock to the recordset through the following statement:

1. Shared lock (S): SELECT * FROM table_name WHERE... LOCK IN SHARE MODE .

2. Exclusive lock (X): SELECT * FROM table_name WHERE. FOR UPDATE .

Use SELECT... IN SHARE MODE acquires a shared lock, which is mainly used to confirm the existence of a row record when data dependencies are needed, and to ensure that no one UPDATE or DELETE the record. However, if the current transaction also needs to update the record, it is likely to cause deadlock. For applications that need to update the row record after locking, you should use SELECT. The exclusive lock is obtained by FOR UPDATE.

InnoDB Row Lock implementation

InnoDB row locking is achieved by locking the index items on the index, unlike Oracle, which is achieved by locking the corresponding data rows in the data block. The row lock implementation feature of InnoDB means that InnoDB uses row-level locks only if the data is retrieved by index conditions, otherwise InnoDB will use table locks!

In practical applications, special attention should be paid to this feature of InnoDB row locks, otherwise it may lead to a large number of lock conflicts, thus affecting concurrency performance. The following is illustrated by some practical examples.

(1) InnoDB does use table locks instead of row locks when querying without index conditions.

Mysql > create table tab_no_index (id int,name varchar (10)) engine=innodb;Query OK, 0 rows affected (0.15 sec) mysql > insert into tab_no_index values (1 Duplicates: 0 Warnings: 0), (2 Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0

In the above example, it looks like session_1 only adds an exclusive lock to one row, but session_2 waits for a lock when it requests an exclusive lock for another row! The reason is that without an index, InnoDB can only use table locks. When we add an index to it, InnoDB locks only the rows that meet the criteria, as shown in the following example:

Create a tab_with_ index table. The id field has a normal index:

Mysql > create table tab_with_index (id int,name varchar (10)) engine=innodb;mysql > alter table tab_with_index add index id (id)

(2) because the row lock of MySQL is a lock for index, not a lock for records, although it accesses records of different rows, lock conflicts will occur if the same index key is used. You should pay attention to this when applying design.

In the following example, the id field of table tab_with_index has an index, and the name field does not have an index:

Mysql > alter table tab_with_index drop index name;1Query OK, 4 rows affected (0.22 sec) Records: 4 Duplicates: 0 Warnings: 0mysql > insert into tab_with_index values; 1Query OK, 1 row affected (0 sec) mysql > select * from tab_with_index where id = 1

A blocking example of the InnoDB storage engine using the same index key:

(3) when the table has multiple indexes, different transactions can use different indexes to lock different rows. In addition, InnoDB will use row locks to lock the data, whether using primary key indexes, unique indexes, or normal indexes.

In the following example, the id field of table tab_with_index has a primary key index and the name field has a normal index:

Mysql > alter table tab_with_index add index name (name); 1Query OK, 5 rows affected (0.23 sec) Records: 5 Duplicates: 0 Warnings: 0

An example of blocking the tables of the InnoDB storage engine using different indexes:

(4) even if the index field is used in the condition, whether or not to use the index to retrieve data is decided by MySQL by judging the cost of different execution plans. If MySQL thinks that a full table scan is more efficient, for example, for some very small tables, it will not use indexes, in which case InnoDB will use table locks instead of row locks. Therefore, when analyzing lock conflicts, don't forget to check the execution plan of SQL to confirm that indexes are actually used.

For example, the name field in the tab_with_index table has an index, but the name field is of type varchar, and the data type of the retrieval value is different from the index field. Although MySQL can convert data types, it does not use indexes, which causes InnoDB to use table locks. We can clearly see this by examining the execution plans of the two SQL with explain.

Mysql > explain select * from tab_with_index where name = 1\ Gmysql > explain select * from tab_with_index where name ='1'\ G Gap lock (Next-Key lock)

When we use range conditions instead of equality conditions to retrieve data and request shared or exclusive locks, InnoDB will lock the index entries of existing data records that meet the conditions; for records whose key values are within the range of conditions but do not exist, it is called "GAP", and InnoDB will also lock this "gap", which is the so-called Next- lock.

For example, if there are only 101records in the emp table, their empid values are 1pr 2, respectively. , 100 SQL 101, below:

Select * from emp where empid > 100 for update

Is a search of range conditions, where InnoDB locks not only records with an empid value of 101, but also "gaps" with an empid greater than 101 (these records do not exist).

The purpose of InnoDB using gap lock is, on the one hand, to prevent misreading in order to meet the requirements of the relevant isolation level. For the above example, if the gap lock is not used, and if other transactions insert any records with an empid greater than 100, then if the transaction executes the above statement again, false reading will occur; on the other hand, it is to meet the needs of its recovery and replication. The impact of its recovery and replication on the locking mechanism, as well as the use of gap locks by InnoDB under different isolation levels, will be further described in subsequent chapters.

Obviously, when using range conditions to retrieve and lock records, the locking mechanism of InnoDB will block the concurrent insertion of key values within the conditional range, which often leads to serious lock waiting. Therefore, in the practical application development, especially the applications with more concurrent inserts, we should optimize the business logic as far as possible, use equal conditions to access updated data as far as possible, and avoid using scope conditions.

In particular, in addition to using a gap lock when locking through a range condition, InnoDB will also use a gap lock if an equal condition is used to request a lock on a record that does not exist! The following example assumes that there are only 101 records in the emp table, with empid values of 1, 2, 2, respectively. , 100101 .

Example of Gap Lock blocking for InnoDB Storage engine

Summary

This paper focuses on the implementation characteristics of MyISAM table-level lock and InnoDB row-level lock in MySQL, and discusses the lock problems and solutions often encountered by the two storage engines.

For MyISAM table locks, the following points are mainly discussed:

(1) the shared read lock (S) is compatible, but the shared read lock (S) and the exclusive write lock (X) and the exclusive write lock (X) are mutually exclusive, that is, read and write are serial.

(2) under certain conditions, MyISAM allows concurrent execution of queries and inserts, which can be used to solve the lock contention problem of queries and inserts for the same table in the application.

(3) the default lock scheduling mechanism of MyISAM is write first, which is not necessarily suitable for all applications. Users can adjust the contention of read-write locks by setting LOW_PRIORITY_UPDATES parameters or specifying LOW_PRIORITY options in INSERT, UPDATE, and DELETE statements.

(4) because the locking granularity of the table lock is large, and the reading and writing are serial, therefore, if there are more update operations, the MyISAM table may have serious lock waiting, so we can consider using the InnoDB table to reduce lock conflicts.

For the InnoDB table, this article mainly discusses the following:

(1) the row lock of InnoDB is based on the index. If the data is not accessed through the index, InnoDB will use table lock.

(2) the mechanism of InnoDB gap lock (Next-key) and the reason why InnoDB uses gap lock are introduced.

Under different isolation levels, the locking mechanism and consistent read strategy of InnoDB are different.

After understanding the characteristics of InnoDB locks, users can reduce lock conflicts and deadlocks through measures such as design and SQL adjustment, including:

Try to use a lower isolation level; carefully design the index and use the index to access data to make locking more accurate, thus reducing the chance of lock conflicts; choose a reasonable transaction size, small transactions are less likely to have lock conflicts; when explicitly locking a recordset, it is best to request a lock of sufficient level at one time. For example, if you want to modify data, it is best to apply for an exclusive lock directly, rather than apply for a shared lock first, and then request an exclusive lock when modifying, which is easy to produce deadlocks; when different programs access a set of tables, they should try to agree to access each table in the same order as far as possible. for a table, access the rows in the table in a fixed order as far as possible. This can greatly reduce the chances of deadlocks; try to access data with equal conditions, so as to avoid the impact of gap locks on concurrent inserts; do not apply for lock levels that exceed the actual needs; do not show locks when querying unless necessary; for certain transactions, table locks can be used to improve processing speed or reduce the possibility of deadlocks.

The principle and usage of mysql lock mechanism will first tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.