Mysql锁与MVCC

---

Mysql锁的类型

MySQL中有哪些锁：

1. 乐观锁（Optimistic Locking）：假设并发操作时不会发生冲突，只在提交事务时检查数据是否被其他事务修改过。常用于读多写少的场景。

2. 悲观锁（Pessimistic Locking）：假设并发操作时会发生冲突，因此在操作期间持有锁来避免冲突。常用于写多读少的场景。

3. 全局锁（Global Lock）：对整个数据库实例加锁，限制除了超级用户外的所有查询和修改操作。一般用于备份、恢复等操作。

4. 表级锁（Table Lock）：对整个表加锁，其他连接无法修改或读取该表的数据，但可以对其他表进行操作。

5. 意向共享锁（Intention Shared Lock）：表级锁的辅助锁，表示事务要在某个表或页级锁上获取共享锁。

6. 意向排它锁（Intention Exclusive Lock）：表级锁的辅助锁，表示事务要在某个表或页级锁上获取排它锁。

7. 页级锁（Page Lock）：对数据页（通常是连续的几个行）加锁，控制并发事务对该页的访问。适用于数据较大且并发量较高的场景。

8. 行级锁（Row Lock）：对单个行加锁，只锁定需要修改的数据行，其他行可以被同时修改或读取。并发性高，但锁管理较复杂。

9. 记录锁（Record Lock）：行级锁的特定类型，锁定单个行，确保其他事务无法同时修改或读取该行。

10. 共享锁（Shared Lock）：也称为读锁，多个事务可以同时持有共享锁并读取数据，但不能修改数据。适用于同时读取同一数据的场景。

11. 排它锁（Exclusive Lock）：也称为写锁，事务持有排它锁时，其他事务无法同时持有共享锁或排它锁，用于保护数据的写操作。

12. 间隙锁（Gap Lock）：锁定一个范围的键，但不包括这些键的实际值。用于防止其他事务在范围内插入数据。

13. 临建锁（Metadata Lock）：锁定数据库对象的元数据，如表结构，用于保证数据定义的一致性。

各种锁解析

锁使用

使用方式：
乐观锁示例：

悲观锁：
悲观锁的实现通常通过使用SELECT … FOR UPDATE或使用LOCK IN SHARE MODE语句来加锁。

-- 事务1：查询并修改订单状态
START TRANSACTION;
-- 查询订单状态，并持有排它锁
SELECT order_id, status FROM orders WHERE order_id = 1 FOR UPDATE;
-- 执行一些业务逻辑判断...
-- 修改订单状态
UPDATE orders SET status = 'completed' WHERE order_id = 1;
COMMIT;

全局锁：

-- 事务1：加全局锁
FLUSH TABLES WITH READ LOCK;
-- 执行一些需要全局锁的操作...
-- 解除全局锁
UNLOCK TABLES;

表级锁的使用：

-- Session 1
START TRANSACTION;
-- 在Session 1中加共享锁
#显式上锁（手动）
lock table tableName read;//读锁
lock table tableName write;//写锁
#隐式上锁（默认，自动加锁自动释放
insert、update、delete //上写锁
-- 解锁
UNLOCK TABLES;
COMMIT;

InnoDB引擎的页级锁的示例

-- Session 1
START TRANSACTION;
-- 获取某个数据页的共享锁
SELECT * FROM products WHERE id = 1 LOCK IN SHARE MODE;
-- 执行一些只读操作
-- 解锁
COMMIT;

行级锁：

-- Session 1
START TRANSACTION;
-- 在Session 1中对某个行加共享锁
SELECT * FROM products WHERE id = 1 FOR SHARE;
-- 执行一些只读操作，例如SELECT语句，可以读取被共享锁保护的行
-- 解锁
COMMIT;

共享锁：

-- Session 1
START TRANSACTION;
-- 在Session 1中对某个行加共享锁
SELECT * FROM products WHERE id = 1 FOR SHARE;
-- 执行一些只读操作，例如SELECT语句，可以读取被共享锁保护的行
-- 解锁
COMMIT;

排它锁：

-- Session 1
START TRANSACTION;
-- 在Session 1中对某个行加排它锁
SELECT * FROM products WHERE id = 1 FOR UPDATE;
-- 执行一些修改操作，例如UPDATE、INSERT、DELETE等
-- 解锁
COMMIT;

