MySQL lock mechanism

Time:2020-11-15

Interior lock

  • 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.

External 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 lock

  • 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.

Watch lock

  • 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.

Read, shared lock

  • 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.

Write, exclusive lock

  • 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.

Lock queue

  • 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.

summary

  • Read locks block writes, but not reads;
  • Write locks block both read and write

Related articles

blog.csdn.net/qq_34377273/article/…
www.cnblogs.com/jkko123/p/10451240…

This work adoptsCC agreementThe author and the link to this article must be indicated in the reprint

Recommended Today

In depth analysis of message middleware selection

preface Message Queuing Middleware (hereinafter referred to as message middleware) refers to the use of efficient and reliable message delivery mechanism for platform independent data exchange, and based on data communication to integrate distributed systems. By providing message delivery and message queuing model, it can provide application decoupling, elastic scaling, redundant storage, traffic peak clipping, […]