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

This article is about the sample analysis of compound primary keys and multi-column indexes when Mysql row locks are encountered. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Background

In cooperation with other project teams to do the system pressure test, there is an occasional deadlock problem in the process. After analyzing the code, it is found that there is a update with compound primary key. Only one field update is used to update the compound primary key table, and at the same time, there is an insert operation on the table within the transaction, resulting in occasional deadlock problems.

For example, there are two primary keys in the table t_lock_test that are both primary key (aformab), but are updated through update t_lock_test.. Where a =?, and then there is insert into t_lock_test values (...) in the transaction.

The locking algorithm in InnoDB is Next-Key Locking, which is probably due to the deadlock caused by this point, but will Next-Key Locking start under the compound primary key, and will Next-Key Locking be triggered under the multi-column federated unique index? there is no answer in the book, so we have to test it in practice.

Locks in InnoDB

Lock is a key feature that distinguishes database system from file system. The locking mechanism is used to manage concurrent access to shared resources [illustration]. It's nice that the InnoDB storage engine locks table data at the row level. However, the InnoDB storage engine also uses locks in many other places within the database, allowing concurrent access to many different resources. For example, to manipulate the LRU list in the buffer pool, delete, add, and move elements in the LRU list, in order to ensure consistency, there must be lock intervention. Database systems use locks to support concurrent access to shared resources and provide data integrity and consistency.

Since locks are mostly used under the InnoDB storage engine, skip MyISAM and talk about InnoDB directly.

Lock type

The InnoDB storage engine implements the following two standard row-level locks:

A shared lock (S Lock) that allows a transaction to read a row of data

Exclusive lock (x lOCK) that allows a transaction to delete or update a piece of data

If one transaction T1 has acquired the shared lock of r, then another transaction T2 can immediately acquire the shared lock of row r because reading does not change the data of r, which is called lock compatibility (Lock Compatible). However, if another transaction T3 box acquires an exclusive lock on row r, such as waiting for T1 and T2 to release the shared lock on row r-this is called lock incompatibility.

Compatibility of exclusive locks and shared locks:

\ XSX incompatible S incompatible

Update operations on data in InnoDB will produce row locks, and you can also add row locks as shown (that is, the so-called "pessimistic lock").

Select for update

Locking algorithm

InnoDB has three algorithms for row locking, which are:

Record Lock: a lock on a single row record, which is literally a row lock

Record Lock will lock the index record (note that the index here is the index, because the primary key index under InnoDB is the data). When the ruguo InnoDB storage engine table is created, no index is set, so the InnoDB storage engine will use the hermit's primary key to lock.

Gap Lock: a gap lock that locks a range but does not include the record itself

Next-Key Lock:Gap Lock+Record Lock, lock a range, and lock the record itself

The lock-in interval division principles of Gap Lock and Next-Key Lock are the same.

For example, if an index has four values of 10-11-13 and 20, the interval of the index is:

(- ∞, 10)

(10pr 11)

(11pr 13)

(13pr 20)

(20dint + ∞]

The locking technology that uses Next-Key Lock is called Next-Key Locking. It is designed to address Phantom Problem, which is described in the next section. Using this locking technology, the lock is not a single value, but a range, which is an improvement of predicate lock (predict lock).

When the index of a query contains a unique (unique) attribute (primary key index, unique index) the InnoDB storage engine optimizes Next-Key Lock by downgrading it to Record Lock, that is, locking only the index itself, not the range.

Let's take a look at an example of a lock under a secondary index (non-unique index):

CREATE TABLE z (an INT, b INT, PRIMARY KEY (a), KEY (b)); INSERT INTO z SELECT 1 INTO z SELECT 1 to insert INTO z SELECT 3 to insert INTO z SELECT 5 to insert INTO z SELECT 7 to insert INTO z SELECT 10 to 8

Column b of table z is the secondary index, if executed in transaction A.

SELECT * FROM z WHERE baked 3 FOR UPDATE