意向共享锁：

-- Session 1
START TRANSACTION;
-- 在Session 1中获取意向共享锁
LOCK TABLES products INTENTIONAL READ;
-- 执行一些只读操作，例如SELECT语句，对表进行读操作
-- 解锁
UNLOCK TABLES;

意向排它锁：

-- Session 1
START TRANSACTION;-- 在Session 1中获取意向排它锁
LOCK TABLES products INTENTIONAL WRITE;-- 执行一些修改操作，例如UPDATE、INSERT、DELETE等-- 解锁
UNLOCK TABLES;

间隙锁：

-- Session 1
START TRANSACTION;
-- 在Session 1中使用范围查询，并对查询结果的间隙加锁
SELECT * FROM products WHERE price BETWEEN 10 AND 20 FOR UPDATE;
-- 执行一些需要对查询结果进行修改的操作，例如UPDATE、DELETE等
-- 解锁
COMMIT;

临建锁：

-- Session 1
SELECT GET_LOCK('my_lock', 10);-- 执行一些需要加锁的操作SELECT RELEASE_LOCK('my_lock');

记录锁：

-- Session 1
START TRANSACTION;-- 获取某个记录的共享锁
SELECT * FROM products WHERE id = 1 FOR SHARE;-- 执行一些读操作-- 释放锁
COMMIT;-- Session 2
START TRANSACTION;-- 获取某个记录的排他锁
SELECT * FROM products WHERE id = 1 FOR UPDATE;-- 执行一些写操作-- 释放锁
COMMIT;

锁使用详细示例
锁解析及使用-微信

MVCC

