MYSQL之Innodb锁

最新推荐文章于 2023-12-21 17:57:54 发布

会飞的小祥

最新推荐文章于 2023-12-21 17:57:54 发布

阅读量189

点赞数

分类专栏： mysql

mysql 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

MYSQL之Innodb锁

Innodb的有行锁和表锁之分，这里摘录一些行锁的特性：

何为共享锁、排他锁、以及意向共享锁、意向排他锁（意向是针对多行的锁）

本文的重点是下面这点：

Innodb的行锁是根据检索的索引项进行加锁（注意不要理解成对索引加锁），具体的说是对所有根据索引检索到的记录进行加锁（由于Innodb使用主键为聚簇索引的方式，也可以理解为对主键进行加锁，Innodb总会有聚簇索引的，即使没有也会隐式生成一个），这样就决定了Innodb的行锁会有下面一些性质：

只有通过索引检索，才能使用行锁，否则使用表锁
使用相同索引键来访问即使是不同行记录，也会出现锁冲突。（这里指的是需要锁的情况下。在非一致性锁定读情况下，即使读取的行已经被使用Select ... For Update，也是可以读的（因为读的是快照））
间隙锁，范围检索以及对不存在记录相等条件的检索都会使用，所以应该在设计上尽量避免，避免导致严重的锁等待

以下情况会使用表锁：

没有使用索引进行检索
判断不同执行计划的代价，如果全表扫描效率更高，也会使用表锁
数据类型转化而导致表锁。如下会对name进行类型转化，而执行全表扫描

使用相同索引键检索即使不同记录，也导致锁等待的例子：
MySQL> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> desc my_lock;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a     | int(11) | YES | MUL | NULL    |       |
| b     | int(11) | YES | MUL | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> select *from my_lock;
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
|    1 |    2 |
|    1 |    3 |
+------+------+
3 rows in set (0.00 sec)

mysql> explain select *from my_lock where a=1 and b=1 for update; //使用了索引a进行检索以更新
+----+-------------+---------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+-------+------+-------------+
| 1 | SIMPLE | my_lock | ref | a,b | a | 5 | const | 1 | Using where |
+----+-------------+---------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql> select *from my_lock where a=1 and b=1 for update;//虽然只检索到一条记录，但对所有a=1的记录锁定
+------+------+
| a | b |
+------+------+
| 1 | 1 |
+------+------+
1 row in set (0.00 sec)

// 这里启动另外一个session，检索另外一条记录以更新，将阻塞，直到session1 -> commit

// mysql> select *from my_lock where a=1 and b=2 for update; -> 阻塞中，直到session1执行下面语句

// mysql> select *from my_lock where a=1 and b=2; -> ok 不会阻塞，对于一致性非锁定读（读取的是快照，对应事务隔离等级2,3）

mysql> commit; //提交解除锁
Query OK, 0 rows affected (0.00 sec)

数据类型转化导致全表扫描的例子：

mysql> alter table tab_no_index add index name(name);

Query OK, 4 rows affected (8.06 sec)

Records: 4 Duplicates: 0 Warnings: 0

mysql> explain select * from tab_with_index where name = 1 \G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE