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

This article mainly introduces the use of the MySQL lock mechanism, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

The locking mechanism of MySQL is relatively simple, and its most prominent feature is that different storage engines support different locking mechanisms. For example, MyISAM and MEMORY storage engines use table-level locks; BDB storage engines use page locks, but also support table-level locks; and InnoDB storage engines support both row-level and table-level locks, but row-level locks are used by default.

The characteristics of the three locks of MySQL can be roughly summarized as follows:

(1) Table-level lock: low overhead, fast locking, no deadlock; large lock granularity, the highest probability of lock conflict and the lowest concurrency.

(2) Row-level locks: high overhead and slow locking; deadlocks occur; locking granularity is the smallest, the probability of lock conflicts is the lowest, and the degree of concurrency is the highest.

(3) Page lock: the overhead and locking time are between table lock and row lock; deadlocks occur; lock granularity is between table lock and row lock, and the concurrency is general.

Only from the point of view of locks, table-level locks are more suitable for applications that focus on queries, with only a small amount of data updated according to index conditions, such as Web applications, while row-level locks are more suitable for applications with a large number of concurrent updates of a small amount of different data according to index conditions and concurrent queries, such as some online transaction processing systems.

1. MyISAM table lock

1. Query table-level lock contention

Show status like 'table%'

If the value of table_locks_waited is high, there is serious table-level lock contention.

2. Lock mode of MySQL table-level lock

There are two modes of table-level locks in MySQL: table shared read locks and table exclusive write locks.

When a session adds a read lock to a table, the session can only access the locked table and can only read; other session can read the table, but write operations will be blocked and need to wait for the lock to be released. When a session adds a write lock to a table, the session can only access the locked table and can perform read and write operations. Other session read and write operations on the table will be blocked and need to wait for the lock to be released.

The read and write operations of the MyISAM table are serial, as well as between write operations.

3. How to add a table lock

Add read lock:

Lock table tbl_name read

Add write lock:

Lock table tbl_name write

Release the lock:

Unlock tables

MyISAM automatically locks all tables involved before executing the query statement and writes locks to the tables before performing the update operation. This process does not require user intervention, so users generally do not need to explicitly lock the MyISAM table directly with the LOCK TABLE command. The purpose of explicitly locking the MyISAM table is to simulate the transaction operation to a certain extent and to achieve the consistent reading of multiple tables at a certain point in time.

Note that when using LOCK TABLES, you not only need to lock all the tables used at once, but also lock the same table with the same alias as in the SQL statement how many times it appears in the SQL statement, otherwise you will also make an error!

4. Concurrent insertion

The MyISAM storage engine has a system variable, concurrent_insert, specifically designed to control its concurrent insertion behavior, which can be 0, 1, or 2, respectively.

(1) when concurrent_insert is set to 0, concurrent insertion is not allowed.

(2) when concurrent_insert is set to 1, if there are no holes in the MyISAM table (that is, there are no deleted rows in the middle of the table), MyISAM allows one process to read the table while another process inserts records from the footer. This is also the default setting for MySQL.

(3) when concurrent_insert is set to 2, records are allowed to be inserted concurrently at the end of the table, regardless of whether there are holes in the MyISAM table.

Just add the "local" option to the table lock command, that is, lock table tbl_name local read, and other users can insert records concurrently at the end of the table if the condition for concurrent insertion of the MyISAM table is met, but the update operation will be blocked, and the locked user will not be able to access the records inserted concurrently by other users.

5. MyISAM lock scheduling

When both the write process and the read process request both write and read locks for the same MyISAM table, the write process takes precedence to acquire the lock. Not only that, even if the read request goes to the lock waiting queue first, and after the write request, the write lock will be inserted before the read lock request! This is because MySQL believes that write requests are generally more important than read requests. This is why MyISAM tables are not suitable for applications with a large number of update operations and query operations, because a large number of update operations will make it difficult for query operations to obtain read locks and may be blocked forever.

Adjust the scheduling behavior of MyISAM with the following settings:

(1) by specifying the startup parameter low-priority-updates, the MyISAM engine gives priority to the read request by default.

(2) reduce the priority of update requests issued by the connection by executing the command SET LOW_PRIORITY_UPDATES=1.

