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

The following mainly brings you the principle of mysql lock implementation mechanism. I hope these words can bring you practical use. This is also the main purpose of this article that I edit the principle of mysql lock implementation mechanism. All right, don't talk too much nonsense, let's just read the following.

Lock is a mechanism by which a computer coordinates multiple processes or threads to access a resource concurrently. In the database, in addition to the contention of traditional computing resources (such as CPU, RAM, Imax O, etc.), data is also a kind of resource shared by many users. How to ensure the consistency and effectiveness of data concurrent access is a problem that must be solved in all databases, and lock conflicts are also an important factor affecting the performance of database concurrent access. From this point of view, locks are particularly important and more complex for databases. In this chapter, we focus on the characteristics of MySQL locking mechanism, common locking problems, and some methods or suggestions to solve MySQL locking problems.

Mysql uses many of these locking mechanisms, such as row locks, table locks, read locks, write locks, etc., all of which are locked before doing an operation. These locks are generally called pessimistic Pessimistic Lock.

Overview of MySQL Lock

Compared with other databases, the locking mechanism of MySQL is relatively simple, and its most prominent feature is that different storage engines support different locking mechanisms. For example, MyISAM and MEMORY storage engines use table-level locks (table-level locking); BDB storage engines use page locks (page-level locking), but also support table-level locks; and InnoDB storage engines support both row-level locks (row-level locking) and table-level locks, but row-level locks are used by default.

Table-level lock: low overhead, fast locking; no deadlock; large lock granularity, the highest probability of lock conflict and the lowest concurrency.

Row-level locks: expensive and slow to add locks; deadlocks occur; locking granularity is the smallest, the probability of lock conflicts is the lowest, and the degree of concurrency is the highest.

Page lock: the overhead and locking time are between table lock and row lock; deadlocks occur; lock granularity is between table lock and row lock, and the concurrency is average.

As can be seen from the above characteristics, it is difficult to say in general which kind of lock is better, only in terms of the characteristics of the specific application, which kind of lock is more appropriate! Only from the point of view of locks: table-level locks are more suitable for applications that focus on queries, with only a small amount of data updated according to index conditions, such as Web applications, while row-level locks are more suitable for applications with 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.

MyISAM table lock

MySQL has two modes of table-level locking: table shared read lock (Table Read Lock) and table exclusive write lock (Table Write Lock).

The read operation to the MyISAM table will not block other users' read requests to the same table, but it will block the write requests to the same table; the write operation to the MyISAM table will block other users' read and write operations to the same table; the read and write operations of the MyISAM table are serial! According to the example shown in Table 20-2, when a thread acquires a write lock on a table, only the thread that holds the lock can update the table. The read and write operations of other threads wait until the lock is released.

The write lock blocking read example of the MyISAM storage engine:

When a thread acquires a write lock on a table, only the thread that holds the lock can update the table. The read and write operations of other threads wait until the lock is released.

Read lock blocking write example of MyISAM storage engine:

A session uses the LOCK TABLE command to add a read lock to the table film_text. This session can query the records in the locked table, but updating or accessing other tables will prompt an error; at the same time, another session can query the records in the table, but the update will cause lock waiting.

How to add a table lock

MyISAM automatically locks all tables involved before executing the query statement (SELECT), and automatically adds write locks to the tables before performing update operations (UPDATE, DELETE, INSERT, etc.). This process does not require user intervention, so users generally do not need to lock the MyISAM table directly with the LOCK TABLE command. In the example, explicit locking is basically for demonstration purposes, not necessarily.

The purpose of locking the MyISAM table display is to simulate the transaction operation to a certain extent and realize the consistent reading of multiple tables at a certain point in time. For example, there is an order table orders, which records the total amount total of each order, and an order schedule order_detail, which records the subtotal subtotal of each product for each order. Suppose we need to check whether the sum of the two tables match, we may need to execute the following two SQL:

Select sum (total) from orders;Select sum (subtotal) from order_detail

At this point, if you do not lock the two tables first, you may have the wrong result, because the order_ detail table may have changed during the execution of the first statement. Therefore, the correct way should be:

Lock tables orders read local, order_detail read local;Select sum (total) from orders;Select sum (subtotal) from order_detail;Unlock tables

In particular, the following two points should be stated:

1. The above example adds the "local" option to LOCK TABLES, and its function is to allow other users to insert records concurrently at the end of the table when the condition of concurrent insertion of MyISAM table is satisfied. The problem of concurrent insertion of MyISAM table will be further introduced later.

2. When explicitly locking a table with LOCK TABLES, all locks related to the table must be acquired at the same time, and MySQL does not support lock upgrade. That is, after LOCK TABLES, you can only access these explicitly locked tables, not unlocked tables; at the same time, if you add a read lock, you can only perform query operations, not update operations. In fact, this is basically the case in the case of automatic locking, where MyISAM always acquires all the locks needed by the SQL statement at once. This is why there is no Deadlock Free in the MyISAM table.

When using LOCK TABLES, you not only need to lock all the tables used at once, but also lock the same table with the same alias as in the SQL statement how many times it appears in the SQL statement, otherwise you will also make an error! Examples are given below.

(1) obtain a read lock on the actor table:

Mysql > lock table actor read; Query OK, 0 rows affected (0.00 sec)

(2) however, accessing through an alias will prompt an error:

Mysql > select a.firstroomnamerecoverya.lastrunnamereb.firstroomnamerecoveryb.lastkeeper name from actor aactor b where a.first_name = b.first_name and a.first_name = 'Lisa' and a.last_name =' Tom' and a.last_name b.lastroomname.' error 1100 (HY000): Table 'a' was not locked with LOCK TABLES

