Introduction to innoDB lock in mysql

Why do we need to start transaction before locking InnoDB

The lock in innodb is released after the transaction is submitted/rolled back. Once the transaction is submitted/rolled back, the lock in the transaction will be automatically released. Innodb defaults to autocommit=1, which turns on automatic submission.

The difference between locks using indexes and not using indexes for retrieval conditions:

When the retrieval conditions have indexes, specific rows will be locked.

If the search conditions are not used, a full table scan will be performed to lock all rows (including non-existent records)

Read lock:

Read lock is Shared, or non-blocking. Multiple users can read the same resource at the same time without interfering with each other.

Write lock:

Write lock is exclusive, which means that a write lock will block other write locks and read locks. In addition, write locks have a higher priority than read locks, so a write lock request may be inserted in front of the read lock queue, but the read lock will not be inserted in front of the write lock.

Table lock ：

　InnoDB also has two table locks: intention shared lock (IS), intention exclusive lock (IX)

Row lock:

　InnoDB implements two types of row-level locks, shared locks and exclusive locks

## Optimistic lock:

Optimistic lock, also called optimistic concurrency control, assumes that multi-user concurrent transactions will not affect each other during processing, and each transaction can handle the part of its impact without generating a lock. data. Before committing data updates, each transaction will first check whether other transactions have modified the data after the transaction read the data. If there are updates from other transactions, the currently committing transaction will be rolled back.

Pessimistic lock:

Pessimistic lock, also called pessimistic concurrency control, when transaction A applies a lock to a certain row of data, and when this transaction releases the lock, other transactions can execute and The lock conflict operation, the lock imposed by transaction A here is called pessimistic lock. Shared locks and exclusive locks (row locks, gap locks, next-key locks) are both pessimistic locks

How to implement pessimistic locks and optimistic locks:

The implementation of pessimistic locks relies on the database The provided lock mechanism is implemented, such as select * from news where id=12 for update, while optimistic locking relies on recording the data version, that is, by adding the version number field to the table as a key factor in whether the submission can be successful.

Shared lock (S):

Shared lock is also called read lock. If a transaction acquires a shared lock of a data row, other transactions can acquire the shared lock. The shared lock corresponding to the row, but the exclusive lock cannot be obtained, that is, when a transaction reads a data row, other transactions can also read, but cannot add, delete or modify the data row

Setting the shared lock: SELECT .... LOCK IN SHARE MODE;

Exclusive lock (X):

Exclusive lock is also called write lock. If a transaction acquires an exclusive lock on a data row, other transactions will No other locks (exclusive locks or shared locks) can be acquired on the row. That is, when a transaction reads a data row, other transactions cannot add, delete, modify or check the data row

Setting an exclusive lock: SELECT .... FOR UPDATE

　

Note:

• For select statements, innodb will not add any locks , that is, multiple select operations can be performed concurrently without any lock conflicts because there are no locks at all.

• For insert, update, and delete operations, innodb will automatically add exclusive locks to the data involved. Only query select requires us to manually set exclusive locks.

Intention shared lock (IS):

Notify the database of what locks need to be applied next and lock the table. If you need to add a shared lock to record A, then innodb will first find this table, add an intention shared lock to the table, and then add a shared lock to record A. That is to say, before adding a shared lock to a data row, you must first obtain the IS lock of the table

Intention exclusive lock (IX):

Notify the database of what lock needs to be applied next and add the table Lock. If you need to add an exclusive lock to record A, then innodb will first find this table, add an intentional exclusive lock to the table, and then add a shared lock to record A. That is to say, before adding an exclusive lock to a data row, you must first obtain the IX lock of the table

The difference between shared locks and intention shared locks, exclusive locks and intention exclusive locks:

• Shared locks and exclusive locks, the system will automatically add shared locks or exclusive locks under certain conditions, or you can manually add shared locks or exclusive locks.

