Internal lock of MySQL Server

Internal locking is one of the two ways to lock the MySQL server. The following describes the internal locking of the MySQL server in detail. If you are interested in locking the MySQL server, take a look.

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 MySQL servers from accessing tables when myisamchk or isamchk is used to check or repair tables.

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 sends out another lock tables, 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, 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.

MySQL Single table size limit

Back up data based on Mysql tables

MySQL Show statement usage

Start and Stop a MySQL Server

Command for viewing the Mysql table structure