Shulou(Shulou.com)06/01 Report--

This article mainly introduces the summary and detailed explanation of MySQL locks, hoping to supplement and update some knowledge for you. If you have other questions to understand, you can continue to follow my updated articles in the industry information.

Overview of Lock

The locking mechanism of MySQL is a rule designed by the database to ensure the consistency of data in the face of concurrent scenarios.

The most prominent feature is that different storage engines support different locking mechanisms, InnoDB supports row locks and table locks, and MyISAM supports table locks.

Table lock is to lock the whole table, which is characterized by fast locking, low overhead, no deadlock, large lock granularity, high probability of lock conflict and relatively low concurrency.

Row lock is to lock up the data by behavior unit, which is characterized by slow locking, high overhead, deadlock, small lock granularity, low probability of lock conflict and high concurrency.

Lock scheduling of MyISAM Lock MyISAM

In the MyISAM engine, the read and write locks are mutually exclusive, and the read and write operations are serial. The lock design is as follows:

For write operations: if there is no lock on the table, add a write lock on it, otherwise, put the request in the write lock queue.

For read operations: if there is no lock on the table, add a read lock on it, otherwise, put the requestor in the read lock queue.

What does this mean?

This means that MyISAM automatically locks all tables involved before executing the query statement, and automatically adds write locks to the tables before executing the update statement (add, delete and modify operation). This process does not require user intervention.

When a lock is released, the lock right is first obtained by the thread in the write lock queue, and only when all the requests in the write lock queue run, it is the turn to read the request in the lock queue. Even if the read request goes to the lock waiting queue first, and after the write request arrives, the write request will be inserted before the read request! This is why MySQL believes that write requests are generally more important than read requests)

This means that if there are many update operations on a table, the select statement will wait until all the other updates are over. This is why the MyISAM table is not suitable for the application of a large number of update operations, because a large number of update operations may make it difficult for query operations to obtain read locks, which will block for a long time, resulting in program response timeouts.

Maybe you need to lock it explicitly.

There are three table lock statements (both MyISAM and InnoDB are the same):

LOCK TABLES tb_name READ; is read locked, and other sessions are readable, but cannot be updated.

LOCK TABLES tb_name WRITE; plus spelling error, other sessions are not readable or writable.

UNLOCK TABLES; release lock

When there are consecutive multiple table updates, there may be frequent table lock competition, the speed of updating data will decrease, and when updating this table, the data of another table may be updated by other threads (MyISAM is no transaction), at this time, we need to lock multiple tables and then update.

Here is an example where two tables are locked and updated at the same time, adding 1 to the user balance with id 1:

LOCK TABLES tb_1 WRITE,tb_2 WRITE

UPDATE tb_1 SET balance=balance+1 WHERE user_id=1

UPDATE tb_2 SET balance=balance+1 WHERE user_id=1

UNLOCK TABLES

Special note: when explicitly locking, all locks involving tables must be obtained at the same time, and only those tables that are explicitly locked can be accessed, not unlocked tables.

(this is the only chapter of MyISAM, and the following chapters are all about InnDB. )

InnoDB lock type shared lock (S lock, read lock)

SELECT * FROM tb_name LOCK IN SHARE MODE

A transaction acquires a read lock on a data row, allowing other transactions to acquire the read lock, but not other transactions to acquire the write lock. In other words, after I have the read lock, other transactions can also be read, but can not be added, deleted or modified.

Exclusive lock (X lock, write lock)

SELECT * FROM tb_name FOR UPDATE

When a transaction acquires a write lock for a data row, other transactions can no longer come to acquire any locks, and all requests are blocked until the current write lock is released.

Intention Lock and MDL Lock

Intention shared lock (IS): before a transaction can add a shared lock to a data row, it must first acquire the IS lock of the table.

Intention exclusive lock (IX): before a transaction can add a shared lock to a data row, it must acquire the IX lock of the table.

MDL lock: in a transaction, InnoDB adds a MDL lock to all tables involved, and other transactions cannot perform any DDL statement operations. (as long as the table involved is locked by MDL in a transaction, whether it is a query statement or an update statement.)

These three locks, which are used internally by InnoDB, are implemented automatically and do not require user intervention.

Several row locking techniques record lock (record lock)

This is an index record lock, which is a lock built on the index record (both the primary key and the unique index count). Very often, locking a piece of data, because there is no index, often causes the entire table to be locked. establishing an appropriate index can prevent scanning the entire table.

For example: open two sessions, two transactions, and do not commit, the table has a primary key, two sessions modify the same data, after the first session update execution, the second session update can not be executed successfully, will enter a waiting state, but if other data rows of update can succeed.

For example, if you open two sessions, two transactions, and none of them are commit, and the table has no primary key and no index, then the second session will enter a waiting state no matter what it changes. Because without an index, the data of the entire table is locked by the first session.

Gap lock (gap lock)

The default isolation level of MySQL is repeatable. In order to avoid phantom reading, this isolation level introduces this gap lock to lock the gap between index items.

Example:

(session 1)

START TRANSACTION

SELECT * FROM tb_name WHERE id > 10 LOCK IN SHARE MODE

(session 2)

START TRANSACTION

INSERT INTO tb_name (id,name) VLUES (11, "Zhang San")

How did it come out? Session 2 enters the execution wait state until the lock for session 1 is released or the lock times out.

Next-key lock (combination of recorder and gap lock)

When InnoDB scans an index record, it first adds a record lock (record Lock) to the selected index record, and then adds a gap lock (gap lock) to the gap between the index records.

Or in the case of a gap lock, if there is no row of data for id=10 in the table, the id added by session 2 should be 10, will it be successful?

The answer is no, because it locks not only the gap between id > 10, but also id=10.

Watch lock

