Lock tables and unlock tables in MySQL

LOCK TABLES
    tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}
    [, tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}] ...
UNLOCK TABLES mysql lock shared Lock tables can lock the table used for the current thread. If the table is locked by other threads, blocking occurs until all the locks can be obtained. Unlock tables can release any lock maintained by the current thread. When the 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 only used to prevent other clients from reading and writing data improperly. Clients that keep locked (even read locked) can perform surface-level operations, such as drop table.

Note: The following describes how to use lock tables for the transaction table:

Mysql lock

· Before trying to lock the table, lock tables is not transaction-safe and will implicitly commit all active transactions. At the same time, starting a transaction (for example, using start transaction) will implicitly execute unlock tables

· The correct way to use lock tables for transaction tables (such as InnoDB) is to set autocommit = 0 and cannot call unlock tables until you explicitly commit the transaction. When you call lock tables, InnoDB internally locks its own table, and MySQL locks its own table. InnoDB releases its table lock when the next commit. However, for MySQL, to release the table lock, you must call unlock tables. You should not make autocommit = 1, because in that case, InnoDB will immediately release the table lock after calling lock tables, and it is easy to form an infinite lock. NOTE: If autocommit = 1, we cannot get the InnoDB table lock at all, which can help the old application software avoid unnecessary locking.

· Rollback will not release non-transaction table locks for MySQL.

mysql update lock

To use lock tables, you must have the lock tables permission and select permission for the relevant table.

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

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

The difference between read local and read is that read local allows non-conflicting insert statements (insert at the same time) to be executed when the lock is kept ). However, if you are planning to operate on database files outside MySQL while you are still locked, you cannot use read local. For InnoDB tables, read local is the same as read.

When you use lock tables, you must lock all tables that you intend to use in the query. Although the lock obtained by using the lock tables statement is still valid, you cannot access any tables not locked by this statement. At the same time, you cannot use a locked table multiple times in a query-replace it with an alias. In this case, you must obtain the lock for each alias. Mysql show locks

mysql> LOCK TABLE t WRITE, t AS t1 WRITE;
mysql> INSERT INTO t SELECT * FROM t;
ERROR 1100: Table 't' was not locked with LOCK TABLES
mysql> INSERT INTO t SELECT * FROM t AS t1;

Mysql select lock
If your query uses an alias to reference a table, you must use the same alias to lock the table. If no alias is specified, the table is not locked. Database locks mysql  mysql> LOCK TABLE t READ;
mysql> SELECT * FROM t AS myalias;
ERROR 1100: Table 'myalias' was not locked with LOCK TABLES
On the contrary, if you use an alias to lock a table, you must use this alias to reference the table in your query.

mysql> LOCK TABLE t AS myalias READ;
mysql> SELECT * FROM t;
ERROR 1100: Table 't' was not locked with LOCK TABLES
mysql> SELECT * FROM t AS myalias;


Write locking usually has a higher priority than read locking to ensure that updates are processed as soon as possible. This means that if a thread gets a read lock, the other thread will apply for a write lock, and the subsequent read lock application will wait until the write thread gets the lock and releases the lock. You can use low_priority write lock to allow other threads to get the read lock when the thread is waiting for the write lock. You should use low_priority write to lock a thread only when you are sure that there is no read lock for the thread.

Lock tables:

1. Sort all the tables to be locked in the order defined internally. From the user's point of view, this order is undefined.

2. If you use one read and one write lock to lock a table, place the write lock before the read lock.

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

This rule ensures that the table will not be locked. However, for this rule, you need to pay attention to 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 no thread applied for read lock is applied. When the thread has obtained the write lock and is waiting for the lock for the next table in the locked table list, all other threads will wait for the write lock to be released. If this is a serious problem for applications, you should consider converting some tables into transaction-safe tables.

You can safely use kill to end a thread waiting for table locking.

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

Generally, you do not need to lock the table because all single update statements are atomic. No other thread can interfere with any other SQL statements currently being executed. However, locking a table has the following benefits:

· If you are running many operations on a group of MyISAM tables, locking the tables you are using can be much faster. Locking a MyISAM table can speed up insertion, update, or deletion. The disadvantage is that no thread can update a table locked with read (including tables locked ), no thread can access the table locked by the Write statement (except for the table locked ).

Some MyISAM operations are faster under lock tables because MySQL does not clear the key cache used for locked tables until the unlock table is called. Generally, the key cache is cleared after each SQL statement.

· If you are using a storage engine in MySQL that does not support transactions, you must use lock tables if you want to determine that there are no other threads between select and update. The example shown in this section 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 no lock tables exists, another thread may Insert a new row in the Trans table between the select and update statements.

By using the relative Update (update customer set value = value + new_value) or last_insert_id () function, you can avoid using lock tables in many cases.

You can also avoid locking tables in some cases by using the user-level consultative lock functions get_lock () and release_lock. These locks are saved in a mixed editing table on the server and use pthread_mutex_lock () and pthread_mutex_unlock () to speed up the process.

Learn more about locking rules

You can use the flush tables with read lock statement to lock all tables in all databases with read locks. If you have a file system that can take snapshots in time, such as Veritas, this is a very convenient way to obtain backups.

Note: If you use alter table for a locked table, the table may be unlocked.