MySQL Lock table detailed

In order to better optimize MySQL in high concurrency, it is necessary to understand the locking table mechanism when MySQL query is updated.
I. Overview
MySQL has three levels of Lock: page level, table level, row level.
MyISAM and MemoryStorageThe engine uses a table-level lock (Table-level locking), and the BDB storage engine uses a page lock (page-level
Locking), but also supports table-level locks; The InnoDB storage engine supports both row-level locks (row-level locking) and table-level locks, but row-level locks are used by default.
MySQL features of these 3 types of locks can be broadly summarized as follows:
Table-level Lock: Low overhead, lock fast, no deadlock, lock granularity, lock conflict is the highest probability, concurrency is the lowest.
Row-level locks: high overhead, slow locking, deadlock, minimum lock granularity, the lowest probability of lock collisions, and the highest degree of concurrency.
Page locks: Overhead and lockingTimeBounded between table and row locks, deadlock occurs, locking granularity bounds between table and row locks, and concurrency is common.
Second, MyISAM table lock
The MyISAM storage engine supports only table locks and is now the most used storage engine.
1, query table level lock contention situation
Can be checked by table_locks_waited and Table_locks_imMediaTE state variables to analyzesystemTable lock contention on:
Mysql> Show status like ' table% ';
+ ——————— –+ ———-+
| variable_name | Value |
+ ——————— –+ ———-+
| Table_locks_immediate | 76939364 |
| table_locks_waited | 305089 |
+ ——————— –+ ———-+
2 rows in Set (0.00 sec) table_locks_waited has a higher value, indicating a more serious table-level lock contention situation.

