The lock mechanism of MySQL common engine

First, Introduction

Default lock level for MySQL engine:

MyISAM and memory Use table-level locks (table-level locking).

The BDB uses page locks (page-level locking) or table-level locks, which are page locks by default.

InnoDB supports row-level locks (row-level locking) and table-level locks, which are row-level locks by default.

Second, row lock and table lock in InnoDB engine

In the InnoDB engine, both row and table locks are supported, so when is the entire table locked and when or just one line is locked?

The InnoDB row lock is implemented by locking the index entry on the index, which differs from Oracle in that Oracle is implemented by locking the corresponding data rows in the data block.

InnoDB This type of row lock implementation is characterized by the fact that InnoDB uses row-level locks only if the data is retrieved by index criteria, otherwise INNODB will use a table lock!

In practice, it is important to pay special attention to this feature of the InnoDB row lock, otherwise, it may lead to a lot of lock conflicts, which can affect the concurrency performance.

Row-level locks are index-based, and table-level locks are used if an SQL statement does not use a row-level lock for the index.

The disadvantage of row-level locks is that if you request a large number of lock resources concurrently, the speed is slow and memory consumption is large.

1, row-level lock and deadlock

There is no deadlock in MyISAM because MyISAM always gets all the locks needed, either all or all.

In InnoDB, locks are obtained gradually, resulting in the possibility of deadlocks.

In MySQL, a row-level lock is not a direct lock record, but a lock index.

Index is divided into primary key index and non-primary key index two kinds:

If an SQL statement operates on the primary key index, MySQL locks the primary key index;

If a statement operates on a non-primary key index, MySQL locks the non-primary key index first, and then locks the associated primary key index.

In the update, delete operation, MySQL not only locks all index records scanned by the Where condition, but also locks adjacent key values, known as Next-key locking.

When two transactions are executed simultaneously, one locks the primary key index and waits for other related indexes. Another lock has a non-primary key index, waiting for the primary key index. This will cause a deadlock to occur.

After a deadlock occurs, InnoDB can generally detect and cause one transaction to release the lock fallback, and the other to acquire the lock to complete the transaction.

There are a number of ways to avoid deadlocks, so let's introduce three common

• If multiple tables are accessed concurrently by different programs, it is possible to significantly reduce deadlock opportunities by agreeing to access the tables in the same order.
• In the same transaction, as far as possible to lock all the resources needed to reduce the deadlock generation probability;
• For business parts that are prone to deadlocks, try using the upgrade lock granularity to reduce the probability of deadlock generation through table-level locking;

2. Table lock

Table locks are divided into table shared read locks (shared locks) and table exclusive write locks (exclusive locks)

• A shared lock (Share lock) is also called a read lock, which is the lock created by the read operation. Other users can read data concurrently, but no transaction will be able to modify the data (to get an exclusive lock on the data) until all shared locks have been freed.

If transaction T adds a shared lock to data A, the other transaction can only have a plus shared lock, and cannot add an exclusive lock. Transactions that are allowed to share locks can read only data and cannot modify data.

Use: SELECT ... LOCK in SHARE MODE;

Adding lock in SHARE MODE after the query statement, MySQL adds a shared lock to each row in the query result, and when no other thread uses an exclusive lock on any row in the query result set, the shared lock can be successfully requested, otherwise it will be blocked. Other threads can also read tables that use shared locks, and those threads are reading the same version of data.

• An exclusive lock (exclusive lock) is also called a write lock, and if the transaction T adds an exclusive lock to data A, no other transaction can add any type of lock to a. A transaction that is granted an exclusive lock can read and modify data.

Use: SELECT ... For UPDATE;

After the query statement is incremented for UPDATE, MySQL adds an exclusive lock to each row in the query result, and when no other thread uses an exclusive lock on any row in the query result set, the exclusive lock can be successfully requested or it will be blocked.

3. Intent Lock

Intent shared Lock (IS): Indicates that a transaction is ready to join a shared lock on a data row, that is, a data row must be acquired before a shared lock

Intent exclusive Lock (IX): Similar to the above, indicates that the transaction is prepared to add an exclusive lock to the data row, stating that the transaction must obtain an IX lock on the table before a data row is added to the exclusive lock.

Intent locks are innodb automatically and do not require user intervention.

Iii. Summary

For INSERT, UPDATE, DELETE,INNODB will automatically add exclusive locks to the data involved;

For a general SELECT statement, InnoDB does not add any locks.

A transaction can display a shared or exclusive lock with the following statement:

Shared Lock: SELECT ... LOCK in SHARE MODE;

Exclusive Lock: SELECT ... For UPDATE;

The lock mechanism of MySQL common engine