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

Editor to share with you the example analysis of row-level locks in Mysql, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

The first big premise we need to know is that mysql locks are implemented by a specific storage engine. So there is a difference in lock implementation mechanisms like Mysql's default engine MyISAM and third-party plug-in engine InnoDB.

Mysql has three levels of locking: table-level locking, page-level locking, and row-level locking

I. definition

The locking mechanism for locking a row of data at a time is row-level locking (row-level). Row-level locking is not implemented by MySQL itself, but by other storage engines themselves

Second, advantages and disadvantages

1. Advantages

Because the lock granularity is small, the contention rate is low, and the concurrency is high.

two。 Shortcoming

The implementation is complex and expensive.

Slow locking and easy deadlock

Third, support storage engine

Row-level locking is mainly used by the InnoDB storage engine and MySQL's distributed storage engine NDBCluster

Fourth, row-level lock types

Row-level locking of InnoDB is also divided into two types: shared lock and exclusive lock. In order to make row-level lock and table-level lock coexist, InnoDB also uses the concept of intentional lock (table-level lock), so there are two kinds of intentional shared lock and intentional exclusive lock.

The purpose of the intention lock is that when a transaction needs to obtain a resource lock, if the resource it needs has been occupied by an exclusive lock, the transaction can need to add an appropriate intention lock to the table that locks the row. If you need a shared lock, add an intended shared lock on top of the table. If you need to add an exclusive lock on a row (or some rows), add an intention exclusive lock on the table first.

Multiple intention sharing locks can exist at the same time, but only one intention exclusive lock can exist at the same time. Therefore, it can be said that the locking modes of InnoDB can actually be divided into four types: shared lock (S), exclusive lock (X), intention shared lock (IS) and intention exclusive lock (IX).

Lock mode compatibility:

Fifth, the implementation of row-level locking

InnoDB row locking is achieved by locking index items on the index. Therefore, InnoDB uses row-level locks only if the data is retrieved by index conditions, otherwise InnoDB will use table locks. Other considerations:

When querying without index conditions, InnoDB uses table locks instead of row locks.

Because the row lock of MySQL is a lock for index, not a lock for records, even if the records of different rows are accessed, lock conflicts will occur if the same index key is used.

When a table has multiple indexes, different transactions can use different indexes to lock different rows, and InnoDB uses row locks to lock the data, whether using primary key indexes, unique indexes, or normal indexes.

Even if index fields are used in the condition, whether or not to use indexes to retrieve data is determined by MySQL by determining the cost of different execution plans. If MySQL thinks that a full table scan is more efficient, such as for 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.

Implicit locking:

InnoDB automatically adds an intentional lock.

For UPDATE, DELETE, and INSERT statements, InnoDB automatically adds an exclusive lock (X) to the dataset involved

For ordinary SELECT statements, InnoDB does not add any locks

Show Lock:

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

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.

How to add table locks in InnoDB:

When locking the InnoDB table with LOCK TABLES, it should be noted that AUTOCOMMIT should be set to 0, otherwise MySQL will not lock the table; before the end of the transaction, do not release the table lock with UNLOCK TABLES, because UNLOCK TABLES will implicitly commit the transaction; COMMIT or ROLLBACK can not release the table lock added by LOCK TABLES, you must release the table lock with UNLOCK TABLES.

SET AUTOCOMMIT=0;LOCK TABLES T1 WRITE, T2 READ,...; [do something with tables T1 and T2 here]; COMMIT;UNLOCK TABLES

Since table locks are used, why not choose the MyISAM engine!

6. Clearance lock (Next-Key lock) 1. Gap lock definition:

The locking rule of Innodb is implemented by marking locking information on the spatial space before and after the last index key pointing to the data record. This locking implementation of Innodb is called "NEXT-KEY locking" because when Query performs a range lookup, it locks all index key values in the entire range, even if that key value does not exist.

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

Mysql > 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).

two。 Disadvantages of gap locks:

Gap lock has a fatal weakness, that is, when a range key value is locked, even some non-existent key values will be innocently locked, so that any data in the range of lock key values cannot be inserted when locked. In some scenarios, this can cause great harm to performance.