(3) lower the priority of the statement by specifying the LOW_PRIORITY attribute of the INSERT, UPDATE, and DELETE statement.

(4) set an appropriate value for the system parameter max_write_lock_count. When the read lock of a table reaches this value, MySQL temporarily reduces the priority of the write request and gives the read process a chance to acquire the lock.

Second, the problem of InnoDB lock

1. Query InnoDB row lock contention

Show status like 'innodb_row_lock%'

If the values of InnoDB_row_lock_waits and InnoDB_row_lock_time_avg are high, lock contention is serious. You can further observe the tables and data rows where lock conflicts occur by setting InnoDB Monitors, and analyze the causes of lock contention.

Turn on the monitor:

CREATE TABLE innodb_monitor (an INT) ENGINE=INNODB;Show innodb status\ G

Stop the monitor:

DROP TABLE innodb_monitor

After opening the monitor, by default, the monitoring content will be recorded in the log every 15 seconds, and if it is opened for a long time, the .err file will become very large, so users should remember to delete the monitoring table to close the monitor after confirming the cause of the problem, or by using the "--console" option to start the server to shut down the log file.

2. Row locking and locking method of InnoDB

There are two kinds of row locks for InnoDB: shared locks (S) and exclusive locks (X). In order to allow row locks and table locks to coexist and implement the multi-granularity locking mechanism, InnoDB also has two kinds of intention locks used internally: intention sharing locks and intention exclusive locks, both of which are table locks. A transaction must acquire the intention lock corresponding to the corresponding table before locking the data row.

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; transactions can explicitly add shared or exclusive locks to the recordset through the following statements.

Set autocommit=0

Shared Lock (S):

SELECT * FROM table_name WHERE... LOCK IN SHARE MODE

Exclusive lock (X):

SELECT * FROM table_name WHERE... FOR UPDATE

Release the lock:

Unlock tables

(implicitly commit transactions)

When a transaction acquires a shared lock for a table, other transactions can query the table's record or add a shared lock to the record. When a transaction updates a table, if another transaction also adds a shared lock to the table, it needs to wait for the lock to be released. If another transaction also updates the table at the same time, it will lead to deadlock, another transaction exits, and the current transaction completes the update operation. When a transaction acquires an exclusive lock on a table, other transactions can only query the records of the table, cannot add shared locks or update records, and wait will occur.

3. Implementation of InnoDB row lock

InnoDB row locks are implemented by locking the index items on the index, and the row lock implementation feature of InnoDB means:

(1) InnoDB uses row-level locks only if the data is retrieved by index conditions, otherwise InnoDB uses table locks.