• Intention shared locks and intention exclusive locks are automatically added and automatically released by the system, and the entire process does not require manual intervention.

• Shared locks and exclusive locks both lock row records, and intention shared locks and intention exclusive locks lock the table.

How to implement locks:

In MySQL, row-level locks do not directly lock records, but lock indexes. Indexes are divided into primary key indexes and non-primary key indexes. If a sql statement operates on the primary key index, MySQL will lock the primary key index; if a statement operates on a non-primary key index, MySQL will first lock the non-primary key index. , and then lock the relevant primary key index .

InnoDB row locks are implemented by locking index entries. If there is no index, InnoDB will lock records through a hidden clustered index. That is to say: if the data is not retrieved through index conditions, then InnoDB will lock all data in the table. The actual effect is the same as the table lock.

Row locks are divided into three situations:

Record Lock: Lock the index item, that is, lock a record.

Gap Lock: Lock the 'gap' between index items, the gap before the first record or the gap after the last record, that is, lock a range of records, excluding the record itself

Next-key Lock: Lock a range of records and include the record itself (a combination of the above two)

Note: The default level of InnoDB is repeatable-read (repeated read) level. The ANSI/IOS SQL standard defines four transaction isolation levels: read uncommitted, read committed, repeatable read, serializable, Gap Lock and The difference between Next-key Lock:

　

Next-Key Lock is a combination of row lock and gap lock. In this way, when InnoDB scans the index record, it will first add row lock to the selected index record ( Record Lock), and then add gap lock (Gap Lock) to the gaps on both sides of the index record. If a gap is locked by transaction T1, other transactions cannot insert records in this gap.

Row locks prevent other transactions from being modified or deleted, Gap locks prevent other transactions from being added, and the Next-Key lock formed by the combination of row locks and GAP locks jointly solves the problem of the RR sector. Phantom reading problem when writing data.

When to use table locks in InnoDB:

InnoDB will use row-level locks in most cases because of transaction and row locks This is often the reason why we choose InnoDB, but in some cases we also consider using table-level locks

• When the transaction needs to update most of the data, the table is relatively large , if the default row lock is used, it is not only inefficient, but also easily causes other transactions to wait for a long time and lock conflicts.

• #Transactions are relatively complex and may cause deadlocks and rollbacks.

Under InnoDB, pay attention to the following two points when using table locks.

(1) Although you can add table-level locks to InnoDB using LOCK TALBES, it must be noted that

table locks are not managed by the InnoDB storage engine layer

, but The upper layer MySQL Server is responsible for . Only when autocommit=0, innodb_table_lock=1 (default setting), the InnoDB layer can know the table lock added by MySQL, and MySQL Server can sense the row lock added by InnoDB. In this case Only under this condition can InnoDB automatically identify deadlocks involving table-level locks; otherwise, InnoDB will not be able to automatically detect and handle such deadlocks. (2) When using LOCAK TABLES to lock InnoDB, be careful to set AUTOCOMMIT to 0, otherwise MySQL will not lock the table; before the end of the transaction, do not use UNLOCAK TABLES to release the table lock, because UNLOCK TABLES will implicitly commit the transaction; COMMIT or ROLLBACK cannot release the table-level lock added with LOCAK TABLES. You must use UNLOCK TABLES to release the table lock. The correct method is as follows:

For example: If you need to write table t1 and Read from table t

SET AUTOCOMMIT=0;
LOCAK TABLES t1 WRITE, t2 READ, ...;[do something with tables t1 and here];COMMIT;
UNLOCK TABLES;

Deadlock:

We said that deadlock will not occur in MyISAM, because MyISAM is always once Sex gets all the locks it needs, either all satisfied or all waiting. In InnoDB, locks are acquired gradually, causing the possibility of deadlock.

