How to lock a MySQL database table

If you run table check/repair programs at the same time, you probably don't want the MySQL server and the utility to access a table at the same time. If both programs write data to the table, it will obviously cause a lot of trouble, and even unexpected situations may occur. If a table is being written to by one program, another program reading at the same time can produce confusing results. This article mainly describes how to lock MySQL database tables.

Methods for locking tables

There are many ways to prevent client requests from interfering with each other or the server and maintenance programs from interfering with each other. If you close the database, you can ensure that there is no interaction between the server and myisamchk and isamchk. But stopping the server is not a good idea, because doing so will make the database and tables that are not faulty unavailable. The main process discussed in this section is to avoid interactions between the server and myisamchk or isamchk. The way to achieve this functionality is to lock the table.

The server has two table locking methods:

1. Internal locking

Internal locking can prevent client requests from interfering with each other - for example, to prevent the client's SELECT query from being interfered by another client's UPDATE query. You can also use an internal locking mechanism to prevent the server from accessing the table while using myisamchk or isamchk to check or repair the table.

Syntax:

Lock tables: LOCK TABLES tbl_name {READ | WRITE},[ tbl_name {READ | WRITE},…]

Unlock tables: UNLOCK TABLES

LOCK TABLES locks tables for the current thread. UNLOCK TABLES releases any locks held by the current thread. When the thread issues another LOCK TABLES, or when the server connection is closed, all tables locked by the current thread are automatically unlocked.

If a thread acquires a READ lock on a table, that thread (and all other threads) can only read from the table. If a thread acquires a WRITE lock on a table, only the thread holding the lock reads or writes the table, and other threads are blocked.

Each thread waits (without timeout) until it acquires all locks it requested.

WRITE locks usually have a higher priority than READ locks to ensure that changes are processed as quickly as possible. This means that if one thread acquires a READ lock, and then another thread requests a WRITE lock, subsequent READ lock requests will wait until the WRITE thread acquires the lock and releases it.

Obviously for the check you only need to acquire the read lock. Furthermore, Zhongqingqiaoxia can only read the table, but cannot modify it, so it also allows other clients to read the table. For repair, you must obtain something that prevents any client from modifying the table while you are operating on it.

2. External locking

The server can also use external locking (file level lock) to prevent other programs from modifying files while the server is using the table. Typically, the server uses external locking in conjunction with myisamchk or isamchk during table check operations. However, external locking is disabled on some systems because it does not work reliably. The procedure chosen to run myisamchk or isamchk depends on whether the server can use external locking. If not used, the internal locking protocol must be used.

If the server is run with the --skip-locking option, external locking is disabled. This option is the default on some systems, such as Linux. You can determine whether the server is capable of using external locks 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 in effect. You can go ahead and run a human and a utility to check the table. The server and utility will cooperate to access the table. However, before running either utility, you should use mysqladmin flush-tables. To repair a table, the table's repair locking protocol should be used.

If skip_locating is on, external locking is disabled, so checking the fix in myisamchk or isamchk means the server is not aware of it and it is best to shut down the server. If the server persists, make sure no clients are accessing it before you use this. The card party's locking protocol must be used to tell the server that the table is not accessible to other clients.

Check the locking protocol of the table

This section only describes how to use the internal locking of the table. For the locking protocol of the check table, this process is only for the check of the table, not for the repair of the table.

1. Call mysql to issue 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 during check . The FLUSH statement causes the server to close the table's file, which will flush any unwritten changes that are still in the cache.

2. Execute the check process

$myisamchk tbl_name$ isamchk tbl_name

3. Release the table lock

mysql>UNLOCK TABLES;

If myisamchk or isamchk indicates that a problem with the table is found, repair of the table will need to be performed.

This article summarizes the above introduction for you. The content mentioned above is all the explanations I want to explain to you about how to lock the MySQL database table. I hope the content mentioned above can be helpful to everyone. help.