• notice
  • Congratulations on the launch of the Sought Tech site

MYSQL lock mechanism and use analysis

This article describes the MySQL lock mechanism and usage with examples.Share it with everyone for your reference, as follows:

MySQL's lock mechanism is relatively simple, and its most notable feature is that different storage engines support different lock mechanisms.For example, the MyISAM and MEMORY storage engines use table-level locks; the BDB storage engine uses page locks, but also supports table-level locks; the InnoDB storage engine supports row-level locks and table-level locks, but uses by default Row-level locks.

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

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

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

(3) Page locks: The overhead and lock time are between table locks and row locks; deadlocks will occur; lock granularity is between table locks and row locks, and the degree of concurrency generally.

Only from the perspective of locks, table-level locks are more suitable for applications that focus on queries and only a small amount of data is updated based on index conditions, such as web applications; while row-level locks are more suitable for a large number of concurrent based on index conditions.Update a small amount of different data, and concurrently query applications, 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 relatively high, it indicates that there is a serious contention for table-level locks.

2.The lock mode of MySQL table-level lock

MySQL table-level lock has two modes: table shared read lock and table exclusive write lock.

When a session adds a read lock to a table, the session can only access the locked table and can only perform read operations; other sessions can read this table, but write operations will be affected Blocking requires waiting for the release of the lock.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.Reading and writing operations to this table by other sessions will be blocked and need to wait for the lock freed.

The read operation and write operation of MyISAM table, and the write operation are serial.

3.How to add table lock

Add read lock:

lock table tbl_name read;

Add write lock:

lock table tbl_name write;

Release the lock:

unlock tables;

MyISAM will automatically add read locks to all tables involved before executing the query statement, and will automatically add write locks to the tables involved before executing the update operation.This process does not require user intervention.Therefore, users generally do not You need to directly use the LOCK TABLE command to explicitly lock the MyISAM table.Explicit locking of MyISAM tables is generally to simulate transaction operations to a certain extent and achieve consistent reading of multiple tables at a certain point in time.

Note that when using LOCK TABLES, not only need to lock all the tables used at once, but also how many times the same table appears in the SQL statement must be locked through the same alias as in the SQL statement, otherwise Will go wrong!

4.Concurrent insertion

The MyISAM storage engine has a system variable concurrent_insert, which is specifically used to control its concurrent insert behavior, and its value can be 0, 1, or 2, respectively.

(1) When concurrent_insert is set to 0, concurrent inserts are 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 While the process is reading the table, another process inserts records from the end of the table.This is also the default setting for MySQL.

(3) When concurrent_insert is set to 2, no matter whether there are holes in the MyISAM table, concurrent insertion of records at the end of the table is allowed.

Just add the "local" option to the table lock command, that is: lock table tbl_name local read, when the conditions for concurrent insertion into MyISAM tables are met , Other users can insert records concurrently at the end of the table, but the update operation will be blocked, and the locked user cannot access the records inserted concurrently by other users.

5.MyISAM lock scheduling

When the writing process and the reading process simultaneously request the write lock and the read lock of the same MyISAM table, the writing process will get the lock first.Not only that, even if the read request arrives in the lock waiting queue first, and the write request arrives later, 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 also the reason 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, which may be blocked forever.

Adjust the scheduling behavior of MyISAM through the following settings:

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

(2) By executing the command SET LOW_PRIORITY_UPDATES=1, the priority of the update request sent by the connection is lowered.

(3) By specifying the LOW_PRIORITY attribute of the INSERT, UPDATE, and DELETE statements, reduce the priority of the 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 prioritizes write requests The level is lowered, giving the reading process a certain chance to obtain the lock.

Second, InnoDB lock problem

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 relatively high, it means that the lock contention is serious.You can set InnoDB Monitors to further observe the tables, data rows, etc.where lock conflicts occur, and analyze the reasons for lock contention.

Open the monitor:

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

Stop the monitor:

DROP TABLE innodb_monitor;

After opening the monitor, the monitored content will be recorded in the log every 15 seconds by default.If it is opened for a long time, the.err file will become very large, so after confirming the cause of the problem, the user must remember to delete the monitor Table to turn off the monitor, or start the server by using the "--console" option to turn off writing log files.

2.InnoDB row lock and locking method

InnoDB has two row locks: shared locks (S) and exclusive locks (X).In order to allow row locks and table locks to coexist and implement a multi-granularity lock mechanism, InnoDB also has two internally used intention locks: intention shared locks and intention exclusive locks, both of which are table locks.A transaction must obtain the intent 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 will automatically add exclusive locks (X) to the data set involved; for ordinary SELECT statements, InnoDB will not add any locks; transactions can explicitly add shared locks or exclusive locks to the record set through the following statement.

Set autocommit=0;

Share 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;

(Submit the transaction implicitly)

When a transaction obtains a shared lock on a table, other transactions can query the record of the table, or add a shared lock to the record.When a transaction updates the table, if there is another transaction that also adds a shared lock to the table, you need to wait for the lock to be released.If another transaction also updates the table at the same time, it will cause a deadlock , Another transaction exits, and the current transaction completes the update operation.When a transaction obtains an exclusive lock on a table, other transactions can only query the records of the table, can not add shared locks, can not update the records, there will be waiting.

3.InnoDB row lock implementation method

InnoDB row lock is realized by locking the index item on the index.The realization characteristic of InnoDB row lock means:

(1) Only when data is retrieved through index conditions, InnoDB will use row-level locks, otherwise, InnoDB will use table locks.

(2) Because MySQL row locks are locks for indexes, not records, although records of different rows are accessed, if the same index key is used, lock conflicts will occur.

(3) When the table has multiple indexes, different transactions can use different indexes to lock different rows.In addition, whether it is using primary key indexes, unique indexes or ordinary indexes, InnoDB will use row locks to lock the data Lock up.(Although a different index is used, it still needs to wait if the record has been locked by another session.)

(4) Even inThe index field is used in the condition, but whether to use the index to retrieve data is determined by MySQL by judging the cost of different execution plans.If MySQL thinks that the full table scan is more efficient, such as for some very small tables, it will not Using indexes, InnoDB will use table locks instead of row locks in this case.

4.Gap lock

When using the range condition to retrieve data, InnoDB will also lock the records whose key value is within the condition range but does not exist.This lock is called "gap lock".The purpose of InnoDB's use of gap locks is to prevent phantom reads on the one hand, and to meet the needs of recovery and replication on the other hand.However, this locking mechanism will block the concurrent insertion of key values ​​within the qualified range, causing serious lock waiting, so you should try to avoid using range conditions to retrieve data.

In addition to using gap locks when locking through range conditions, if an equal condition is used to request a lock for a record that does not exist, InnoDB will also use gap locks!

5.The impact of recovery and replication needs on InnoDB lock mechanism

MySQL records successfully executed INSERT, UPDATE, DELETE and other SQL statements to update data through BINLOG, and thereby realizes the recovery and master-slave replication of the MySQL database.MySQL's recovery mechanism (replication is actually continuous recovery based on BINLOG in Slave Mysql) has the following characteristics:

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

(2) MySQL Binlog is recorded in the order of transaction submission, and recovery is also carried out in this order.

So MySQL's recovery and replication requirements for the locking mechanism are: before a transaction is committed, other concurrent transactions cannot insert any records that meet its locking conditions, that is, phantom 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 the SQL statement "create table new_tab...select...From source_tab where...", the user did not do any update operation on the source_tab, but MySQL did the SQL statement Special processing, added a shared lock to source_tab.This is because, without locking, if another transaction updates source_tab during the execution of this SQL statement and commits it first, then in BINLOG, the location of the update operation will be before the SQL statement, use this BINLOG If the database is restored, the results of the restoration will be inconsistent with the actual application logic, and replication will cause inconsistencies between the master and slave databases.In fact, the data inserted into target_tab or new_tab by the application is the data before source_tab is updated by another transaction, but BINLOG records the update first and then executes the select...insert...statement.If the SELECT of the above statement is a range condition, InnoDB will also add gap locks to the source table.Therefore, this kind of SQL statement will block concurrent updates to the original table and should be avoided as much as possible.

