mysql锁分类和机制思维导图模板_ProcessOn思维导图、流程图

mysql高级

三个日志

## 二 语句分析  ### 2.1 查询语句 说了以上这么多，那么究竟一条 sql 语句是如何执行的呢？其实我们的 sql 可以分为两种，一种是查询，一种是更新（增加，更新，删除）。我们先分析下查询语句，语句如下： ```sql select * from tb_student  A where A.age='18' and A.name=' 张三 '; ``` 结合上面的说明，我们分析下这个语句的执行流程： - 先检查该语句是否有权限，如果没有权限，直接返回错误信息，如果有权限，在 MySQL8.0 版本以前，会先查询缓存，以这条 sql 语句为 key 在内存中查询是否有结果，如果有直接缓存，如果没有，执行下一步。 - 通过分析器进行词法分析，提取 sql 语句的关键元素，比如提取上面这个语句是查询 select，提取需要查询的表名为 tb_student，需要查询所有的列，查询条件是这个表的 id='1'。然后判断这个 sql 语句是否有语法错误，比如关键词是否正确等等，如果检查没问题就执行下一步。 - 接下来就是优化器进行确定执行方案，上面的 sql 语句，可以有两种执行方案：   ```   a.先查询学生表中姓名为“张三”的学生，然后判断是否年龄是 18。   b.先找出学生中年龄 18 岁的学生，然后再查询姓名为“张三”的学生。   ```   那么优化器根据自己的优化算法进行选择执行效率最好的一个方案（优化器认为，有时候不一定最好）。那么确认了执行计划后就准备开始执行了。 - 进行权限校验，如果没有权限就会返回错误信息，如果有权限就会调用数据库引擎接口，返回引擎的执行结果。 ### 2.2 更新语句 以上就是一条查询 sql 的执行流程，那么接下来我们看看一条更新语句如何执行的呢？sql 语句如下： ``` update tb_student A set A.age='19' where A.name=' 张三 '; ``` 我们来给张三修改下年龄，在实际数据库肯定不会设置年龄这个字段的，不然要被技术负责人打的。其实这条语句也基本上会沿着上一个查询的流程走，只不过执行更新的时候肯定要记录日志啦，这就会引入日志模块了，MySQL 自带的日志模块是 **binlog（归档日志）** ，所有的存储引擎都可以使用，我们常用的 InnoDB 引擎还自带了一个日志模块 **redo log（重做日志）**，我们就以 InnoDB 模式下来探讨这个语句的执行流程。流程如下： - 先查询到张三这一条数据，如果有缓存，也是会用到缓存。 - 然后拿到查询的语句，把 age 改为 19，然后调用引擎 API 接口，写入这一行数据，InnoDB 引擎把数据保存在内存中，同时记录 redo log，此时 redo log 进入 prepare 状态，然后告诉执行器，执行完成了，随时可以提交。 - 执行器收到通知后记录 binlog，然后调用引擎接口，提交 redo log 为提交状态。 - 更新完成。 **这里肯定有同学会问，为什么要用两个日志模块，用一个日志模块不行吗?** 这是因为最开始 MySQL 并没有 InnoDB 引擎（InnoDB 引擎是其他公司以插件形式插入 MySQL 的），MySQL 自带的引擎是 MyISAM，但是我们知道 redo log 是 InnoDB 引擎特有的，其他存储引擎都没有，这就导致会没有 crash-safe 的能力(crash-safe 的能力即使数据库发生异常重启，之前提交的记录都不会丢失)，binlog 日志只能用来归档。 并不是说只用一个日志模块不可以，只是 InnoDB 引擎就是通过 redo log 来支持事务的。那么，又会有同学问，我用两个日志模块，但是不要这么复杂行不行，为什么 redo log 要引入 prepare 预提交状态？这里我们用反证法来说明下为什么要这么做？ - **先写 redo log 直接提交，然后写 binlog**，假设写完 redo log 后，机器挂了，binlog 日志没有被写入，那么机器重启后，这台机器会通过 redo log 恢复数据，但是这个时候 bingog 并没有记录该数据，后续进行机器备份的时候，就会丢失这一条数据，同时主从同步也会丢失这一条数据。 - **先写 binlog，然后写 redo log**，假设写完了 binlog，机器异常重启了，由于没有 redo log，本机是无法恢复这一条记录的，但是 binlog 又有记录，那么和上面同样的道理，就会产生数据不一致的情况。 如果采用 redo log 两阶段提交的方式就不一样了，写完 binglog 后，然后再提交 redo log 就会防止出现上述的问题，从而保证了数据的一致性。那么问题来了，有没有一个极端的情况呢？假设 redo log 处于预提交状态，binglog 也已经写完了，这个时候发生了异常重启会怎么样呢？ 这个就要依赖于 MySQL 的处理机制了，MySQL 的处理过程如下： - 判断 redo log 是否完整，如果判断是完整的，就立即提交。 - 如果 redo log 只是预提交但不是 commit 状态，这个时候就会去判断 binlog 是否完整，如果完整就提交 redo log, 不完整就回滚事务。 这样就解决了数据一致性的问题。 ## 三 总结 - MySQL 主要分为 Server 层和引擎层，Server 层主要包括连接器、查询缓存、分析器、优化器、执行器，同时还有一个日志模块（binlog），这个日志模块所有执行引擎都可以共用，redolog 只有 InnoDB 有。 - 引擎层是插件式的，目前主要包括，MyISAM,InnoDB,Memory 等。 - 查询语句的执行流程如下：权限校验（如果命中缓存）--->查询缓存--->分析器--->优化器--->权限校验--->执行器--->引擎 - 更新语句执行流程如下：分析器---->权限校验---->执行器--->引擎---redo log(prepare 状态)--->binlog--->redo log(commit状态) ## 四 参考 - 《MySQL 实战45讲》 - MySQL 5.6参考手册:<https://dev.MySQL.com/doc/refman/5.6/en/>

