The lock mechanism of MySQL

Lock overview The features of the MySQL lock mechanism: Different storage engines support different locking mechanisms. MyISAM and memory storage engines support table-level locks, BDB storage engines use page locks, and InnoDB storage engines support row-level locks.

• Table-level Lock: The cost is small, lock fast, no deadlock, locking granularity, lock collision probability is the highest, concurrency is the lowest; Apply to query-based, only a small number of index conditions to update data applications, such as Web applications;
• Row-level lock overhead, locking slow, there will be deadlock, locking granularity is small, the probability of locking conflict is the lowest, the highest concurrency; it is suitable for applications that have a large number of concurrent queries, such as some online transaction processing (OLTP) systems, that can update a small amount of different data by index and concurrently.
• Page lock cost and lock time between table lock and row lock, there will be deadlock, lock granularity between table lock and row lock, concurrency is general;

MyISAM table lock query table-level lock race condition

Mysql> Show status like ' table% ';
+-----------------------+-------+
| variable_name | Value |
+-----------------------+-------+
| Table_locks_immediate | 19 |
| table_locks_waited | 0 |
+-----------------------+-------+
2 rows in Set (0.00 sec)

The larger the value of the table_locks_waited, the more severe the contention of table-level locks the lock mode of table-level lock

• Table Shared read lock
• Table Shared write lock

MyISAM Table read operation does not block other users from reading requests to the same table, but blocks write requests to the same table;
The write operation of the MyISAM table blocks other users from reading and writing requests to the same table.
The read and write operations of the MyISAM table and the write operation are serial

How to add a table lock

The MyISAM table automatically adds a read lock to the table that is involved before executing the SELECT statement, and automatically writes locks to the table involved before the update, DELETE, insert.

Of course, you can also show manual locking to simulate transactional operations.

mysql> lock table Tbl_name1 read/write;
mysql> lock Tables Tbl_name1 read [local], tbl_name2 write [local];

mysql> unlock tables;

Note: After locking the table, the alias used for the table is not allowed and the alias needs to be locked, as follows:

mysql> Lock Table tbl_name1 as TB1 read;
Mysql> Select a.id from Tbl_name tb1;

Concurrent insertion of MyISAM

MyISAM Read and write operations are serial, but to a certain extent, they are also supported for querying and inserting concurrently, but cannot be deleted and updated.

There is a system variable Concurrent_insert in the MyISAM engine that is specifically designed to control the behavior of concurrent insertions with three values:

0-> not allow concurrent insertion;
1-> allows a second process to insert records from the tail at the same time as the MyISAM table without holes (default)
2-> can be inserted at the end of a table, whether or not it is empty

Organizing Space Fragmentation:

mysql> optimize table tbl_name;

Lock scheduling for MyISAM

It has been known that the MyISAM storage engine read and write locks are mutually exclusive, read and write operations are serial, but the timely read request arrives in the waiting queue, the write request arrives at the waiting queue, and the write lock is inserted before the read lock, because MySQL considers the write operation to be more important than the read operation.

Also, this is why the MyISAM table is not suitable for a large number of updates and query operations, because a large number of update and query operations occupy the lock waiting queue and read locks are long awaited.

To solve this problem, we have some parameter settings to adjust the scheduling behavior of the MyISAM.

• Start Low-priority-updates, making the MyISAM default based on read requests with priority rights;

• Reduce the priority of update requests

Mysql> Set Low_priority_updates=1

• Specify the Low_priority property of the Insert, UPDATE, DELETE statement to reduce the precedence of the statement

• A compromise scenario: set a suitable value for the system parameter Max_write_locl_count, and when the read operation of the table reaches this value, MySQL temporarily lowers the priority of the write request.

InnoDB Lock

The biggest difference between InnoDB and MyISAM is: 1, support affairs; 2, the use of row-level lock.

Query for contention for InnoDB row locks

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.00 sec)

The larger the value of innodb_row_lock_waits and Innodb_row_lock_time_avg, the more severe the contention of table-level locks Innodb Row lock mode

• Shared Lock (S)

  Allows a transaction to read one line, preventing other transactions from acquiring an exclusive lock on the same data set

• Exclusive Lock (X)

  Allow transactions that obtain exclusive locks to update data to prevent other transactions from acquiring shared read and exclusive write locks on the same data set

In order to lock table lock coexistence, the implementation of multi-granularity lock mechanism, InnoDB there are two internal use of intent lock, both intent locks are table locks

• Intent shared lock (IS)

  A transaction must obtain an IS lock on a data row before it is shared with a lock

• Intent exclusive Lock (IX)

  Transactions must obtain an IX lock on a data row before it is added to an exclusive lock

Intent locks are automatically added by InnoDB and do not require user intervention

For UPDATE, DELETE, INSERT statements, InnoDB automatically adds an exclusive lock to the data set involved, and for the SELECT statement, InnoDB does not add any locks.

This article is from the "Twilight Back" blog, make sure to keep this source http://muhuizz.blog.51cto.com/11321490/1965996

The lock mechanism of MySQL