2019-07-28 InnoDB锁和索引？索引和执行计划？

最新推荐文章于 2023-02-24 10:51:40 发布

jupiter_888

最新推荐文章于 2023-02-24 10:51:40 发布

阅读量104

点赞数

本文链接：https://blog.csdn.net/jupiter_888/article/details/103853127

版权

select ... for share 可在多个事务中读，但是不可被其他事务写
select ... for update

https://www.hollischuang.com/archives/1716

https://www.cnblogs.com/ktgu/p/3529143.html

死锁：锁被一个客户端长期占据，一直处于锁定状态；对方持有对方的锁，进入僵持状态；
活锁：是指线程1可以使用资源，但它很礼貌，让其他线程先使用资源，线程2也可以使用资源，但它很绅士，也让其他线程先使用资源。这样你让我，我让你，最后两个线程都无法使用资源。
饥饿：是指如果线程T1占用了资源R，线程T2又请求封锁R，于是T2等待。T3也请求资源R，当T1释放了R上的封锁后，系统首先批准了T3的请求，T2仍然等待。然后T4又请求封锁R，当T3释放了R上的封锁之后，系统又批准了T4的请求......，T2可能永远等待。

Innodb行级锁都是基于索引的？

http://www.hollischuang.com/archives/934

上面我们提到，使用select…for update会把数据给锁住，不过我们需要注意一些锁的级别，MySQL InnoDB默认行级锁。行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁把整张表锁住，这点需要注意。

Innodb锁

InnoDB锁和索引的关系？
执行计划对select ... for update 行级锁的影响？
InnoDB事务和锁的持有？
select ... for update 有什么问题？

文档中的 index records 是啥？

14.6.2.2 The Physical Structure of an InnoDB Index

Index records are stored in the leaf pages of their B-tree data structure.

1. InnoDB锁和索引的关系？

14.7.3 Locks Set by Different SQL Statements in InnoDB

If you have no indexes suitable for your statement and MySQL must scan the entire table to process the statement, every row of the table becomes locked, which in turn blocks all inserts by other users to the table. It is important to create good indexes so that your queries do not unnecessarily scan many rows.
如果没有匹配的索引则扫描全表，并且把所有行都锁定。
因此必须要创建必须的索引，否则全表扫描，所有行都被锁定。

A locking read, an UPDATE, or a DELETE generally set record locks on every index record that is scanned in the processing of the SQL statement. It does not matter whether there are WHERE conditions in the statement that would exclude the row. InnoDB does not remember the exact WHEREcondition, but only knows which index ranges were scanned.
locking read， UPDATE, DELETE 设置 record locks在所有被扫描的索引记录上。
InnoDB不会记得确切的where条件，仅记得被扫描的索引范围。
如果不用索引，所有被扫描的行都被锁上。
用了索引，所有被扫描的索引记录也都是锁上。
只要被扫描，都会被锁上。
因为有索引，所以可以直接定位，而不用全表扫描，只需要扫描相关的索引记录就行了（可能会扫描多行，锁定多行）。如果是唯一性索引，则只需要扫描一行就行了。
因为没有索引，必须要全表扫描，所以表中所有行都被锁定。

索引文件和记录文件，两份文件，分开存储

index-record locks 锁定的是索引文件中行
InnoDB所有表中如果不设置索引，或主键索引，则clustered index作为默认索引。

1. InnoDB锁和索引的关系？举例子

mysql> show create table t ;
+-------+------------------------------------------------------------------------------------+
| Table | Create Table                                                                       |
+-------+------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `i` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

insert

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> 
mysql> select * from t;
+------+
| i    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    5 |
|    5 |
|    5 |
|    5 |
+------+
9 rows in set (0.00 sec)

mysql> insert into t(i) values(7);
Query OK, 1 row affected (0.00 sec)

mysql> SHOW ENGINE INNODB STATUS\G

------------
TRANSACTIONS
------------
Trx id counter 152491
Purge done for trx's n:o < 152490 undo n:o < 0 state: running but idle
History list length 5
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 281479467061704, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281479467059896, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281479467058992, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 152490, ACTIVE 5 sec
1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 1
MySQL thread id 24, OS thread handle 123145358168064, query id 3204 localhost root starting
SHOW ENGINE INNODB STATUS

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

update

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> 
mysql> select * from t;
+------+
| i    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    5 |
|    5 |
|    5 |
|    5 |
+------+
9 rows in set (0.00 sec)

mysql> update t set i=10 where i=8;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0



mysql> SHOW ENGINE INNODB STATUS\G

------------
TRANSACTIONS
------------
Trx id counter 152491
Purge done for trx's n:o < 152490 undo n:o < 0 state: running but idle
History list length 5
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 281479467061704, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281479467059896, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281479467058992, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 152490, ACTIVE 97 sec
3 lock struct(s), heap size 1136, 12 row lock(s), undo log entries 1
MySQL thread id 24, OS thread handle 123145358168064, query id 3206 localhost root starting
SHOW ENGINE INNODB STATUS

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

delete



mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t;
+------+
| i    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    5 |
|    5 |
|    5 |
|    5 |
+------+
9 rows in set (0.00 sec)


mysql> delete from t where i=9;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW ENGINE INNODB STATUS\G

------------
TRANSACTIONS
------------
Trx id counter 152491
Purge done for trx's n:o < 152490 undo n:o < 0 state: running but idle
History list length 5
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 281479467061704, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281479467059896, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281479467058992, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 152490, ACTIVE 158 sec
3 lock struct(s), heap size 1136, 12 row lock(s), undo log entries 1
MySQL thread id 24, OS thread handle 123145358168064, query id 3208 localhost root starting
SHOW ENGINE INNODB STATUS

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

总结：验证了 repeatable-read isolation level下的设置的锁

locking read:
select ... for update
select ... lock in share mode
UPDATE
INSERT

InnoDB不会记得确切的where条件，仅记得被扫描的索引范围。
如果不用索引，所有被扫描的行都被锁上。
用了索引，所有被扫描的索引记录也都是锁上。
只要被扫描，都会被锁上。
因为有索引，所以可以直接定位，而不用全表扫描，只需要扫描相关的索引记录就行了（可能会扫描多行，锁定多行）。如果是唯一性索引，则只需要扫描一行就行了。
因为没有索引，必须要全表扫描，所以表中所有行都被锁定。

index-record locks 仅锁定索引记录（索引文件中的）
Gap locks仅锁定间隙（索引文件中）
next-key locks：
index-record locks和Gap locks的结合，同时锁定索引记录和索引记录间隙。
如果没有索引，则全表扫描，锁住表中所有记录。