After a deadlock occurs, InnoDB can generally detect it and make one transaction release the lock and roll back, and another acquire the lock to complete the transaction. However, InnoDB cannot completely automatically detect deadlocks when external locks or locks are involved. This needs to be solved by setting the lock wait timeout parameter innodb_lock_wait_timeout. It should be noted that this parameter is not only used to solve the deadlock problem. When concurrent access is relatively high, if a large number of transactions are suspended because they cannot obtain the required locks immediately, it will occupy a large amount of computer resources and cause serious performance problems. problems and even bring down the database. We can avoid this situation by setting an appropriate lock wait timeout threshold.

There are many ways to avoid deadlocks. Here are three common ones:

1. If different programs will access multiple tables concurrently, try to agree to access the tables in the same order. , which can greatly reduce the chance of deadlock. If two sessions access the two tables in a different order, the chance of deadlock is very high! But if accesses are performed in the same order, deadlock may be avoided.

2. In the same transaction, try to lock all the resources required at once to reduce the probability of deadlock.

3. For business parts that are very prone to deadlocks, you can try to upgrade the locking granularity and use table-level locking to reduce the possibility of deadlocks.

4. When the program processes data in batches, if the data is sorted in advance to ensure that each thread processes records in a fixed order, the possibility of deadlock can also be greatly reduced.

5. Under the REPEATEABLE-READ isolation level, if two threads use SELECT...ROR UPDATE on the same condition record at the same time to add an exclusive lock, if there is no record that matches the condition , both threads will lock successfully. The program discovers that the record does not yet exist and attempts to insert a new record. If both threads do this, a deadlock will occur. In this case, changing the isolation level to READ COMMITTED can avoid the problem.

6. When the isolation level is READ COMMITED, if both threads execute SELECT...FOR UPDATE first, determine whether there are records that meet the conditions, and if not, insert the records. At this time, only one thread can insert successfully, and the other thread will wait for a lock. When the first thread submits, the second thread will make an error due to the primary key again, but although this thread makes an error, it will obtain an exclusive lock! At this time, if a third thread applies for an exclusive lock, a deadlock will also occur. In this case, you can directly perform the insertion operation and then catch the primary key duplicate exception, or when encountering the primary key duplicate error, always execute ROLLBACK to release the acquired exclusive lock

##　

ps: If a deadlock occurs, you can use the SHOW INNODB STATUS command to determine the cause of the last deadlock and improvement measures.

Summary:

For the

InnoDB table, there are mainly the following points

(1) InnoDB marketing is based on indexes. If data is accessed without an index, InnoDB uses table locks.

(2) InnoDB gap lock mechanism, and the reasons why InnoDB uses gap locks.

(3) Under different isolation levels, InnoDB’s locking mechanism and consistent read strategy are different.

(4) MySQL recovery and replication also have a great impact on the InnoDB lock mechanism and consistent read strategy.

(5) Lock conflicts and even deadlocks are difficult to completely avoid.

After understanding the lock characteristics of InnoDB, users can reduce lock conflicts and deadlocks through design and SQL adjustments, including:

• Try to use lower isolation Level

• Carefully design the index, and try to use the index to access data to make locking more accurate, thereby reducing the chance of lock conflicts.

• Choose a reasonable transaction size, and the probability of lock conflicts for small transactions is smaller.

• When display locking the record set, it is best to request a sufficient level of lock at one time. For example, if you want to modify data, it is best to apply for an exclusive lock directly instead of applying for a shared lock first and then request an exclusive lock when modifying, which can easily lead to deadlock.

• When different programs access a group of tables, they should try to agree to access each table in the same order. For a table, try to access the rows in the table in a fixed order. . This can greatly reduce the chance of deadlock.

• Try to use equal conditions to access data, so as to avoid the impact of gap locks on concurrent insertion.

• Do not apply for a lock level that exceeds the actual need; unless necessary, do not display locking when querying.

• For some specific transactions, table locks can be used to increase processing speed or reduce the possibility of deadlock.

The above is the detailed content of Introduction to innoDB lock in mysql. For more information, please follow other related articles on the PHP Chinese website!