Shulou(Shulou.com)06/01 Report--

Locks summarize the characteristics of mysql locking 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: low overhead, fast locking, no deadlock, large lock granularity, the highest probability of lock conflict and the lowest degree of concurrency; suitable for query-based applications with only a small amount of data updated according to index conditions, such as Web application; row-level lock overhead, slow locking, deadlock, small lock granularity, the lowest probability of lock conflict, the highest degree of concurrency It is suitable for applications where there are a large number of concurrent updates of a small amount of different data according to index conditions and concurrent queries, such as some online transaction processing (OLTP) systems. Page lock overhead and locking time are between table lock and row lock, deadlock occurs, locking granularity is between table lock and row lock, and the concurrency is average. MyISAM table lock queries table lock contention.

Mysql > show status like 'table%'

+-+ +

| | Variable_name | Value |

+-+ +

| | Table_locks_immediate | 19 | |

| | Table_locks_waited | 0 | |

+-+ +

2 rows in set (0.00 sec)

The higher the value of table_locks_waited, the more serious the contention of table-level locks. The lock mode of table-level locks, table shared read locks, table shared write locks.

The MyISAM table read operation will not block other users' read requests to the same table, but will block write requests to the same table.

The write operation of the MyISAM table blocks other users' read and write requests to the same table.

The MyISAM table is serial between read and write operations, as well as between write operations

How to add a table lock

The MyISAM table automatically adds read locks to the tables involved before executing the SELECT statement, and automatically adds write locks to the tables involved before executing UPDATE, DELETE, and INSERT.

Of course, manual locking can also be displayed to simulate transaction 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, it is not allowed to use the alias of the table, 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

The read and write operations of MyISAM are serial, but to some extent, it also supports the concurrency of queries and inserts, but cannot be deleted or updated.

In the MyISAM engine, there is a system variable concurrent_insert, which is specifically used to control the behavior of concurrent inserts, and has three values:

0-> concurrent inserts are not allowed

1-> on the premise that there are no holes in the MyISAM table, another process is allowed to insert records from the tail while reading (default)

2-> with or without holes, you can insert concurrently at the end of the table.

Defragment the space debris:

Mysql > optimize table tbl_name

Lock scheduling of MyISAM

It is known that the read lock and write lock of the MyISAM storage engine are mutually exclusive, and the read and write operations are serial, but the timely read request reaches the waiting queue first, the write request reaches the waiting queue after the write request, and the write lock is also inserted before the read lock, because MySQL thinks the write operation is more important than the read operation.

At the same time, this is also the reason why MyISAM tables are not suitable for a large number of update and query operations, because a large number of update and query operations will occupy the lock waiting queue, and read locks will be waiting for a long time.

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

Start low-priority-updates so that MyISAM defaults to priority based on read requests

Lower the priority of update requests

Mysql > set LOW_PRIORITY_UPDATES=1

Lower the priority of the statement by specifying the LOW_PRIORITY attribute of the INSERT, UPDATE, and DELETE statement

A compromise: set the system parameter max_write_locl_count to an appropriate value, and when the read operation of the table reaches that value, MySQL temporarily reduces the priority of the write request.

InnoDB lock

The biggest differences between InnoDB and MyISAM are: 1, supporting transactions; 2, using row-level locks.

Query 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 higher the values of Innodb_row_lock_waits and Innodb_row_lock_time_avg, the more serious the contention of table-level locks. InnoDB row lock mode shared lock (S)

Allow one transaction to read a row, preventing other transactions from acquiring exclusive locks on the same dataset

Exclusive lock (X)

Allow transactions that acquire exclusive locks to update data, preventing other transactions from acquiring shared read locks and exclusive write locks for the same dataset

In order for row lock table locks to coexist and implement multi-granularity locking mechanism, InnoDB also has two kinds of internal intention locks, both of which are table lock intention shared locks (IS).

Before a transaction can add a shared lock to a data row, it must first acquire the IS lock of the table

Intention exclusive lock (IX)

Before a transaction can add an exclusive lock to a data row, it must first acquire the IX lock of the table

Intention locks are added automatically by InnoDB without user intervention

For UPDATE, DELETE, and INSERT statements, InnoDB automatically adds exclusive locks to the dataset involved; for SELECT statements, InnoDB does not add any locks.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.