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

This article mainly explains the relationship between MYsql lock and index. The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn the relationship between MYsql lock and index.

The locking of mysql innodb is realized by locking index.

Select for update. An example of an exclusive lock.

If the field does not have an index, table-level locking is performed even if the wehre condition is used

If there is an index, all rows corresponding to the index value in the where condition are locked, which can be understood as indexing the index value (so even if another transaction queries other rows, they will be locked because the index value is the same. )

There is an index, and different index values are used to look up the data, but the result of the query is the same row, which can be understood as a real data row lock.

Summary

This chapter focuses on the implementation characteristics of MyISAM table-level lock and InnoDB row-level lock in MySQL, and discusses the lock problems and solutions often encountered by the two storage engines.

For MyISAM table locks, the following points are mainly discussed:

(1) the shared read lock (S) is compatible, but the shared read lock (S) and the exclusive write lock (X) and the exclusive write lock (X) are mutually exclusive, that is, read and write are serial.

(2) under certain conditions, MyISAM allows concurrent execution of queries and inserts, which can be used to solve the lock contention problem of queries and inserts for the same table in the application.

(3) the default lock scheduling mechanism of MyISAM is write first, which is not necessarily suitable for all applications. Users can adjust the contention of read-write locks by setting LOW_PRIORITY_UPDATES parameters or specifying LOW_PRIORITY options in INSERT, UPDATE, and DELETE statements.

(4) because the locking granularity of the table lock is large, and the reading and writing are serial, therefore, if there are more update operations, the MyISAM table may have serious lock waiting, so we can consider using the InnoDB table to reduce lock conflicts.

For the InnoDB table, this chapter mainly discusses the following.

L InnoDB's row locks are based on lock references, and InnoDB uses table locks if the data is not accessed through the index.

L introduces the mechanism of InnoDB gap lock (Next-key) and the reason why InnoDB uses gap lock.

Under different isolation levels, the locking mechanism and consistent read strategy of InnoDB are different.

The recovery and replication of MySQL also have a great influence on the InnoDB locking mechanism and consistent read strategy.

Lock conflicts and even deadlocks are difficult to avoid completely.

After understanding the characteristics of InnoDB locks, users can reduce lock conflicts and deadlocks through measures such as design and SQL adjustment, including:

L use a lower isolation level as much as possible

L carefully design the index and use the index to access the data as far as possible to make the locking more accurate, thus reducing the chance of lock conflict

L choose a reasonable transaction size, and small transactions are less likely to have lock conflicts.

When locking a recordset display, it is best to request a lock of sufficient level at one time. For example, if you want to modify the data, it is best to apply for an exclusive lock directly, rather than apply for a shared lock first, and then request an exclusive lock when you modify it, which can easily lead to a deadlock.

When different programs access a set of tables, they should try to agree to access the tables in the same order, and for a table, access the rows in the table in a fixed order as far as possible. This will greatly reduce the chances of deadlocks.

L access data with equal conditions as far as possible, so as to avoid the effect of gap lock on concurrent insertion.

L do not apply for more than the actual lock level; do not show locking when querying unless you have to

For some specific transactions, table locks can be used to improve processing speed or reduce the possibility of deadlocks.

Lock is a mechanism by which a computer coordinates multiple processes or threads to access a resource concurrently. In the database, in addition to the contention of traditional computing resources (such as CPU, RAM, Imax O, etc.), data is also a kind of resource shared by many users. How to ensure the consistency and effectiveness of data concurrent access is a problem that must be solved in all databases, and lock conflicts are also an important factor affecting the performance of database concurrent access. From this point of view, locks are particularly important and more complex for databases. In this chapter, we focus on the characteristics of MySQL locking mechanism, common locking problems, and some methods or suggestions to solve MySQL locking problems.

Overview of MySQL Lock

Compared with other databases, 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 (table-level locking); BDB storage engines use page locks (page-level locking), but also support table-level locks; and InnoDB storage engines support both row-level locks (row-level locking) 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.

Overhead, locking speed, deadlock, granularity, concurrency performance

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

Row-level locks: expensive and slow to add locks; deadlocks occur; locking granularity is the smallest, the probability of lock conflicts is the lowest, and the degree of concurrency is the highest.

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

As can be seen from the above characteristics, it is difficult to say in general which kind of lock is better, only in terms of the characteristics of the specific application, which kind of lock is more appropriate! Only from the perspective 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 (OLTP) systems. This is also mentioned in the "Development" of this book when it introduces the choice of table types. The following sections focus on MySQL table locks and InnoDB row locks. Since BDB has been replaced by InnoDB and is about to become a thing of the past, we will not discuss it further here.

MyISAM table lock

The MyISAM storage engine only supports table locks, which is the only lock type supported in the first few versions of MySQL. With the increasing requirements of applications for transaction integrity and concurrency, MySQL began to develop a transaction-based storage engine, and then slowly emerged a BDB storage engine that supports page locks and an InnoDB storage engine that supports row locks (actually InnoDB is a separate company that has now been acquired by Oracle). But MyISAM's table lock is still the most widely used lock type. This section details the use of MyISAM table locks.

Query table-level lock contention

Table locking contention on the system can be analyzed by examining the table_locks_waited and table_locks_immediate state variables:

Mysql > show status like 'table%'

+-+ +

| | Variable_name | Value |

+-+ +

| | Table_locks_immediate | 2979 | |

| | Table_locks_waited | 0 | |

+-+ +

2 rows in set (0.00 sec)

If the value of Table_locks_waited is high, there is a serious table-level lock contention condition.

Lock mode of MySQL table-level lock

MySQL has two modes of table-level locking: table shared read lock (Table Read Lock) and table exclusive write lock (Table Write Lock). The compatibility of lock mode is shown in Table 20-1.

Table lock compatibility in table 20-1 MySQL

Request lock mode

Is it compatible?

Current lock mode

None read lock, write lock, read lock, write lock, no lock

It can be seen that the read operation to the MyISAM table will not block other users' read requests to the same table, but will block the write requests to the same table; the write operation to the MyISAM table will block other users' read and write operations to the same table; the read operation and write operation of the MyISAM table are serial! According to the example shown in Table 20-2, when a thread acquires a write lock on a table, only the thread that holds the lock can update the table. The read and write operations of other threads wait until the lock is released.

Table 20-2 write blocking read examples for the MyISAM storage engine

Session_1session_2

Get the WRITE lock of table film_text

Mysql > lock table film_text write

Query OK, 0 rows affected (0.00 sec)

Currently, session can perform query, update, and insert operations on locked tables:

Mysql > select film_id,title from film_text where film_id = 1001

+-+ +

| | film_id | title |

+-+ +

| | 1001 | Update Test |

+-+ +

1 row in set (0.00 sec)

