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

This article will explain in detail how to lock the data table in MySQL. The content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

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: LOCK TABLES tbl_name {READ | WRITE}, [tbl_name {READ | WRITE}, …]

Unlock table: 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 TABLES, 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 the default in some systems, such as. You can determine whether the server can use external locks by running the admin variables 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 mysqladmin flush-tables before running any utility. In order to repair the table, the table's repair locking protocol should be used.

◆ external locking is disabled if skip_locaking is on, 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.

Check the locking protocol of the 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-u root-p db_namemysql > LOCK TABLE tbl_name READ;mysql > FLUSH TABLES

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

$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 was found, you will need to perform a repair of the table.

Fix the locking protocol for the table

This is only described if you use the internal locking of the table. The locking process of repairing a table is similar to checking the locking process of a table, but there are two differences. First, you have to get a write lock instead of a read lock. Because you need to modify the table, clients are not allowed to access it at all. Second, the FLUSH TABLE statement must be issued after the repair, because the new index file created by myisamchk and isamchk will not notice the change unless the cache of the changed table is refreshed again. This example is also suitable for the process of optimizing tables.

1. Call mysql to issue the following statement:

$mysql-u root-p db_namemysql > LOCK TABLE tbl_name WRITE;mysql > FLUSH TABLES

two。 Make a copy of the datasheet, then run myisamchk and isamchk:

$cp tbl_name.* / some/other/dir$myisamchk-recover tbl_name$ isamchk-recover tbl_name

-- the recover option is set only for installation. The choice of these special options will depend on the type of fix you perform.

3. Flush the cache again and release the table lock:

Mysql > FLUSH TABLES;mysql > UNLOCK TABLES

About how to lock the data table in MySQL to share here, I hope the above content can be of some help to you, you can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.