MySQL Advanced Nine - MyISAM Table Lock (Shared Read Lock)

Lock is a mechanism for computers to coordinate multiple processes or threads to access a certain resource concurrently. In a database, data is also a resource shared by many users. How to ensure the consistency and effectiveness of data access is a problem for all databases. Lock conflicts are also an important factor affecting the performance of concurrent access to the database. From this perspective, locks are particularly important and complex for databases.

MySQL Lock Overview

Compared with other databases, MySQL’s lock mechanism is relatively simple. Its most significant feature is that different storage engines support different lock mechanisms. For example, the MyISAM and MEMORY storage engines use table-level locking (table-level locking); the BDB storage engine uses page-level locking (page-level locking), but also supports table-level locking, but by default uses row-level locking. level lock.

You can analyze table lock contention on the system by checking the table_locks_waited and table_locks_immediate status variables:

show status like 'table%';

The result is if Table_locks_waited A relatively high value indicates that there is serious table-level lock contention.

Characteristics of these three locks in MySQL:

Overhead, locking speed, deadlock, granularity, concurrency performance

Table-level lock: low overhead, fast locking, No deadlock will occur, the locking granularity is large, the probability of conflict is the highest, and the concurrency is the lowest;

Row-level lock: high overhead, slow locking, deadlock will occur, the locking granularity is small, and lock conflicts occur The lowest probability and the highest concurrency;

Page lock: the cost is between the first two, deadlock will occur, the locking granularity is between the first two, and the concurrency is average;

Just from the perspective of locking: table-level locks are more suitable for applications that are mainly query-based and only update a small amount of data according to index conditions, such as web applications; while row-level locks are more suitable for applications that have a large number of concurrent updates based on index conditions and a small number of different data. Data, and applications with only concurrent queries, such as some online transaction processing (OLTP) systems.

MySQL table-level locks have two modes: table shared read lock (Table Read Lock) and table exclusive write lock (Table Write LocK).

Lock the table

1. Add shared read lock

lock table table name read

2.Unlock

unlock tables ;

3. Table exclusive write lock

lock table table name write;

Description:

The read operation of the MyISAM table will not block other The user's read request for the same table will block the request for the same table; the write operation for the MyISAM table will block other users' read and write operations for the same table; the read operation of the MyISAM table is different from the write operation. time, and write operations are serial! When a thread obtains a write lock on a table, only the thread holding the lock can update the table. Read and write operations from other threads will wait until the lock is released.

A SESSION uses the Lock table command to lock the table. This SESSION can query the records in the locked table, but an error will be prompted when updating or accessing other tables; at the same time, another SESSION can query the records in the table, but There will be a lock wait when updating.

The above is the content of MySQL Advanced Nine - MyISAM table lock (shared read lock). For more related content, please pay attention to the PHP Chinese website (www.php.cn)!