2, the MySQL table-level lock Lock mode
There are two modes of table-level lock for MySQL: Table shared read lock (tables read lock) and table exclusive write lock (table write
Lock). MyISAM will automatically add read locks to all tables involved before executing the query statement (SELECT), and will automatically write locks to the table involved before performing the update operation (update, DELETE, INSERT, etc.).
As a result, the MyISAM table will be operated in the following situations:
A, read operations on the MyISAM table (read-lock), does not block other processes from reading requests to the same table, but blocks write requests to the same table. Write operations for other processes are performed only when the read lock is released.
b, write to the MyISAM table (write lock), will block the other process to the same table read and write operations, only when the write lock is released, the other process will perform read and write operations.
Here is an example to verify the above view. Data table Gz_phone has more than 2 million data, field id,phone,ua,day. The table is now simultaneously analyzed with multiple clients at the same time.
A, when I use client 1 for a relatively long time read operations, respectively, with client 2 for read and write operations:
CLIENT1:
Mysql>select Count (*) from Gz_phone GROUP by UA;
75508 rows in Set (3 min 15.87 sec) Client2:
Select Id,phone from Gz_phone limit 1000, 10;
+--+ ——-+
| ID | Phone |
+--+ ——-+
| 1001 | 2222 |
| 1002 | 2222 |
| 1003 | 2222 |
| 1004 | 2222 |
| 1005 | 2222 |
| 1006 | 2222 |
| 1007 | 2222 |
| 1008 | 2222 |
| 1009 | 2222 |
| 1010 | 2222 |
+--+ ——-+
Rows in Set (0.01 sec)
mysql> Update gz_phone set phone= ' 11111111111′where id=1001;
Query OK, 0 rows affected (2 min 57.88 sec)
Rows matched:1 changed:0 warnings:0
Description When a data table has a read lock, the query actions of other processes can be executed immediately, but the update operation waits for the read lock to be released before it executes.
b, when using Client 1 for a longer time update operation, with the client 2,3 read and write operations:
CLIENT1:
mysql> Update gz_phone set phone= ' 11111111111′;
Query OK, 1671823 rows affected (3 min 4.03 sec)
Rows matched:2212070 changed:1671823 warnings:0 client2:
Mysql> Select Id,phone,ua,day from Gz_phone limit 10;
+--+ ——-+ ——————-+ ———— +
| ID | Phone | UA | Day |
+--+ ——-+ ——————-+ ———— +
| 1 | 2222 | sonyericssonk310c | 2007-12-19 |
| 2 | 2222 | sonyericssonk750c | 2007-12-19 |
| 3 | 2222 | MAUI WAP Browser | 2007-12-19 |
| 4 | 2222 | Nokia3108 | 2007-12-19 |
| 5 | 2222 | lenovo-i750 | 2007-12-19 |
| 6 | 2222 | bird_d636 | 2007-12-19 |
| 7 | 2222 | sonyericssons500c | 2007-12-19 |
| 8 | 2222 | samsung-sgh-e258 | 2007-12-19 |
| 9 | 2222 | nokian73-1 | 2007-12-19 |
| 10 | 2222 | Nokia2610 | 2007-12-19 |
+--+ ——-+ ——————-+ ———— +
Rows in Set (2 min 58.56 sec) Client3:
mysql> Update gz_phone set phone= ' 55555′where id=1;
Query OK, 1 row affected (3 min 50.16 sec)
Rows matched:1 changed:1 warnings:0
Indicates that when a data table has a write lock, the read and write operations of other processes are required to wait for the reading lock to be released before execution.
3. Concurrent Insertion
In principle, when a data table has a read lock, other processes cannot update the table, but under certain conditions, the MyISAM table also supports concurrency of query and insert operations.
The MyISAM storage engine has a system variable Concurrent_insert that is specifically designed to control the behavior of its concurrent insertions, with values of 0, 1, or 2, respectively.
A, concurrent insertions are not allowed when Concurrent_insert is set to 0 o'clock.
B, when Concurrent_insert is set to 1 o'clock, if there are no holes in the MyISAM table (that is, rows in the middle of the table are not deleted), 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.
C, when Concurrent_insert is set to 2 o'clock, the record is allowed to be inserted concurrently at the end of the table, regardless of whether there is an empty hole in the MyISAM table.
4, MyISAM lock scheduling
Because MySQL considers writing requests to be more important than read requests, MySQL will take precedence in writing if there are read and write requests at the same time. This way, when a large number of update operations are performed (especially in the case of an index in the updated field), the MyISAM table makes the query operation difficult to obtain a read lock, which causes the query to block.
There are some settings that we can use to adjust the scheduling behavior of MyISAM:
A, by specifying the startup parameter low-priority-updates, so that the MyISAM engine defaults to the read request to give priority to the right.
b, by executing the command set Low_priority_updates=1, the update request issued by this connection has a lower priority.
C, reduce the priority of the statement by specifying the Low_priority property of the Insert, UPDATE, DELETE statement.
The above 3 methods are either update priority or query first method. Here to illustrate is, do not blindly to the MySQL set to read first, because some long-running query operations, will also make the write process "starved". Only according to your actual situation, to decide which action to set priority. These methods still do not solve the problem of query and update fundamentally at the same time.
In a MySQL with large data volumes and published, we can also use another strategy to optimize the load balancing by MySQL master-slave (read-write) separation, which avoids the priority of which operations can lead to a blockage of another operation. Here is a space to illustrate MySQL's read-write separationTechnology。