(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.

(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. Although a different index is used, it is necessary to wait if the record is already locked by another session. )

(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.

4. Gap lock

When retrieving data using a range condition, InnoDB also locks records whose key values are within the condition range but does not exist. This lock is called a "gap lock". The purpose of using gap locks in InnoDB is to prevent misreading on the one hand and to meet the needs of recovery and replication on the other. However, this locking mechanism will block the concurrent insertion of key values within the conditional range, resulting in serious lock waiting, so we should try to avoid using range conditions to retrieve data.

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!

5. The influence of the need for recovery and replication on InnoDB locking Mechanism

MySQL records the successful SQL statements of INSERT, UPDATE, DELETE and other updated data through BINLOG, and thus realizes the recovery of MySQL database and master-slave replication. MySQL's recovery mechanism (replication is actually constantly doing BINLOG-based recovery in Slave Mysql) has the following characteristics:

(1) the recovery of MySQL is at the SQL statement level, that is, the SQL statement in BINLOG is re-executed.

(2) the Binlog of MySQL is recorded in the order in which transactions are committed, and the recovery is also carried out in this order.

Therefore, the requirement of the locking mechanism for the recovery and replication of MySQL is that before a transaction is committed, other concurrent transactions cannot insert any records that meet its locking conditions, that is, false reads are not allowed.

In addition, for general select statements, MySQL uses multiple versions of data to achieve consistency without any locks. However, for "insert into target_tab select * from source_tab where." And "create table new_tab... select... From source_tab where..." For this kind of SQL statement, the user does not do any update operation to the source_tab, but MySQL does special treatment to this kind of SQL statement and adds a shared lock to the source_tab. This is because, without locking, if another transaction updates the source_tab and commits first during the execution of the SQL statement, then in BINLOG, the location of the update operation will precede the SQL statement, and if the BINLOG is used for database recovery, the recovery result will be inconsistent with the actual application logic, and replication will lead to inconsistency between the master and slave database. Because in fact, the data inserted into the target_tab or new_tab by the application is the data before the source_tab is updated by another transaction, while the BINLOG records the data before the select...insert... is updated. Statement. If the SELECT of the above statement is a scope condition, InnoDB also adds a gap lock to the source table. Therefore, this SQL statement will block concurrent updates to the original table and should be avoided as far as possible.

6. The use of table locks in InnoDB and matters needing attention

For InnoDB tables, row-level locks should be used in most cases, but table-level locks can also be considered in individual special transactions, mainly in the following two cases:

(1) the transaction needs to update most or all of the data, and the table is relatively large. If the default row lock is used, not only the execution efficiency of this transaction is low, but also other transactions may wait for a long time and cause lock conflicts. In this case, you can consider using table locks to improve the execution speed of the transaction.

(2) the transaction involves multiple tables, which is more complex, which is likely to cause deadlock and cause a large number of transaction rollback. In this case, you can also consider locking the tables involved in the transaction at one time, so as to avoid deadlock and reduce the overhead of the database due to transaction rollback.

In addition, there are two things to note when using table locks in InnoDB:

(1) although you can add a table-level lock to InnoDB using LOCK TABLES, the table lock is not managed by the InnoDB storage engine layer, but by the upper layer ── MySQL Server. Only when autocommit=0 and innodb_table_locks=1 (the default setting), the InnoDB layer knows the table lock added by MySQL, and MySQL Server can perceive the row lock added by InnoDB, in this case, InnoDB can automatically identify the deadlock involving table-level lock; otherwise, InnoDB will not be able to automatically detect and deal with this deadlock.

(2) 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 use UNLOCK TABLES to release the table lock, 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.

7. About deadlocks

MyISAM table locks are deadlock free, because MyISAM always gets all the locks you need at once, either satisfying them or waiting, so there is no deadlock. However, in InnoDB, locks are acquired step by step except for transactions composed of a single SQL, which determines that it is possible to have a deadlock in InnoDB.

After a deadlock occurs, InnoDB usually automatically detects it and causes one transaction to release the lock and roll back, while the other transaction acquires the lock and continues to complete the transaction. However, when external locks or table locks are involved, InnoDB does not automatically detect deadlocks, which needs to be solved by setting the lock wait timeout parameter innodb_lock_wait_timeout.

Generally speaking, deadlocks are a problem of application design, and most of them can be avoided by adjusting business processes, database object design, transaction size, and SQL statements to access the database. Here are several common ways to avoid deadlocks through examples.

(1) in the application, if different programs will access multiple tables concurrently, it should be agreed to access the tables in the same order as far as possible, which can greatly reduce the chance of deadlock.

(2) when the program processes data in batches, if the data is sorted in advance to ensure that each thread processes records in a fixed order, the possibility of deadlock can also be greatly reduced.

(3) in a transaction, if you want to update the record, you should directly apply for a lock of sufficient level, that is, an exclusive lock, instead of applying for a shared lock first, and then apply for an exclusive lock when updating, because when the user applies for an exclusive lock, other transactions may have acquired the shared lock of the same record, resulting in lock conflicts or even deadlocks.

(4) under the REPEATABLE-READ isolation level, if two threads add an exclusive lock to the same condition record at the same time, both threads will lock successfully if the condition record is not met. 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.

(5) when the isolation level is READ COMMITTED, if both threads execute SELECT...FOR UPDATE first, determine whether there is a qualified record, and if not, insert the record. At this point, only one thread can insert successfully, and the other thread will have a lock wait. when the first thread commits, the second thread will make an error because of the primary key, but although this thread has made an error, it will get an exclusive lock! At this point, if a third thread comes to apply for an exclusive lock, there will also be a deadlock. In this case, you can do the insert operation directly, and then catch the primary key weight exception, or always perform the ROLLBACK release of the exclusive lock obtained when the primary key weight error is encountered.

Thank you for reading this article carefully. I hope the article "what is the use of MySQL locking mechanism" shared by the editor will be helpful to everyone? at the same time, I also hope that you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.