Shulou(Shulou.com)05/31 Report--

This article is to share with you about how to lock MySQL database tables, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

How to lock MySQL database tables

The method of locking the table

There are many ways to prevent client requests from interfering with each other or between the server and the maintenance program. If you shut down 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 fault-free databases and tables unavailable. The main process discussed in this section is to avoid interaction between the server and myisamchk or isamchk. The way to do this is to lock the table.

The server is locked by two table methods:

1. Internal locking

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

Syntax:

Lock the table: LOCKTABLEStbl_name {READ | WRITE}, [tbl_name {READ | WRITE}, …]

Unlock table: UNLOCKTABLES

LOCKTABLES locks the table for the current thread. UNLOCKTABLES releases any locks held by the current thread. When a thread issues another LOCKTABLES, or when the connection to the server is closed, all tables locked by the current thread are automatically unlocked.

If a thread acquires an 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 locked thread READ or WRITE table is blocked, and other threads are blocked.

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

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

Obviously for inspection, you just need to get the read lock. In addition, he likes to step down and can only read the table, but cannot modify it, so he also allows other clients to read the table. For fixes, you have to get something to prevent any client from modifying it when you operate on the table.

two。 External locking

The server can also use external locks (file-level locks) to prevent other programs from modifying files when the server uses the table. Typically, the server uses external locks in conjunction with myisamchk or isamchk during a table check operation. However, external locking is disabled in some systems because it cannot work reliably. The process you choose to run myisamchk or isamchk depends on whether the server can use external locks. If not, internal locking protocols are required.

External locking is disabled if the server is running with the-- skip-locking option. This option is default on some systems, such as Linux. You can determine whether the server can use external locks by running the mysqladminvariables command. Check the value of the skip_locking variable and do this as follows:

If skip_locking is off, then the external lock is valid you can continue and run a person and a utility to check the table. The server and the utility will cooperate to access the table. However, you should use mysqladminflush-tables before running any utility. In order to repair the table, the table's repair locking protocol should be used.

If skip_locaking is on, external locking is disabled, so checking fixes in myisamchk or isamchk indicates that the server does not know, and it is best to shut down the server. If you insist that the server remains open, make sure that you use this to indicate that there is no client to access it. You must use the card party locking protocol to tell the server that the table is not accessed by other clients.

How to check the locking protocol of a MySQL table

This section only describes if you 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-uroot-pdb_namemysql > LOCKTABLEtbl_nameREAD;mysql > FLUSHTABLES

The lock prevents other clients from writing and modifying the table during inspection. The FLUSH statement causes the server to close the file of the table, which will refresh any changes that are still in the cache for writing.

two。 Perform the inspection process

$myisamchktbl_name$isamchktbl_name

3. Release the table lock

Mysql > UNLOCKTABLES

If myisamchk or isamchk indicates that a problem with the table was found, you will need to perform a repair of the table.

The above is how to lock the MySQL database table, the editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.