(3) aliases need to be locked separately:

Mysql > lock table actor as a read,actor as b read;Query OK, 0 rows affected (0.00 sec)

(4) the query based on the alias can be executed correctly:

Mysql > select a.firstrunname.lastroomnamereb.firstroomnamerewritingb.lastroomname from actor an actor b where a.first_name = b.first_name and a.first_name = 'Lisa' and a.last_name =' Tom' and a.last_name b.last_name +-+ | first_name | last_name | first_name | last_name | +-+ | Lisa | Tom | LISA | MONROE | +-+ 1 row in set (0.00 sec) query table-level lock contention

Table locking contention on the system can be analyzed by examining the table_locks_waited and table_locks_immediate state variables:

Mysql > show status like 'table%';1Variable_name | Value Table_locks_immediate | 0 Table_locks_waited | 0 2 rows in set (2979 sec))

If the value of Table_locks_waited is high, there is a serious table-level lock contention condition.

Concurrent insertion (Concurrent Inserts)

As mentioned above, the reading and writing of the MyISAM table are serial, but this is overall. Under certain conditions, MyISAM tables also support the concurrency of query and insert operations.

The MyISAM storage engine has a system variable, concurrent_insert, specifically designed to control its concurrent insertion behavior, which can be 0, 1, or 2, respectively.

1. When concurrent_insert is set to 0, concurrent insertion is not allowed.

2. When concurrent_insert is set to 1, if there are no holes in the MyISAM table (that is, there are no deleted rows in the middle of the table), MyISAM allows one process to read the table while another process inserts records from the end of the table. This is also the default setting for MySQL.

3. When concurrent_insert is set to 2, records are allowed to be inserted concurrently at the end of the table, regardless of whether there are holes in the MyISAM table.

In the following example, session_1 acquires a READ LOCAL lock on a table that can query the table but cannot update the table; other threads (session_2), although they cannot delete and update the table, can insert the table concurrently, assuming that there are no holes in the middle of the table.

As mentioned above, the reading and writing of the MyISAM table are serial, but this is overall. Under certain conditions, MyISAM tables also support the concurrency of query and insert operations.

The MyISAM storage engine has a system variable, concurrent_insert, specifically designed to control its concurrent insertion behavior, which can be 0, 1, or 2, respectively.

When concurrent_insert is set to 0, concurrent insertion is not allowed. When concurrent_insert is set to 1, if there are no holes in the MyISAM table (that is, no deleted rows in the middle of the table), MyISAM allows one process to read the table while another process inserts records from the footer. This is also the default setting for MySQL. When concurrent_insert is set to 2, records are allowed to be inserted concurrently at the end of the table, regardless of whether there are holes in the MyISAM table.

In the following example, session_1 acquires a READ LOCAL lock on a table that can query the table but cannot update the table; other threads (session_2), although they cannot delete and update the table, can insert the table concurrently, assuming that there are no holes in the middle of the table.

Examples of read-write (INSERT) concurrency of the MyISAM storage engine:

You can take advantage of the concurrent insert feature of the MyISAM storage engine to resolve lock contention for queries and inserts of the same table in application. For example, setting the concurrent_insert system variable to 2 always allows concurrent insertions; at the same time, defragment the space by executing OPTIMIZE TABLE statements periodically during the idle period of the system to recover the middle hole caused by deleting the record.

Lock scheduling of MyISAM

As mentioned earlier, the read and write locks of the MyISAM storage engine are mutually exclusive, and the read and write operations are serial. So how does MySQL handle that one process requests a read lock for a MyISAM table while another process requests a write lock for the same table? The answer is that the writing process acquires the lock first. Not only that, even if the read request goes to the lock waiting queue first, and after the write request, the write lock will be inserted before the read lock request! This is because MySQL believes that write requests are generally more important than read requests. This is the reason why MyISAM tables are not suitable for applications with a large number of update operations and query operations, because a large number of update operations will make it difficult for query operations to obtain read locks, which may be blocked forever. This situation can sometimes get very bad! Fortunately, we can adjust the scheduling behavior of MyISAM through some settings.

1. By specifying the startup parameter low-priority-updates, the MyISAM engine gives priority to the read request by default.

2. Reduce the priority of update requests issued by the connection by executing the command SET LOW_PRIORITY_UPDATES=1.

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

Although the above three methods are either update priority or query priority, they can still be used to solve the serious problem of reading lock waiting in relatively important applications (such as user login system).

In addition, MySQL also provides a compromise method to adjust the read-write conflict, that is, set an appropriate value for the system parameter max_write_lock_count. When the read lock of a table reaches this value, MySQL temporarily lowers the priority of the write request, giving the read process a chance to acquire the lock.

The problems and solutions caused by the write-first scheduling mechanism have been discussed above. Here is another point to emphasize: some query operations that need to run for a long time will also make the writing process "starve to death"! Therefore, in the application, we should try to avoid long-running query operations, and do not always want to use a SELECT sentence to solve the problem, because this seemingly ingenious SQL statement is often more complex and takes a long time to execute. If possible, we can use intermediate tables and other measures to do certain "decomposition" of SQL statements, so that each step of the query can be completed in a short time, so as to reduce lock conflicts. If complex queries are inevitable, try to schedule execution during database idle periods, such as some periodic statistics that can be scheduled to be performed at night.

InnoDB locks will be explained later.

For the above about the principle of mysql lock implementation mechanism, we do not think it is very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

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.