How to lock the MySQL database table?

The following articles mainly introduce the detailed content parsing of locking in the MySQL database table, including internal locking and External Locking. If you are interested in the actual operation content, you can browse the following articles and hope they will help you in your future studies. Internal locking can prevent client requests from interfering with each other. For example

The following articles mainly introduce the detailed content parsing of locking in the MySQL database table, including internal locking and External Locking. If you are interested in the actual operation content, you can browse the following articles and hope they will help you in your future studies. Internal locking can prevent client requests from interfering with each other. For example

The following articles mainly introduce the detailed content parsing of locking in the MySQL database table, including internal locking and External Locking. If you are interested in the actual operation content, you can browse the following articles and hope they will help you in your future studies.

Internal locking can avoid mutual interference between client requests-for example, to prevent the SELECT query of the client from being disturbed by the UPDATE query of the other client. The internal locking mechanism can also be used to prevent the server from accessing the table when myisamchk or isamchk is used to check or repair the table.
The MySQL database table is locked, and the server is locked by two methods:

1. Internal lock

Internal locking can avoid mutual interference between client requests-for example, to prevent the SELECT query of the client from being disturbed by the UPDATE query of the other client. The internal locking mechanism can also be used to prevent the server from accessing the table when myisamchk or isamchk is used to check or repair the table.

Syntax:

LOCK table: lock tables tbl_name {READ | WRITE}, [tbl_name {READ | WRITE},…]

UNLOCK table: UNLOCK TABLES

Lock tables is the table locked by the current thread. Unlock tables releases any lock 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, the thread (and all other threads) can only READ from the table. If a thread acquires a WRITE lock on a table, only the lock-holding thread reads or writes the table, and other threads are blocked.

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

The WRITE lock generally has a higher priority than the READ lock to ensure that the changes are processed as soon as possible. This means that if a thread acquires the READ lock and another thread requests a WRITE lock, the subsequent READ lock request will wait until the WRITE thread gets the lock and releases it.

Obviously, you only need to obtain the read lock for the check. In addition, you can only read the table, but cannot modify it. Therefore, it allows other clients to read the table. You must obtain the fix to prevent any client from modifying it when you operate on the table.

2. External lock

The server can also use external locks (file-level locks) to prevent other programs from modifying files when the server uses tables. In general, in the table check operation, the server locks the external key to use with myisamchk or isamchk. However, external locks are disabled in some systems because they cannot work reliably. The process of running myisamchk or isamchk depends on whether the server can use external locks. If not, use the internal lock protocol.

If the server runs with the -- skip-locking option, the external lock is disabled. This option is default in some systems, such as Linux. You can run the MySQL database table admin variables command to determine whether the server can use External Locking. Check the value of the skip_locking variable and follow these methods:

If skip_locking is off, the external lock is valid. You can continue and run a utility to check the table. Servers and utilities will work together to access tables. However, before running any utility, use MySQLadmin flush-tables. To repair a table, use the repair lock protocol of the table.

If skip_locaking is on, the external lock is disabled. Therefore, if the myisamchk or isamchk is used to check and fix the vulnerability, the server is unknown. You are advised to disable the server. If the server is always on, make sure that you use this to indicate that no client is allowed to access it. The clientworker lock protocol must be used to tell the server that the table is not accessed by other clients.

Checklist lock protocol

This section only describes how to use internal locking of a table. For the checklist locking protocol, this process only checks the table and does not fix the table.

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 data to and modifying the table during the check. The FLUSH statement causes the server to close the table file. It will refresh and still tell the cache of any write changes.

2. Execute the check process

 
 

  
  
$myisamchk tbl_name$ isamchk tbl_name 
 
 

3. Release the table lock

MySQL> unlock tables;

If myisamchk or isamchk indicates a problem with the table, the table must be repaired.

Fix table locking protocols

This section only describes how to use internal locking of a table. The table repair locking process is similar to the checklist locking process, but there are two differences. First, you must get the write lock instead of the read lock. Because you need to modify the table, the client is not allowed to access it. Second, the flush table statement must be released after the fix is executed. Because the new index files created by myisamchk and isamchk will not be noticed unless the High-speed cache of the modified TABLE is refreshed again. This example is also suitable for table optimization.

1. Call the MySQL database table to publish the following statements:

 
 

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

2. copy the data table and run myisamchk and isamchk:

 
 

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

The recover option is only set for installation. The selection of these special options depends on the type of the repair you perform.

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

 
 

  
  
MySQL>FLUSH TABLES;mysql>UNLOCK TABLES; 
 
 

The above content is a detailed description of the MySQL database table locking. I hope you will gain some benefits.