The analysis of MySQL lock table mechanism and the problem of locking table

For better optimization of MySQL in high concurrency situations, it is necessary to understand the locking mechanism of MySQL query update.
First, overview
MySQL has three levels of locks: page level, table level, row level. The
MyISAM and memory storage engines use table-level locks (table-level locking), BDB the storage engine with page locks (page-level
Locking), but also support table-level locks The InnoDB storage engine supports both row-level locks (row-level locking) and table-level locks, but by default, row-level locks are used. The characteristics of the
MySQL 3 locks can be summed up as follows:
Table-level Lock: Low overhead, lock fast, no deadlock, lock granularity, the highest probability of lock conflict, the least concurrency.
Row-level locks: large overhead, slow lock, deadlock, minimum locking granularity, lowest probability of lock collisions, and highest concurrency.
Page Lock: Overhead and lock time bounded between table and row locks, deadlocks occur, locking granularity bounded between table locks and row locks, and concurrency is general. The
second, MyISAM table lock
MyISAM storage engine supports only table locks and is the storage engine that is now used most.
1, query table-level lock contention
You can analyze table lockout contention on the system by examining table_locks_waited and table_locks_immediate state variables:

The code is as follows	Copy Code
Mysql> Show status like ' table% '; + ——————— –+ ———-+ | variable_name | Value | + ——————— –+ ———-+ | Table_locks_immediate | 76939364 | | table_locks_waited | 305089 | + ——————— –+ ———-+

The high value of the 2 rows in Set (0.00 sec) table_locks_waited indicates a more serious table-level lock contention situation.

2, MySQL table-level lock mode
MySQL has two modes of table-level locks: Table shared read lock (table read lock) and table exclusive write lock (table write
Lock). MyISAM automatically reads locks on all the tables involved before executing a query (SELECT), and automatically writes locks to the tables involved before the update operation (update, DELETE, insert, and so on) is performed.
Therefore, the operation of the MyISAM table can be as follows:
A, read operations on the MyISAM table (read locks), does not block read requests from other processes to the same table, but blocks write requests to the same table. Writes to other processes are performed only when the read lock is freed.
B, writes to the MyISAM table (write-lock), blocks other processes from reading and writing to the same table, and does not perform read-write operations on other processes until the write lock is freed.
The example below validates the above view. Data table Gz_phone has more than 2 million data, field id,phone,ua,day. The table is now operated on simultaneously with multiple clients.
A, when I am using Client 1 for a longer read operation, read and write with Client 2 respectively:
Client1:

code is as follows

copy code

mysql> Select COUNT (*) from the 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 | | 10 06 | 2222 | | 1007 | 2222 | | 1008 | 2222 | | 1009 | 2222 | | 1010 | 2222 | +--+ ——-+ rows in Set (0.01 sec) mysql> update gz_phone set phone= ' 11111111111′where id=1001; Query OK, 0 rows affected (2 min 57.88 sec) Rows matched:1 changed:0 warnings:0

Shows that when a data table has a read lock, the other process's query operations can be executed immediately, but the update operation waits for the read lock to be released before it executes.
b, when using Client 1 for a longer period of update operation, the client 2,3 to read and write separately:
CLIENT1:

The code is as follows

Copy Code

mysql> Update gz_phone set phone= ' 11111111111′; 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 | 2007-12-19 | | 2 | 2222 | sonyericssonk750c | 2007-12-19 | | 3 | 2222 | MAUI WAP Browser | 2007-12-19 | | 4 | 2222 | Nokia3108 | 2007-12-19 | | 5 | 2222 | lenovo-i750 | 2007-12-19 | | 6 | 2222 | bird_d636 | 2007-12-19 | | 7 | 2222 | sonyericssons500c | 2007-12-19 | | 8 | 2222 | samsung-sgh-e258 | 2007-12-19 | | 9 | 2222 | nokian73-1 | 2007-12-19 | | 10 | 2222 | Nokia2610 | 2007-12-19 | +--+ ——-+ ——————-+ ———— + Rows in Set (2 min 58.56 sec) Client3: mysql> Update gz_phone set phone= ' 55555′where id=1; Query OK, 1 row affected (3 min 50.16 sec) Rows matched:1 changed:1 warnings:0

The

