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

Today, I will talk to you about the research on how to conduct innodb transaction locks. Many people may not know much about it. In order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

1. The select * for update statement adds an exclusive line lock.

2. Select... From table_name where... The for update statement adds an exclusive row lock on the primary key when the row plan uses index constant lookups or index range scans (in the case of index override queries).

3. Select.. When the for update statement uses a full index scan, exclusive row locks are also added to all records of the primary key if an override index is used.

4. When the update statement execution plan uses an index constant lookup or an index range scan, an exclusive row lock is added on the record corresponding to the primary key in addition to adding an exclusive row lock on the secondary index (even if an overridden index is used).

5.When the update statement execution plan uses a full scan of the secondary index, adding exclusive row locks to all records of the secondary index will also add exclusive row locks on all records of the primary key (even if an overridden index is used).

6. Test whether there is a gap lock if the secondary index is a unique index

Prepare test data:

CREATE TABLE T5 (

An int (11) NOT NULL

B int not null

C int not null

PRIMARY KEY (`a`)

UNIQUE key (b)

UNIQUE key (c)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

Insert into T5 values (1pm 1pm 1)

Insert into T5 values (2Jing 2jue 2)

Insert into T5 values (3pm 3pm 3)

Insert into T5 values (4pm 4pm 4)

Insert into T5 values (5pm 5pm 5)

Insert into T5 values (6, 6, 6)

Insert into T5 values (7, 7 and 7)

Mysql > select * from T5

+-- +

| | a | b | c | |

+-- +

| | 1 | 1 | 1 |

| | 2 | 2 | 2 |

| | 3 | 3 | 3 |

| | 4 | 4 | 4 |

| | 5 | 5 | 5 |

| | 6 | 6 | 6 |

| | 7 | 7 | 7 |

+-- +

7 rows in set (0.00 sec)

1. The select * for update statement adds an exclusive line lock.

-- SESSION 1

Mysql > select @ @ global.tx_isolation,@@tx_isolation

+-+ +

| | @ @ global.tx_isolation | @ @ tx_isolation |

+-+ +

| | REPEATABLE-READ | REPEATABLE-READ |

+-+ +

1 row in set (0.00 sec)

Set session innodb_lock_wait_timeout=1000000

-- session 2

Mysql > select @ @ global.tx_isolation,@@tx_isolation

+-+ +

| | @ @ global.tx_isolation | @ @ tx_isolation |

+-+ +

| | REPEATABLE-READ | REPEATABLE-READ |

+-+ +

1 row in set (0.00 sec)

Mysql > set session innodb_lock_wait_timeout=1000000

Query OK, 0 rows affected (0.00 sec)

-- SESSION 1

Mysql > begin

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from T3 where axi5 for update

+-- +

| | a |

+-- +

| | 5 |

+-- +

1 row in set (0.00 sec)

-- SESSION 2

Mysql > begin

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from T3 where astat5 for update;-blocked

-- SESSION 3 View Lock Information

Mysql > select * from innodb_locks\ G

* * 1. Row *

Lock_id: 324675084:253:3:6

Lock_trx_id: 324675084

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t3`

Lock_index: PRIMARY

Lock_space: 253

Lock_page: 3

Lock_rec: 6

Lock_data: 5

* 2. Row * *

Lock_id: 324675083:253:3:6

Lock_trx_id: 324675083

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t3`

Lock_index: PRIMARY

Lock_space: 253

Lock_page: 3

Lock_rec: 6

Lock_data: 5

2 rows in set (0.00 sec)

Conclusion:

Through the experiment, we see that select * from T3 where adep5 for update is added to the exclusive line lock.

2. Select... From table_name where... The for update statement adds an exclusive row lock on the primary key when the row plan uses index constant lookups or index range scans (in the case of index override queries).

Mysql > explain select b from t5 where baked 5 for update\ G

* * 1. Row *

Id: 1

Select_type: SIMPLE

Table: t5

Type: const

Possible_keys: b

Key: b

Key_len: 4

Ref: const

Rows: 1

Extra: NULL

1 row in set (0.00 sec)

The execution plan uses an index constant lookup.

-- SESSION 1

Mysql > begin

Mysql > select b from t5 where baked 5 for update

+-- +

| | b |

+-- +

| | 5 |

+-- +

1 row in set (0.00 sec)

-- SESSION 2

Mysql > select c from T5 where Centr5 for update;-blocked

-- SESSION 3 View Lock Information

SELECT r.trx_id AS waiting_trx_id, r.`trx _ mysql_thread_ id` AS waiting_thread

TIMESTAMPDIFF (SECOND,r.trx_wait_started,CURRENT_TIMESTAMP) AS wait_time

R.`trx _ query`AS waiting_query, l.`lock _ table`AS waiting_table_lock

L.lock_index AS waiting_index_lock

B.trx_id AS blocking_trx_id, b.`trx _ mysql_thread_ id` AS blocking_thread

SUBSTRING (p. Hostdome 1) AS blocking_host INSTR (p. Host1')

SUBSTRING (p. Hostmending INSTR (p.hostdint') + 1) AS blocking_port

IF (p.command = "Sleep", p.timejin0) AS idle_in_trx

B.`trx _ query`AS blocking_query

FROM information_ schema.`INNODB _ LOCK_ WAITS` AS w

INNER JOIN information_ schema.`INNODB _ TRX` AS b ON b.trx_id=w.blocking_trx_id

INNER JOIN information_ schema.`INNODB _ TRX` AS r ON r.trx_id = w.requesting_trx_id

INNER JOIN information_ schema.`INNODB _ LOCKS` AS l ON w.requested_lock_id=l.lock_id

LEFT JOIN information_ schema.`PROCESSLIST` AS p ON p.id=b.trx_mysql_thread_id

ORDER BY wait_time DESC\ G

* * 1. Row *

Waiting_trx_id: 324675156

Waiting_thread: 2

Wait_time: 77

Waiting_query: select c from T5 where Centro 5 for update

Waiting_table_lock: `test`.`t5`

Waiting_index_lock: PRIMARY

Blocking_trx_id: 324675155

Blocking_thread: 1

Blocking_host:

Blocking_port: localhost

Idle_in_trx: 150

Blocking_query: NULL

1 row in set (0.00 sec)

Mysql > select * from innodb_locks\ G

* * 1. Row *

Lock_id: 324675156:255:3:6

Lock_trx_id: 324675156

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t5`

Lock_index: PRIMARY

Lock_space: 255

Lock_page: 3

Lock_rec: 6

Lock_data: 5

* 2. Row * *

Lock_id: 324675155:255:3:6

Lock_trx_id: 324675155

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t5`

Lock_index: PRIMARY

Lock_space: 255

Lock_page: 3

Lock_rec: 6

Lock_data: 5

2 rows in set (0.00 sec)

Roll back transactions for SESSION1 and SESSION 2

-- SESSINO 1

Mysql > begin

Query OK, 0 rows affected (0.00 sec)

Mysql > select b from t5 where baked 5 for update

+-- +

| | b |

+-- +

| | 5 |

+-- +

1 row in set (0.00 sec)

-- SESSION2

Mysql > begin

Query OK, 0 rows affected (0.00 sec)

Mysql > select b from T5 where baggage 5 for update;-blocked

-- SESSION 3 View Lock Information

SELECT r.trx_id AS waiting_trx_id, r.`trx _ mysql_thread_ id` AS waiting_thread

TIMESTAMPDIFF (SECOND,r.trx_wait_started,CURRENT_TIMESTAMP) AS wait_time

R.`trx _ query`AS waiting_query, l.`lock _ table`AS waiting_table_lock

L.lock_index AS waiting_index_lock

B.trx_id AS blocking_trx_id, b.`trx _ mysql_thread_ id` AS blocking_thread

SUBSTRING (p. Hostdome 1) AS blocking_host INSTR (p. Host1')

SUBSTRING (p. Hostmending INSTR (p.hostdint') + 1) AS blocking_port

IF (p.command = "Sleep", p.timejin0) AS idle_in_trx

B.`trx _ query`AS blocking_query

FROM information_ schema.`INNODB _ LOCK_ WAITS` AS w

INNER JOIN information_ schema.`INNODB _ TRX` AS b ON b.trx_id=w.blocking_trx_id

INNER JOIN information_ schema.`INNODB _ TRX` AS r ON r.trx_id = w.requesting_trx_id

INNER JOIN information_ schema.`INNODB _ LOCKS` AS l ON w.requested_lock_id=l.lock_id

LEFT JOIN information_ schema.`PROCESSLIST` AS p ON p.id=b.trx_mysql_thread_id

ORDER BY wait_time DESC\ G

* * 1. Row *

Waiting_trx_id: 324675159

Waiting_thread: 2

Wait_time: 8

Waiting_query: select b from T5 where baked 5 for update

Waiting_table_lock: `test`.`t5`

Waiting_index_lock: b

Blocking_trx_id: 324675158

Blocking_thread: 1

Blocking_host:

Blocking_port: localhost

Idle_in_trx: 21

Blocking_query: NULL

1 row in set (0.00 sec)

Mysql > select * from innodb_locks\ G

* * 1. Row *

Lock_id: 324675159:255:4:6

Lock_trx_id: 324675159

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t5`

Lock_index: b

Lock_space: 255

Lock_page: 4

Lock_rec: 6

Lock_data: 5

* 2. Row * *

Lock_id: 324675158:255:4:6

Lock_trx_id: 324675158

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t5`

Lock_index: b

Lock_space: 255

Lock_page: 4

Lock_rec: 6

Lock_data: 5

2 rows in set (0.00 sec)

We see that the SQL statement select b from T5 where bounded 5 for update adds an exclusive row lock on the key 5 of the secondary index b.

From the above two examples, we can see that the execution plan of the SQL executed by SESSION 1 uses index constant lookups, and the SQL will only add exclusive row locks on the records of the secondary index bread5.

At the same time, an exclusive row lock is added to the record of the record corresponding to the primary key (adep5).

3. Select.. When the for update statement uses a full index scan, when an override index is used, exclusive locks are added to all index entries of the secondary index and exclusive row locks are added to all records of the primary key.

Mysql > explain select b from T5 for update\ G

* * 1. Row *

Id: 1

Select_type: SIMPLE

Table: t5

Type: index

Possible_keys: NULL

Key: b

Key_len: 4

Ref: NULL

Rows: 7

Extra: Using index

1 row in set (0.00 sec)

Mysql > explain select C from T5 for update\ G

* * 1. Row *

Id: 1

Select_type: SIMPLE

Table: t5

Type: index

Possible_keys: NULL

Key: c

Key_len: 4

Ref: NULL

Rows: 7

Extra: Using index

1 row in set (0.00 sec)

The execution plans of the above two SQL use an overlay index to perform an index full scan.

-- SESSION 1

Mysql > begin

Query OK, 0 rows affected (0.00 sec)

Mysql > select b from T5 for update

+-- +

| | b |

+-- +

| | 1 |

| | 2 |

| | 3 |

| | 4 |

| | 5 |

| | 6 |

| | 7 |

+-- +

-- SESSION 2

Mysql > begin

Query OK, 0 rows affected (0.00 sec)

Mysql > select b from T5 for update;-blocked

-- SESSION 3 View Lock Information

SELECT r.trx_id AS waiting_trx_id, r.`trx _ mysql_thread_ id` AS waiting_thread

TIMESTAMPDIFF (SECOND,r.trx_wait_started,CURRENT_TIMESTAMP) AS wait_time

R.`trx _ query`AS waiting_query, l.`lock _ table`AS waiting_table_lock

L.lock_index AS waiting_index_lock

B.trx_id AS blocking_trx_id, b.`trx _ mysql_thread_ id` AS blocking_thread

SUBSTRING (p. Hostdome 1) AS blocking_host INSTR (p. Host1')

SUBSTRING (p. Hostmending INSTR (p.hostdint') + 1) AS blocking_port

IF (p.command = "Sleep", p.timejin0) AS idle_in_trx

B.`trx _ query`AS blocking_query

FROM information_ schema.`INNODB _ LOCK_ WAITS` AS w

INNER JOIN information_ schema.`INNODB _ TRX` AS b ON b.trx_id=w.blocking_trx_id

INNER JOIN information_ schema.`INNODB _ TRX` AS r ON r.trx_id = w.requesting_trx_id

INNER JOIN information_ schema.`INNODB _ LOCKS` AS l ON w.requested_lock_id=l.lock_id

LEFT JOIN information_ schema.`PROCESSLIST` AS p ON p.id=b.trx_mysql_thread_id

ORDER BY wait_time DESC\ G

* * 1. Row *

Waiting_trx_id: 324675162

Waiting_thread: 2

Wait_time: 19

Waiting_query: select b from t5 for update

Waiting_table_lock: `test`.`t5`

Waiting_index_lock: b

Blocking_trx_id: 324675161

Blocking_thread: 1

Blocking_host:

Blocking_port: localhost

Idle_in_trx: 29

Blocking_query: NULL

1 row in set (0.00 sec)

Mysql > select * from innodb_locks\ G

* * 1. Row *

Lock_id: 324675162:255:4:2

Lock_trx_id: 324675162

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t5`

Lock_index: b

Lock_space: 255

Lock_page: 4

Lock_rec: 2

Lock_data: 1

* 2. Row * *

Lock_id: 324675161:255:4:2

Lock_trx_id: 324675161

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t5`

Lock_index: b

Lock_space: 255

Lock_page: 4

Lock_rec: 2

Lock_data: 1

2 rows in set (0.00 sec)

SESSION 2 is blocked on an index entry with an index health value of 1 on secondary index b.

SESSION 1 and SESSION 2 roll back transactions

-- SESSINO 1

Mysql > begin

Query OK, 0 rows affected (0.00 sec)

Mysql > select b from T5 for update

+-- +

| | b |

+-- +

| | 1 |

| | 2 |

| | 3 |

| | 4 |

| | 5 |

| | 6 |

| | 7 |

+-- +

7 rows in set (0.00 sec)

-- SESSION 2

Mysql > begin

Query OK, 0 rows affected (0.00 sec)

Mysql > select b from T5 where baggage 7 for update;-blocked

-- SESSION 3 View Lock Information

SELECT r.trx_id AS waiting_trx_id, r.`trx _ mysql_thread_ id` AS waiting_thread

TIMESTAMPDIFF (SECOND,r.trx_wait_started,CURRENT_TIMESTAMP) AS wait_time

R.`trx _ query`AS waiting_query, l.`lock _ table`AS waiting_table_lock

L.lock_index AS waiting_index_lock

B.trx_id AS blocking_trx_id, b.`trx _ mysql_thread_ id` AS blocking_thread

SUBSTRING (p. Hostdome 1) AS blocking_host INSTR (p. Host1')

SUBSTRING (p. Hostmending INSTR (p.hostdint') + 1) AS blocking_port

IF (p.command = "Sleep", p.timejin0) AS idle_in_trx

B.`trx _ query`AS blocking_query

FROM information_ schema.`INNODB _ LOCK_ WAITS` AS w

INNER JOIN information_ schema.`INNODB _ TRX` AS b ON b.trx_id=w.blocking_trx_id

INNER JOIN information_ schema.`INNODB _ TRX` AS r ON r.trx_id = w.requesting_trx_id

INNER JOIN information_ schema.`INNODB _ LOCKS` AS l ON w.requested_lock_id=l.lock_id

LEFT JOIN information_ schema.`PROCESSLIST` AS p ON p.id=b.trx_mysql_thread_id

ORDER BY wait_time DESC\ G

* * 1. Row *

Waiting_trx_id: 324675164

Waiting_thread: 2

Wait_time: 41

Waiting_query: select b from T5 where bust 7 for update

Waiting_table_lock: `test`.`t5`

Waiting_index_lock: b

Blocking_trx_id: 324675163

Blocking_thread: 1

Blocking_host:

Blocking_port: localhost

Idle_in_trx: 57

Blocking_query: NULL

1 row in set (0.00 sec)

Mysql > select * from innodb_locks\ G

* * 1. Row *

Lock_id: 324675164:255:4:8

Lock_trx_id: 324675164

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t5`

Lock_index: b

Lock_space: 255

Lock_page: 4

Lock_rec: 8

Lock_data: 7

* 2. Row * *

Lock_id: 324675163:255:4:8

Lock_trx_id: 324675163

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t5`

Lock_index: b

Lock_space: 255

Lock_page: 4

Lock_rec: 8

Lock_data: 7

2 rows in set (0.00 sec)

SESSION 2 is blocked on an index entry with an index health value of 7 on secondary index b.

Combined with the situation that SESSION 2 is blocked on the index item with an index health value of 1 of the secondary index b, the SQL of select b from T5 for update can be determined.

Exclusive row locks have been added to all index entries in secondary index b.

SESSION 1 and SESSION 2 roll back the transaction.

-- SESSION 1

Mysql > begin

Query OK, 0 rows affected (0.00 sec)

Mysql > select b from T5 for update

+-- +

| | b |

+-- +

| | 1 |

| | 2 |

| | 3 |

| | 4 |

| | 5 |

| | 6 |

| | 7 |

+-- +

7 rows in set (0.00 sec)

-- SESSION 2

Mysql > begin

Query OK, 0 rows affected (0.00 sec)

Mysql > select c from T5 for update;-blocked

-- SESSION 3 View Lock Information

SELECT r.trx_id AS waiting_trx_id, r.`trx _ mysql_thread_ id` AS waiting_thread

TIMESTAMPDIFF (SECOND,r.trx_wait_started,CURRENT_TIMESTAMP) AS wait_time

R.`trx _ query`AS waiting_query, l.`lock _ table`AS waiting_table_lock

L.lock_index AS waiting_index_lock

B.trx_id AS blocking_trx_id, b.`trx _ mysql_thread_ id` AS blocking_thread

SUBSTRING (p. Hostdome 1) AS blocking_host INSTR (p. Host1')

SUBSTRING (p. Hostmending INSTR (p.hostdint') + 1) AS blocking_port

IF (p.command = "Sleep", p.timejin0) AS idle_in_trx

B.`trx _ query`AS blocking_query

FROM information_ schema.`INNODB _ LOCK_ WAITS` AS w

INNER JOIN information_ schema.`INNODB _ TRX` AS b ON b.trx_id=w.blocking_trx_id

INNER JOIN information_ schema.`INNODB _ TRX` AS r ON r.trx_id = w.requesting_trx_id

INNER JOIN information_ schema.`INNODB _ LOCKS` AS l ON w.requested_lock_id=l.lock_id

LEFT JOIN information_ schema.`PROCESSLIST` AS p ON p.id=b.trx_mysql_thread_id

ORDER BY wait_time DESC\ G

* * 1. Row *

Waiting_trx_id: 324675166

Waiting_thread: 2

Wait_time: 48

Waiting_query: select c from t5 for update

Waiting_table_lock: `test`.`t5`

Waiting_index_lock: PRIMARY

Blocking_trx_id: 324675165

Blocking_thread: 1

Blocking_host:

Blocking_port: localhost

Idle_in_trx: 65

Blocking_query: NULL

1 row in set (0.00 sec)

Mysql > select * from innodb_locks\ G

* * 1. Row *

Lock_id: 324675166:255:3:2

Lock_trx_id: 324675166

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t5`

Lock_index: PRIMARY

Lock_space: 255

Lock_page: 3

Lock_rec: 2

Lock_data: 1

* 2. Row * *

Lock_id: 324675165:255:3:2

Lock_trx_id: 324675165

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t5`

Lock_index: PRIMARY

Lock_space: 255

Lock_page: 3

Lock_rec: 2

Lock_data: 1

2 rows in set (0.00 sec)

SESSION 2 is blocked on an index entry with a primary key health value of 1.

SESSION 1 and SESSION 2 roll back the transaction.

-- SESSION 1

Mysql > begin

Query OK, 0 rows affected (0.00 sec)

Mysql > select b from T5 for update

+-- +

| | b |

+-- +

| | 1 |

| | 2 |

| | 3 |

| | 4 |

| | 5 |

| | 6 |

| | 7 |

+-- +

7 rows in set (0.00 sec)

-- SESSION 2

Mysql > begin

Query OK, 0 rows affected (0.00 sec)

Mysql > select c from T5 where Centr7 for update;-blocked

-- SESSION 3 View Lock Information

SELECT r.trx_id AS waiting_trx_id, r.`trx _ mysql_thread_ id` AS waiting_thread

TIMESTAMPDIFF (SECOND,r.trx_wait_started,CURRENT_TIMESTAMP) AS wait_time

R.`trx _ query`AS waiting_query, l.`lock _ table`AS waiting_table_lock

L.lock_index AS waiting_index_lock

B.trx_id AS blocking_trx_id, b.`trx _ mysql_thread_ id` AS blocking_thread

SUBSTRING (p. Hostdome 1) AS blocking_host INSTR (p. Host1')

SUBSTRING (p. Hostmending INSTR (p.hostdint') + 1) AS blocking_port

IF (p.command = "Sleep", p.timejin0) AS idle_in_trx

B.`trx _ query`AS blocking_query

FROM information_ schema.`INNODB _ LOCK_ WAITS` AS w

INNER JOIN information_ schema.`INNODB _ TRX` AS b ON b.trx_id=w.blocking_trx_id

INNER JOIN information_ schema.`INNODB _ TRX` AS r ON r.trx_id = w.requesting_trx_id

INNER JOIN information_ schema.`INNODB _ LOCKS` AS l ON w.requested_lock_id=l.lock_id

LEFT JOIN information_ schema.`PROCESSLIST` AS p ON p.id=b.trx_mysql_thread_id

ORDER BY wait_time DESC\ G

* * 1. Row *

Waiting_trx_id: 324675168

Waiting_thread: 2

Wait_time: 44

Waiting_query: select c from T5 where Centr7 for update

Waiting_table_lock: `test`.`t5`

Waiting_index_lock: PRIMARY

Blocking_trx_id: 324675167

Blocking_thread: 1

Blocking_host:

Blocking_port: localhost

Idle_in_trx: 63

Blocking_query: NULL

1 row in set (0.00 sec)

Mysql >

Mysql > select * from innodb_locks\ G

* * 1. Row *

Lock_id: 324675168:255:3:8

Lock_trx_id: 324675168

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t5`

Lock_index: PRIMARY

Lock_space: 255

Lock_page: 3

Lock_rec: 8

Lock_data: 7

* 2. Row * *

Lock_id: 324675167:255:3:8

Lock_trx_id: 324675167

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t5`

Lock_index: PRIMARY

Lock_space: 255

Lock_page: 3

Lock_rec: 8

Lock_data: 7

2 rows in set (0.00 sec)

SESSION 2 is blocked on an index entry with a primary key health value of 7.

Combined with the situation that SESSION 2 is blocked on an index entry with a primary key index health value of 1, the SQL of select b from T5 for update can be determined.

Exclusive row locks have been added to all index entries of the primary key.

Combined with the SQL of select b from T5 for update, an exclusive row lock is added to all the index entries of the secondary index b to determine the select.. The for update statement uses a secondary index (override index)

When you perform an index full scan, exclusive row locks are added to all index entries of the secondary index and all index entries of the primary key.

4. When the update statement execution plan uses an index constant lookup or index range scan, in addition to adding an exclusive row lock to the index item corresponding to the secondary index, an exclusive row lock is added to the record corresponding to the primary key (even if an overridden index is used).

4.1 execution plan of SQL statement

Sql_1

Mysql > explain update T5 set baggage\ G

* * 1. Row *

Id: 1

Select_type: SIMPLE

Table: t5

Type: index

Possible_keys: NULL

Key: PRIMARY

Key_len: 4

Ref: NULL

Rows: 7

Extra: Using temporary

1 row in set (0.00 sec)

In the sql_1 execution plan, type:index means to scan the full table according to the index order, which has the advantage of avoiding sorting and the disadvantage of turning the continuous IO of the full table scan into a random IO.

Sql_2

Mysql > explain select b from T5\ G

* * 1. Row *

Id: 1

Select_type: SIMPLE

Table: t5

Type: index

Possible_keys: NULL

Key: b

Key_len: 4

Ref: NULL

Rows: 7

Extra: Using index

1 row in set (0.00 sec)

The SQL_2 execution plan uses an override index. Type: index, key: B, Extra: Using index use override index full scan.

SQL_3

Mysql > explain update T5 set Crunc\ G

* * 1. Row *

Id: 1

Select_type: SIMPLE

Table: t5

Type: index

Possible_keys: NULL

Key: PRIMARY

Key_len: 4

Ref: NULL

Rows: 7

Extra: Using temporary

1 row in set (0.00 sec)

In the SQL_3 execution plan, type:index means to scan the full table according to the index order, which has the advantage of avoiding sorting and the disadvantage of turning the continuous IO of the full table scan into a random IO.

SQL_4

Mysql > explain update T5 set baccalaureate where bundles 5\ G

* * 1. Row *

Id: 1

Select_type: SIMPLE

Table: t5

Type: range

Possible_keys: b

Key: b

Key_len: 4

Ref: const

Rows: 1

Extra: Using where

1 row in set (0.00 sec)

Although SQL_4 updates only one record, the execution plan does not use constant retrieval, but uses index range scanning.

SQL_5

Mysql > explain select b from t5 where baked 5 for update\ G

* * 1. Row *

Id: 1

Select_type: SIMPLE

Table: t5

Type: const

Possible_keys: b

Key: b

Key_len: 4

Ref: const

Rows: 1

Extra: NULL

1 row in set (0.00 sec)

SQL_5 is a SELECT statement equivalent to SQL_4, and SQL_5 uses constant retrieval, from which it is inferred that UPDATE

Sentences cannot be retrieved using constants. Even if UPDATE operates on only a row of records in the primary key, constant retrieval is not used.

SQL_6

Mysql > explain update T5 set Centrc where Cobb 5\ G

* * 1. Row *

Id: 1

Select_type: SIMPLE

Table: t5

Type: range

Possible_keys: c

Key: c

Key_len: 4

Ref: const

Rows: 1

Extra: Using where

1 row in set (0.00 sec)

The execution plan of SQL_6 is to scan the index range on the secondary index C.

SQL_7

Mysql > explain update t5 set breadb where b in (1p3)\ G

* * 1. Row *

Id: 1

Select_type: SIMPLE

Table: t5

Type: range

Possible_keys: b

Key: b

Key_len: 4

Ref: const

Rows: 2

Extra: Using where; Using temporary

1 row in set (0.00 sec)

SQL_7 scans the index range at the secondary index b and accesses two records to get the required data.

SQL_8

Mysql > explain update t5 set bimb where b in (1m 3pm 5)\ G

* * 1. Row *

Id: 1

Select_type: SIMPLE

Table: t5

Type: index

Possible_keys: b

Key: PRIMARY

Key_len: 4

Ref: NULL

Rows: 7

Extra: Using where; Using temporary

1 row in set (0.00 sec)

SQL_8 scans the whole table according to the index order. Its advantage is that it avoids sorting, and its disadvantage is that it turns the continuous IO of the full table scan into random IO.

4.2 Lock analysis

-- SESSION 1

Mysql > use test

Mysql > begin

Mysql > update T5 set Bronb where b in (1Pol. 3)

-- SESSION 2

Mysql > use test

Mysql > begin

Mysql > update T5 set Centrc where c in (1p3);-- blocked

-- SESSION 3

SELECT r.trx_id AS waiting_trx_id, r.`trx _ mysql_thread_ id` AS waiting_thread

TIMESTAMPDIFF (SECOND,r.trx_wait_started,CURRENT_TIMESTAMP) AS wait_time

R.`trx _ query`AS waiting_query, l.`lock _ table`AS waiting_table_lock

L.lock_index AS waiting_index_lock

B.trx_id AS blocking_trx_id, b.`trx _ mysql_thread_ id` AS blocking_thread

SUBSTRING (p. Hostdome 1) AS blocking_host INSTR (p. Host1')

SUBSTRING (p. Hostmending INSTR (p.hostdint') + 1) AS blocking_port

IF (p.command = "Sleep", p.timejin0) AS idle_in_trx

B.`trx _ query`AS blocking_query

FROM information_ schema.`INNODB _ LOCK_ WAITS` AS w

INNER JOIN information_ schema.`INNODB _ TRX` AS b ON b.trx_id=w.blocking_trx_id

INNER JOIN information_ schema.`INNODB _ TRX` AS r ON r.trx_id = w.requesting_trx_id

INNER JOIN information_ schema.`INNODB _ LOCKS` AS l ON w.requested_lock_id=l.lock_id

LEFT JOIN information_ schema.`PROCESSLIST` AS p ON p.id=b.trx_mysql_thread_id

ORDER BY wait_time DESC\ G

* * 1. Row *

Waiting_trx_id: 324675599-transaction ID of SESSION 2, transaction ID waiting for lock

Waiting_thread: 2-- MSYQL thread ID waiting for the lock

Wait_time: 30

Waiting_query: update T5 set Clearc where c in (1Pol 3)

Waiting_table_lock: `test`.`t5`

Waiting_index_lock: PRIMARY

Blocking_trx_id: 324675598-transaction ID of SESSION 1, transaction ID of holding lock

Blocking_thread: 1-- MYSQL thread ID that holds the lock

Blocking_host:

Blocking_port: localhost

Idle_in_trx: 52

Blocking_query: NULL

1 row in set (0.12 sec)

Mysql > select * from innodb_locks\ G

* * 1. Row *

Lock_id: 324675599:255:3:2

Lock_trx_id: 324675599

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t5`

Lock_index: PRIMARY

Lock_space: 255

Lock_page: 3

Lock_rec: 2

Lock_data: 1-- SESSION 2 is blocked on an index entry with a primary key value of 1

* 2. Row * *

Lock_id: 324675598:255:3:2

Lock_trx_id: 324675598

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t5`

Lock_index: PRIMARY

Lock_space: 255

Lock_page: 3

Lock_rec: 2

Lock_data: 1-- SESSION 1 holds an exclusive row lock on an index item with a primary key health value of 1

2 rows in set (0.00 sec)

-- SESSION 4

Mysql > begin

Mysql > update T5 set Centrc where Cobb 3;-blocked

-- SESSION 5

Mysql > begin

Query OK, 0 rows affected (0.00 sec)

Mysql > update T5 set baggage where baccalaure2

Query OK, 0 rows affected (0.00 sec)

Rows matched: 1 Changed: 0 Warnings: 0

The UPDATE statement of SESSION 5 is not blocked

-- SESSION 3 View Lock Information

SELECT r.trx_id AS waiting_trx_id, r.`trx _ mysql_thread_ id` AS waiting_thread

TIMESTAMPDIFF (SECOND,r.trx_wait_started,CURRENT_TIMESTAMP) AS wait_time

R.`trx _ query`AS waiting_query, l.`lock _ table`AS waiting_table_lock

L.lock_index AS waiting_index_lock

B.trx_id AS blocking_trx_id, b.`trx _ mysql_thread_ id` AS blocking_thread

SUBSTRING (p. Hostdome 1) AS blocking_host INSTR (p. Host1')

SUBSTRING (p. Hostmending INSTR (p.hostdint') + 1) AS blocking_port

IF (p.command = "Sleep", p.timejin0) AS idle_in_trx

B.`trx _ query`AS blocking_query

FROM information_ schema.`INNODB _ LOCK_ WAITS` AS w

INNER JOIN information_ schema.`INNODB _ TRX` AS b ON b.trx_id=w.blocking_trx_id

INNER JOIN information_ schema.`INNODB _ TRX` AS r ON r.trx_id = w.requesting_trx_id

INNER JOIN information_ schema.`INNODB _ LOCKS` AS l ON w.requested_lock_id=l.lock_id

LEFT JOIN information_ schema.`PROCESSLIST` AS p ON p.id=b.trx_mysql_thread_id

ORDER BY wait_time DESC\ G

* * 1. Row *

Waiting_trx_id: 324675599-transaction ID waiting for lock (transaction ID of SESSION 2)

Waiting_thread: 2-- MYSQL thread ID waiting for lock (MYSQL thread ID of SESSION 2)

Wait_time: 1081

Waiting_query: update T5 set Clearc where c in (1Pol 3)

Waiting_table_lock: `test`.`t5`

Waiting_index_lock: PRIMARY

Blocking_trx_id: 324675598-transaction ID that holds locks (transaction ID of SESSION 1)

Blocking_thread: 1-- MySQL thread ID that holds the lock (MSYQL thread ID of SESSION 1)

Blocking_host:

Blocking_port: localhost

Idle_in_trx: 1103

Blocking_query: NULL

* 2. Row * *

Waiting_trx_id: 324675601-transaction ID waiting for lock (transaction ID of SESSION 4)

Waiting_thread: 4-- MYSQL thread ID waiting for lock (MYSQL thread ID of SESSION 4)

Wait_time: 63

Waiting_query: update T5 set Centrc where Cobb 3

Waiting_table_lock: `test`.`t5`

Waiting_index_lock: PRIMARY

Blocking_trx_id: 324675598-transaction ID that holds locks (transaction ID of SESSION 1)

Blocking_thread: 1-- MySQL thread ID that holds the lock (MSYQL thread ID of SESSION 1)

Blocking_host:

Blocking_port: localhost

Idle_in_trx: 1103

Blocking_query: NULL

2 rows in set (0.01sec)

Mysql > select * from innodb_locks\ G

* * 1. Row *

Lock_id: 324675601:255:3:4

Lock_trx_id: 324675601

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t5`

Lock_index: PRIMARY

Lock_space: 255

Lock_page: 3

Lock_rec: 4

Lock_data: 3-- SESSION 4 is blocked on an index entry with a primary key value of 3

* 2. Row * *

Lock_id: 324675598:255:3:4

Lock_trx_id: 324675598

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t5`

Lock_index: PRIMARY

Lock_space: 255

Lock_page: 3

Lock_rec: 4

Lock_data: 3

* 3. Row * *

Lock_id: 324675599:255:3:2

Lock_trx_id: 324675599

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t5`

Lock_index: PRIMARY

Lock_space: 255

Lock_page: 3

Lock_rec: 2

Lock_data: 1-- SESSION 2 is blocked on an index entry with a primary key value of 1

* * 4. Row *

Lock_id: 324675598:255:3:2

Lock_trx_id: 324675598

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t5`

Lock_index: PRIMARY

Lock_space: 255

Lock_page: 3

Lock_rec: 2

Lock_data: 1

4 rows in set (0.00 sec)

Through the above tests, we can see that when the secondary index is a unique index, the SQL statement executes a scan for index access or

When retrieving constants, the transaction will only deal with the secondary index items that meet the WHERE sentence filtering criteria and the primary key index entries that meet the criteria.

Exclusive row locks are added on the. Index items that do not meet the filtering criteria will not be added.

5.When the update statement execution plan uses a full scan of the secondary index, adding exclusive row locks to all records of the secondary index will also add exclusive row locks on all records of the primary key (even if an overridden index is used).

5.1 SQL execution Plan

SQL_1

Mysql > explain update t5 set bimb where b in (1m 3pm 5)\ G

* * 1. Row *

Id: 1

Select_type: SIMPLE

Table: t5

Type: index

Possible_keys: b

Key: PRIMARY

Key_len: 4

Ref: NULL

Rows: 7

Extra: Using where; Using temporary

1 row in set (0.00 sec)

SQL_1 scans the whole table according to the index order. Its advantage is that it avoids sorting, and its disadvantage is that it turns the continuous IO of the full table scan into random IO. A full table scan in index order adds exclusive row locks on all primary key index entries

Because the primary key index page sub-leaf of INNODB is actually the data page of the table, exclusive row locks are added to all records in the table.

SQL_2

Mysql > explain update T5 set Centrc where c in (1p3)\ G

* * 1. Row *

Id: 1

Select_type: SIMPLE

Table: t5

Type: range

Possible_keys: c

Key: c

Key_len: 4

Ref: const

Rows: 2

Extra: Using where; Using temporary

1 row in set (0.00 sec)

The SQL_2 execution plan uses index range scans.

5.2 Lock analysis

-- SESSION 1

Mysql > begin

Query OK, 0 rows affected (0.00 sec)

Mysql > update T5 set baccarat where b in (1m 3pm 5)

Query OK, 0 rows affected (0.00 sec)

Rows matched: 3 Changed: 0 Warnings: 0

-- SESSION 2

Mysql > begin

Query OK, 0 rows affected (0.00 sec)

Mysql > update T5 set Centrc where c in (1p3);-- blocked

-- SESSION 4

Mysql > use test

Database changed

Mysql > begin

Query OK, 0 rows affected (0.00 sec)

Mysql > update T5 set Centrc where Cobb 7;-blocked

SESSION 1 executes SQL_1, which uses a full table scan in indexed order, adding exclusive row locks on all index entries of the primary key, thus blocking SESSION 4.

-- SESSIO 3 View Lock Information

SELECT r.trx_id AS waiting_trx_id, r.`trx _ mysql_thread_ id` AS waiting_thread

TIMESTAMPDIFF (SECOND,r.trx_wait_started,CURRENT_TIMESTAMP) AS wait_time

R.`trx _ query`AS waiting_query, l.`lock _ table`AS waiting_table_lock

L.lock_index AS waiting_index_lock

B.trx_id AS blocking_trx_id, b.`trx _ mysql_thread_ id` AS blocking_thread

SUBSTRING (p. Hostdome 1) AS blocking_host INSTR (p. Host1')

SUBSTRING (p. Hostmending INSTR (p.hostdint') + 1) AS blocking_port

IF (p.command = "Sleep", p.timejin0) AS idle_in_trx

B.`trx _ query`AS blocking_query

FROM information_ schema.`INNODB _ LOCK_ WAITS` AS w

INNER JOIN information_ schema.`INNODB _ TRX` AS b ON b.trx_id=w.blocking_trx_id

INNER JOIN information_ schema.`INNODB _ TRX` AS r ON r.trx_id = w.requesting_trx_id

INNER JOIN information_ schema.`INNODB _ LOCKS` AS l ON w.requested_lock_id=l.lock_id

LEFT JOIN information_ schema.`PROCESSLIST` AS p ON p.id=b.trx_mysql_thread_id

ORDER BY wait_time DESC\ G

* * 1. Row *

Waiting_trx_id: 324676114-transaction ID waiting for lock (transaction ID of SESSION 2)

Waiting_thread: 2-- MYSQL thread ID waiting for lock (MYSQL thread ID of SESSION 2)

Wait_time: 1212

Waiting_query: update T5 set Clearc where c in (1Pol 3)

Waiting_table_lock: `test`.`t5`

Waiting_index_lock: PRIMARY

Blocking_trx_id: 324676113-transaction ID that holds locks (transaction ID of SESSION 1)

Blocking_thread: 1-- MySQL thread ID that holds the lock (MSYQL thread ID of SESSION 1)

Blocking_host:

Blocking_port: localhost

Idle_in_trx: 1224

Blocking_query: NULL

* 2. Row * *

Waiting_trx_id: 324676115-transaction ID waiting for lock (transaction ID of SESSION 4)

Waiting_thread: 4-- MYSQL thread ID waiting for lock (MYSQL thread ID of SESSION 4)

Wait_time: 12

Waiting_query: update T5 set where 7

Waiting_table_lock: `test`.`t5`

Waiting_index_lock: PRIMARY

Blocking_trx_id: 324676113-transaction ID that holds locks (transaction ID of SESSION 1)

Blocking_thread: 1-- MySQL thread ID that holds the lock (MSYQL thread ID of SESSION 1)

Blocking_host:

Blocking_port: localhost

Idle_in_trx: 1224

Blocking_query: NULL

2 rows in set (0.00 sec)

We see that it is SESSION 1 that blocks SESSION 2 and SESSION 4.

Mysql > select * from innodb_locks\ G

* * 1. Row *

Lock_id: 324676115 ID 25515-- transaction ID of SESSION 4

Lock_trx_id: 324676115

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t5`

Lock_index: PRIMARY

Lock_space: 255

Lock_page: 3

Lock_rec: 8

Lock_data: 7-SESSION 4 is blocked on an index entry with a primary key value of 7

* 2. Row * *

Lock_id: 324676113:255:3:8

Lock_trx_id: 324676113

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t5`

Lock_index: PRIMARY

Lock_space: 255

Lock_page: 3

Lock_rec: 8

Lock_data: 7

* 3. Row * *

Lock_id: 324676114 ID 25515-- transaction ID of SESSION 2

Lock_trx_id: 324676114

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t5`

Lock_index: PRIMARY

Lock_space: 255

Lock_page: 3

Lock_rec: 2

Lock_data: 1-- SESSION 2 is blocked on an index entry with a primary key value of 1

* * 4. Row *

Lock_id: 324676113:255:3:2

Lock_trx_id: 324676113

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t5`

Lock_index: PRIMARY

Lock_space: 255

Lock_page: 3

Lock_rec: 2

Lock_data: 1

4 rows in set (0.00 sec)

Through the above test, it is proved that full table scanning in index order will add exclusive lines to all primary key index entries.

Lock, because the primary key index page sub-leaf of INNODB is actually the data page of the table, so it is all the records in the whole table.

An exclusive line lock has been added on the.

6. Test whether there is a gap lock if the secondary index is a unique index

6.1 View execution plan

Mysql > explain update T5 set bimb where b > 1 and b begin

Query OK, 0 rows affected (0.00 sec)

Mysql > update T5 set bimb where b > 1 and b begin

Mysql > update T5 set baggage where bail1;-- not blocked

Mysql > update T5 set Block4;-- blocked

Although the SQL statement of SESSION 1 does not need to update the record of bread4, an exclusive line lock is added to the index entry of bread4.

-- SESSION 4

Mysql > update T5 set baggage where bread5;-- not blocked

-- SESSION 3 View Lock Information

SELECT r.trx_id AS waiting_trx_id, r.`trx _ mysql_thread_ id` AS waiting_thread

TIMESTAMPDIFF (SECOND,r.trx_wait_started,CURRENT_TIMESTAMP) AS wait_time

R.`trx _ query`AS waiting_query, l.`lock _ table`AS waiting_table_lock

L.lock_index AS waiting_index_lock

B.trx_id AS blocking_trx_id, b.`trx _ mysql_thread_ id` AS blocking_thread

SUBSTRING (p. Hostdome 1) AS blocking_host INSTR (p. Host1')

SUBSTRING (p. Hostmending INSTR (p.hostdint') + 1) AS blocking_port

IF (p.command = "Sleep", p.timejin0) AS idle_in_trx

B.`trx _ query`AS blocking_query

FROM information_ schema.`INNODB _ LOCK_ WAITS` AS w

INNER JOIN information_ schema.`INNODB _ TRX` AS b ON b.trx_id=w.blocking_trx_id

INNER JOIN information_ schema.`INNODB _ TRX` AS r ON r.trx_id = w.requesting_trx_id

INNER JOIN information_ schema.`INNODB _ LOCKS` AS l ON w.requested_lock_id=l.lock_id

LEFT JOIN information_ schema.`PROCESSLIST` AS p ON p.id=b.trx_mysql_thread_id

ORDER BY wait_time DESC\ G

* * 1. Row *

Waiting_trx_id: 324676117

Waiting_thread: 2

Wait_time: 137

Waiting_query: update T5 set baggage where Bourne 4

Waiting_table_lock: `test`.`t5`

Waiting_index_lock: b

Blocking_trx_id: 324676116

Blocking_thread: 1

Blocking_host:

Blocking_port: localhost

Idle_in_trx: 278

Blocking_query: NULL

1 row in set (0.00 sec)

Mysql > select * from innodb_locks\ G

* * 1. Row *

Lock_id: 324676117:255:4:5

Lock_trx_id: 324676117

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t5`

Lock_index: b

Lock_space: 255

Lock_page: 4

Lock_rec: 5

Lock_data: 4

* 2. Row * *

Lock_id: 324676116:255:4:5

Lock_trx_id: 324676116

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t5`

Lock_index: b

Lock_space: 255

Lock_page: 4

Lock_rec: 5

Lock_data: 4

2 rows in set (0.00 sec)

There is no gap lock in the lock information, only exclusive line locks. The test shows that when range conditional filtering is used in WHERE sentences, there is no gap lock when the secondary index is a unique index, but the index entry with the maximum value in the range condition (SQL) is locked.

The sentence actually does not need this record.

-- SESSINO 1

Mysql > begin

Mysql > update T5 set bimb where b > 6

Rows matched: 1 Changed: 0 Warnings: 0

-- SESSSION 2

Mysql > begin

Mysql > insert into T5 values (8pc8);-- blocked

-- SESSION 3 View Lock Information

SELECT r.trx_id AS waiting_trx_id, r.`trx _ mysql_thread_ id` AS waiting_thread

TIMESTAMPDIFF (SECOND,r.trx_wait_started,CURRENT_TIMESTAMP) AS wait_time

R.`trx _ query`AS waiting_query, l.`lock _ table`AS waiting_table_lock

L.lock_index AS waiting_index_lock

B.trx_id AS blocking_trx_id, b.`trx _ mysql_thread_ id` AS blocking_thread

SUBSTRING (p. Hostdome 1) AS blocking_host INSTR (p. Host1')

SUBSTRING (p. Hostmending INSTR (p.hostdint') + 1) AS blocking_port

IF (p.command = "Sleep", p.timejin0) AS idle_in_trx

B.`trx _ query`AS blocking_query

FROM information_ schema.`INNODB _ LOCK_ WAITS` AS w

INNER JOIN information_ schema.`INNODB _ TRX` AS b ON b.trx_id=w.blocking_trx_id

INNER JOIN information_ schema.`INNODB _ TRX` AS r ON r.trx_id = w.requesting_trx_id

INNER JOIN information_ schema.`INNODB _ LOCKS` AS l ON w.requested_lock_id=l.lock_id

LEFT JOIN information_ schema.`PROCESSLIST` AS p ON p.id=b.trx_mysql_thread_id

ORDER BY wait_time DESC\ G

* * 1. Row *

Waiting_trx_id: 324676121-transaction ID waiting for lock (transaction ID of SESSION 2)

Waiting_thread: 2-- MYSQL thread ID waiting for lock (MYSQL thread ID of SESSION 2)

Wait_time: 13

Waiting_query: insert into T5 values (8meme 8penny 8)

Waiting_table_lock: `test`.`t5`

Waiting_index_lock: b

Blocking_trx_id: 324676120-transaction ID that holds locks (transaction ID of SESSION 1)

Blocking_thread: 1-- MySQL thread ID that holds the lock (MSYQL thread ID of SESSION 1)

Blocking_host:

Blocking_port: localhost

Idle_in_trx: 51

Blocking_query: NULL

1 row in set (0.00 sec)

Mysql > select * from innodb_locks\ G

* * 1. Row *

Lock_id: 324676121:255:4:1

Lock_trx_id: 324676121

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t5`

Lock_index: b

Lock_space: 255

Lock_page: 4

Lock_rec: 1

Lock_data: supremum pseudo-record-- SESSION 2 is blocked on the pseudo record that represents the last row of the data page

* 2. Row * *

Lock_id: 324676120:255:4:1

Lock_trx_id: 324676120

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`t5`

Lock_index: b

Lock_space: 255

Lock_page: 4

Lock_rec: 1

Lock_data: supremum pseudo-record

2 rows in set (0.00 sec)

When the scope query condition in the WHERE sentence is larger than the last row in the table, an exclusive row lock is added to the pseudo record of the last row of the data page, making it impossible to insert a new record into the table that is larger than the primary key value of the last row.

Summary:

When the secondary index is a unique index, when the SQL statement execution plan is index access scan or constant retrieval, the transaction will only add exclusive row locks on the secondary index items that meet the WHERE sentence filtering criteria and the primary key index entries that meet the criteria, and no locks will be added on index entries that do not meet the filtering criteria.

A full table scan in index order adds exclusive row locks to all primary key index entries, because the primary key index page sub-leaf of INNODB is actually the data page of the table, so exclusive row locks are added to all records in the whole table.

INNODB does a full table scan when there is no index on the table (nor a clearly defined primary key, only a hidden primary key provided by INNODB), adding exclusive row locks on all records in the table. In the case of a primary key index on the table, the execution plan uses a full table scan in index order to add exclusive row locks on all primary key index entries.

When range condition filtering is used in WHERE sentences, a gap lock is not generated when the secondary index is a unique index, but the index entry with the maximum value in the range condition is locked (this record is not actually required by the SQL statement).

When the scope query condition in the WHERE sentence is larger than the last row in the table, an exclusive row lock is added to the pseudo record of the last row of the data page, making it impossible to insert a new record into the table that is larger than the primary key value of the last row.

After reading the above, do you have any further understanding of how to study innodb transaction locks? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.