三锁2

按使用方式

乐观锁

悲观苏

按锁级别

共享锁

排它锁

意向锁

间隙锁（Next-Key锁）

按锁粒度

Subtopic

mysql锁快乐家族

概述

定义

锁是计算机协调多个进程或线程并发访问某一资源的机制。 在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解 决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

举个栗子

生活购物 打个比方，我们到淘宝上买一件商品，商品只有一件库存，这个时候如果还有另一个人买， 那么如何解决是你买到还是另一个人买到的问题？ 这里肯定要用到事务，我们先从库存表中取出物品数量，然后插入订单，付款后插入付款表信息， 然后更新商品数量。在这个过程中，使用锁可以对有限的资源进行保护，解决隔离和并发的矛盾。

锁的分类

从对数据操作类型（读/写）分

读锁（共享锁）

针对同一份数据，多个读操作可以同时进行而不会互相影响

写锁（排它锁）

当前写操作没有完成前，他会阻断其他写锁和读锁

从对数据操作的粒度分

表锁

行锁

三锁

表锁（偏读）

特点

偏向MyISAM存储引擎，开销小，加锁块。 锁定粒度大，发生锁冲突的概率最高，并发读最低。

案例分析

建表SQL

【表级锁分析--建表SQL】 create table mylock( id int not null primary key auto_increment, name varchar(20) )engine myisam; insert into mylock(name) values('a'); insert into mylock(name) values('b'); insert into mylock(name) values('c');...

加读锁

我们为mylock表加read锁(读阻塞写例子) session1 session2 获得表mylock的READ锁定 连接终端 当前session可以查询该表记录 其他session也可以查询该表的记录 ...

加写锁

mylockwrite(MyISAM) session1 session2 获得表mylock的WRITE锁定 待Session1开启写锁后，session2再连接终端 当前session对锁定表的查询+更新+插入操作都可以执行： 其他session对锁定表的查询被阻塞，需要等待锁被释放： 在锁表前，如果session2有数据缓存，锁表以后，在锁住的表不发生改变的情况下session2可以读出缓存数据，一旦数据发生改变，缓存将失效，操作将被阻塞...住

结论

mylockwrite(MyISAM) session1 session2 获得表mylock的WRITE锁定 待Session1开启写锁后，session2再连接终端 当前session对锁定表的查询+更新+插入操作都可以执行： 其他session对锁定表的查询被阻塞，需要等待锁被释放： 在锁表前，如果session2有数据缓存，锁表以后，在锁住的表不发生改变的情况下session2可以读出缓存数据，一旦数据发生改变，缓存将失效，操作将被阻塞...住

行锁（偏写）

特点

偏向InonoDB存储引擎，开销大，加锁慢。 会出现死锁。 锁定粒度最小，发生锁冲突的概率最低，并发读最大。

行锁支持事务

事务及其ACID属性

事务是由一组SQL语句组成的逻辑处理单元，事务具有以下4个属性，通常简称为事务的ACID属性。 l 原子性（Atomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。 l 一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性；事务结束时， 所有的内部数据结构（如B树索引或双向链表）也都必须是正确的。 l 隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的 ，反之亦然。 l 持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

并发事务处理带来的问题

