Shulou(Shulou.com)05/31 Report--

This article mainly shows you "how InnoDB uses intention locks". The content is simple and clear. I hope it can help you solve your doubts. Let me lead you to study and learn this article "how InnoDB uses intention locks".

One, shared / exclusive lock (Shared and Exclusive Locks)

Of course, standard row-level locks (row-level locking), shared / exclusive locks, are also implemented in InnoDB:

(1) A transaction can read a row only if it gets the shared S lock of a row.

(2) only when the transaction gets the exclusive X lock of a row of records can the row be modified or deleted.

The compatible mutex table is as follows:

S X

S compatible mutex

X mutex

That is:

(1) multiple transactions can get an S lock, and reading can be done in parallel.

(2) only one transaction can get the X lock, and write / read must be mutually exclusive.

The potential problem with shared / exclusive locks is that they cannot be fully parallelized. The solution is multiple versions of data. The specific idea is "Why is InnoDB concurrency so high?" As introduced in, I will not go any further here.

Second, intention lock (Intention Locks)

InnoDB supports multi-granularity locks (multiple granularity locking), which allows row-level locks and table-level locks to coexist. In practical applications, InnoDB uses intentional locks.

Intention lock means that at some point in the future, a transaction may have to add a shared / exclusive lock, so declare an intention in advance.

Intention locks have the following characteristics:

(1) first of all, an intentional lock is a table-level lock (table-level locking)

(2) intention locks are divided into:

Intention shared lock (intention shared lock, IS), which indicates that the transaction intends to add a shared S lock to certain rows in the table

Intention exclusive lock (intention exclusive lock, IX), which indicates that the transaction intends to add exclusive X locks to certain rows in the table

For example:

Select... Lock in share mode, to set the IS lock

Select... For update, to set the IX lock

(3) the intention locking protocol (intention locking protocol) is not complex:

In order for a transaction to acquire the S lock of some rows, it must first acquire the IS lock of the table

In order for a transaction to acquire an X lock for some rows, it must first acquire the IX lock for the table

(4) because the intention lock only indicates the intention, it is actually a relatively weak lock. The intention locks are not mutually exclusive, but can be parallel. The compatibility mutex table is as follows:

IS IX

IS compatible

IX compatible

(5) well, since the intention locks are compatible with each other, what is the significance? It is mutually exclusive with the shared lock / exclusive lock, and its compatibility mutex table is as follows:

S X

IS compatible mutex

IX mutex

Voiceover: exclusive locks are strong locks and are not compatible with other types of locks. It is also easy to understand that when you modify and delete a row, you must acquire a strong lock to prohibit other concurrency on the row to ensure data consistency.

Third, insert intention lock (Insert Intention Locks)

For the modification and deletion of existing data rows, the mutex X lock must be strengthened, so is it necessary to add such a strong lock to implement mutex for data insertion? Insert the intention lock and give birth.

Insert intention locks, a kind of Gap Locks (therefore, also implemented on indexes), are specific to insert operations.

Voiceover: a little awkward, gap lock will be introduced in the next article, for the time being, it is a lock implemented on the index to lock a certain range of the index.

The way it is played is:

When multiple transactions insert records in the same index and in the same range, if the inserted positions do not conflict, they will not block each other.

Voiceover: the official website says

Insert Intention Lock signals the intent to insertin such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap.

In this way, the previous example of digging a hole can be answered.

Under MySQL,InnoDB,RR:

T (id unique Competition, name)

There is data in the data table:

10, shenjian

20, zhangsan

30, lisi

Transaction An executes first, inserts a row in 10 and 20 records, and has not yet committed:

Insert into t values (11, xxx)

Executed after transaction B, and a row was also inserted into the 10 and 20 records:

Insert into t values (12, ooo)

(1) what locks will be used?

(2) will transaction B be blocked?

Answer: although the transaction isolation level is RR, although it is the same index, although it is the same interval, the inserted records do not conflict, so here:

The insert intention lock is used

Does not block transaction B

Summary of ideas

(1) InnoDB uses shared locks to improve the concurrency of reading.

(2) in order to ensure strong consistency of data, InnoDB uses strong mutexes to ensure serialization of modification and deletion of records on the same line.

(3) InnoDB uses insert intention locks to improve insertion concurrency.

End

What would be the result if it was not insertion concurrency, but read-write concurrency?

MySQL,InnoDB, the default isolation level (RR).

T (id unique Competition, name)

There is data in the data table:

10, shenjian

20, zhangsan

30, lisi

Transaction An executes first, queries some records, but has not yet committed:

Select * from t where id > 10

Executed after transaction B, inserting a row in 10 and 20 records:

Insert into t values (11, xxx)

The above is all the content of the article "how to use intention Lock in InnoDB". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.