Mysql lock Table Mechanism Analysis and lock table Problems

Mysql lock table is an optimization item for better processing of ultra-large loads. Next I will introduce some problems encountered by the mysql lock table mechanism and mysql lock table.

To optimize mysql in high concurrency, it is necessary to understand the lock table mechanism when mysql queries an update.
I. Overview
MySQL has three levels of locks: Page, table, and row.
The MyISAM and MEMORY storage engines use table-level locking. The BDB storage engine uses page-level locking.
The InnoDB Storage engine supports both row-level locking and table-level locking. However, row-level locking is used by default.
The features of the three MySQL locks are summarized as follows:
Table-level locks: low overhead, fast locking, no deadlock, large lock granularity, the highest probability of lock conflict, the lowest concurrency.
Row-level locks: high overhead, slow locking, deadlock, minimum lock granularity, the lowest probability of lock conflict, and the highest concurrency.
Page lock: overhead and lock time are between table locks and row locks. Deadlocks may occur. The lock granularity is between table locks and row locks, with a general concurrency.
Ii. MyISAM Table lock
The MyISAM storage engine only supports table locks and is the most widely used storage engine.
1. 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:

The Code is as follows:	Copy code
Mysql> show status like 'table % '; + -------- + ---- + | Variable_name | Value | + -------- + ---- + | Table_locks_immediate | 76939364 | | Maid | 305089 | + -------- + ---- +

2 rows in set (0.00 sec) Table_locks_waited has a high value, which indicates that there is a serious table-Level Lock contention.

