First, the mechanism of locking

The purpose of database locking: a rule designed to ensure data consistency and order the concurrent access of shared resources. For any kind of database needs to have the corresponding locking mechanism, so MySQL is no exception. MySQL database due to the characteristics of its own structure, there are a variety of data storage engine, according to the characteristics of the application scenario for each storage engine that are not quite same, in order to meet the needs of their specific application scenarios, each storage engine locking mechanism is optimized for the particular scenario facing their design, so each storage engine locking mechanism also has bigger difference. MySQL storage engines use three types (levels) of locking mechanisms: table level locking, row level locking, and page level locking.

1. Table-level lock (table-level)

Table level locking is the most granular locking mechanism in MySQL’s storage engines. The biggest feature of this locking mechanism is that the implementation logic is very simple and the system has minimal negative impact. Locks are acquired and released quickly. Because table-level locking locks the entire table at once, it’s a good way to avoid the deadlocks that plague us. Disadvantages: The biggest negative impact of large locking granularity is that the probability of locking resource contention will be the highest, resulting in a large discount. Table level locking is mainly used by MyISAM, MEMORY, CSV and other non-transactional storage engines.

2. Row-level locking (row-level)

3. Page level locking (Page-level)

Page-level locking is a unique level of locking in MySQL and is not common in other database management software. Features: 1. The granularity of locking is between row-level locking and table-level locking. 2.

In addition, page-level locking, like row-level locking, is deadlocked. In the process of database resource locking, as the granularity of locked resources decreases, the amount of memory required to lock the same amount of data becomes more and more, and the implementation algorithm becomes more and more complex. However, as the granularity of locked resources decreases, the likelihood of application access requests encountering lock waits decreases, and the overall concurrency of the system increases. Page – level locking is primarily used by BerkeleyDB storage engines.

In general, the features of the three types of MySQL locks can be summarized as follows: table-level locks: low overhead and fast locking; No deadlocks occur; Large lock granularity has the highest probability of lock conflict and the lowest concurrency. Row-level lock: expensive, slow lock; Deadlocks occur; The lock granularity is the lowest, the probability of lock conflict is the lowest, and the concurrency is the highest. Page lock: the overhead and lock time are between table lock and row lock. Deadlocks occur; The locking granularity is between table locks and row locks, and the concurrency is average.

Application: From the lock point of view, table-level lock is more suitable for the query based, only a small amount of data update according to the index conditions of the application, such as Web applications; Row-level locking is more suitable for applications with a large number of concurrent updates of a small amount of different data based on index conditions and concurrent queries, such as some online transaction processing (OLTP) systems.

Table level locking

1.MySQL table level lock mode

MySQL Table level locks have two modes: Table Read Lock and Table Write Lock. Compatibility of lock mode: Read operations on MyISAM tables do not block other users’ read requests to the same table, but block write requests to the same table. (Read blocking write) Write operations on the MyISAM table will block other users’ read and write operations on the same table. (Write blocks read/write) The MyISAM table is serial between read/write operations and between write operations. When a thread acquires a write lock on a table, only the thread holding the lock can update the table. Read and write operations on other threads wait until the lock is released.

Alter table lock (MyISAM)

3.MyISAM table lock optimization suggestions

For the MyISAM storage engine, using table-level locking costs less to implement than row-level locking or page-level locking, and the locking itself consumes the least resources. However, due to the large granularity of locking, there will be more contention for locked resources than other locking levels, which will reduce the concurrent processing capacity to a large extent. Therefore, when optimizing the MyISAM storage engine lock problem, the key is how to improve the concurrency. Since the locking level is impossible to change, we first need to make the locking time as short as possible, and then make the possible concurrent operations as concurrent as possible. MySQL > select * from table where lock contention exists; select * from table where lock contention exists;

mysql> show status like 'table%';
+----------------------------+---------+
| Variable_name              | Value   |
+----------------------------+---------+
| Table_locks_immediate      | 100     |
| Table_locks_waited         | 11      |
+----------------------------+---------+ Copy the code

• 1
• 2
• 3
• 4
• 5
• 6
• 7

There are two status variables to record table level locks in MySQL. The two variables are described as follows: Table_locks_immediate: indicates the number of table level locks. Table_locks_waited: number of waits caused by table level lock contention; Both status values are recorded from the start of the system. If the corresponding event occurs once, the number increases by 1. If the Table_locks_waited state value is high, it indicates that the table-level lock contention is serious in the system, and it is necessary to further analyze why there are more lock resource contention.

(2) Shorten the lock time how to make the lock time as short as possible? The only way to do this is to keep our Query execution time as short as possible. A) Reduce the large complex Query as much as possible and divide the complex Query into several small Query distributions; B) Establish enough efficient indexes as far as possible to make data retrieval faster; C) Try to make MyISAM storage engine tables only store necessary information and control field types; D) Optimize MyISAM table data files as appropriate.