Because column b is a secondary index, the Next-Key Locking algorithm is used at this time, and the scope of the lock is (1p3). In particular, InnoDB also adds Gap Lock to the next value of the secondary index, that is, there is a lock with a secondary index range of (3mem6). Therefore, if you run the following SQL in new transaction B, it will be blocked:

1. SELECT * FROM z WHERE a = 5 LOCK IN SHARE MODE;//S Lock 2. INSERT INTO z SELECT 4 INSERT INTO z SELECT 2 Ting 3. INSERT INTO z SELECT 6 Lock 5

The first SQL cannot be executed because the SQL executed in transaction A has put an X lock on the value of column axi5 in the clustered index, so execution is blocked.

The second SQL, the primary key inserts 4, is fine, but the inserted secondary index value 2 is in the locked range (1d3), so execution will also be blocked.

The third SQL, the inserted primary key 6 is not locked, and 5 is not in range (1). But the inserted b-column value of 5 is in the next Gap Lock range (3J6), so you also need to wait.

The following SQL statement, which is not within the scope of Next-Key Lock and Gap Lock, will not be blocked and can be executed immediately:

INSERT INTO z SELECT 8 ~ 6 ~ insert INTO z SELECT 2 ~ 0 ~ insert INTO z SELECT 6 ~ 7

As you can see from the above example, the purpose of Gap Lock is to organize multiple transactions to insert data into a unified scope, which can lead to Phantom Problem. In the example, transaction A has locked the record of bread3. If there is no Gap Lock lock at this time, other transactions can insert records with index b in column 3, which will cause users in transaction A to execute the same query again and return different records, which leads to phantom reading problems.

Users can also turn off Gap Lock by displaying it in two ways (but not recommended):

Set the isolation level of the transaction to READ COMMITED

Set the parameter innodb_locks_unsafe_for_binlog to 1

In InnoDB, for Insert operations, the next record inserted into the record is checked to see if it is locked, and insertion is not allowed if it is already locked. For the above example, transaction A has locked the record of bread3 in table z, that is, the range (1d3) has been locked, and performing the following inserts in other transactions will also cause blocking:

INSERT INTO z SELECT 2,0

Because when a record with a value of 2 is inserted into the secondary index column b, it will detect that the next record 3 has been indexed, and after modifying the value of column b, it can be executed

INSERT INTO z SELECT 2,0

Fantasy reading (Phantom Problem)

Phantom reading means that under the same transaction, executing the same SQL statement twice in a row may result in different results, and the second SQL may return rows that did not exist before.

Under the default transaction isolation level (REPEATABLE READ), the InnoDB storage engine uses the Next-Key Locking mechanism to avoid phantom reading problems.

Compound primary key and lock

The above lock mechanism (from "Mysql Technology Insider InnoDB Storage engine version 2") is only for secondary and clustered indexes, so what is the manifestation of compound primary key downlink locks? I can't find the answer from the book. Let's actually test it.

First create a table with a compound primary key

CREATE TABLE `composite_primary_lock_ test` (`id1` int, `id2` int) NOT NULL, PRIMARY KEY (`id1`, `id2`) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;INSERT INTO `composite_primary_lock_ test` (`id1`, `id2`) VALUES (10,10); INSERT INTO `composite_primary_lock_ test` (`id1`, `id2`) VALUES (1,8); INSERT INTO `composite_primary_lock_ test` (`id1`, `id2`) VALUES (3,6); INSERT INTO `composite_primary_lock_ test` (`id1`, `id2`) VALUES (5,6) INSERT INTO `composite_primary_lock_ test` (`id1`, `id2`) VALUES (3,3); INSERT INTO `composite_primary_lock_ test` (`id1`, `id2`) VALUES (1,1); INSERT INTO `composite_primary_lock_ test` (`id1`, `id2`) VALUES (5,1); INSERT INTO `composite_primary_lock_ test` (`id1`, `id2`) VALUES (7,1)

Transaction A first queries the columns of id2=6 and adds row locks

Select * from composite_primary_lock_test where id2 = 6 lock in share mode