1.MySQL lock table requests are available in two ways: Read lock and write lock syntax lock tables T read/write the common denominator is that when the lock table is executed, no other process other than the current process can access the table unless one of the following three scenarios occurs: 1. The process executes the unlock statement unlock Tables 2. The process performs additional lock table requests 3. The process exits or disconnects from the MySQL database, and the difference is that the lock table process that performs the read lock can only query the table for the data that cannot be modified. A process that executes a write lock can have a pruning check all permissions can be understood as the latter contains the former is actually the latter priority is higher than the former usually I do write lock, the following examples are also in write for example
2.After the process executes lock tables T write lock table, if need to access to table T1, MySQL will error error 1100:table ' T1 ' is not locked with lock tables solution: Process one time to multiple table lock, Syntax: l Ock Tables T write,t1 write,... Unlock method See 1,unlock tables only once
MySQL Lock and deadlock
The MyISAM and memory storage engines use a table-level lock Table-level locking
The BDB storage engine uses a page lock Page-level locking, but also supports table-level locks
The InnoDB storage engine supports both row-level locks row-level locking and table-level locks, but row-level locks are used by default
Table-level lock cost is small, lock fast, no deadlock, lock granularity is high, the probability of lock conflict is highest, and the concurrency is lowest.
Row-level locks are expensive, locking slow, deadlock is minimized, lock collisions are minimal, and concurrency is highest
Page lock overhead and lock time are bounded between table and row locks, deadlock occurs, lock granularity bounds between table and row locks, and concurrency is common
only from the angle of the lock:
Table-level locks are more appropriate for applications that are primarily query-based and have only a small number of updates by index criteria, such as web apps
Row-level locks are more suitable for applications that have a large number of simultaneous updates of different data by index and concurrent queries, such as some online transaction processing systems

Dead lock
The so-called deadlock <deadlock>: Refers to two or more than two processes in the course of execution,
As a result of competing for resources, a kind of waiting phenomenon, if there is no external force, they will not be able to proceed.
At this point the system is in a deadlock state or the system generates a deadlock, and these processes, which are always aulympic each other, are known as deadlock processes.
Table-level locks do not generate deadlocks. So the solution to the deadlock is mostly true for the most commonly used InnoDB.
When you encounter a problem
Execute show Processlist first to find the deadlock thread number. Then kill Processno.
Of course, the main solution is to take a look at the specific operation. Deadlocks may occur
Show InnoDB status check engine status to see which statements generate deadlocks
And then it's settled.
How to solve or to see the specific question.
MyISAM uses the function of the Flock class, which is to lock the whole file directly (called File lock), InnoDB uses the function of the Fcntl class, can lock the local data in the file (called row lock), so the difference is here.
In addition, the MYISAM data table is stored according to a single file, can be locked for a single table file, but InnoDB is an entire file, the index, data, structure are all stored in the Ibdata file, so you must use row locking.

1, for MySQL, there are three kinds of lock level: page level, table level, row level.
The typical representative engine for the page level is BDB.
The typical representative engine for the table level is myisam,memory and the ISAM of a long time ago.
The typical representative engine for the row level is InnoDB.
2, we use in the actual application of the most is the row lock.

The advantages of row-level locks are as follows:
1) Reduce the lock state when many connections make separate queries.
2), if an exception occurs, you can reduce the loss of data. Because you can roll back only one row or a few rows of data at a time.
The disadvantages of row-level locks are as follows:
1), which consumes more memory than page-level locks and table-level locks.
2), when querying than page-level locks and table-level locks require more I/O, so we often use the row-level lock in the write operation instead of the read operation.
3), easy to appear deadlock.
3. MySQL writes and reads the database with write queue and read queue.

For a write lock as follows:
1), if the table is not locked, then write to lock it.
2), otherwise, put the request into the write lock queue.
For read locks, the following:
1), if the table does not have a write lock, then add a read lock.
2), otherwise, put the request in the read lock queue.
Of course, we can use low_priority and high_priority to change these behaviors in writing and reading operations respectively.

4, let me take a simple example to explain the above statement.

Let's run a long query
1), Client 1:
Mysql> Select COUNT (*) from the content group by content;
...
Client 2:
Mysql> Update content Set content = ' I love you ' where id = 444;
Query OK, 1 row affected (30.68 sec)
Rows matched:1Changed:1warnings:0
It took half a minute.
2), we now terminate client 1.
At this point, client 2:
Mysql> Update content Set content = ' I hate you ' where id = 444;
Query OK, 1 row affected (0.02 sec)
Rows matched:1Changed:1warnings:0
It only took 20 milliseconds.

This example is a good illustration of the operation of the read-write queue.
For client 1 in 1, when the table is not locked and of course there is no write lock, then client 1 adds a read lock to the table.
For client 2 in 1, this time the update request is placed in the write lock queue because the table has a read lock.
When the read lock is released, that is, show processlist in the status of Copy to TMP table, the update operation begins execution.

