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

This article introduces the knowledge about "Mysql external locking and MySQL server internal locking". In the actual case operation process, many people will encounter such difficulties. Next, let Xiaobian lead you to learn how to deal with these situations! I hope you can read carefully and learn something!

Mysql What is external locking

The server can also use external locks (file-level locks) 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 in table checking operations.

However, Mysql external locking is disabled on some systems because it does not work reliably. The procedure chosen for running myisamchk or isamchk depends on whether the server can use external locking. If not used, internal locking protocols must be used.

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

◆ If skip_locking is off, external locking is in effect You can go ahead and run people and a utility to check the table. The server and utility will cooperate to access the table. However, before running any utility, you should use mysqladminflush-tables. To repair a table, you should use the table repair locking protocol.

◆ If skip_locking is on, Mysql external locking is disabled, so checking for fixes in myisamchk or isamchk indicates that the server does not know, and it is best to shut down the server. If you insist on keeping the server open, make sure that no clients access it before you use this representation. The card party locking protocol must be used to tell the server that the table is inaccessible to other clients.

What is MySQL Server Internal Lock?

Internal locking prevents requests from interfering with each other--for example, prevents a client's SELECT query from being interfered with by another client's UPDATE query. Internal locking mechanisms can also be used to prevent MySQL servers from accessing tables when they are checked or repaired using myisamchk or isamchk.

Grammar:

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 MySQL 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, then only the thread holding the lock READ or WRITE the table, other threads are blocked.

Each thread waits (no timeout) until it acquires 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 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 checking, you just need to get the read lock. Furthermore, it can only read tables, but cannot modify them, so it also allows other clients to read tables. For fixes, you must obtain something to prevent any client from modifying the table while you are working on it.

"Mysql external lock and MySQL server internal lock is what" content introduced here, thank you for reading. If you want to know more about industry-related knowledge, you can pay attention to the website. Xiaobian will output more high-quality practical articles for everyone!

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.