Understanding the row level lock of MySQL

Time:2020-8-1

1、 Definition

One row of data is locked each time, and the locking mechanism is row level locking. Row level locking is not implemented by MySQL itself, but by other storage engines

2、 Advantages and disadvantages

  1. advantage

    • Because of small lock granularity, low contention rate and high concurrency.
  2. shortcoming

    • The implementation is complex and expensive.
    • Lock slow, prone to deadlock

3、 Support storage engine

Row level locking is mainly used by InnoDB storage engine and MySQL distributed storage engine ndbcluster

4、 Row level lock type

Row level locking of InnoDB is also divided into two types: shared lock and exclusive lock. In order to make row level locking and table level locking coexist in the implementation of locking mechanism, InnoDB also uses itIntent lock (table level lock)There are two kinds of lock: intention sharing lock and intention exclusive lock.

The function of intent lock is that when a transaction needs to obtain a resource lock, if it encounters that the resource it needs has been occupied by an exclusive lock, the transaction can add an appropriate intent lock to the table of locked rows. If you need a shared lock, add an intentional shared lock to the table. If you need to add an exclusive lock to a row (or some rows), you should first add an intent exclusive lock to the table.

Multiple intention shared locks can coexist at the same time, but only one intention exclusive lock can exist at the same time. Therefore, it can be said that InnoDB’s locking modes can be divided into four types: shared lock (s), exclusive lock (x), intentional shared lock (is) and intentional exclusive lock (IX)

Lock mode compatibility:

5、 Implementation of row level locking

InnoDB row lock is implemented by locking the index entries 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 precautions:

  • InnoDB uses table locks instead of row locks when querying without index conditions.
  • Since MySQL row locks are for indexes, not records, lock conflicts will occur even if the same index key is used to access records in different rows.
  • When a table has multiple indexes, different transactions can use different indexes to lock different rows. In addition, InnoDB will use row locks to lock data, whether using primary key index, unique index or ordinary index.
  • Even if the index field is used in the condition, whether to use the index to retrieve the data is determined by MySQL by judging the cost of different execution plans. If MySQL thinks that the full table scanning efficiency is higher, for example, for some very small tables, it will not use indexes. In this case, InnoDB will use table locks instead of row locks. Therefore, when analyzing lock conflicts, don’t forget to check the SQL execution plan to see if the index is actually used.

Implicit locking:

  • InnoDB automatically adds intentional lock.
  • For update, delete and insert statements, InnoDB will automatically add exclusive locks (x) to the involved data sets;
  • For ordinary select statements, InnoDB does not add any locks;

Display lock:

  • Shared lock (s):SELECT * FROM table_name WHERE … LOCK IN SHARE MODE
  • Exclusive lock (x) :SELECT * FROM table_name WHERE … FOR UPDATE

With select In share mode obtains the shared lock, which is mainly used to confirm the existence of a row of records when data dependency is required, and to ensure that no one updates or deletes 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 after locking row records, select should be used For update mode to obtain exclusive lock.

How to lock InnoDB tables

When using lock tables to lock InnoDB table, you should pay attention to setting autocommit 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 cannot release the table level lock added with lock tables, and you must use unlock tables to release the table lock.

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

Since they are all locked with tables, why not choose MyISAM engine!

6、 Clearance lock (next key lock)

1. Definition of clearance lock:

The locking rule of nnodb is realized by marking the locking information in the space space before the first index key and after the last index key. The implementation of InnoDB’s locking is called “next-key locking”, because if query is searched by range during execution, it will lock all index key values in the whole range, even if the key value does not exist.

Example: if there are only 101 records in the EMP table, the empid values are 1,2 , 100101, the following SQL:

mysql> select * from emp where empid > 100 for update;

InnoDB not only locks the records with empid value of 101, but also locks the “gap” with empid greater than 101 (these records do not exist).

  1. Disadvantages of clearance lock:

Gap lock has a fatal weakness, that is, after locking a range key value, even some nonexistent key values will be locked innocently, resulting in unable to insert any data within the locked key value range when locking. In some scenarios, this can be very detrimental to performance

*When query fails to make use of the index, InnoDB will give up row level locking and use table level locking instead, which will reduce the concurrency performance;
*When the index used by a query does not contain all the filtering conditions, some of the data pointed to by the index key used for data retrieval may not belong to the column or column 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 a query uses an index to locate data, it will be locked if the same key is used but different rows of data are accessed (the index is only part of the filter condition)

3. Function of clearance lock:

Prevent phantom reading to meet the requirements of relevant isolation level.
For the need of data recovery and replication.

4. Attention

In the practical application development, especially in the applications with more concurrent inserts, we should try our best to optimize the business logic, use equality conditions to access updated data, and avoid using scope conditions.
In addition to using gap lock when locking by range condition, InnoDB will also use gap lock if it requests to lock a nonexistent record using equality condition.

7、 View row level lock contention

implement 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 also set InnoDB monitors to further observe the tables and data rows with lock conflicts, and analyze the causes of lock contention. For example:

Setup monitor: MySQL > create table InnoDB_ monitor(a INT) engine=InnoDB;

View: MySQL > show engine InnoDB status;

Stop viewing: MySQL > drop table InnoDB_ monitor;

8、 Deadlock

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

How to find Deadlock: in the transaction management and locking mechanism of InnoDB, there is a special mechanism to detect the deadlock, which will be detected in a short time after the deadlock occurs in the system

terms of settlement:

Roll back the smaller transaction
Under the same condition of read – read, if two threads can be isolated at the same time If exclusive lock is added for update, both threads will be locked successfully if there is no record meeting the condition. When the program finds that the record does not exist, it tries to insert a new record. If both threads do this, a deadlock will occur. In this case, change the isolation level to read committed to avoid the problem.
Determine the transaction size: the amount of data inserted, updated or deleted by the transaction

be careful:

When more than InnoDB storage engines are involved in the deadlock scenario, InnoDB cannot detect the deadlock. In this case, the parameter InnoDB can only be limited by locking timeout_ lock_ wait_ Time out.

9、 Optimize row level locking

Although the performance loss of InnoDB storage engine may be higher than that of table level locking due to the implementation of row level locking mechanism, it is far better than that of table level locking of MyISAM in terms of overall concurrent processing capacity. When the system concurrency is high, the overall performance of InnoDB will have obvious advantages compared with MyISAM. However, row level locking of InnoDB also has its vulnerability. When we use it improperly, the overall performance of InnoDB may not be better than that of MyISAM, or even worse.

(1) In order to make rational use of InnoDB’s row level locking, we must do the following work well:

  • As far as possible, all data retrieval is completed through index, so as to avoid InnoDB upgrading to table level locking because it cannot be locked by index key;
  • Reasonably design the index to make InnoDB lock the index key as accurate as possible, and reduce the locking range as much as possible, so as to avoid unnecessary locking and affecting the execution of other queries;
  • The scope based data retrieval filtering conditions should be reduced as much as possible to avoid locking records that should not be locked because of the negative impact of gap locking;
  • The size of transaction should be controlled as much as possible to reduce the amount of locked resources and the length of locking time;
  • If the business environment allows, try to use a lower level of transaction isolation to reduce the additional cost of MySQL because of the implementation of transaction isolation level.

(2) Due to the row level locking and transactional nature of InnoDB, deadlock will certainly occur. Here are some common suggestions to reduce the probability of deadlock generation

  • In similar business modules, the access sequence should be the same as possible to prevent deadlock;
  • In the same transaction, lock all the resources needed at one time as much as possible to reduce the probability of deadlock;
  • For services that are prone to deadlock, we can try to upgrade the locking granularity and reduce the probability of deadlock through table level locking.

Link to the original text: https://blog.csdn.net/zcl_ love_ wx/article/details/81983267