5, the master and slave can be used in the replication different locking system to achieve the best performance. (The premise, of course, is that SQL statements are optimal.) )

Through the locking mechanism, multiple threads can be implemented to manipulate a table at the same time. As shown, at some point, User A, User B, user C may be at the same time or successively (the previous job has not been completed) to the data table a query or update operation. When a thread involves an update operation, it is required to gain exclusive access. During the update process, all other threads that want to access the table must wait until their updates are complete. This can lead to the issue of lock contention. This results in a prolonged wait time for the user. In this article, I will discuss with you what measures can be taken to effectively avoid lock competition and reduce the waiting time for MySQL users.

Reduce lock contention reduce MySQL user wait time

Background simulation:

In order to explain this problem more clearly, the author first simulates a daily case. Through the case of people to read the following content, may be more clearly organized. Now the MySQL database encounters the situation as shown.

First, User A makes a query request to data table A.

Then, User B sends an update request to data table A. At this time, User B's request only after the user A's job completed before it can be executed.

Finally, User C sends a query request to data table A. In the MySQL database, the priority of the UPDATE statement is higher than the query statement, for which the user's query statement can only be executed after the User B's update job is completed. and User B's update job must be completed after the user a query statement to be able to execute. At this point, there is a more serious problem of lock competition.

What do the database engineers do now in the process of database design and optimization, and what measures are taken to reduce the disadvantage of this lock competition?

Step one: Use lock tables to improve update speed

For update jobs, many updates in one lock are faster than all locked updates. For this reason, if a table is updated more frequently, such as a supermarket cashier system, you can increase the update speed by using the Lock tables option. The speed of the update increases, then the conflict with the Select query job will be significantly reduced, the phenomenon of lock competition can also be significantly suppressed.

Step two: Divide a table into several tables to reduce lock contention

such as a large shopping supermarket, such as Wal-Mart, its sales Record table daily update operation is very much. At this time, if the user in the update at the same time, another user needs to query it, it is obvious that the phenomenon of lock competition will be more serious. In response to this situation, you can actually divide a table into several tables artificially. If you can set up a single data table for each register. In this case, the user's operations between the registers are done in their own tables, without interfering with one another. In data statistical analysis, they can be consolidated into a single table through a view.

Step three: Adjust the priority of a job

By default, in the MySQL database, the update operation has a higher priority than the select query. As shown, if User B first issues a query request, then User C then issues an update request. When the user a query job is completed, the system will first execute whose request? Note that by default, the system does not follow the first served rule, that is, the User B's query request is not executed first, but the user's update process is performed. This is primarily because the update process has a higher priority than the query process.

However, in some specific cases, this priority may not be in line with the needs of the enterprise. At this point, the database administrator needs to adjust the statement priority according to the actual situation. There are three ways to do this if you really need it.

One is through the Low_prioity property. This property can reduce the priority of a particular statement. If you can reduce the priority of a particular UPDATE statement or INSERT statement. However, it is important to note that this property is useful only for specific statements. That is, its scope is only for a specific statement, without affecting the global.

The second is through the High_prioity property. corresponding to the Low_prioity property, there is a high_prioity property. As the name implies, this property can be used to increase the priority of a particular SELECT query statement. As the above case, in the User C query statement to add the high_prioity attribute, then the user a query completed, will immediately execute the user's query statement c. User B's Update operation will not be performed until user C has finished executing. Visible, the priority of the query statement is improved at this time. It is important to note that this scope is limited to specific query statements, just like the above attribute. There is no effect on other query statements that do not add this parameter. That is, if the other query statement does not add this attribute, then its priority level is still lower than the update process.

