- MySQL performs internal locking within its own server to manage contention for table content by multiple sessions.
- Internal lock can be divided into row lock and table lock.
- MySQL provides options for client sessions to explicitly acquire table locks to prevent other sessions from accessing tables.
- You can use the lock table and unlock tables statements to control locking.
LOCK TABLES table_ Name [read|write] ා lock Unlock tables Flush tables with read lock ා In order to ensure the consistency of backup, all backup methods use flush tables with readlock. However, if there are long-running transactions on the table, this method may bring danger.
- Row level locks are fine-grained. Only the rows that are accessed are locked.
- This allows simultaneous write access through multiple sessions, making it suitable for multi-user, highly concurrent, and OLTP applications.
- Only InnoDB supports row level locking.
- MySQL uses table level locks on MyISAM, memory, and merge tables, allowing only one session to update these tables at a time.
- This locking level makes these storage engines more suitable for read-only or read-only or single user applications.
- When a table is locked as read, multiple sessions can read data from the table without acquiring the lock.
- In addition, multiple sessions can acquire locks on the same table, which is why read locks are also known as shared locks.
- When the read lock is held, no session can write data to the table (including the session holding the lock).
- If there are any write attempts, the operation will wait until the read lock is released.
- When a table is locked as write, no session other than the session holding the lock can read or write data to the table.
- No other session can acquire any locks unless the existing lock is released.
- That’s why write locks are called exclusive locks. If there are any read / write attempts, the operation will wait until the write lock is released.
- With the exception of shared locks (a table can have multiple shared locks), no two locks can be added to a table together.
- If a table already has a shared lock and an exclusive lock is coming in, it will remain in the queue until the shared lock is released.
- When the exclusive lock is in the queue, all subsequent shared locks are blocked and remain in the queue.
- The metadata lock is acquired when InnoDB reads / writes data from the table.
- If the second transaction requests write lock, the transaction is kept in the queue until the first transaction completes.
- If the third transaction wants to read data, it must wait until the second transaction completes.
- Read locks block writes, but not reads;
- Write locks block both read and write
This work adoptsCC agreementThe author and the link to this article must be indicated in the reprint