Will the lock be degraded to Record Lock at this time? Transaction B Update data within the scope of a Next-Key Lock (id1=1,id2=8) to prove:

UPDATE `composite_primary_lock_ test` SE WHERE `id1` = 1 AND `id2` = 8

The result is that UPDATE is blocked, so try adding the lock with both primary keys in the where:

Select * from composite_primary_lock_test where id2 = 6 and id1 = 5 lock in share mode

Execute UPDATE

UPDATE `composite_primary_lock_ test` SE WHERE `id1` = 1 AND `id2` = 8

As a result, UPDATE is not blocked.

There is more than one locked id2=6 data above, so try to lock the unique data id2=8 based on only one primary key, will it be downgraded to a row-level lock:

Select * from composite_primary_lock_test where id2 = 8 lock in share mode;UPDATE `composite_primary_lock_ test` SE WHERE `id1` = 12 AND `id2` = 10

As a result, it was also blocked, and the experiment proved that:

Under the compound primary key, if all the primary keys are not locked, InnoDB will use the Next-Key Locking algorithm. If all the primary keys are taken, it will be treated as a unique index, downgraded to Record Lock, and only the current record will be locked.

Multi-column index (federated index) and lock

The above only validates the locking mechanism under the compound primary key, so will the multi-column index be the same as the compound index mechanism? What about multi-column unique indexes?

Create a new test table and initialize the data

CREATE TABLE `multiple_idx_lock_ test` (`id` int, `idx1` int) NOT NULL, `idx2` int (255) DEFAULT NULL, PRIMARY KEY (`id`, `idx1`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;ALTER TABLE `multiple_idx_lock_ test` ADD UNIQUE INDEX `idx_ multi` (`idx1`, `idx2`) USING BTREE;INSERT INTO `multiple_idx_lock_ test` (`id`, `idx1`, `idx2`) VALUES (1,1,1); INSERT INTO `multiple_idx_lock_ test` (`id`, `idx1`, `idx2`) VALUES (5,2,2) INSERT INTO `multiple_idx_lock_ test` (`id`, `idx1`, `idx2`) VALUES (7,3,3); INSERT INTO `multiple_idx_lock_ test` (`id`, `idx1`, `idx2`) VALUES (4,4,4); INSERT INTO `multiple_idx_lock_ test` (`id`, `idx1`, `idx2`) VALUES (2,4,5); INSERT INTO `multiple_idx_lock_ test` (`id`, `idx1`, `idx2`) VALUES (3, 5, 5); INSERT INTO `multiple_idx_lock_ test` (`id`, `idx1`, `idx2`) VALUES (8, 6, 5) INSERT INTO `multiple_idx_lock_ test` (`id`, `idx1`, `idx2`) VALUES (6,6,6)

Transaction A query adds S locks, using only the idx1 column, and follows the leftmost principle:

Select * from multiple_idx_lock_test where idx1 = 6 lock in share mode

Now insert a piece of data in the Next-Key Lock range:

INSERT INTO `multiple_idx_lock_ test` (`id`, `idx1`, `idx2`) VALUES (9, 6, 7)

The result is blocked, try again to lock through all the fields in the multi-column index:

Select * from multiple_idx_lock_test where idx1 = 6 and idx2 = 6 lock in share mode

Insert a piece of data within the range of Next-Key Lock:

INSERT INTO `multiple_idx_lock_ test` (`id`, `idx1`, `idx2`) VALUES (9, 6, 7)

As a result, it was not blocked.

Thus, when using a multi-column unique index, locking needs to be clear about the rows to be locked (that is, all columns that use the index when locking) before InnoDB considers the record to be a unique value and the lock is degraded to Record Lock. Otherwise, the Next-Key Lock algorithm will be used to lock the data in the range.

Thank you for reading! On "when the Mysql row lock encountered composite primary key and multi-column index example analysis" this article is shared here, I hope the above content can be of some help to you, so that you can learn more knowledge, if you think the article is good, you can share it out for more people to see it!

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.