The third is through the Set Low_priorit_updates=1 option. The above two properties are for a specific statement without causing a global impact. If the database administrator needs to adjust the priority level for a connection now, how can it be implemented? As in the above example, users now need to increase the priority level of the query statement that the user is connecting to, rather than using the properties above each time the query is used. You will need to use the Set Low_priorit_updates=1 option at this point. With this option, all update processes in a specific connection can be made with a lower priority. Note that this option is useful only for specific connections. For other connections, it is not applicable.

Four is the use of low_priority_updates option. The properties mentioned above, the preceding two for a specific statement, and the subsequent one for a specific connection, will not affect the entire database. If you need to reduce the priority of the UPDATE statement in the entire database scope now, can it be implemented? As in the above case, let the user C's query statement be executed more than the User B's update without using other parameters. If the user has this requirement, you can use Low_priority_ Updates option to start the database. When you start the database with this option, the system gives a lower priority to all UPDATE statements in the database. At this point the user's query statement will be more than the user User B's update request to execute earlier. For query jobs, there is no lock-in situation. For this user a query request and User C query request can be carried out simultaneously. By adjusting the priority of the statement execution, the situation of lock competition can be reduced effectively.

Visible, you can use attributes or options to adjust the precedence of a statement. If there is an application, it is mainly for users to query. Updates are typically performed by an administrator and are not sensitive to the data that is updated by the user. At this point, based on the principle of user precedence, you can consider increasing the priority of the query. In this case, for the user, it is less likely to encounter lock competition, which can shorten the user's waiting time. When adjusting user priorities, you need to consider the scope of their adjustments. Whether to adjust specific statements, adjust specific connections, or take effect on the entire database.

Step four: For mixed operation scenarios, you can use specific options

Sometimes you run into mixed operations, such as when there are updates and inserts and query operations, depending on the specific situation, a specific option is used. If you need to insert and delete the data table at the same time now, if you can use the Insert delayed option, it will be of great help to the user. There is a case of lock contention if the SELECT and DELETE statements are executed against the same data table. At this point, the database administrator can also choose to use the Delete limint option to resolve the speed problem, depending on the situation.

In general, lock contention is different from deadlock and does not have a significant impact on the operation of the database. It just might prolong the user's wait time. If the user's concurrent access is not very high, then the phenomenon of lock competition will be very few. The use of the above measures will not yield much. Conversely, if a user has more concurrent access to a table, especially if different users perform mixed jobs such as querying, updating, deleting, inserting, and so on, then taking these measures can greatly reduce the lock conflict and reduce the user's waiting time.

Feel our Technical Department's summary meeting, every time can learn a lot of new knowledge, hope later have to share the link of technical experience. In fact, sharing is often a few words, sometimes heard, may not know the principle, the process of realization, and even can only understand a small part. But it doesn't matter, it's important to know that there are so many detours that will take less time to use. As for the "double-table concurrency" proposed by the last summary, the conjecture principle is as follows:

In the Web interaction program that distributes the registration password, the design of the common, regardless of concurrency, is as follows:

Design a table: A field is an ID, a field is a password that needs to be distributed, and the other is a flag bit that flags whether the password has been distributed, initially 0;

Program design: From the table to find a flag bit 0 password, set the flag bit 1, and then sent to the user.

But the problem with this method is that when the user's access is high concurrency, multiple users will get the same password,

The reason is (conjecture is for reference only):

MySQL database operation mode is similar to the operating system read and write lock, that is, allow the simultaneous operation of multiple reading locks, at this time is not allowed to write, when the read lock release allowed to write, and should write the lock function, read the lock is blocked. Therefore, when the user is high concurrency, multiple read locks can be read together, the first read lock is released, it will be the flag position of 1, but because there are other read locks in the read, so the first operation of the write lock blocked here, can not be read to this line of the Flag field, set to 1 in time. The additional read-lock read flag bit is still 0, and when all concurrent read locks are released, the write lock for all operations begins to function, and multiple concurrent write operations block execution, which in turn is 1. This allows multiple concurrent operations to read a single piece of data.

