mysql无锁查询快照读_mysql snapshot read快照读及current read当前读与锁lock之一

前言

mysql读操作分为2种类型:snapshot read及current read,前者基于mysql mvcc实现,读取数据块是undo block,后者是基于当前数据块。这2种不同的读取类型与锁的关系是什么，本文作一点儿简要的记录。

联系方式

微信公众号

微信

目录

snapshot read是否加锁

snapshot read由于操作非当前数据块，不会加锁

current read加锁2种类型

x --排它锁

s --共享锁

current read几种操作场景

查询current read不同操作场景持锁语句

(注：关注列lock_mode)

mysql> select * from information_schema.innodb_locks;

+-------------------------+-----------------+-----------+-----------+------------------+-----------------+------------+-----------+----------+----------------+

| lock_id                 | lock_trx_id     | lock_mode | lock_type | lock_table       | lock_index      | lock_space | lock_page | lock_rec | lock_data      |

+-------------------------+-----------------+-----------+-----------+------------------+-----------------+------------+-----------+----------+----------------+

| 11909:473:3:2           | 11909           | X         | RECORD    | `zxydb`.`t_read` | GEN_CLUST_INDEX |        473 |         3 |        2 | 0x00000836F561 |

| 421207096970864:473:3:2 | 421207096970864 | S         | RECORD    | `zxydb`.`t_read` | GEN_CLUST_INDEX |        473 |         3 |        2 | 0x00000836F561 |

+-------------------------+-----------------+-----------+-----------+------------------+-----------------+------------+-----------+----------+----------------+

2 rows in set, 1 warning (0.00 sec)