When it comes to MyISAM table locks, which block each other, some people may think that in MyISAM storage engine tables can only be fully serialized, there is no way to parallel. Not to be forgotten, MyISAM’s storage engine also has a very useful feature called Concurrent Insert.

The MyISAM storage engine has an option that controls whether Concurrent Insert is enabled: concurrent_insert, which can be set to 0,1 or 2. The three values are specified as follows: concurrent_INSERT =2, which allows concurrent insertions at the end of the MyISAM table regardless of whether there is a void in the table; Concurrent_insert =1, MyISAM allows another process to insert records from the end of the table while one process is reading the table, if there are no holes in the MyISAM table (that is, there are no deleted rows in the middle of the table). This is also the default setting for MySQL; Concurrent_insert =0, concurrent inserts are not allowed. The concurrent insert feature of MyISAM storage engine can be used to solve lock contention for the same table query and insert in the application. For example, setting the concurrent_INSERT system variable to 2 always allows concurrent inserts; At the same time, the OPTIMIZE TABLE statement was executed periodically during system downtime to clean up space fragmentation and recover the middle hole caused by record deletion.

(4) Use read and write priority (write before read) MyISAM storage engine is read and write block each other, so, one process request a MyISAM table read lock, another process also request the same table write lock, MySQL how to handle? The answer is that the writer gets the lock first. In addition, even if the read request arrives in the lock wait queue first and the write request arrives later, the write lock is inserted before the read lock request. This is because MySQL table level locking has different priorities for read and write. By default, write priority is higher than read priority. So, if we can determine the read and write priorities based on the differences in each system environment, we can make this connection have a higher read priority than write priority by executing SET LOW_PRIORITY_UPDATES=1. If the system is read – oriented, you can set this parameter. If the system is write – oriented, you do not need to set this parameter. Lower the priority of INSERT, UPDATE, and DELETE statements by specifying the LOW_PRIORITY attribute. Although the above methods are either update-first or query-first methods, they can still be used to solve the problem of read lock waiting in applications where the query is relatively important (such as user login system). In addition, MySQL also provides a compromise to adjust read/write conflicts by setting the system parameter max_write_lock_count to an appropriate value. When a table’s read locks reach this value, MySQL temporarily lowers the priority of write requests (when there are many read locks, the write request priority is lowered). Give the read process a certain chance to get the lock. Here’s another point: Some queries that need to run for a long time will also make the writing process “starve to death”. Therefore, the application should try to avoid long-running query operations. Do not always want to use a SELECT statement to solve the problem, because this seemingly clever SQL statement is often more complex and takes a long time to execute. If possible, the SQL statement can be “broken down” by using intermediate tables and other measures, so that each step of the query can be completed in a short time, thus reducing lock conflicts. If complex queries are unavoidable, try to schedule them during database downtime, such as some scheduled statistics that can be scheduled at night.

3. Row locking (InnoDB storage engine)

1.InnoDB locking mode and implementation mechanism

Considering that row-level locking is implemented by each storage engine, and the specific implementation is also different, and InnoDB is the most widely used storage engine in the current transactional storage engine, so here we mainly analyze InnoDB locking features. In general, InnoDB’s locking mechanism has a lot in common with Oracle. InnoDB row-level lock 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 intentional shared lock and intentional exclusive lock these two types.

When a transaction needs to lock a resource that it needs, it can add another shared lock if a shared lock is locking the resource that it needs, but it cannot add an exclusive lock. However, if the resource that you need to lock is already occupied by an exclusive lock, you can only acquire the locked resource and add your own lock after the resource is released by the lock. Shared lock: You can add a shared lock. But do not add exclusive lock. Exclusive lock: after encounter can only wait.

The purpose of intent lock is to add an appropriate intent lock to the table when a transaction needs to obtain a resource lock, if the resource it needs has been occupied by an exclusive lock.

If you need a shared lock, add an intended shared lock to the table first. If you want to add an exclusive lock to a row (or rows), add an intentional exclusive lock to the table first.

Intentional shared locks can coexist simultaneously, but only one intentional exclusive lock can exist at a time. Therefore, it can be said that InnoDB can actually be divided into four locking modes: shared lock (S), exclusive lock (X), intentional shared lock (IS) and intentional exclusive lock (IX). We can summarize the coexistence logic of these four modes by the following table:

InnoDB grants the requested lock to a transaction if its lock mode is compatible with the current one; Otherwise, if the two are incompatible, the transaction waits for the lock to be released. Intent locks are automatically added by InnoDB without user intervention. For UPDATE, DELETE, and INSERT statements, InnoDB automatically adds an exclusive lock (X) to the data set involved; InnoDB does not place any locks on normal SELECT statements; Transactions can be displayed by adding a shared or exclusive lock to a recordset using the following statement. Shared LOCK (S) : LOCK IN SHARE Exclusive LOCK (X) : FOR UPDATE

