How to lock MySQL database tables

If you run the table's check/fix program at the same time, you may not want the MySQL server and utility to access a table at the same time. If two of programs write data to a table, it's obviously going to be a lot of trouble, and even unexpected things happen. If the table is being written by one program, another program that reads simultaneously can have confusing results. This article mainly describes how to lock the MySQL database table.

Ways to lock tables

There are many ways to prevent client requests from interfering with each other or the server and maintenance programs interfering with each other. If you close the database, you can ensure that there is no interaction between the server and the Myisamchk and Isamchk. But stopping the operation of the server is not a good thing, because doing so will make the database and tables that have no faults available. This section focuses on the process of avoiding interaction between servers and Myisamchk or ISAMCHK. The way to implement this functionality is to lock the table.

The server is locked by two kinds 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 interfered with by another client's update query. You can also use the internal locking mechanism to prevent a server from accessing tables when they are using Myisamchk or isamchk to check or repair tables.

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. When a thread issues another lock table, or when the server's connection is closed, all tables that are locked by the current thread are automatically unlocked.

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 holding the lock is read or write, and other threads are blocked.

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

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

Obviously for checking, you just need to get read locks. In addition, the preference is to read the table, but not modify it, so he also allows other clients to read the table. For repairs, you have to get some so you can prevent any client from modifying the table while you are working on it.

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 external locking with MYISAMCHK or isamchk in the table's check operation. However, external locking is disabled in some systems because he cannot reliably work. The process you select for running Myisamchk or isamchk depends on whether the server can use an external lock. If not used, the internal locking protocol is mandatory.

If the server is running with the--skip-locking option, external locking is disabled. This option is default on some systems, such as Linux. You can determine whether the server can use an external lock by running the mysqladmin variables command. Check the value of the skip_locking variable and follow the following methods:

If skip_locking is off, external locking is valid you can continue and run people and a utility to check the table. The server and utility will collaborate to access the table. However, you should use Mysqladmin Flush-tables before running any of the utilities. To fix the table, you should use the Repair lockout protocol for the table.

If Skip_locaking is on, the external lock is disabled, so the myisamchk or isamchk check fix indicates that the server does not know and it is best to shut down the server. If you insist that the server remains open, the month ensures that no client is available to access it when you use this representation. You must use the locking protocol of the card party to tell the server that the table is not accessed by other clients.

Check the locking protocol for a table

This section only describes if you use the internal locking of a table. For the locking protocol of the checklist, this procedure is only for table checks and not for table repairs.

1. Invoke MySQL to publish the following statement:

$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 examined. The flush statement causes the server to close the table's file, and it refreshes any changes that are still being told to the cache for write.

2. Perform the inspection process

$myisamchk tbl_name$ Isamchk Tbl_name

3. Release table lock

Mysql>unlock TABLES;

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