The MyISAM of the lock mechanism in MySQL

Before the lock mechanism, look at the MySQL storage engine, after all, the different engine lock mechanism is also different.

Three common engine types:

MyIsam : transaction is not supported, foreign key is not supported, so access speed is fast. lock mechanism is a table lock that supports full-text indexing

InnoDB : supports transactions, supports foreign keys, so contrast MyISAM , InnoDB processing is less efficient and takes up more disk space to retain data and indexes. lock mechanism is a row lock and does not support full-text indexing

Memory : data is stored in memory, the default hash index, ideal for storing temporary data, when the server shuts down, the data will be lost.

How to select a storage engine:

MyISAM : Applications are read-only and insert-based, with little update and delete operations, and the integrity and concurrency requirements for transactions are not high.

InnoDB : for transactional applications, foreign keys are supported, and if the application has high requirements for transactional integrity, data consistency is required under concurrency conditions. Update deletion and so on frequently (InnoDB can effectively reduce due to deletion and update caused by lock), for data accuracy requirements are relatively high, this engine is suitable.

Memory : typically used to update less frequent small tables for quick access to results.

the lock in MySQL

If you are familiar with multi-threading, then there must be a concept of locking, which is the mechanism by which the computer coordinates multiple processes or threads for concurrent access to a resource.

the lock in Mysql is divided into table and row locks:

As the name implies, a table lock is a table lock, and a row lock is a lock on a row.

table Lock features : Low overhead, no deadlock, high probability of a lock conflict, and a lower degree of concurrency.

the characteristics of the row lock : High overhead, will create a deadlock, the probability of a lock collision is low, the concurrency is higher.

So the MyISAM and memory engines use a table lock, while The InnoDB storage engine uses a row lock.

MyISAMthe locking mechanism:

Divided into shared read locks and exclusive write locks.

read locks are : When a process reads a table (select), other threads can read, but not write. The simple understanding is that you can't write when I read it.

Write locks are : When a process is written to a table of some kind (insert,update,Delete), Other threads cannot be written or read. Can be understood as, when I write, you can not read, also can not write.

So MyISAM Read and write operations, and write operations are serial! MyISAM automatically adds a corresponding lock to the table when performing read and write operations (that is, using the lock table command instead of the display),MyISAM Always get All the locks required by the SQL statement at once, which is Why the MyISAM will not deadlock.

Here are examples of write locks and read locks, respectively:

Write Lock:

Transaction 1	Transaction 2
Obtain a write lock on the first_test table: mysql> lock table First_test write; Query OK, 0 rows Affected (0.00 sec)
The current transaction can be executed for query, update, and insert operations mysql> select * from first_test ; +----+------+ | id  | age   | +----+------+ |  1   |   10  | |  2   |   11  | |  3   |   12  | |  4   |   13  | +----+------+ 4 rows in set  (0.00 sec) mysql> insert into first_test (age)  values; query ok, 1 row affected  (0.11 sec)	Other transactions are blocking queries to the locked table and need to wait until the lock is released to execute mysql> select * from first_ test; Wait
mysql> unlock table; Query OK, 0 rows Affected (0.00 sec)	Wait
	Mysql> select * from First_test; +----+------+ | ID | Age | +----+------+ |   1 | 10 | |    2 | 11 | |    3 | 12 | |    4 | 13 | |    5 | 14 | +----+------+ 5 rows in Set (9 min 45.02 sec)

The read lock example is as follows:

Transaction 1	Transactions 2
Get lock on table first_read mysql> lock table First_test read; Query OK, 0 rows Affected (0.00 sec)
The current transaction can query the table record: mysql> select * from first_test; +----+------+ | id   |  age | +----+------+ |  1   |    10 | |  2   |    11 | |  3   |    12 | |  4   |    13 | |  5   |    14 | +----+------+ 5 rows in set  (0.00 sec)	Other transactions can also be found in this table information mysql> select * from first_test; +----+------+ | id   |  age  | +----+------+ |  1   |    10 | |  2   |    11 | |  3   |    12 | |  4   |    13 | |  5   |    14 | +----+------+ 5 rows in set  (0.00 sec)
But the current transaction cannot query for tables that are not locked: mysql> select * from goods; error 1100  (HY000): table  ' goods '  was not locked with lock tables	Other transactions can query or update an unlocked table: mysql> select * from goods; +----+------------+------+ | id   | name     | num | +----+------------+------+ |  1  | firstgoods  |   11 | |  3 | thirdgoods |   11 | |  4 | fourth            |   11 | +----+------------+------+ 10 rows in set  (0.00 sec)
and inserting an update-locked table will error: mysql> insert into first_test (age)  values; error 1099  (HY000): table  ' first_test '  was  locked with  a read  lock and can ' t be updated mysql> update first_test set age=100 where id =1; error 1099  (HY000): table  ' first_test '  was locked with  a read lock and can ' t be updated	Waits when a locked table is updated: mysql> update first_test set age=100 where  id =1; Wait
mysql> unlock table; Query OK, 0 rows Affected (0.00 sec)	mysql> Update first_test set age=100 where id = 1; Query OK, 1 row affected (38.82 sec) Rows matched:1 changed:1 warnings:0