The solution to this problem is to still use the MySQL read-write lock mechanism, for this mechanism, the write lock must be mutually exclusive, although allow simultaneous multiple read operations, but always allow only one write operation. Just now the problem is that multiple read data operation is caused by concurrent execution, to avoid this, need to read the time also lock, do not allow concurrent reading. I don't know if MySQL has this set of settings directly to implement, but can be solved by the following trickery way.

Because the lock is mutually exclusive at the time of writing, so create a table, just save a field, is a self-increment ID, when there is an operation need to request a password, first insert an empty data in this table, so that return a MySQL allocation of the self-increment ID, Use this ID to go to the first table to take the corresponding ID of the password is possible.

Does not appear multiple users to get the same password is explained that at this time, multiple concurrent operations can certainly get a different ID, because when the insertion of the write lock is mutually exclusive, concurrent multiple operations to write the database, it will block the queue, the first operation after writing, released the lock, to get the MySQL assigned ID, Subsequent operations require an insert operation, and MySQL will insert this sequence of operations into different rows of the database, returning different IDs, while the operation is concurrent and reachable, but for MySQL it is a single execution insert statement, so of course the operation is different rows, return different IDs , so that in the first table to find a different password, the user is assigned to a different password.

When the assigned ID is greater than the total number of IDs in the password table, the password is all sent out.



This principle can be used for other programs that need to prevent the concurrent reading of dirty data, and the MySQL read-write lock can help us to do other mutual exclusion programs. That day heard Wu Hao said MEMCA c He also has a similar read and write lock, do not know if using that can improve concurrency efficiency, than the operation of MySQL faster.

LOCK TABLES

 Tbl_name [as alias] {READ [LOCAL] | [Low_priority] WRITE}

 [, Tbl_name [as alias] {READ [LOCAL] | [Low_priority] WRITE}] ...

UNLOCK TABLES

Lock tables can lock a table for the current thread. If the table is locked by another thread, it will clog until all locks can be obtained. UNLOCK tables can release any locks held by the current thread. When a thread publishes another lock tables, or when the connection to the server is closed, all tables locked by the current thread are implicitly unlocked.

Table locking is used only to prevent other clients from improperly reading and writing. A client that maintains a lock (even a read lock) can perform surface-level operations, such as drop TABLE.

Note that the following is a description of using the lock tables for the transaction table:

· Before attempting to lock a table, lock tables is not transaction-safe and implicitly commits all active transactions. Also, starting a transaction (for example, using Start TRANSACTION) implicitly executes the unlock TABLES

·The correct way to use lock TABLES for a transaction table (such as InnoDB) is to set autocommit=0 and not call unlock TABLES until you explicitly commit the transaction. When you call lock tables, InnoDB internally takes its own table lock, and MySQL takes its own table lock. InnoDB releases its table lock on the next commit, but for MySQL, to release the table lock, you must call unlock TABLES. You should not let autocommit=1, because then, InnoDB will release the table lock immediately after calling lock tables, and it is easy to form a deadlock. Note that if autocommit=1, we simply cannot get the InnoDB table lock, which can help the old application to avoid unnecessary deadlock.

· Rollback does not release the non-transactional table lock for MySQL.

To use lock TABLES, you must have the lock TABLES permission and select permission for the related table.

The main reason for using lock tables is to emulate a transaction, or to speed up when updating a table. This will be explained in more detail later.

If a thread obtains a read lock on a table, the thread (and all other threads) can only read from that table. If a thread obtains a write lock on a table, only the thread that holds the lock can write to the table. Other threads are blocked until the lock is released.

The difference between read local and read is that read local allows the execution of a non-conflicting INSERT statement (simultaneous insertion) when the lock is persisted. However, if you are planning to manipulate database files outside of MySQL while you remain locked, you cannot use read LOCAL. For the InnoDB table, read local is the same as read.

When you use lock tables, you must lock all the tables that you intend to use in the query. Although the lock obtained using the lock tables statement is still valid, you cannot access any tables that are not locked by this statement. Also, you cannot use a locked table multiple times in a single query-using aliases instead, in which case you must obtain a lock on each alias separately.