indicates that when a data table has a write lock, the read and write operations of the other processes are not performed until the read lock is released.
3, concurrent insert
in principle, when a data table has a read lock, other processes cannot update the table, but under certain conditions, the MyISAM table also supports concurrent query and insert operations. The
MyISAM storage engine has a system variable Concurrent_insert that is designed to control the behavior of its concurrent inserts, with values of 0, 1, or 2, respectively.
A, when Concurrent_insert is set to 0 o'clock, concurrent inserts are not allowed.
B, when Concurrent_insert is set to 1 o'clock, if there is no hole in the MyISAM table (that is, there are no deleted rows in the middle of the table), MyISAM allows another process to insert records from the end of the table while one process reads the table. This is also the default setting for MySQL.
C, when Concurrent_insert is set to 2 o'clock, the record is allowed to be inserted at the end of the table, regardless of whether there is a hole in the MyISAM table.
4, MyISAM lock scheduling
because MySQL considers write requests to be more important than read requests, MySQL will give precedence to write if a read and write request is made at the same time. This makes it difficult for query operations to gain read locks, which can cause queries to block if the MyISAM table is in a large number of update operations (especially if an index exists in the updated field).
We can adjust the MyISAM scheduling behavior with some settings:
A, by specifying the startup parameter low-priority-updates, so that the MyISAM engine defaults to the right to read requests.
B, to make the update requests issued by the connection less priority by executing the command set Low_priority_updates=1.
C, reducing the precedence of a statement by specifying the Low_priority property of the Insert, UPDATE, and DELETE statement.
The above 3 methods are either updating first or querying the preferred method. Here to explain is, do not blindly set the MySQL to read first, because some require long-running query operations, will also make the writing process "starved to death." Only according to your actual situation, decide which kind of operation to set priority. These methods still do not fundamentally solve the problem of querying and updating at the same time.
in a large volume of data and published in MySQL, we can also use another strategy for optimization, that is, through the MySQL master (read and write) separation to achieve load balancing, so as to avoid the priority of which operation may lead to another operation of the blockage. Here is a space to illustrate MySQL's read-write separation technology.

kill the locked watch.

The code is as follows

Copy Code

Show Processlist View the state of the table in the database and whether it is locked; Kill ID//destroy the locked table =================================================== Set autocommit=0; SELECT * from t1 where uid= ' xxxx ' for update//row lock with index (for example, UID) or table lock INSERT into T1 values (1, ' xxxxx '); Commit ===================================================== Lock tables T1 write|read; INSERT into T1 values (2, ' xxxxx '); Insert only Unlock tables;

Some methods are described below to avoid or reduce the competition caused by table locking:

· An attempt was made to run the SELECT statement faster. You may have to create some summary (summary) tables to do this.
· Start mysqld with--low-priority-updates. This will give all updated (modified) Statements of one table a lower priority than the SELECT statement. In this case, the 2nd SELECT statement in the previous case will be executed before the UPDATE statement, rather than waiting for the 1th select to complete.
· You can use the Set_updates=1 statement to specify that all updates in a specific connection should use low-priority levels.
· You can give a specific insert, UPDATE, or DELETE statement a lower priority with the Low_priority property.
· You can use the High_priority property to give a specific SELECT statement a higher priority.
· Specify a low value for the MAX_WRITE_LOCK_COUNT system variable to start the mysqld to force MySQL to temporarily increase the priority of all SELECT statements waiting for a table after a specific number of inserts have been completed. This allows a read lock to be given after a certain number of write locks.
· If you have questions about insert combination Select, switch to use the new MyISAM table because they support concurrent select and insert.
· If you mix inserts and deletes on the same table, insert delayed will help a lot.
· The limit option for delete can be helpful if you are having problems mixing the SELECT and DELETE statements with the same table.
· Using Sql_buffer_result with the SELECT statement can help 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 will have the same priority and will be helpful for some applications.

Here are some techniques for table locking in MySQL:

• You can do parallel operations if you do not mix updates with the choice of checking many rows in the same table.
• You can use lock TABLES to improve speed because many updates are much faster in one lock than those that are not locked. It is also helpful to cut the contents of a table into several tables.
• If a speed problem is encountered in a table lock in MySQL, you can convert the table to a InnoDB or BDB table to improve performance