Concurrent Insertions

Just say that Mysql is serial in both insertion and modification, but MyISAM also supports concurrent operations for querying and inserting.

MyISAM has a system variable concurrent_insert(default of 1) that controls the behavior of concurrent insertions (users inserting data at the end of the table).

concurrent insertions are not allowed when Concurrent_insert is 0.

When Concurrent_insert is 1 , if there is no hole in the table (the row is not deleted in the middle),MyISAM Allows one process to read a table while another process inserts records from the end of the table.

When Concurrent_insert is 2 , You can insert a record at the end regardless of whether the MyISAM table is empty.

Transaction 1	Transaction 2
mysql> Lock table First_test read local; Query OK, 0 rows Affected (0.00 sec) -- adding the local option is a description that allows data to be inserted at the end of the table if it satisfies concurrent insertions
The current process cannot insert and update operations mysql> insert into first_test (age)  values; error 1099  (HY000): table  ' first_test '  was locked  with a read lock and can ' T be updated mysql> update first_test set age=200 where id  =1; error 1099  (HY000): table  ' first_test '  was locked  with a read lock and can ' T be updated	Other processes can insert, but the update waits: mysql> insert into first_test (age)  values; query ok, 1 row affected  (0.00 sec) mysql>  update first_test set age=200 where id =2; Wait
The current process cannot access data inserted by another process Mysql> select * from First_test; +----+------+ | ID | Age | +----+------+ |  1 | 100 | |   2 | 11 | |   3 | 12 | |   4 | 13 | |   5 | 14 | |   6 | 14 | +----+------+ 6 rows in Set (0.00 sec)
Release the lock and be happy. mysql> unlock table; Query OK, 0 rows Affected (0.00 sec)	Wait
Inserted and updated are all out: Mysql> select * from First_test; +----+------+ | ID | Age | +----+------+ |  1 | 100 | |  2 | 200 | |   3 | 12 | |   4 | 13 | |   5 | 14 | |   6 | 14 | |   7 | 15 | +----+------+ 7 Rows in Set (0.00 sec)	mysql> Update first_test set age=200 where id = 2; Query OK, 1 row affected (1 min 39.75 sec) Rows matched:1 changed:1 warnings:0

To be aware of:

Concurrent insertions are resolution of lock contention for queries and insertions in the same table.

If concurrent insertions to an empty table are fragmented, you can use the optimize table command to reclaim the holes created by deleting records when you are idle.

Lock scheduling

In MyISAM , when a process requests a read lock on a table while another process requests a write lock,Mysql will first get the latter to write the lock. Even if a read request reaches the lock wait queue before the write request, the write lock is inserted before the read lock.

Because Mysql always thinks that write requests are generally more important than read requests, which is why MyISAM is not suitable for applications with a large number of read and write operations, because a large number of write requests can make it difficult to get a read lock on a query operation , it is possible to block forever.

Treatment methods:

1. Specify the low_priority property of the Insert,update,Delete statement , Lower its priority.

2, specify the start parameter low-priority-updates, make MyISAM default to read request priority right.

3. Execute the command set Low_priority_updates=1to reduce the request made by this connection.

4, specify max_write_lock_count set a suitable value, when the write lock reaches this value, temporarily reduce the priority of the write request, let the read request to obtain the lock.

However, the above-mentioned reason is that when encountering complex query statements, write requests may be difficult to obtain the lock, this is a very tangled problem, so we generally avoid the use of complex query statements, if the law avoids, you can then the database idle phase (late night) execution.

Reference:

MySQL database development, optimization and management maintenance. Second Edition

Copyright NOTICE: This article is the original blogger articles, reproduced please indicate the source.

The MyISAM of the lock mechanism in MySQL