mysql> LOCK TABLE t write, t as T1 write;

Mysql> INSERT into t SELECT * from T;

ERROR 1100:table ' t ' is not locked with LOCK TABLES

Mysql> INSERT into t SELECT * from t as T1;

If your query uses an alias to refer to a table, you must lock the table with the same alias. If no alias is specified, the table is not locked.

mysql> LOCK TABLE T READ;

Mysql> SELECT * from t as Myalias;

ERROR 1100:table ' Myalias ' is not locked with LOCK TABLES

Conversely, if you use an alias to lock a table, you must use that alias to reference the table in your query.

Mysql> LOCK TABLE t as Myalias READ;

Mysql> SELECT * from T;

ERROR 1100:table ' t ' is not locked with LOCK TABLES

Mysql> SELECT * from t as Myalias;

The write lock usually has a higher priority than the read lock to ensure that updates are processed as quickly as possible. This means that if a thread obtains a read lock, another thread will request a write lock, and subsequent read lock requests will wait until the write thread obtains the lock and releases the lock. You can use the Low_priority write lock to allow other threads to obtain a read lock while the thread is waiting for the write lock. You should use the Low_priority write lock only if you are sure that there will eventually be a time when no thread has a read lock.

The LOCK tables is performed in the following manner:

1.All tables to be locked are categorized in the order in which they are internally defined. From the user's perspective, this order is undefined.

2.If a table is locked with one read and one write lock, the write lock is placed before the read lock.

3.Lock one table at a time until the thread gets all the locks.

This rule ensures that the table lock does not deadlock. However, for this rule, you need to be aware of other things:

If you are using a low_priority write lock on a table, this only means that MySQL waits for a specific lock until the thread of the read lock is not requested. When a thread has obtained a write lock and is waiting for a lock on the next table in the Locked table list, all other threads wait for the write lock to be released. If this becomes a serious problem for your application, you should consider converting some of the tables into transaction-safe tables.

You can safely use Kill to end a thread that is waiting for a table lock.

Note that you cannot use the insert delayed to lock any table that you are using because, in this case, the insert is executed by another thread.

Typically, you do not need to lock the table because all of the individual update statements are atomic, and no other thread can interfere with any other currently executing SQL statement. However, in several cases, locking a table can be beneficial:

·If you are running many operations on a set of MyISAM tables, it can be much faster to lock the table you are using. Locking the MyISAM table can speed up insertion, update, or deletion. The downside is that no thread can update a table locked with read (including a locked table), and no thread can access the table locked with write (except for the table that holds the lock).

Some MyISAM operations are faster under lock tables because MySQL does not empty the critical cache for locked tables until unlock table is called. Typically, the critical cache is emptied after each SQL statement.

·If you are using a storage engine that does not support transactions in MySQL, you must use lock TABLES if you want to determine that there are no other threads between select and update. The example shown here requires lock TABLES for safe execution:

· mysql> LOCK TABLES Trans READ, customer WRITE;

·Mysql> SELECT SUM (value) from trans WHERE customer_id=some_id;

· Mysql> UPDATE Customer

·- SET total_value=sum_from_previous_statement

·-WHERE customer_id=some_id;

· Mysql> UNLOCK TABLES;

If there is no lock TABLES, it is possible that another thread will insert a new row in the trans table between the execution of the SELECT and UPDATE statements.

You can avoid using the lock TABLES in many cases by using the relative update (update customer SET value=value+new_value) or the last_insert_id () function.

You can also avoid locking tables in some cases by using the user-level advisor locking function Get_lock () and Release_lock (). These locks are saved in a mixed table in the server, using Pthread_mutex_lock () and Pthread_mutex_unlock () to speed up.

To learn more about locking rules

You can use the flush TABLES with read lock statement to lock all tables that are located in all databases with read locks. If you have a file system that can take snapshots in a timely manner, such as Veritas, this is a very handy way to get backup.

Note: If you use ALTER TABLE on a locked table, the table may be unlocked.

MySQL Lock table detailed