The InnoDB of the lock mechanism in MySQL

We know that MySQL was in the past, the storage engine was MyISAM by default ,but as the requirements for transactions and concurrency increased, the InnoDB engine was introduced. It has a range of features that support transactional security.

InnoDBLock Mode

InnoDB implements two types of row locks.

shared Lock (S ): allows a transaction to read one line, preventing other transactions from acquiring an exclusive lock on the same data set.

Exclusive Lock (X ): transactions that allow exclusive locks to update data, but organize other transactions to obtain shared and exclusive locks of the same data set.

You can understand this:

Shared lock is when I read, you can read, but not write. An exclusive lock is when I write, you can't read or write. In fact, it is MyISAM read lock and write lock, but the object is different.

In addition to the InnoDB There are two table locks:

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 .

InnoDB line Lock mode compatibility List:

Attention:

When the lock mode of a transaction request is compatible with the current lock,InnoDB grants the requested lock to the transaction, whereas if the request is incompatible, the transaction waits for the lock to be released.

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

For insert,update,Delete,InnoDB automatically adds an exclusive lock to the data involved ( X); for a general Select statement, theInnoDB does not have any locks, and the transaction can display a shared or exclusive lock through the following statement.

Shared Lock:SELECT * FROM table_name where ..... lock in Share mode

Exclusive Lock:SELECT * FROM table_name where ... . FOR update

Examples of adding shared locks:

Use Select .... for update to add an exclusive lock

How locks are implemented:

InnoDB A row lock is implemented by locking an index entry, and if there is no index,InnoDB locks the record through a hidden clustered index.

that is, if you do not retrieve the data by index criteria, then InnoDB all data in the table is locked and the actual effect is the same as the table lock.

Row locks are divided into three scenarios:

record lock : Locks a record by locking the index entry.

Gap Lock: locks a range of records, not including the record itself, between the ' gap ' between index items, the gap before the first record, or the gap after the last record

Next-keyLock: Locks a range of records and includes the record itself (the combination of the two above).

Note: The default level for InnoDB is Repeatable-read level, so the following is said in the RR level.

Before the difference between the Gap lock and next-key lock , until you see a word on the internet suddenly enlightened, I hope to help you.

Next-key Lock is a combination of row and gap locks, so that when InnoDB scans the index record, a row lock (recordlock)is first added to the selected index record . The gap lock is then added to the clearance on bothsides of the index record. If a gap is locked by the transaction T1 , other transactions cannot be inserted into the record at this interval .

Dry said not boring, we look at specific examples:

Suppose we have a table:

+----+------+

| ID | Age |

+----+------+

|    1 | 3 |

|    2 | 6 |

|    3 | 9 |

+----+------+

The table structure is as follows:

CREATE TABLE ' Test ' (
' id ' int (one) not NULL auto_increment,
' Age ' int (one) DEFAULT NULL,
PRIMARY KEY (' id '),
KEY ' KeyName ' (' age ')
) Engine=innodb auto_increment=302 DEFAULT CHARSET=GBK;

So our index of age is divided into

(Negative infinity, 3],

(3,6],

(6,9],

(9,positive Infinity) ;

Let's look at a few things:

1. When transaction a executes the following statement:

Mysql> SELECT * from fenye where age=6for update;

not only the row lock is used to lock the corresponding data row, but also the interval on both sides, (5, 6 ] and (6,9) have been added to the Gap lock.

Such transactions B There's no way in this two-zone Insert into new data, However, transaction B can insert data in a range outside of two intervals.

2. When transaction a executes

SELECT * from Fenye where age=7 for update;

Then it will lock the interval (6,9] and other transactions cannot insert or update the data in this interval.

3, if the data of the query is no longer in scope,

For example, transaction a executes select * from Fenye where age=100 for update;

Then the lock interval is (9,positive infinity).

Summary:

Row locks prevent other transactions from being modified or deleted,GAP locks prevent other transactions from being added, and the next-key locks formed by the combination of row and gap locks are resolved together. RR the level of the Phantom reading problem when writing data.

When toInnoDBUse table locks in:InnoDB uses row-level locks in most cases, because transaction and row locks are often the reason we choose InnoDB, but in some cases we also consider using table-level locks.

1, when the transaction needs to update most of the data, the table is relatively large, if the use of the default row lock, not only inefficient, but also easy to cause other transactions long wait and lock conflict.

2, the transaction is more complex, it is likely to cause a deadlock caused by rollback.

Deadlock:

We have said that there is no deadlock in MyISAM, because MyISAM always get all the locks they need, either all of them, or all waiting. In the InnoDB , the lock is gradually obtained, resulting in the possibility of deadlock.

In the above example, we can see that when two transactions need to obtain the lock held by the other side to continue to complete the transaction, causing both sides are waiting, resulting in a deadlock.

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.

Avoid deadlocks:

There are several ways to avoid deadlocks, and here are just a few of the three common types:

1, if different programs will access multiple tables concurrently, as far as possible to agree to access the table in the same order, can greatly reduce the deadlock opportunity.

2, in the same transaction, as far as possible to lock all the resources needed to reduce the deadlock generation probability;

3, for very easy to generate deadlock in the business part, you can try to use the upgrade lock granularity, through table-level locking to reduce the probability of deadlock;

Reference:

The relationship between the transaction isolation level and the lock in Innodb

MySQL InnoDB lock Mechanism (ii)

Innodb lock mechanism:next-key lock discussion

MySQL database development, optimization and management maintenance. Second Edition

Copyright NOTICE: This article is the original blogger articles, reproduced please indicate the source.

The InnoDB of the lock mechanism in MySQL