更新丢失（Lost Update）

当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题－－最后的更新覆盖了由其他事务所做的更新。 例如，两个程序员修改同一java文件。每程序员独立地更改其副本，然后保存更改后的副本，这样就覆盖了原始文档。最后保存其更改副本的编辑人员覆盖前一个程序员所做的 更改。 如果在一个程序员完成并提交事务之前，另一个程序员不能访问同一文件，则可避免此问题。

脏读（Dirty Reads）

一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做”脏读”。 一句话：事务A读取到了事务B已修改但尚未提交的的数据，还在这个数据基础上做了操作。 此时，如果B事务回滚，A读取的数据无效，【不符合一致性要求】。

不可重复度（Non-Repeateble Reads）

在一个事务内，多次读同一个数据。 在这个事务还没有结束时，另一个事务也访问该同一数据。 那么，在第一个事务的两次读数据之间。由于第二个事务的修改，那么第一个事务读到的数据可能不一样，这样就发生了在一个事务内两次读到的数据是不一样的，因此称为不可重复读，即原始读取不可重复。   一句话：一个事务范围内两个相同的查询却返回了不同数据。

幻读（Phantom Reads）

一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。 一句话：事务A 读取到了事务B提交的新增数据，【不符合隔离性】。

事务隔离级别

脏读”、“不可重复读”和“幻读”，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。 数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上“串行化”进行，这显然与“并发”是矛盾的。 同时，不同的应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对“不可重复读”和“幻读”并不敏感，可能更关心 数据并发访问的能力。 常看当前数据库的事务隔离级别：show variables like 'tx_isolation';

案例分析

建表SQL

CREATE TABLE if not EXISTS `testinnodblock`  ( a int(11), b VARCHAR(16) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='测试lock';

行锁定演示

session1

SET AUTOCOMMIT=0 ;-- 禁止自动提交 show variables like 'AUTOCOMMIT'; BEGIN  ;   INSERT INTO `testinnodblock` values (1, 'b2'); INSERT INTO `testinnodblock` values (3, '3'); INSERT  INTO `testinnodblock` values (4, '4000'); INSERT  INTO `testinnodblock` values (5, '5000'); INSERT  INTO `testinnodblock` values (6, '6000'); INSERT  INTO `testinnodblock` values (7, '7000'); INSERT  INTO `testinnodblock` values (8, '8000'); INSERT  INTO `testinnodblock` values (9, '9000');   先不执行 COMMIT;

session2

update   `testinnodblock` set b = '3-2' where a = 3;

session2阻塞一段时间后，会报错： [SQL]update   `testinnodblock` set b = '3-2' where a = 3; [Err] 1205 - Lock wait timeout exceeded; try restarting transaction

解决办法：https://blog.csdn.net/weixin_34166472/article/details/88805074

show variables like '%timeout%';

connect_timeout             10 delayed_insert_timeout        300 have_statement_timeout       YES innodb_flush_log_at_timeout          1 innodb_lock_wait_timeout           20 innodb_print_lock_wait_timeout_info OFF innodb_rollback_on_timeout          OFF interactive_timeout             28800 lock_wait_timeout            5 net_read_timeout          150 net_write_timeout                   300 rpl_semi_sync_master_timeout 10000 rpl_stop_slave_timeout 31536000 slave_net_timeout               3600 thread_pool_idle_timeout         60 wait_timeout             28800

总结

行锁定基本演示 Session1 Session2 更新但是不提交，没有手写commit; Session_2被阻塞，只能等待 提交更新 解除阻塞，更新正常进行

无索引行锁升级为表锁

Session1 Session2 正常情况，各自锁定各自的行，互相不影响，一个2000另一个3000 由于在column字段b上面建了索引，如果没有正常使用，会导致行锁变表锁 比如没加单引号导致索引失效，行锁变表锁 被阻塞，等待。只到Session_1提交后才阻塞解除，完成更新

select也可以加锁

读锁

select .. lock in share mode

共享锁(Share Lock) 共享锁又称读锁，是读取操作创建的锁。 其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。 如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。 获准共享锁的事务只能读数据，不能修改数据。 用法 SELECT ... LOCK IN SHARE MODE; 在查询语句后面增加 LOCK IN SHARE MODE ，Mysql会对查询结果中的每行都加共享锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请共享锁，否则会被阻塞。其他线程也可以读取使用了共享...锁

session1

select * from `testinnodblock` LOCK IN SHARE MODE ; -- COMMIT;

session2

update   `testinnodblock` set b = '3-3' where a = 3;

select * from `testinnodblock`  for UPDATE;