2. MySQL table-Level Lock mode
There are two Table-level locks in MySQL: Table Read Lock and Table Write
Lock ). Before executing a query statement (SELECT), MyISAM automatically locks all involved tables before performing UPDATE operations (UPDATE, DELETE, INSERT, etc, A write lock is automatically applied to the involved table.
Therefore, the MyISAM table may be operated in the following situations:
A. Read operations (read lock) on the MyISAM table will not block read requests from other processes to the same table, but will block write requests to the same table. Write operations of other processes are performed only after the read lock is released.
B. Write operations on the MyISAM table (with write locks) will block read and write operations on the same table by other processes. Only when the write lock is released, to read and write other processes.
The following example is used to verify the above points. The data table gz_phone contains more than 2 million data, field id, phone, ua, and day. Now, you can use multiple clients to perform Operation Analysis on the table at the same time.
A. When I use Client 1 for a long read operation, I use Client 2 for read and write operations respectively:
Client1:

The Code is as follows:

Copy code

Mysql> select count (*) from gz_phone group by ua; 75508 rows in set (3 min 15.87 sec) client2: Select id, phone from gz_phone limit 1000,10; + -- + --- + | Id | phone | + -- + --- + | 1001/2222 | | 1002/2222 | | 1003/2222 | | 1004/2222 | | 1005/2222 | | 1006/2222 | | 1007/2222 | | 1008/2222 | | 1009/2222 | | 1010/2222 | + -- + --- + 10 rows in set (0.01 sec) Mysql> update gz_phone set phone = '000000' where id = 11111111111; Query OK, 0 rows affected (2 minutes 57.88 sec) Rows matched: 1 Changed: 0 Warnings: 0

This means that when the data table has a read lock, the query operation of other processes can be executed immediately, but the update operation will be executed only after the read lock is released.
B. When client 1 is used for a long update operation, client 2 and 3 are used for read/write operations respectively:
Client1:

The Code is as follows:

Copy code

Mysql> update gz_phone set phone = '000000 ′; Query OK, 1671823 rows affected (3 min 4.03 sec) Rows matched: 2212070 Changed: 1671823 Warnings: 0 client2: Mysql> select id, phone, ua, day from gz_phone limit 10; + -- + --- + ------- + ---- + | Id | phone | ua | day | + -- + --- + ------- + ---- + | 1 | 2222 | SonyEricssonK310c | | 2 | 2222 | SonyEricssonK750c | | 3 | 2222 | maui wap Browser | | 4 | 2222 | Nokia3108 | 2007-12-19 | | 5 | 2222 | LENOVO-I750 | | 6 | 2222 | BIRD_D636 | 2007-12-19 | | 7 | 2222 | SonyEricssonS500c | | 8 | 2222 | SAMSUNG-SGH-E258 | | 9 | 2222 | NokiaN73-1 | | 10 | 2222 | Nokia2610 | 2007-12-19 | + -- + --- + ------- + ---- + 10 rows in set (2 min 58.56 sec) client3: Mysql> update gz_phone set phone = '000000' where id = 1; Query OK, 1 row affected (3 min 50.16 sec) Rows matched: 1 Changed: 1 Warnings: 0

This indicates that when a data table has a write lock, read/write operations of other processes must be performed only after the read lock is released.
3. Concurrent Inserts
In principle, when a data table has a read lock, other processes cannot update the table. However, under certain conditions, the MyISAM table also supports concurrent query 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.
A. Concurrent inserts are not allowed when concurrent_insert is set to 0.
B. When concurrent_insert is set to 1, if the MyISAM table has no 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.
C. 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.
4. Lock Scheduling for MyISAM
MySQL considers that write requests are generally more important than read requests. Therefore, if a read/write request is performed simultaneously, MYSQL will give priority to write requests. In this way, when the MyISAM Table performs a large number of update operations (especially when indexes exist in the updated fields), it is difficult for the query operation to obtain the read lock, leading to query blocking.
We can adjust the scheduling behavior of MyISAM through some settings:
A. by specifying the startup parameter low-priority-updates, the MyISAM engine gives the Read Request priority by default.
B. Run the SET LOW_PRIORITY_UPDATES = 1 command to lower the priority of the update request sent by the connection.
C. Reduce the priority of an INSERT, UPDATE, or DELETE Statement by specifying the LOW_PRIORITY attribute.
The above three methods are either Update-first or query-first methods. It should be noted that do not blindly set mysql to read first, because some query operations that require a long time to run will also starve the write process ". You can only decide which operation is preferred based on your actual situation. These methods do not fundamentally solve the problem of query and update at the same time.
In a mysql with a large amount of data and published data, we can use another method for optimization, that is, implementing Load Balancing through mysql master-slave (read/write) separation, in this way, you can avoid the first operation which may lead to congestion of the other operation. The following sections describe the mysql read/write splitting technology.

Kill the locked table

 

The Code is as follows:

Copy code

Show processlist: Check whether the table status in the database is locked; Kill id // kill the locked table ========================================================== ============== Set autocommit = 0; Select * from t1 where uid = 'xxxx' for update // row lock in the case of an index (such as uid); otherwise, table lock Insert into t1 values (1, 'xxxxx '); Commit; ========================================================== ================== Lock tables t1 write | read; Insert into t1 values (2, 'xxxxx'); // only insert Unlock tables;

The following describes some methods to avoid or reduce the competition caused by table locking:

· Try to make the SELECT statement run faster. You may have to create some summary tables to do this.
· Start mysqld with -- low-priority-updates. This gives all statements that update (modify) a table with a lower priority than the SELECT statement. In this case, the 2nd SELECT statements in the previous case will be executed before the UPDATE statement, without waiting for 1st SELECT statements to complete.
· You can use the SET_UPDATES = 1 Statement to specify that all updates in a specific connection should have a low priority.
· The LOW_PRIORITY attribute can be used to give a specific INSERT, UPDATE, or DELETE statement with a lower priority.
· The HIGH_PRIORITY attribute can be used to give a specific SELECT statement with a higher priority.
· Specify a low value for the max_write_lock_count system variable to start mysqld to force MySQL to temporarily increase the priority of all SELECT statements waiting for a table after a specific number of inserts are completed. In this way, the READ lock is allowed after a certain number of WRITE locks.
· If you have questions about INSERT and SELECT, switch to the new MyISAM table because they support concurrent SELECT and INSERT.
· If you INSERT or delete a table in a hybrid manner, insert delayed will be of great help.
· If you have a problem using the SELECT and DELETE statements in combination with the same table, the delete limit option can be helpful.
· SQL _BUFFER_RESULT can be used for SELECT statements to shorten the table lock time.
· You can change the lock code in mysys/thr_lock.c to use a single queue. In this case, write locks and read locks have the same priority and will be helpful to some applications.

Here are some techniques related to table locking in MySQL:

· You can perform parallel operations if you do not need to check the selection of many rows in the same table and do not need to perform hybrid updates.
· You can use lock tables to increase the speed, because many updates in a LOCK are much faster than those that are not locked. It is also helpful to split the table content into several tables.
· If you encounter speed problems during table lock in MySQL, you can convert the table to an InnoDB or BDB table to improve performance.