How to lock a MySQL database table

If you run the check/fix for the table at the same time, you may not want the MySQL server and utility to access a table at the same time. If all two programs write data to a table, it's obviously a big hassle, and even unexpected things happen. If a table is being written by one program, another program that reads at the same time can produce confusing results. This article focuses on how to lock a MySQL database table.

Methods for locking tables

There are many ways to prevent client requests from interfering with each other or between server and maintenance programs. If you close the database, you can ensure that there is no interaction between the server and the Myisamchk and Isamchk. However, stopping the server from running is not a good thing to note because doing so will make the database and tables that are not in trouble unusable. The main discussion in this section is to avoid interactions between servers and Myisamchk or ISAMCHK. The way to accomplish this is to lock the table.

The server is locked by two types of tables:

1. Internal locking

Internal locking prevents client requests from interfering with each other--for example, by avoiding the client's select query being disturbed by another client's update query. The internal locking mechanism can also be used to prevent the server from accessing the table when using Myisamchk or isamchk to check or repair the table.

Grammar:

Lock table: Lock TABLES tbl_name {READ | write},[Tbl_name {READ | WRITE},...]

Ching: UNLOCK TABLES

Lock tables locks the table for the current thread. UNLOCK Tables releases any locks held by the current thread. All tables locked by the current thread are automatically unlocked when the thread issues another lock tables, or when the server's connection is closed.

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

Each thread waits (no timeouts) until it obtains all the locks it requests.

Write locks usually have a higher priority than the read lock to ensure that changes are processed as soon as possible. This means that if one thread obtains a read lock, and then another thread requests a write lock, then the read lock request will wait until the write thread gets the lock and frees it.

Obviously for the check, you just need to get a read lock. In addition, it can only read the table, but cannot modify it, so he also allows other clients to read the table. For the fix, you have to get some to prevent any client from modifying it while you are working on the table.

2. External locking

The server can also use an external lock (file-level lock) to prevent other programs from modifying the file while the server is using the table. Typically, the server uses an external lock with MYISAMCHK or isamchk in the check operation of the table. However, external locking is disabled in some systems because he cannot work reliably. The procedure you choose to run Myisamchk or isamchk depends on whether the server can use an external lock. If not used, the internal locking protocol is required.

If the server is running with the--skip-locking option, the external lock is disabled. This option is the default in some systems, such as Linux. You can determine whether the server can use an external lock by running the MySQL admin variables command. Check the value of the skip_locking variable and proceed as follows:

If Skip_locking is off, the external lock is valid and you can continue and run the person and a utility to check the table. The server and utilities will work together to access the table. However, you should use Mysqladmin Flush-tables before running any of the utilities. To repair the table, you should use the repair locking protocol for the table.

If skip_locaking is on, external locking is disabled, so checking the repair in Myisamchk or ISAMCHK indicates that the server does not know, preferably shutting down the server. If the persistence is the server remains on, the month ensures that you use this to indicate that no client is accessing it. The card party's lock protocol must be used to tell the server that the table is not being accessed by other clients.

Check the locking protocol of the table

This section only covers internal locking if you use the table. For the locking protocol of the checklist, this procedure is only for table checking, not for table repair.

1. Call MySQL to publish the following statements:

$mysql –u root–p db_namemysql>lock TABLE tbl_name read;mysql>flush TABLES;

This lock prevents other clients from writing to the table and modifying the table while it is being inspected. The flush statement causes the server to close the file for the table, and it refreshes any changes that are still being told in the cache for writes.

2. Perform the inspection process

$myisamchk tbl_name$ Isamchk Tbl_name

3. Release the table lock

Mysql>unlock TABLES;

If Myisamchk or isamchk points to a problem with the table, you will need to perform a repair of the table.

In this article for you to summarize the above described above, the above mentioned is what I want to explain to you about how to lock the MySQL database table of all the explanations, I hope that the above mentioned in the content can be helpful to everyone.

