MyISAM Table lock

The MyISAM storage engine only supports table locks, which is the only supported lock type in MySQL versions. With the increasing requirements of applications on transaction integrity and concurrency, MySQL began to develop a transaction-based storage engine, later, we gradually saw the support for page locks for the bdb storage engine and the InnoDB Storage engine that supports row locks (InnoDB is actually a separate company and has now been acquired by Oracle ). However, MyISAM Table locks are still the most widely used lock types. This section describes how to use the MyISAM Table lock.

 

Query table-Level Lock contention

 

You can analyze the table lock contention on the system by checking the table_locks_waited and table_locks_immediate status variables:

 

1. 
   
2. Mysql> show status like 'table % ';
   
3. + ----------------------- + ------- +
   
4. | Variable_name | value |
   
5. + ----------------------- + ------- +
   
6. | Table_locks_immediate | 2979 |
   
7. | Table_locks_waited | 0 |
   
8. + ----------------------- + ------- +
   
9. 2 rows in SET (0.00 Sec ))

Copy code

 

If the value of table_locks_waited is relatively high, it indicates a serious table-Level Lock contention.

 

MySQL table-Level Lock mode

 

There are two table-level locks for MySQL: Table read lock and table write lock ). The compatibility of lock modes is shown in Table 20-1.

 

Table 20-1 compatibility of table locks in MySQL

Whether the request lock mode is compatible with the current lock mode	None	Read lock	Write lock
Read lock	Yes	Yes	No
Write lock	Yes	No	No

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

 

Table 20-2 example of write blocking reading of MyISAM storage engine

Session_1	Session_2
Get the write lock for the table film_text mysql> lock table film_text write; query OK, 0 rows affected (0.00 Sec)
The current session can perform query, update, and insert operations on the locked table: 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, '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	Query by other sessions on the locked table is blocked. Wait for the lock to be released: mysql> select film_id, title from film_text where film_id = 1001; wait
Release lock: mysql> unlock tables; query OK, 0 rows affected (0.00 Sec)	Wait
	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

 