6.InnoDB uses table locks and precautions

For InnoDB tables, row-level locks should be used in most cases, but in some special transactions, table-level locks can also be considered.There are two main situations:

(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 may cause other transactions to wait for long time locks and lock conflicts.This situation Next, you can consider using table locks to improve the execution speed of the transaction.

(2) The transaction involves multiple tables, which is more complicated, which may cause deadlock and cause a large number of transaction rollbacks.In this case, you can also consider locking the tables involved in the transaction once to avoid deadlocks and reduce the overhead of the database due to transaction rollbacks.

In addition, you need to pay attention to the following two points when using table locks in InnoDB:

(1) Although you can use LOCK TABLES to add table-level locks to InnoDB, the table locks are not managed by the InnoDB storage engine layer, but by the upper layer—MySQL Server, only when autocommit=0, innodb_table_locks=1 (default setting), the InnoDB layer can know the table locks added by MySQL, MySQL Server can also perceive the row locks added by InnoDB.In this case, InnoDB can automatically identify deadlocks involving table-level locks; otherwise, InnoDB will not be able to automatically detect and handle such deadlocks.

(2) When using LOCK TABLES to lock the InnoDB table, pay attention to setting AUTOCOMMIT to 0, otherwise MySQL will not lock the table; Before the end of the transaction, do not use UNLOCK TABLES to release table locks, because UNLOCK TABLES will implicitly commit Transaction; COMMIT or ROLLBACK cannot release table-level locks added with LOCK TABLES, you must use UNLOCK TABLES Release the table lock.

7.About deadlock

MyISAM table lock is deadlock free, this is because MyISAM always obtains all the locks it needs at one time, either all of them are satisfied, or waiting, so it won’t A deadlock occurred.But in InnoDB, except for transactions composed of a single SQL, locks are gradually acquired, which determines that deadlocks are possible in InnoDB.

After a deadlock occurs, InnoDB can generally automatically detect it, and make one transaction release the lock and roll back, and another transaction obtains the lock and continues to complete the transaction.However, when external locks or table locks are involved, InnoDB cannot fully automatically detect deadlocks.This needs to be resolved by setting the lock wait timeout parameter innodb_lock_wait_timeout.

Generally speaking, deadlock is a problem of application design.By adjusting the business process, database object design, transaction size, and SQL statements to access the database, most deadlocks can be avoided.Here are some examples to introduce several common methods to avoid deadlock.

(1) In the application, if different programs will access multiple tables concurrently, try to agree to access the tables in the same order, 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 the 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 sufficient level of lock, that is, an exclusive lock, instead of applying for a shared lock first, and then apply for an exclusive lock when updating, because when a user applies for an exclusive lock, Other transactions may have acquired the shared lock of the same record, causing lock conflicts and even deadlocks.

(4) Under the isolation level of REPEATABLE-READ, if two threads record the same conditions at the same time, use SELECT...FOR UPDATE adds an exclusive lock.If there is no record that meets this condition, both threads will lock successfully.The program finds that the record does not yet exist, and 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.

(5) When the isolation level is READ COMMITTED, if both threads execute SELECT...FOR UPDATE, judge whether there is a record that meets the conditions, if not, insert the record.At this time, only one thread can insert successfully, and another thread will wait for a lock.When the first thread submits, the second thread will make a mistake due to the primary key, but although this thread makes an error, it will get an exclusive lock! At this time, if a third thread applies for an exclusive lock again, a deadlock will also occur.In this case, you can directly do the insert operation, and then catch the primary key heavy exception, or when encountering the primary key heavy error, always execute ROLLBACK to release the exclusive lock obtained.

Tags

Technical otaku

Sought technology together

Related Topic

1 Comments

author

buy atorvastatin 80mg without preion & lt;a href="https://lipiws.top/"& gt;lipitor brand& lt;/a& gt; buy lipitor 80mg without preion

Tcukmd

2024-03-07

Leave a Reply

+