SELECT * FROM table_name WHERE... LOCK IN SHARE MODE LOCK IN SHARE MODE (X) : SELECT * FROM table_name WHERE... FOR UPDATECopy the code

• 1
• 2

Use the SELECT… IN SHARE MODE A shared lock is used to confirm the existence of a row and ensure that no UPDATE or DELETE operation is performed on the record when a data dependency relationship is required. However, if the current transaction also needs to update the record, it is likely to cause a deadlock. For applications that need to update a locked row record, use SELECT… The FOR UPDATE mode obtains exclusive locks.

2.InnoDB row lock implementation (index lock)

3. Next-key lock (a record whose Key value is within the condition range but does not exist)

When we retrieve data using a range condition rather than an equality condition and request a shared or exclusive lock, InnoDB locks the index entries of existing data records that meet the condition. For records whose Key values are within the condition range but do not exist, called gaps, InnoDB also locks this GAP. This locking mechanism is called next-key locking. Example: if there are only 101 records in the emp table, the empid values are 1,2… SQL > alter table SQL > alter table SQL

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

• 1

For a range criteria, InnoDB will lock not only eligible records with an EMPID value of 101, but also “gaps” where empID is greater than 101 (these records do not exist).

InnoDB uses gap locks for the following purposes: (1) preventing phantom reads to meet the relevant isolation level requirements. For the example above, without gap locking, if another transaction inserts any record with an EMPID greater than 100, a phantom will occur if this transaction executes the above statement again; (2) to meet the needs of its recovery and replication. Obviously, when records are retrieved and locked using range criteria, even nonexistent keys can be locked innocently, making it impossible to insert any data within the locked key at the time of the lock. In some scenarios this can be detrimental to performance.

In addition to the negative impact of gap locking on InnoDB’s performance, there are several other major performance risks associated with index locking: (1) InnoDB will abandon row level locking and use table level locking when Query cannot take advantage of indexes, resulting in reduced concurrency performance; (2) When the index used by Query does not contain all the filter criteria, some of the data only considered by the index key used in data retrieval may not belong to the row of the Query result set, but will also be locked, because the gap lock locks a range rather than a specific index key; (3) When a Query uses an index to locate data, the same key is used but different rows are accessed (the index is only part of a filter condition, such as an index, or another filter condition). Therefore, in practical application development, especially for applications with a large number of concurrent inserts, we should try to optimize business logic, try to use equality conditions to access updated data, and avoid using scope conditions.

In particular, InnoDB uses a gap lock when using a range condition, but also when using an equality condition to request a non-existent record.

4. A deadlock

5. When to use table locks

For InnoDB tables, row-level locking should be used in most cases, because transactions and row locking are often the reasons why we choose InnoDB tables. But in some special affairs, also can consider to use a table level lock: (1) the transaction needs to be updated most or all of the data table and bigger, if use the default row locks, not only this transaction execution efficiency is low, and it may cause other transactions lock waits for a long time and lock conflicts, this situation can be considered to improve the execution speed of the transaction table lock. (2) The transaction involves multiple tables and is relatively complex, which is likely to cause deadlocks and a large number of transactions to roll back. In this case, you can also consider locking the tables involved in a transaction once to avoid deadlocks and reduce database overhead due to transaction rollback. Of course, you should not have too many of these two types of transactions in your application, otherwise you should consider using MyISAM tables. There are two things to note when using table locks under InnoDB. Table locking is not managed by the InnoDB storage engine layer, but by MySQL Server. Only when autoCOMMIT =0 and InnoDB_table_locks=1 (the default setting) does InnoDB layer know that MySQL is adding table locks and MySQL Server is aware that InnoDB is adding row locks (mutual aware). InnoDB automatically recognizes deadlocks that involve table-level locks, otherwise InnoDB cannot automatically detect and handle such deadlocks. MySQL will not LOCK InnoDB TABLES if AUTOCOMMIT is set to 0. Do not release the table lock with UNLOCK TABLES before the transaction ends, because UNLOCK TABLES implicitly commits the transaction; COMMIT or ROLLBACK cannot release table locks that have been added using LOCK TABLES. UNLOCK TABLES must be used to release table locks. For example, if you want to write table T1 and read from table T, do as follows:

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

• 1
• 2
• 3
• 4
• 5

Close commit, lock table, handle transaction, commit, unlock table

6.InnoDB row lock optimization suggestions

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     |
+-------------------------------+-------+
Copy the code

mysql> create table InnoDB_monitor(a INT) engine=InnoDB;
Copy the code

mysql> show engine InnoDB status;
Copy the code

mysql> drop table InnoDB_monitor;
Copy the code