Before executing a query statement (select), MyISAM automatically locks all involved tables before performing update operations (Update, delete, insert, etc, the write lock is automatically applied to the involved table. This process does not require user intervention. Therefore, you generally do not need to directly use the lock table command to explicitly lock the MyISAM table. In the example of this book, explicit locking is basically for convenience, not required.

 

Lock the MyISAM table, which is generally used to simulate transaction operations to a certain extent to achieve consistent reading of multiple tables at a certain time point. For example, there is an order table orders, which records the total amount of each order, and there is an order list order_detail, where records the amount of each product in each order subtotal, if we need to check whether the total amount of the two tables is consistent, we may need to execute the following two SQL statements:

 

1. 
   
2. Select sum (total) from orders;
   
3. Select sum (subtotal) from order_detail;

Copy code

 

In this case, if you do not lock two tables first, an error may occur because the order_detail table may have changed during the execution of the first statement. Therefore, the correct method should be:

1. 
   
2. Lock tables orders read local, order_detail read local;
   
3. Select sum (total) from orders;
   
4. Select sum (subtotal) from order_detail;
   
5. Unlock tables;

Copy code
Note the following two points.

 

• The above example adds the "local" option when locking tables. Its function is to allow other users to insert records concurrently at the end of the table when the MyISAM table's Concurrent Insertion conditions are met, for more information about Concurrent Insertion of MyISAM tables, see the following sections.
• When you use lock tables to explicitly add a table lock to a table, you must obtain all the locks related to the table at the same time, and MySQL does not support lock upgrade. That is to say, after you execute lock tables, you can only access the explicitly locked tables, but not the unlocked tables. if you add a read lock, you can only perform the query operation, you cannot perform the update operation. In fact, this is basically the case when automatic locking is applied. MyISAM always obtains all the locks required by SQL statements at a time. This is exactly why the MyISAM table does not have a deadlock (deadlock free.
  

 

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 records in the locked table, but an error will be prompted when updating or accessing other tables. At the same time, another session can query the records in the table, but the update will cause a lock wait.

 

Table 20-3 Example of read blocking write of MyISAM storage engine

Session_1	Session_2
Get read of table film_text lock mysql> lock table film_text read; query OK, 0 rows affected (0.00 Sec)
The current session can query this 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 sessions can also query the table's records 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 sessions 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
An error will be prompted when inserting or updating the locked table in the current session: mysql> insert into film_text (film_id, title) values (1002, 'test'); error 1099 (hy000 ): table 'Film _ text' was locked with a read lock and can't be updatedmysql> 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 will wait for the lock to be obtained: mysql> Update film_text set Title = 'test' where film_id = 1001; wait
Release mysql> unlock tables; query OK, 0 rows affected (0.00 Sec)	Wait
	The session gets the lock and the update operation is complete: 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 you use lock tables, you not only need to lock all the tables used at one time, but also the number of times that the same table appears in an SQL statement, the number of times it is locked using the same alias as the SQL statement. Otherwise, an error will occur! The following is an example.

 

(1) obtain the read lock for the actor table:

1. 
   
2. Mysql> lock table actor read;
   
3. Query OK, 0 rows affected (0.00 Sec)

Copy code

 

(2) but an error will be prompted during access through the alias:

1. 
   
2. Mysql> select. first_name,. last_name, B. first_name, B. last_name from actor a, actor B where. first_name = B. first_name and. first_name = 'lisa 'and. last_name = 'Tom 'and. last_name <> B. last_name;
   
3. Error 1100 (hy000): Table 'A' was not locked with lock tables

Copy code

 

(3) You need to lock aliases separately:

1. 
   
2. Mysql> lock table actor as a read, actor as B read;
   
3. Query OK, 0 rows affected (0.00 Sec)

Copy code

 

(4) query by alias can be correctly executed:

1. 
   
2. Mysql> select. first_name,. last_name, B. first_name, B. last_name from actor a, actor B where. first_name = B. first_name and. first_name = 'lisa 'and. last_name = 'Tom 'and. last_name <> B. last_name;
   
3. + ------------ + ----------- +
   
4. | First_name | last_name |
   
5. + ------------ + ----------- +
   
6. | Lisa | Tom | Lisa | Monroe |
   
7. + ------------ + ----------- +
   
8. 1 row in SET (0.00 Sec)

Copy code

Concurrent Inserts)

The read and write operations of MyISAM tables are serial, but this is general. Under certain conditions, MyISAM tables also support concurrent queries and insert operations.

 

The MyISAM storage engine has a system variable concurrent_insert, which is used to control its Concurrent Insertion behavior. The values can be 0, 1, or 2, respectively.

• Concurrent inserts are not allowed when concurrent_insert is set to 0.
• When concurrent_insert is set to 1, if the MyISAM table does not have holes (that is, the rows in the middle of the table are not deleted), MyISAM allows a process to read the table at the same time, another process inserts records from the end of the table. This is also the default setting of MySQL.
• When concurrent_insert is set to 2, records can 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 a read local lock for the table. This thread can query the table but cannot update the table; for other threads (session_2), although the table cannot be deleted or updated, the table can be inserted concurrently. It is assumed that there is no holes in the table.

 

Table 20-4 insert concurrency examples of MyISAM storage engine

Session_1	Session_2
Obtain the read local of the table film_text to lock mysql> lock table film_text read local; query OK, 0 rows affected (0.00 Sec)
The current session cannot update or insert the locked table: mysql> insert into film_text (film_id, title) values (1002, 'test'); error 1099 (hy000 ): table 'Film _ text' was locked with a read lock and can't be updatedmysql> 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 sessions can be inserted, but the update will wait: mysql> insert into film_text (film_id, title) values (1002, 'test'); query OK, 1 row affected (0.00 Sec) mysql> Update film_text set Title = 'Update test' where film_id = 1001; wait
The current session cannot access records inserted by other sessions: mysql> select film_id, title from film_text where film_id = 1002; empty set (0.00 Sec)
Release lock: mysql> unlock tables; query OK, 0 rows affected (0.00 Sec)	Wait
After the current session is unlocked, you can obtain records inserted by other sessions: 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 gets the lock and the update operation is complete: 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 Insertion feature of the MyISAM storage engine can be used to solve the application's contention for the same table query and insertion locks. For example, if you set the concurrent_insert system variable to 2, concurrent insertion is always allowed. At the same time, you can regularly execute the optimize TABLE statement in the idle time of the system to sort out space fragments, reclaim the middle hole generated by deleting the record. For more information about optimize table statements, see section 18th "two simple and practical optimization methods.

 

Lock scheduling of MyISAM

 

As mentioned above, the Read and Write locks of the MyISAM storage engine are mutually exclusive, and read/write operations are sequential. So, one process requests the read lock of a MyISAM table, and the other process also requests the write lock of the same table. How does MySQL handle this? The answer is that the write process obtains the lock first. Not only that, even if the Read Request first goes to the lock wait queue, after the write request arrives, the write lock will be inserted before the read lock request! This is because MySQL considers that write requests are generally more important than read requests. This is precisely why the MyISAM table is not suitable for a large number of update operations and query operation applications, because a large number of update operations will make query operations difficult to obtain read locks, and thus may be blocked forever. This situation may sometimes become 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 the Read Request priority by default.
• Run the set low_priority_updates = 1 command to lower the priority of the update request sent by the connection.
• You can specify the low_priority attribute of an insert, update, or delete statement to reduce the priority of the statement.
  
  

Although the above three methods are both update-first or query-first methods, they can still be used to query applications that are relatively important (such as user logon to the system, read lock wait is a serious problem. In addition, MySQL also provides a compromise to adjust read/write conflicts, that is, to 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 write requests, giving the read process a certain chance to get the lock. The Problems and Solutions brought about by the write priority scheduling mechanism have been discussed above. It should also be emphasized that some query operations that require a long time to run will also starve the write process "! Therefore, the application should try to avoid long-running query operations. Do not always want to use a SELECT statement to solve the problem, because this seemingly clever SQL statement is often complicated, the execution takes a long time. If possible, you can use an intermediate table or other measures to "break down" the SQL statement, so that each step of the query can be completed in a short time, this reduces lock conflicts. If complex queries are unavoidable, they should be executed during idle time periods. For example, some regular statistics can be executed at night.

MyISAM Table lock