Row locks should be used mostly in InnoDB, because transactions and row locks are often the reasons for choosing InnoDB tables, but table locks can also be considered in individual special transactions.

Case 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 transactions.

Case 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 database overhead caused by transaction rollback.

Of course, there should not be too many of these two situations, otherwise it should be split in terms of business and programming, rather than being undertaken by the database.

Examples are as follows:

LOCK TABLES tb_name WRITE; UNLOCK TABLES

Note: when locking a table in a transaction, do not release the lock before the end of the transaction, because unlock tables implicitly commits the transaction, so the correct thing to do is to release the lock after the end of the transaction.

Lock waiting and deadlock

Lock waiting refers to the lock generated during a transaction, and other transactions need to wait for the last transaction to release its lock before it can occupy the resource. if the transaction is not released, it needs to wait until the lock waiting time is exceeded. A timeout error will be reported.

Check the allowed time for lock waiting:

SHOW VARIABLES LIKE "innodb_lock_wait_timeout"

Deadlock refers to the phenomenon of mutual waiting caused by the competition for resources between two or more processes in the process of execution, which is the so-called dead cycle.

The typical experimental process is that two transactions concurrently modify one of their own data, and then modify each other's locked data, both have to wait for the other party's lock, deadlock occurs.

The problem of deadlocks is not terrible, and there are usually the following ways to solve deadlocks:

1. Do not put irrelevant operations into transactions, small transactions are less likely to conflict.

two。 If different programs access multiple tables concurrently, try to agree to access the tables in the same order so that the transaction forms a well-defined query and there is no deadlock.

3. Try to look up the data according to the index, and the range search increases the possibility of lock conflicts.

4. For business parts that are very prone to deadlocks, you can try to upgrade the lock granularity and reduce the probability of deadlocks by table locking.

Lock monitoring table lock monitoring

Get table lock contention:

SHOW STATUS LIKE "table%"

Checked a lot of information, it is indeed this method of acquisition, but I did not test its usefulness, tried two databases are not good, very strange.

Query which tables are being locked:

SHOW OPEN TABLES WHERE In_use > 0

This command monitors the table locked by the table lock, testing that if you use a row lock, this command is unresponsive, and you really have to do it yourself to find out the truth.

Row lock monitoring

Get row lock contention:

SHOW STATUS LIKE "innodb_row_lock%"

Here are a few tables that can help us monitor current transactions and analyze possible lock problems.

Select * from information_schema.innodb_trx

The main fields are as follows:

Trx_id: the unique transaction id number

Trx_state: status of the current transaction, lock wait lock wait status, running execution status.

Trx_started: transaction start time

Trx_wait_started: transaction start wait time

Trx_mysql_thread_id: thread id

Trx_query: the SQL statement that the transaction runs

Object that holds the lock:

Select * from information_schema.innodb_locks;

Objects that the lock is waiting for:

Select * from information_schema.innodb_lock_waits

Why does decryption lock a row of data so slowly?

Experiment content: two sessions, two transactions, session 1 lock, session 2 change, the target is different row data.

The where condition of session 1 must be an index to lock this row, otherwise the data of the entire table will be locked so that session 2 cannot be locked.

The where condition of session 2 must also be an index to lock this row, otherwise it will try to lock the data of the entire table, and one row of the data of the whole table is already locked by session 1, so session 2 cannot be locked.

Why should I lock a row and MySQL lock the whole table for me?

Even if indexes 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, for example, for some very small tables, it will not use indexes, in which case InnoDB will lock the entire table record (to be clear, row locks are not upgraded to table locks, it actually locks all rows).

What about the mixed use of storage engines in transactions?

MySQL's service layer does not manage transactions, which are implemented by lower-level storage engines (table locks are implemented by MySQL's service layer), so it is risky to use tables with multiple storage engines in the same transaction.

For example, if you manipulate both innodb and myisam tables in a transaction, the normal commit will not be a problem, but if you want to roll back, the myisam table will not be rolled back.

Therefore, it is best not to use tables with different storage engines in a transaction.

Open the transaction first and then lock the table? Or do you lock the table before you start the transaction?

The answer is to open the transaction before locking the table, because the START TRANSACTION statement implies UNLOCK TABLES, and the opening of the transaction is tantamount to releasing the previous table lock.

I just open a transaction to execute SQL, does it count as a lock?

InnoDB uses a two-phase locking protocol.

Locks can be executed at any time during transaction execution, and locks are released only on commit or rollback (row locks ^ _ ^, table locks are not at the storage engine level), and all locks are released at the same time.

According to the isolation level, innodb automatically adds locks when needed, giving priority to the isolation level rules, and then row locks. If the data is really isolated, it will not be locked (if you do not believe that friends can test it, the transaction change data will be locked automatically, but the transaction check data will not be locked).

The explicit lock statements are LOCK IN SHARE MODE and FOR UPDATE.

(for the content of isolation level, please jump here: https://www.linuxidc.com/Linux/2018-11/155273.htm)

How to test whether it is locked or not?

There are two ways:

The first is to use an explicit lock statement in a transaction, and you won't feel locked without using it in a transaction.

Second, turn off autocommit mode

SET autocommit=0

After closing, you can explicitly lock without opening the transaction, and it won't release the lock until you execute commit or rollback.

This actually proves something that many people don't know: every SQL is a transaction. It's just that they all commit automatically, so people don't feel the existence of the transaction. When autocommit is turned off, the transaction must be committed manually for SQL to take effect.

Query whether automatic submission is enabled:

SHOW VARIABLES LIKE "autocommit"

Read the above summary and detailed explanation of MySQL locks, hoping to bring some help to everyone in practical application. Due to the limited space in this article, it is inevitable that there will be deficiencies and need to be supplemented. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.

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.