The article number originated in public address: Michael mo coding 】 【 mysql those thing | mysql lock

directory

  • Lock definition
  • Classification of lock
  • Read locks and write locks
  • Table and row locks
  • InnoDB shared and exclusive locks
  • InnoDB intentional locks and exclusive locks
  • InnoDB row locks
  • Lock the InnoDB clearance
    • concept
    • InnoDB uses gap locking for purposes
  • InnoDB row lock implementation
  • Small talk
  • Welcome to join my official account [Maimocoding] pk big factory together

Lock definition

A lock is a mechanism by which a computer coordinates concurrent access to a resource by multiple processes or threads.

In a database, in addition to traditional computing resources (such as CPU, RAM, I/O, etc.) contention, data is also a resource that needs to be shared by users. Lock conflict is also an important factor affecting the performance of concurrent database access.

Classification of lock

  • In terms of performance, there are optimistic locks (implemented by version comparison) and pessimistic locks
  • The types of database operations can be divided into read and write locks (both pessimistic locks)
  • The granularity of operations on data is divided into table locks and row locks

Read locks and write locks

  • Read lock (shared lock) : Multiple read operations on the same data can be performed simultaneously without affecting each other, but will block write operations
  • Write lock (mutex) : Blocks other write locks and read locks until the current write operation is complete

Table and row locks

Table locks

  • Each operation locks the entire table. Low overhead, fast lock; Deadlock does not occur; The lock granularity is large and the probability of lock conflict is the highest. The lowest degree of concurrency;
  • Table locking is more suitable for query-oriented applications with few concurrent users and only a small number of data updates based on index conditions, such as Web applications

Row locks

  • Each operation locks one row of data. High overhead, slow lock; Deadlocks occur; The lock granularity is minimum and the probability of lock conflict is minimum. The highest concurrency;
  • Row-level locking is only implemented at the storage engine layer, not the mysql server.
  • Row-level locking is more suitable for applications with a large number of concurrent updates of a small amount of different data based on index conditions and concurrent queries, such as some online transaction processing (OLTP) systems

InnoDB shared and exclusive locks

InnoDB implements two types of row locking:

  • Shared lock (S): An exclusive lock that allows one transaction to read a row, preventing other transactions from acquiring the same data set
  • Exclusive lock (X): Allows transactions that acquire exclusive locks to update data, preventing other transactions from acquiring shared read locks and exclusive write locks of the same data set

InnoDB also has two types of Intention Locks for internal use, both of which are table Locks:

  • Intended shared lock (IS) : a transaction that intends to assign a shared lock to a row must acquire an IS lock on that table before assigning a shared lock to a row
  • Intentional exclusive lock (IX) : a transaction that intends to lock a row exclusively must acquire an IX lock on the table before it can lock a row exclusively

InnoDB intentional locks and exclusive locks

  • Intent locks are automatically added by the InnoDB engine without user intervention

  • For UPDATE INSERT DELETE statements, InnoDB automatically adds an exclusive lock (X) to the data set involved.

  • InnoDB does not place any locks on normal SELECT statements

  • A transaction can explicitly assign a shared or exclusive lock to a result set by using the following statement

    • Shared lock (S) :SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE. Other sessions can still query records, and can also add a share mode lock to the record. However, if the current transaction needs to update the record, a deadlock is likely.
    • Exclusive lock (X) :SELECT * FROM table_name WHERE ... FOR UPDATE . Other sessions can query the record, but cannot apply a shared or exclusive lock to the record. Instead, they wait to acquire the lock

InnoDB row locks

  • InnoDB row locks are implemented by locking index entries on indexes, unlike Oracle, which locks rows in data blocks. InnoDB’s row-locking implementation means that InnoDB uses row-locking only when data is retrieved by index criteria. Otherwise, InnoDB uses table locking!
  • InnoDB uses row locks to lock data whether using a primary key index, a unique index, or a normal index.
  • Row locks can only be used if the execution plan actually uses an index: Even if index fields are used in conditions, it is up to MySQL to decide whether to use indexes to retrieve data by determining the cost of different execution plans. If MySQL decides that a full table scan is more efficient, such as for some small tables, it will not use indexes. In this case InnoDB will use table locks instead of row locks.
  • MySQL locks rows only for indexes, not for records. If multiple sessions use the same index key, lock conflicts may occur. To get the lock). Keep this in mind when designing applications.

Lock the InnoDB clearance

concept

When we retrieve data using a range condition rather than an equality condition and request a shared or exclusive lock, InnoDB locks the index entries of existing data records that meet the condition. For records whose Key values are within the condition range but do not exist, called gaps, InnoDB also locks this GAP. This locking mechanism is called next-key locking.

Apparently, InnoDB’s locking mechanism blocks concurrent insertions of key values within the qualifying range when using range criteria to retrieve and lock records, often resulting in severe lock waits. Therefore, in practical application development, especially for applications with a large number of concurrent inserts, we should try to optimize business logic, try to use equality conditions to access updated data, and avoid using scope conditions.

InnoDB uses gap locking for:

  • Prevent phantoms to meet the requirements of the relevant isolation level; Meet recovery and replication requirements

  • MySQL uses BINLOG to input SQL statements that UPDATE data successfully, such as INSERT, UPDATE, and DELETE, and thus restore MySQL databases and replicate primary and secondary data. The MySQL recovery mechanism (replication is a continuous binlog-based recovery performed by Slave MySQL) has the following features:

    • MySQL restore is SQL statement level, that is, re-execute the SQL statement in BINLOG.
    • Second, MySQL’s Binlog is recorded in the order in which transactions were committed, and recovery is performed in this order.

Thus, MySQL’s recovery mechanism requires that, before a transaction is committed, other concurrent transactions cannot insert any records that meet their locking conditions, that is, phantom reads are not allowed.

InnoDB lock mode

– Implicit locking:

InnoDB uses a two-phase lock protocol during transaction execution:

Locks can be performed at any time and InnoDB automatically locks when needed depending on the isolation level;

Locks are released only when commit or ROLLBACK is performed, and all locks are released at the same time.

– Explicit lock:

select. lock in share mode/ / Shared lock
select.for update / / exclusive lock
Copy the code

– select for update:

When the SELECT query is executed, the corresponding index access item is locked (X lock), which means that the lock corresponding to this statement is equivalent to the effect of the update.

– select *** for update scenarios

The for update clause is used to ensure that the data is up to date and that only you can modify the data.

– select lock in share mode

The in Share mode clause adds a share lock to the found data, indicating that other transactions can only perform simple select operations on the data, but not DML operations.

– select *** lock in share mode usage scenario

To ensure that the data is not being modified by other transactions, that is, to ensure that the data is the latest data, and do not allow others to modify the data. However, you may not be able to modify the data, because other transactions may also lock the data in share mode.

– Performance impact

  • select for updateStatement, equivalent to oneupdateStatements. In busy situations, if transactions are not committed or rolled back in time, other transactions may wait for a long time, affecting the concurrency efficiency of the database.
  • select lock in share modeThe statement is a feature that places a shared lock (S lock) on the data being searched. It allows other transactions to also lock S on the data, but does not allow modification of the data. If not timelycommitorrollbackIt can also cause a lot of transaction waiting.

Small talk

  • After reading the article, I am not and mysql lock cp rate increased again
  • I’m Maimo, welcome to talk to me

Original is not easy, feel that the article is written well small partners, a praise 👍 to encourage it ~

The article will be updated continuously. You can search “Maimo Coding” on wechat to read it for the first time. Every day to share quality articles, large factory experience, school recruitment experience, help the school recruitment interview, is worth paying attention to every programmer platform.