Mysql > insert into film_text (film_id,title) values (1003 recorder Test')

Query OK, 1 row affected (0.00 sec)

Mysql > update film_text set title = 'Test' where film_id = 1001

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

Other session queries on the locked table are blocked and need to wait for the lock to be released:

Mysql > select film_id,title from film_text where film_id = 1001

wait for

Release the lock:

Mysql > unlock tables

Query OK, 0 rows affected (0.00 sec)

wait for

Session2 gets the lock, and the query returns:

Mysql > select film_id,title from film_text where film_id = 1001

+-+ +

| | film_id | title |

+-+ +

| | 1001 | Test |

+-+ +

1 row in set (57.59 sec)

How to add a table lock

MyISAM automatically locks all tables involved before executing the query statement (SELECT), and automatically adds write locks to the tables before performing update operations (UPDATE, DELETE, INSERT, etc.). This process does not require user intervention, so users generally do not need to lock the MyISAM table directly with the LOCK TABLE command. In the examples in this book, explicit locking is basically for convenience, not necessarily.

The purpose of locking the MyISAM table display is to simulate the transaction operation to a certain extent and realize the consistent reading of multiple tables at a certain point in time. For example, there is an order table orders, which records the total amount total of each order, and an order schedule order_detail, which records the subtotal subtotal of each product for each order. Suppose we need to check whether the sum of the two tables match, we may need to execute the following two SQL:

Select sum (total) from orders

Select sum (subtotal) from order_detail

At this point, if you do not lock the two tables first, you may have the wrong result, because the order_ detail table may have changed during the execution of the first statement. Therefore, the correct way should be:

Lock tables orders read local, order_detail read local

Select sum (total) from orders

Select sum (subtotal) from order_detail

Unlock tables

The following two points should be specifically stated.

? The above example adds the "local" option to LOCK TABLES, and its function is to allow other users to insert records concurrently at the end of the table when the condition of concurrent insertion of MyISAM table is met. The problem of concurrent insertion of MyISAM table will be further introduced in later chapters.

? When explicitly locking a table with LOCK TABLES, you must acquire all locks involving the table at the same time, and MySQL does not support lock escalation. That is, after LOCK TABLES, you can only access these explicitly locked tables, not unlocked tables; at the same time, if you add a read lock, you can only perform query operations, not update operations. In fact, this is basically the case in the case of automatic locking, where MyISAM always acquires all the locks needed by the SQL statement at once. This is why there is no Deadlock Free in the MyISAM table.

In the example shown in Table 20-3, a session uses the LOCK TABLE command to add a read lock to the table film_text. This session can query the records in the locked table, but updating or accessing other tables will prompt an error; at the same time, another session can query the records in the table, but the update will cause a lock wait.

Table 20-3 write examples of read blocking for MyISAM storage engine

Session_1session_2

Get the READ lock of table film_text

Mysql > lock table film_text read

Query OK, 0 rows affected (0.00 sec)

The current session can query the table record

Mysql > select film_id,title from film_text where film_id = 1001

+-+ +

| | film_id | title |

+-+ +

| | 1001 | ACADEMY DINOSAUR |

+-+ +

1 row in set (0.00 sec)

Other session can also query the records of this table

Mysql > select film_id,title from film_text where film_id = 1001

+-+ +

| | film_id | title |

+-+ +

| | 1001 | ACADEMY DINOSAUR |

+-+ +

1 row in set (0.00 sec)

The current session cannot query tables that are not locked

Mysql > select film_id,title from film where film_id = 1001

ERROR 1100 (HY000): Table 'film' was not locked with LOCK TABLES

Other session can query or update unlocked tables

Mysql > select film_id,title from film where film_id = 1001

+-+ +

| | film_id | title |

+-+ +

| | 1001 | update record |

+-+ +

1 row in set (0.00 sec)

Mysql > update film set title = 'Test' where film_id = 1001

Query OK, 1 row affected (0.04 sec)

Rows matched: 1 Changed: 1 Warnings: 0

Inserting or updating locked tables in the current session will prompt an error:

Mysql > insert into film_text (film_id,title) values (1002 recorder Test')

ERROR 1099 (HY000): Table 'film_text' was locked with a READ lock and can't be updated

Mysql > update film_text set title = 'Test' where film_id = 1001

ERROR 1099 (HY000): Table 'film_text' was locked with a READ lock and can't be updated

Other session update lock tables wait for the lock to be acquired:

Mysql > update film_text set title = 'Test' where film_id = 1001

wait for

Release lock

Mysql > unlock tables

Query OK, 0 rows affected (0.00 sec)

wait for

Session acquires the lock, and the update operation is completed:

Mysql > update film_text set title = 'Test' where film_id = 1001

Query OK, 1 row affected (1 min 0.71 sec)

Rows matched: 1 Changed: 1 Warnings: 0

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! Examples are given below.

(1) obtain a read lock on the actor table:

Mysql > lock table actor read

Query OK, 0 rows affected (0.00 sec)

(2) however, accessing through an alias will prompt an error:

Mysql > select a.firstrunname.lastroomnamereb.firstroomnamerewritingb.lastroomname from actor an actor b where a.first_name = b.first_name and a.first_name = 'Lisa' and a.last_name =' Tom' and a.last_name b.last_name

ERROR 1100 (HY000): Table 'a'was not locked with LOCK TABLES

(3) aliases need to be locked separately:

Mysql > lock table actor as a read,actor as b read

Query OK, 0 rows affected (0.00 sec)

(4) the query based on the alias can be executed correctly:

Mysql > select a.firstrunname.lastroomnamereb.firstroomnamerewritingb.lastroomname from actor an actor b where a.first_name = b.first_name and a.first_name = 'Lisa' and a.last_name =' Tom' and a.last_name b.last_name

+-+

| | first_name | last_name | first_name | last_name | |

+-+

| | Lisa | Tom | LISA | MONROE | |

+-+

1 row in set (0.00 sec)

Concurrent insertion (Concurrent Inserts)

As mentioned above, the reading and writing of the MyISAM table are serial, but this is overall. Under certain conditions, MyISAM tables also support the concurrency of query and insert operations.

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.

When concurrent_insert is set to 0, concurrent insertion is not allowed.

When concurrent_insert is set to 1, if there are no holes in the MyISAM table (that is, 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.

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.

In the example shown in Table 20-4, session_1 acquires an READ LOCAL lock on a table that can query the table but cannot update the table; other threads (session_2), although they cannot delete and update the table, can insert the table concurrently, assuming that there are no holes in the middle of the table.

Table 20-4 read-write (INSERT) concurrency examples for MyISAM storage engine

Session_1session_2

Get the READ LOCAL lock of table film_text

Mysql > lock table film_text read local

Query OK, 0 rows affected (0.00 sec)

Currently, session cannot update or insert locking tables:

Mysql > insert into film_text (film_id,title) values (1002 recorder Test')

ERROR 1099 (HY000): Table 'film_text' was locked with a READ lock and can't be updated

Mysql > update film_text set title = 'Test' where film_id = 1001

ERROR 1099 (HY000): Table 'film_text' was locked with a READ lock and can't be updated

Other session can insert, but the update waits:

Mysql > insert into film_text (film_id,title) values (1002 recorder Test')

Query OK, 1 row affected (0.00 sec)

Mysql > update film_text set title = 'Update Test' where film_id = 1001

wait for

The current session cannot access records inserted by other session:

Mysql > select film_id,title from film_text where film_id = 1002

Empty set (0.00 sec)

Release the lock:

Mysql > unlock tables

Query OK, 0 rows affected (0.00 sec)

wait for

Other session inserted records can be obtained after the current session is unlocked:

Mysql > select film_id,title from film_text where film_id = 1002

+-+ +

| | film_id | title |

+-+ +

| | 1002 | Test |

+-+ +

1 row in set (0.00 sec)

Session2 acquires the lock, and the update operation is completed:

Mysql > update film_text set title = 'Update Test' where film_id = 1001

Query OK, 1 row affected (1 min 17.75 sec)

Rows matched: 1 Changed: 1 Warnings: 0

The concurrent insert feature of the MyISAM storage engine can be used to solve the 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 insertions; at the same time, defragment the space by executing OPTIMIZE TABLE statements periodically during the idle period of the system to recover the middle hole caused by deleting the record. For a detailed description of the OPTIMIZE TABLE statement, you can see the section "two simple and practical optimization methods" in Chapter 18.

Lock scheduling of MyISAM

As mentioned earlier, the read and write locks of the MyISAM storage engine are mutually exclusive, and the read and write operations are serial. So how does MySQL handle that one process requests a read lock for a MyISAM table while another process requests a write lock for the same table? The answer is that the writing process acquires the lock first. 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. This situation can sometimes get very bad! Fortunately, we can adjust the scheduling behavior of MyISAM through some settings.

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

? Lower the priority of update requests issued by the connection by executing the command SET LOW_PRIORITY_UPDATES=1.

? Lower the priority of the statement by specifying the LOW_PRIORITY property of the INSERT, UPDATE, and DELETE statement.

Although the above three methods are either update priority or query priority, they can still be used to solve the serious problem of reading lock waiting in relatively important applications (such as user login system).

In addition, MySQL also provides a compromise method to adjust the read-write conflict, that is, set an appropriate value for the system parameter max_write_lock_count. When the read lock of a table reaches this value, MySQL temporarily lowers the priority of the write request, giving the read process a chance to acquire the lock.

The problems and solutions caused by the write-first scheduling mechanism have been discussed above. I would also like to emphasize one point here: some query operations that need to run for a long time will also make the writing process starve to death! Therefore, in the application, we should try to avoid long-running query operations, and do not always want to use a SELECT statement to solve the problem, because this seemingly ingenious SQL statement is often more complex and takes a long time to execute. If possible, we can use intermediate tables and other measures to do certain "decomposition" of SQL statements, so that each step of the query can be completed in a short time, so as to reduce lock conflicts. If complex queries are inevitable, try to schedule execution during database idle periods, such as some periodic statistics that can be scheduled to be performed at night.

InnoDB lock problem

There are two biggest differences between InnoDB and MyISAM: one is to support transactions (TRANSACTION); the other is to use row-level locks. There are many differences between row-level locks and table-level locks. In addition, the introduction of transactions also brings some new problems. Let's first introduce a little background, and then discuss the locking problem of InnoDB in detail.

Background knowledge 1. Transaction and its ACID attributes

A transaction is a logical processing unit composed of a set of SQL statements. A transaction has the following four attributes, which are usually referred to as the ACID attributes of a transaction.

Atomicity: a transaction is an atomic operation unit that either performs all or none of the changes to the data.

Consistency (Consistent): data must be in a consistent state at the beginning and completion of a transaction. This means that all relevant data rules must be applied to the modification of the transaction to maintain data integrity; at the end of the transaction, all internal data structures (such as B-tree indexes or two-way linked lists) must also be correct.

Isolation: the database system provides an isolation mechanism to ensure that transactions are executed in an "independent" environment that is not affected by external concurrent operations. This means that the intermediate state during the transaction is not visible to the outside, and vice versa.

L Durable: after a transaction is completed, its modification of the data is permanent and can be maintained even in the event of a system failure.

A bank transfer is a typical example of a transaction.

2. Problems caused by concurrent transaction processing

Compared with serial processing, concurrent transaction processing can greatly increase the utilization of database resources and improve the transaction throughput of the database system, thus supporting more users. However, concurrent transaction processing can also bring some problems, including the following situations.

Lost update (Lost Update): when two or more transactions select the same row and then update the row based on the initially selected value, the problem of missing updates occurs because each transaction is unaware of the existence of other transactions-the last update overwrites updates made by other transactions. For example, two editors made an electronic copy of the same document. Each editor changes its copy independently, and then saves the changed copy, thus overwriting the original document. The editor who finally saved a copy of his changes overrides the changes made by another editor. This problem can be avoided if another editor cannot access the same file before one editor completes and commits the transaction.

Dirty reading (Dirty Reads): a transaction is modifying a record, and the data of this record is in an inconsistent state before the transaction is completed and committed; at this time, another transaction also reads the same record. If uncontrolled, the second transaction reads these "dirty" data and makes further processing accordingly, resulting in uncommitted data dependencies. This phenomenon is vividly called "dirty reading".

Unrepeatable Non-Repeatable Reads: a transaction reads previously read data again at some time after reading some data, only to find that the data it reads has changed, or some records have been deleted! This phenomenon is called "unrepeatable reading".

L Phantom Reads: a transaction re-reads previously retrieved data according to the same query conditions, only to find that other transactions insert new data that meets its query criteria. This phenomenon is called "phantom reading".

3. Transaction isolation level

Among the problems caused by concurrent transactions mentioned above, "update loss" should usually be avoided completely. However, preventing the loss of updates can not be solved by the database transaction controller alone, and the application needs to add the necessary locks to the data to be updated. Therefore, it should be the responsibility of the application to prevent the loss of updates.

In fact, "dirty reading", "unrepeatable reading" and "phantom reading" are all problems of database read consistency, which must be solved by the database to provide a certain transaction isolation mechanism. Database transaction isolation can be basically divided into the following two ways.

One is to lock the data before reading it to prevent other transactions from modifying the data.

The other is to generate a consistent data snapshot (Snapshot) of a data request point in time without adding any locks, and use this snapshot to provide a certain level of consistent reading (statement level or transaction level). From the user's point of view, it seems that a database can provide multiple versions of the same data, so this technology is called data multi-version concurrency control (MultiVersion Concurrency Control, referred to as MVCC or MCC), and is often called multi-version database.

The stricter the transaction isolation of the database, the less the side effects, but the greater the cost, because transaction isolation is essentially to make transactions "serialized" to a certain extent, which is obviously contradictory to "concurrency". At the same time, different applications have different requirements for read consistency and transaction isolation. For example, many applications are not sensitive to "unrepeatable reading" and "phantom reading" and may be more concerned about the ability to access data concurrently.

In order to solve the contradiction between "isolation" and "concurrency", ISO/ANSI SQL92 defines four transaction isolation levels. The isolation degree of each level is different and the allowed side effects are also different. Applications can balance the contradiction between "isolation" and "concurrency" by selecting different isolation levels according to their own business logic requirements. Table 20-5 provides a good overview of the characteristics of these four isolation levels.

Table 20-5 comparison of four isolation levels

Read data consistency and allowed concurrent side effects

Isolation level

Read data consistency dirty reading can not repeat fantasy reading

Uncommitted read (Read uncommitted)

At the lowest level, it is only guaranteed that physically corrupted data will not be read

Degree of submission (Read committed)

Whether the statement level is yes or not

Repeatable read (Repeatable read)

Whether the transaction level is

Serializer (Serializable)

Highest level, transaction level No No

Finally, it is important to note that each specific database does not necessarily fully implement the above four isolation levels. For example, Oracle only provides two standard isolation levels, Read committed and Serializable, as well as self-defined Read only isolation levels; SQL Server supports an isolation level called "snapshot" in addition to the four isolation levels defined by ISO/ANSI SQL92 above, but strictly speaking, it is an Serializable isolation level implemented with MVCC. MySQL supports all four isolation levels, but there are some features in the implementation, such as using MVCC consistent reading under some isolation levels, but not in some cases, which will be further described in later chapters.

Get InnoDB row lock contention

You can analyze row lock contention on the system by checking the InnoDB_row_lock state variable:

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

+-+ +

5 rows in set (0.01 sec)

If you find that lock contention is serious, such as high values of InnoDB_row_lock_waits and InnoDB_row_lock_time_avg, you can further observe the tables and data rows that have lock conflicts by setting InnoDB Monitors, and analyze the causes of lock contention.

The specific methods are as follows:

Mysql > CREATE TABLE innodb_monitor (an INT) ENGINE=INNODB

Query OK, 0 rows affected (0.14 sec)

You can then view it with the following statement:

Mysql > Show innodb status\ G

* * 1. Row *

Type: InnoDB

Name:

Status:

...

...

-

TRANSACTIONS

-

Trx id counter 0 117472192

Purge done for trx's n:o

< 0 117472190 undo n:o < 0 0 History list length 17 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 117472185, not started, process no 11052, OS thread id 1158191456 MySQL thread id 200610, query id 291197 localhost root ---TRANSACTION 0 117472183, not started, process no 11052, OS thread id 1158723936 MySQL thread id 199285, query id 291199 localhost root Show innodb status … 监视器可以通过发出下列语句来停止查看： mysql>

DROP TABLE innodb_monitor

Query OK, 0 rows affected (0.05 sec)

After setting up the monitor, in the display content of SHOW INNODB STATUS, there will be detailed information about the current lock waiting, including table name, lock type, locking record and so on, which is convenient for further analysis and problem determination. 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.

Row Lock Mode and locking method of InnoDB

InnoDB implements the following two types of row locks.

L shared lock (S): allows one transaction to read a row, preventing other transactions from acquiring exclusive locks for the same dataset.

L exclusive lock (X): allows transaction update data to be acquired with exclusive locks, preventing other transactions from acquiring shared read locks and exclusive write locks for the same dataset.

In addition, in order to allow row locks and table locks to coexist and implement a multi-granularity locking mechanism, InnoDB also has two internally used Intention Locks locks, both of which are table locks.

Intention shared lock (IS): a transaction intends to add a row shared lock to a data row, and the transaction must acquire the IS lock of the table before adding a shared lock to the data row.

Intention exclusive lock (IX): a transaction intends to add an exclusive lock to a data row, and the transaction must acquire the IX lock of the table before adding an exclusive lock to the data row.

The compatibility of the above lock modes is shown in Table 20-6.

Table 20-6 InnoDB row lock mode compatibility list

Request lock mode

Is it compatible?

Current lock mode

XIXSISX conflict IX conflict compatibility conflict compatibility S conflict compatibility IS conflict compatibility

If the lock mode of a transaction request is compatible with the current lock, InnoDB grants the requested lock to the transaction; conversely, if the two are not compatible, the transaction waits for the lock to be released.

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 be shown to add shared or exclusive locks to the recordset through the following statement.

? Shared lock (S): SELECT * FROM table_name WHERE... LOCK IN SHARE MODE .

? Exclusive lock (X): SELECT * FROM table_name WHERE. FOR UPDATE .

Use SELECT... IN SHARE MODE acquires a shared lock, which is mainly used to confirm the existence of a row record when data dependencies are needed, and to ensure that no one UPDATE or DELETE 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 the row record after locking it, SELECT... should be used. The exclusive lock is obtained by FOR UPDATE.

In the example shown in Table 20-7, SELECT... Update the record after IN SHARE MODE is locked to see what happens, where the actor_id field of the actor table is the primary key.

Table 20-7 examples of shared locks for the InnoDB storage engine

Session_1session_2

Mysql > set autocommit = 0

Query OK, 0 rows affected (0.00 sec)

Mysql > select actor_id,first_name,last_name from actor where actor_id = 178,

+-+

| | actor_id | first_name | last_name | |

+-+

| | 178 | LISA | MONROE |

+-+

1 row in set (0.00 sec)

Mysql > set autocommit = 0

Query OK, 0 rows affected (0.00 sec)

Mysql > select actor_id,first_name,last_name from actor where actor_id = 178,

+-+

| | actor_id | first_name | last_name | |

+-+

| | 178 | LISA | MONROE |

+-+

1 row in set (0.00 sec)

The current session adds the shared lock of share mode to the record of actor_id=178:

Mysql > select actor_id,first_name,last_name from actor where actor_id = 178lock in share mode

+-+

| | actor_id | first_name | last_name | |

+-+

| | 178 | LISA | MONROE |

+-+

1 row in set (0.01 sec)

Other session can still query the record, and you can also add share mode's shared lock to the record:

Mysql > select actor_id,first_name,last_name from actor where actor_id = 178lock in share mode

+-+

| | actor_id | first_name | last_name | |

+-+

| | 178 | LISA | MONROE |

+-+

1 row in set (0.01 sec)

The current session updates the locked record and waits for the lock:

Mysql > update actor set last_name = 'MONROE T' where actor_id = 178,

wait for

Other session also updates the record, which will cause the deadlock to exit:

Mysql > update actor set last_name = 'MONROE T' where actor_id = 178,

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

After obtaining the lock, you can successfully update:

Mysql > update actor set last_name = 'MONROE T' where actor_id = 178,

Query OK, 1 row affected (17.67 sec)

Rows matched: 1 Changed: 1 Warnings: 0

Update the record after locking with SELECT...FOR UPDATE, as shown in Table 20-8.

Table 20-8 examples of exclusive locks for the InnoDB storage engine

Session_1session_2

Mysql > set autocommit = 0

Query OK, 0 rows affected (0.00 sec)

Mysql > select actor_id,first_name,last_name from actor where actor_id = 178,

+-+

| | actor_id | first_name | last_name | |

+-+

| | 178 | LISA | MONROE |

+-+

1 row in set (0.00 sec)

Mysql > set autocommit = 0

Query OK, 0 rows affected (0.00 sec)

Mysql > select actor_id,first_name,last_name from actor where actor_id = 178,

+-+

| | actor_id | first_name | last_name | |

+-+

| | 178 | LISA | MONROE |

+-+

1 row in set (0.00 sec)

The current session records for actor_id=178 plus for update exclusive locks:

Mysql > select actor_id,first_name,last_name from actor where actor_id = 178for update

+-+

| | actor_id | first_name | last_name | |

+-+

| | 178 | LISA | MONROE |

+-+

1 row in set (0.00 sec)

Other session can query the record, but cannot add a shared lock to the record, and will wait for the lock to be acquired:

Mysql > select actor_id,first_name,last_name from actor where actor_id = 178,

+-+

| | actor_id | first_name | last_name | |

+-+

| | 178 | LISA | MONROE |

+-+

1 row in set (0.00 sec)

Mysql > select actor_id,first_name,last_name from actor where actor_id = 178for update

wait for

The current session can update the locked record and release the lock after the update:

Mysql > update actor set last_name = 'MONROE T' where actor_id = 178,

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

Mysql > commit

Query OK, 0 rows affected (0.01 sec)

Other session acquires locks and records submitted by other session:

Mysql > select actor_id,first_name,last_name from actor where actor_id = 178for update

+-+

| | actor_id | first_name | last_name | |

+-+

| | 178 | LISA | MONROE T |

+-+

1 row in set (9.59 sec)

InnoDB Row Lock implementation

InnoDB row locking is achieved by locking the index items on the index, unlike Oracle, which is achieved by locking the corresponding data rows in the data block. The row lock implementation feature of InnoDB means that InnoDB uses row-level locks only if the data is retrieved by index conditions, otherwise InnoDB will use table locks!

In practical applications, special attention should be paid to this feature of InnoDB row locks, otherwise it may lead to a large number of lock conflicts, thus affecting concurrency performance. The following is illustrated by some practical examples.

(1) InnoDB does use table locks instead of row locks when querying without index conditions.

In the example shown in Table 20-9, the tab_no_index table does not have an index at first:

Mysql > create table tab_no_index (id int,name varchar (10)) engine=innodb

Query OK, 0 rows affected (0.15 sec)

Mysql > insert into tab_no_index values (1), (2) (2), (3), (4)

Query OK, 4 rows affected (0.00 sec)

Records: 4 Duplicates: 0 Warnings: 0

Table 20-9 example of using table locks for InnoDB storage engine tables without indexes

Session_1session_2

Mysql > set autocommit=0

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from tab_no_index where id = 1

+-+ +

| | id | name |

+-+ +

| | 1 | 1 |

+-+ +

1 row in set (0.00 sec)

Mysql > set autocommit=0

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from tab_no_index where id = 2

+-+ +

| | id | name |

+-+ +

| | 2 | 2 |

+-+ +

1 row in set (0.00 sec)

Mysql > select * from tab_no_index where id = 1 for update

+-+ +

| | id | name |

+-+ +

| | 1 | 1 |

+-+ +

1 row in set (0.00 sec)

Mysql > select * from tab_no_index where id = 2 for update

wait for

In the example shown in Table 20-9, it looks like session_1 only adds an exclusive lock to one row, but session_2 waits for a lock when it requests an exclusive lock for another row! The reason is that without an index, InnoDB can only use table locks. When we add an index to it, InnoDB locks only eligible rows, as shown in tables 20-10.

Create a tab_with_ index table. The id field has a normal index:

Mysql > create table tab_with_index (id int,name varchar (10)) engine=innodb

Query OK, 0 rows affected (0.15 sec)

Mysql > alter table tab_with_index add index id (id)

Query OK, 4 rows affected (0.24 sec)

Records: 4 Duplicates: 0 Warnings: 0

Table 20-10 examples of InnoDB storage engine tables using row locks when using indexes

Session_1session_2

Mysql > set autocommit=0

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from tab_with_index where id = 1

+-+ +

| | id | name |

+-+ +

| | 1 | 1 |

+-+ +

1 row in set (0.00 sec)

Mysql > set autocommit=0

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from tab_with_index where id = 2

+-+ +

| | id | name |

+-+ +

| | 2 | 2 |

+-+ +

1 row in set (0.00 sec)

Mysql > select * from tab_with_index where id = 1 for update

+-+ +

| | id | name |

+-+ +

| | 1 | 1 |

+-+ +

1 row in set (0.00 sec)

Mysql > select * from tab_with_index where id = 2 for update

+-+ +

| | id | name |

+-+ +

| | 2 | 2 |

+-+ +

1 row in set (0.00 sec)

(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. You should pay attention to this when applying design.

In the example shown in tables 20-11, the id field of table tab_with_index has an index, and the name field has no index:

Mysql > alter table tab_with_index drop index name

Query OK, 4 rows affected (0.22 sec)

Records: 4 Duplicates: 0 Warnings: 0

Mysql > insert into tab_with_index values (1)

Query OK, 1 row affected (0.00 sec)

Mysql > select * from tab_with_index where id = 1

+-+ +

| | id | name |

+-+ +

| | 1 | 1 |

| | 1 | 4 |

+-+ +

2 rows in set (0.00 sec)

Table 20-11 blocking examples of the InnoDB storage engine using the same index key

Session_1session_2

Mysql > set autocommit=0

Query OK, 0 rows affected (0.00 sec)

Mysql > set autocommit=0

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from tab_with_index where id = 1 and name ='1' for update

+-+ +

| | id | name |

+-+ +

| | 1 | 1 |

+-+ +

1 row in set (0.00 sec)

Although session_2 accesses a different record than session_1, because it uses the same index, you need to wait for the lock:

Mysql > select * from tab_with_index where id = 1 and name ='4' for update

wait for

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

In the example shown in tables 20-12, the id field of table tab_with_index has a primary key index and the name field has a normal index:

Mysql > alter table tab_with_index add index name (name)

Query OK, 5 rows affected (0.23 sec)

Records: 5 Duplicates: 0 Warnings: 0

Table 20-12 blocking examples of InnoDB storage engine tables using different indexes

Session_1session_2

Mysql > set autocommit=0

Query OK, 0 rows affected (0.00 sec)

Mysql > set autocommit=0

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from tab_with_index where id = 1 for update

+-+ +

| | id | name |

+-+ +

| | 1 | 1 |

| | 1 | 4 |

+-+ +

2 rows in set (0.00 sec)

Session_2 uses name's index to access the record, and because the record is not indexed, the lock can be obtained:

Mysql > select * from tab_with_index where name ='2' for update

+-+ +

| | id | name |

+-+ +

| | 2 | 2 |

+-+ +

1 row in set (0.00 sec)

Since the accessed record has been locked by session_1, wait for the lock to be acquired. :

Mysql > select * from tab_with_index where name ='4' for update

(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. Therefore, when analyzing lock conflicts, don't forget to check the execution plan of SQL to confirm that indexes are actually used. For a detailed discussion of when MySQL does not use indexes, see the introduction to the "indexing issues" section of this chapter.

In the following example, the data type of the retrieval value is different from that of the index field, and although MySQL can convert the data type, it does not use the index, which causes InnoDB to use table locks. We can clearly see this by examining the execution plans of the two SQL with explain.

In the example, the name field of the tab_with_index table has an index, but the name field is of type varchar. If the where condition is not compared with the varchar type, the name will be converted and the full table scan will be performed.

Mysql > alter table tab_no_index add index name (name)

Query OK, 4 rows affected (8.06sec)

Records: 4 Duplicates: 0 Warnings: 0

Mysql > explain select * from tab_with_index where name = 1\ G

* * 1. Row *

Id: 1

Select_type: SIMPLE

Table: tab_with_index

Type: ALL

Possible_keys: name

Key: NULL

Key_len: NULL

Ref: NULL

Rows: 4

Extra: Using where

1 row in set (0.00 sec)

Mysql > explain select * from tab_with_index where name ='1'\ G

* * 1. Row *

Id: 1

Select_type: SIMPLE

Table: tab_with_index

Type: ref

Possible_keys: name

Key: name

Key_len: 23

Ref: const

Rows: 1

Extra: Using where

1 row in set (0.00 sec)

Gap lock (Next-Key lock)

When we use range conditions instead of equality conditions to retrieve data and request shared or exclusive locks, InnoDB will lock the index entries of existing data records that meet the conditions; for records whose key values are within the range of conditions but do not exist, it is called "GAP", and InnoDB will also lock this "gap", which is the so-called Next-Key lock.

For example, if there are only 101 records in the emp table, the values of their empid are 1, 2, and 100, respectively, and the following SQL:

Select * from emp where empid > 100 for update

Is a search of range conditions, where InnoDB locks not only records with an empid value of 101, but also "gaps" with an empid greater than 101 (these records do not exist).

The purpose of InnoDB using gap lock is, on the one hand, to prevent misreading in order to meet the requirements of the relevant isolation level. For the above example, if you do not use gap lock, if other transactions insert any records with an empid greater than 100, then if the transaction executes the above statement again, false reading will occur; on the other hand, it is to meet its recovery and replication needs. The impact of its recovery and replication on the locking mechanism, as well as the use of gap locks by InnoDB under different isolation levels, will be further described in subsequent chapters.

Obviously, when using range conditions to retrieve and lock records, the locking mechanism of InnoDB will block the concurrent insertion of key values within the conditional range, which often leads to serious lock waiting. Therefore, in the practical application development, especially the applications with more concurrent inserts, we should optimize the business logic as far as possible, use equal conditions to access updated data as far as possible, and avoid using scope conditions.

In particular, 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!

In the example shown in Table 20-13, if there are only 101 records in the emp table, the empid values are 1, 2, and 100, respectively.

Table 20-13 examples of gap lock blocking for the InnoDB storage engine

Session_1session_2

Mysql > select @ @ tx_isolation

+-+

| | @ @ tx_isolation |

+-+

| | REPEATABLE-READ |

+-+

1 row in set (0.00 sec)

Mysql > set autocommit = 0

Query OK, 0 rows affected (0.00 sec)

Mysql > select @ @ tx_isolation

+-+

| | @ @ tx_isolation |

+-+

| | REPEATABLE-READ |

+-+

1 row in set (0.00 sec)

Mysql > set autocommit = 0

Query OK, 0 rows affected (0.00 sec)

The current session adds for update locks to records that do not exist:

Mysql > select * from emp where empid = 102for update

Empty set (0.00 sec)

At this point, if another session inserts a record with an empid of 102 (note: this record does not exist), a lock wait will also occur:

Mysql > insert into emp (empid,...) Values (102)

Blocking waiting

Thank you for your reading, the above is the content of "the relationship between MYsql lock and index". After the study of this article, I believe you have a deeper understanding of the relationship between MYsql lock and index, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.