When Query cannot take advantage of the index, Innodb will give up using row-level locking and switch to table-level locking, resulting in reduced concurrency performance

When the index used by Quuery does not contain all the filtering conditions, some of the data pointed to by the index key used in data retrieval may not belong to the row of the result set of the Query, but it will also be locked because the gap lock locks a range, not a specific index key.

When Query uses an index to locate data, it will also be locked if it uses the same index key but accesses different rows of data (the index is only part of the filter condition).

3. The function of gap lock:

Prevent misreading to meet the requirements of the relevant isolation level.

For the needs of data recovery and replication.

4. Be careful

In practical application development, especially for applications with more concurrent inserts, we should optimize business logic as much as possible, use equal conditions to access updated data as far as possible, and avoid using scope conditions.

In addition to using a gap lock when locking through a range condition, InnoDB also uses a gap lock if an equal condition is used to request a lock on a record that does not exist.

7. Check row-level lock contention

Execute SQL:mysql > show status like 'InnoDB_row_lock%'

Mysql > show status like 'InnoDB_row_lock%' +-- +-+ | Variable_name | Value | +-+-+ | InnoDB_row_lock_current_waits | 0 | InnoDB_row_lock_time | | 0 | | InnoDB_row_lock_time_avg | 0 | | InnoDB_row_lock_time_max | 0 | | InnoDB_row_lock_waits | 0 | +-+-+ |

If you find that lock contention is serious, you can further observe the tables, data rows, etc., where lock conflicts occur by setting InnoDB Monitors, and analyze the causes of lock contention. Such as:

Set up the monitor: mysql > create table InnoDB_monitor (an INT) engine=InnoDB

View: mysql > show engine InnoDB status

Stop viewing: mysql > drop table InnoDB_monitor

Specific reference: InnoDB Monitor

8. Deadlock

What is a deadlock: you wait for me to release the lock, I wait for you to release the lock will form a deadlock.

How to find deadlocks: in the transaction management and locking mechanism of InnoDB, there is a special mechanism to detect deadlocks, which will be detected shortly after the deadlocks are generated in the system.

Solution:

Roll back the smaller transaction

Under the REPEATABLE-READ isolation level, if two threads use SELECT for the same condition record at the same time. FOR UPDATE adds an exclusive lock, and in the absence of a record that meets the condition, both threads will lock successfully. The program finds that the record does not yet exist and attempts to insert a new record, and if both threads do so, a deadlock occurs. In this case, changing the isolation level to READ COMMITTED avoids the problem.

Determine the size of a transaction: the amount of data that each transaction inserts, updates, or deletes

Note:

When more than InnoDB storage engine is involved in a deadlock scenario, InnoDB cannot detect the deadlock, so it can only be solved by locking the timeout parameter InnoDB_lock_wait_timeout.

IX. Optimize row-level locking

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 high, 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.

(1) if we want to make rational use of InnoDB row-level locking to enhance our strengths and circumvent our weaknesses, we must do the following:

Let all data retrieval be done through the index as much as possible, so as to prevent InnoDB from upgrading to table-level locking because it cannot be locked by the index key.

Reasonably design the index, let InnoDB lock on the index key as accurately as possible, narrow the locking range as much as possible, and avoid causing unnecessary locking and affecting the execution of other Query.

Minimize the filtering conditions of range-based data retrieval to avoid locking records that should not be locked because of the negative effects of gap locks.

Try to control the size of transactions and reduce the amount of locked resources and the length of locking time.

As far as the business environment allows, try to use a lower level of transaction isolation to reduce the additional cost of implementing the transaction isolation level for MySQL.

(2) due to the row-level locking and transactionality of InnoDB, deadlocks are bound to occur. Here are some common suggestions to reduce the probability of deadlocks:

In similar business modules, access according to the same access order as far as possible to prevent deadlock

In the same transaction, try to lock all the resources needed at once to reduce the probability of deadlock.

For business parts that are very prone to deadlocks, you can try to use upgrade locking granularity to reduce the probability of deadlocks through table-level locking.

The above is all the contents of the article "sample Analysis of Row-level Lock in Mysql". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.