MVCC(Mutil-Version Concurrency Control)，多版本并发控制。是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问。用于支持读已提交(RC）和可重复读(RR）隔离级别的实现。

数据库通过加锁，可以实现事务的隔离性，串行化隔离级别就是加锁实现的，但是加锁会降低数据库性能。
因此，数据库引入了MVCC多版本并发控制，在读取数据不用加锁的情况下，实现读取数据的同时可以修改数据，修改数据时同时可以读取数据。

MVCC主要是用来解决【读-写】冲突的无锁并发控制，可以解决以下问题：

在并发读写数据时，可以做到在读操作时不用阻塞写操作，写操作不用阻塞读操作，提高数据库并发读写的性能。
可以解决脏读，幻读，不可重复读等事务隔离问题，但不能解决【写-写】引起的更新丢失问题。

一般数据库中都会采用以上MVCC与锁的两种组合来解决并发场景的问题，以此最大限度的提高数据库性能。

MVCC + 悲观锁：MVCC解决读-写冲突，悲观锁解决写-写冲突。
MVCC + 乐观锁：MVCC解决读-写冲突，乐观锁解决写-写冲突。

在InnoDB存储引擎，针对每行记录都有固定的两个隐藏列【DB_TRX_ID】【DB_ROLL_PTR】以及一个可能存在的隐藏列【DB_ROW_ID】。

隐式字段	描述	是否必须存在
DB_TRX_ID	事物Id，也叫事物版本号，占用6byte的标识，事务开启之前，从数据库获得一个自增长的事务ID，用其判断事务的执行顺序	是
DB_ROLL_PTR	占用7byte，回滚指针，指向这条记录的上一个版本的undo log记录，存储于回滚段（rollback segment）中	是
DB_ROW_ID	隐含的自增ID（隐藏主键），如果表中没有主键和非NULL唯一键时，则会生成一个单调递增的行ID作为聚簇索引	否

MVCC实际上是使用的update undo log 实现的快照读。

当事务对某一行数据进行改动时，会产生一条Undo日志，多个事务同时操作一条记录时，就会产生多个版本的Undo日志，这些日志通过回滚指针（DB_ROLL_PTR）连成一个链表，称为版本链。

MVCC能否解决幻读问题：
首先可以明确的是，MVCC在快照读的情况下可以解决幻读问题，但是在当前读的情况下是不能解决幻读的。

快照读和当前读

快照读【Consistent Read】

也叫普通读，读取的是记录数据的可见版本（可能是过期的数据），不加锁，不加锁的普通select语句都是快照读，即不加锁的非阻塞读。
快照读的执行方式是生成 ReadView，直接利用 MVCC 机制来进行读取，并不会对记录进行加锁。

如下语句：

select * from tableName;

当前读
也称锁定读【Locking Read】，读取的是记录数据的最新版本，并且需要先获取对应记录的锁，并且当前读返回的记录都会加上锁，保证其他事务不会再并发的修改这条记录。update、insert、delete 都是当前读。排它锁。如下语句：

SELECT * FROM student LOCK IN SHARE MODE;  # 共享锁
SELECT * FROM student FOR UPDATE; # 排他锁
INSERT INTO student values ...  # 排他锁
DELETE FROM student WHERE ...  # 排他锁
UPDATE student SET ...  # 排他锁

当前读每次都会重新生成一个Read View，新增、删除、修改、排他锁、共享锁都是当前读。

当前读状态下可以解决幻读问题

读视图【Read View】

Read View提供了某一时刻事务系统的快照，主要是用来做可见性判断, 里面保存了对本事务不可见的其他活跃事务。

当事务在开始执行的时候，会产生一个读视图（Read View），用来判断当前事务可见哪个版本的数据，即可见性判断。
实际上在innodb中，每个SQL语句执行前都会生成一个Read View。

Read View重要的四个属性：

• creator_trx_id
  创建当前read view的事务ID
• m_ids
  当前系统中所有的活跃事务的 id，活跃事务指的是当前系统中开启了事务，但还没有提交的事务;
• m_low_limit_id
  表示在生成ReadView时，当前系统中活跃的读写事务中最小的事务id，即m_ids中的最小值。
• m_up_limit_id
  当前系统中事务的 id 值最大的那个事务 id 值再加 1，也就是系统中下一个要生成的事务 id。

ReadView 会根据这 4 个属性，结合 undo log 版本链，来实现 MVCC 机制，决定一个事务能读取到数据那个版本。

读已提交只能读Read View中比自己小的事务ID，可重复读能读取比自己大的已提交的事务ID

在读已提交(Read Committed)的隔离级别下实现MVCC，同一个事务里面，【每一次查询都会产生一个新的Read View副本】，这样可能造成同一个事务里前后读取数据可能不一致的问题（不可重复读并发问题）。
读已提交下只要数据的事务ID不在m_ids中，就能查到当前数据

在可重复读(Repeatable read)的隔离级别下实现MVCC，【同一个事务里面，多次查询，都只会产生一个共用Read View】，所有就算期间有事务已经提交m_ids也不会改变，以此解决不可重复读的并发问题。

原博客
MVCC解析-微信

mvcc案例解析

串行化的解决

读取的是记录数据的最新版本，并且当前读返回的记录都会加上锁，保证其他事务不会再并发的修改这条记录。update、insert、delete 都是当前读。排它锁

exlpain字段解析

通过EXPLAIN，可以分析出以下结果：

表的读取顺序
数据读取操作的操作类型
哪些索引被实际使用
表之间的引用
每张表有多少行被优化器查询

• id表示查询语句的序号，自动分配，顺序递增，值越大，执行优先级越高。id相同时，优先级由上而下。
• select_type列
  select_type表示查询类型，常见的有SIMPLE简单查询、PRIMARY主查询、SUBQUERY子查询、UNION联合查询、UNION RESULT联合临时表结果等。
• table列
  table表示SQL语句查询的表名、表别名、临时表名。
• partitions列
  partitions表示SQL查询匹配到的分区，没有分区的话显示NULL。
• type列
  type表示表连接类型或者数据访问类型，就是表之间通过什么方式建立连接的，或者通过什么方式访问到数据的。具体有以下值，性能由好到差依次是：
  system > const > eq_ref > ref > ref_or_null > index_merge > range > index > ALL
  • system
    当表中只有一行记录，也就是系统表，是 const 类型的特列。
  • const
    表示使用主键或者唯一性索引进行等值查询，最多返回一条记录。性能较好，推荐使用。
  • eq_ref
    表示表连接使用到了主键或者唯一性索引，下面的SQL就用到了user表主键id。
  • ref
    表示使用非唯一性索引进行等值查询。
  • ref_or_null
    表示使用非唯一性索引进行等值查询，并且包含了null值的行。
  • index_merge
    表示用到索引合并的优化逻辑，即用到的多个索引。
  • range
    表示用到了索引范围查询。
  • index
    表示使用索引进行全表扫描。
  • ALL
    表示全表扫描，性能最差。
• possible_keys列
  表示可能用到的索引列，实际查询并不一定能用到。
• key列
  表示实际查询用到索引列。
• key_len列
  表示索引所占的字节数。
• ref列
  表示where语句或者表连接中与索引比较的参数，常见的有const（常量）、func（函数）、字段名。如果没用到索引，则显示为NULL。
• rows列
  表示执行SQL语句所扫描的行数。
• filtered列
  表示按条件过滤的表行的百分比。
• Extra列
  表示一些额外的扩展信息，不适合在其他列展示，却又十分重要。
  • Using where
    表示使用了where条件搜索，但没有使用索引。
  • Using index
    表示用到了覆盖索引，即在索引上就查到了所需数据，无需二次回表查询，性能较好。
  • Using filesort
    表示使用了外部排序，即排序字段没有用到索引。
  • Using temporary
    表示用到了临时表，下面的示例中就是用到临时表来存储查询结果。
  • Using join buffer
    表示在进行表关联的时候，没有用到索引，使用了连接缓存区存储临时结果。
  • Using index condition
    表示用到索引下推的优化特性。

explain案例解析

explain案例解析

ACID的原理

mysql将数据存储到数据库之前都是先通过日志的方式来存储数据，因为日志的存储是顺序存储，可以通过偏移量来控制或者查找，而数据库的持久化存储，是见缝插针，这样可能最大化利用磁盘空间，存储完还需要记录数据的地址，所以相比日志存储比较慢。

    原子性的实现：通过`Redo log`和`Undo log`，重做和回滚。如果事务提交了，那么就会执行Redo log写到数据库，如果没有提交就会执行undo log。

日志

redo log和binlog区别 ：

redo log是属于innoDB层面，
binlog属于MySQL Server层面的，这样在数据库用别的存储引擎时可以达到一致性的要求。

redo log是物理日志，记录该数据页更新的内容；
binlog是逻辑日志，记录的是这个更新语句的原始逻辑

redo log是循环写，日志空间大小固定；
binlog是追加写，是指一份写到一定大小的时候会更换下一个文件，不会覆盖。

binlog可以作为恢复数据使用，主从复制搭建，
redo log作为异常宕机或者介质故障后的数据恢复使用。

redo log(重做日志)和binlog(归档日志)。redo log是InnoDB存储引擎层的日志，binlog是MySQL Server层记录的日志， 两者都是记录了某些操作的日志(不是所有)自然有些重复（但两者记录的格式不同）。

引擎

MySQL5.5版本后，MySQL的默认内置存储引擎已经从MyISAM变成InnoDB

InnoDB：

• 支持事务;

• 行级锁定(更新数据时一般指锁定当前行)：通过索引实现、全表扫描忍让时表锁、注意间隙所的影响;

• 读写阻塞与事务的隔离级别相关；

• 具有非常高的缓存特性(既能缓存索引、也能缓存数据)；

• 这个表和主键以组(Cluster)的方式存储、组成一颗平衡树；

• 所有的辅助索引(secondary indexes)都会保存主键信息；

• 支持分区、表空间类似与oracle 数据库；

• 支持外键约束、不支持全文检索(5.5.5之前的MyISAM支持全文检索、5.5.5之后就不在支持);

• 相对MyISAM而言、对硬件的要求比较高

MyISAM特性

• 不支持事务

• 表级锁定，数据更新时锁定整个表：其锁定机制是表级锁定，这虽然可以让锁定的实现成本很小但是也同时大大降低了其并发性能。

• 读写互相阻塞：不仅会在写入的时候阻塞读取，myisam还会在读取的时候阻塞写入，但读本身并不会阻塞另外的读。

• 只会缓存索引：MyISAM可以通过key_buffer_size缓存索引，以大大提高访问性能，减少产品IO，但是这个缓存区只会缓存索引，而不会缓存数据。

• 读取速度较快，占用资源相对少。

• 不支持外键约束，但支持全文索引。

整合SpringBoot

整合博客

博客记录

mvcc解析