无锡网站建设选千客云网络,wordpress搜索参数,腾讯企业邮箱免费版,湖北网站建设 鄂 icp#x1f525;作者主页#xff1a;小林同学的学习笔录 #x1f525;mysql专栏#xff1a;小林同学的专栏 目录 1.锁 1.1 概述 1.2 全局锁 1.2.1 语法 1.2.1.1 加全局锁 1.2.1.2 数据备份 1.2.1.3 释放锁 1.2.1.4 特点 1.2.1.5 演示 1.3 表级锁 1.3.1 介绍 … 作者主页小林同学的学习笔录 mysql专栏小林同学的专栏 目录 1.锁 1.1 概述 1.2 全局锁 1.2.1 语法 1.2.1.1 加全局锁 1.2.1.2 数据备份 1.2.1.3 释放锁 1.2.1.4 特点 1.2.1.5 演示 1.3 表级锁 1.3.1 介绍 1.3.2 表锁 1.3.2.1 语法 1.3.2.2 特点 1.3.2.3 结论 1.3.3 元数据锁 1.3.4 意向锁 1.3.4.1 介绍 1.3.4.2 分类 1.3.4.3 演示 1.4 行级锁 1.4.1 介绍 1.4.2 行锁 1.4.3 演示 1.4.4 间隙锁临键锁 1.4.4.1 示例演示 1.锁 1.1 概述 锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中除传统的计算资源
CPU、RAM、I/O的争用以外数据也是一种供许多用户共享的资源。
如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题锁冲突也是影响数据
库并发访问性能的一个重要因素。从这个角度来说锁对数据库而言显得尤其重要也更加复杂。
MySQL中的锁按照锁的力度分分为以下三类
全局锁锁定数据库中的所有表。表级锁每次操作锁住整张表。行级锁每次操作锁住对应的行数据。 在介绍锁之前先回顾一下DML、DQL、DDL分别代表什么? 数据操作语言DMLDML 用于对数据库中的数据执行操作例如插入、更新、删除、查询数
据简称增删改查尽管 SELECT 语句通常被归类为 DQL但它也可以在某种程度上被认为是
DML因为它允许检索数据。 数据查询语言DQLDQL 用于从数据库中检索数据。它专门用于执行查询操作 数据定义语言DDLDDL 用于定义数据库的结构包括创建、修改和删除数据库对象
例如表、索引、视图等。DDL 的操作影响数据库的整体结构。 1.2 全局锁 全局锁Global Lock是数据库管理系统中的一种锁定机制通常用于锁定整个数据库实例而
不是单个表或行。全局锁可以阻止对整个数据库的写入操作但通常不会阻止读取操作。这种锁定
机制在某些情况下可以用于数据库备份、恢复或维护操作。
其典型的使用场景是做全库的逻辑备份对所有的表进行锁定从而获取一致性视图保证数据的
完整性. 为什么全库逻辑备份就需要加全就锁呢
①.先来分析一下不加全局锁可能存在的问题。
假设在数据库中存在这样三张表: tb_stock 库存表tb_order 订单表tb_orderlog 订单日志表。 在进行数据备份时先备份了tb_stock库存表。
然后接下来在业务系统中执行了下单操作扣减库存生成订单更新tb_stock表插入
tb_order表。
然后再执行备份 tb_order表的逻辑。
业务中执行插入订单日志操作。
最后又备份了tb_orderlog表。
此时备份出来的数据是存在问题的。因为备份出来的数据tb_stock表与tb_order表的数据不一
致(有最新操作的订单信息,但是库存数没减)。 ②.再来分析一下加了全局锁后的情况 对数据库进行进行逻辑备份之前先对整个数据库加上全局锁一旦加了全局锁之后其他的
DDL、DML全部都处于阻塞状态但是可以执行DQL语句也就是处于只读状态而数据备份就
是查询操作。那么数据在进行逻辑备份的过程中数据库中的数据就是不会发生变化的这样就保
证了数据的一致性和完整性。 1.2.1 语法 1.2.1.1 加全局锁 flush tables with read lock ; 1.2.1.2 数据备份 mysqldump -uroot –p1234 itcast itcast.sql
mysqldump -uXxx –pXxx 数据库名 磁盘地址 数据库名.sql
例如mysqldump -uXxx –pXxx db_01 D:/db_01 1.2.1.3 释放锁 unlock tables ; 1.2.1.4 特点 数据库中加全局锁是一个比较重的操作存在以下问题
如果在主库上备份那么在备份期间都不能执行更新业务基本上就得停摆。
如果在从库上备份那么在备份期间从库不能执行主库同步过来的二进制日志binlog会导致
主从延迟。 在InnoDB引擎中我们可以在备份时加上参数 --single-transaction 参数来完成不加锁的一致性数
据备份。
mysqldump --single-transaction -uroot –p123456 itcast itcast.sql 1.2.1.5 演示 开启两个命令行
第一个命令行 第二个命令行 会处于阻塞状态光标一直在闪只有全局锁被释放才可以执行相应的操作 1.3 表级锁 1.3.1 介绍 表级锁每次操作锁住整张表。锁定力度大发生锁冲突的概率最高并发度最低。应用在
MyISAM、InnoDB、BDB等存储引擎中。 对于表级锁主要分为以下三类
表锁元数据锁meta data lockMDL意向锁 1.3.2 表锁 对于表锁分为两类
①.表共享读锁read lock
②.表独占写锁write lock 1.3.2.1 语法 加锁lock tables 表名... read/write。
释放锁unlock tables / 客户端断开连接 。 1.3.2.2 特点 ①.读锁 左侧为客户端一对指定表加了读锁不会影响右侧客户端二的读但是会阻塞右侧客户端的写。 ②.写锁 左侧为客户端一对指定表加了写锁会阻塞右侧客户端的读和写。 1.3.2.3 结论 读锁不会阻塞其他客户端的读但是会阻塞写。写锁既会阻塞其他客户端的读又会阻塞其他客户
端的写。 1.3.3 元数据锁
MDL(meta data lock)加锁过程是系统自动控制无需显式使用在访问一张表的时候会自动加
上。MDL锁主要作用是维护表元数据的数据一致性在表上有活动事务的时候不可以对元数据进
行写入操作。为了避免DML与DDL冲突保证读写的正确性。 注意有元数据锁只有在事务开启才有的锁当事务提交之后相应的锁会被释放. 这里的元数据大家可以简单理解为就是一张表的表结构。 也就是说某一张表涉及到未提交的
事务时是不能够修改这张表的表结构的。
在MySQL5.5中引入了MDL当对一张表进行增删改查的时候加MDL读锁(共享)当对表结构进
行变更操作的时候加MDL写锁(排他)另外共享锁是兼容的。 常见的SQL操作时所添加的元数据锁 1.3.3.1 演示 ①.当执行SELECT、INSERT、UPDATE、DELETE等语句时添加的是元数据共享锁
SHARED_READ / SHARED_WRITE之间是兼容的所以不会出现阻塞状态。 ②.当执行SELECT语句时添加的是元数据共享锁SHARED_READ会阻塞元数据排他锁
EXCLUSIVE之间是互斥的所以出现了阻塞状态。 我们可以通过下面的SQL来查看数据库中的元数据锁的情况
select object_type,object_schema,object_name,lock_type,lock_duration fromperformance_schema.metadata_locks ; 1.3.4 意向锁 1.3.4.1 介绍 为了避免DML在执行时加的行锁与表锁的冲突在InnoDB中引入了意向锁使得表锁不用检查
每行数据是否加行锁使用意向锁来减少表锁的检查。
假如没有意向锁客户端一对表加了行锁后客户端二如何给表加表锁呢?
来通过示意图简单分析一下
首先客户端一开启一个事务然后执行DML操作在执行DML语句时会对涉及到的行加锁。 当客户端二想对这张表加表锁时会检查当前表是否有对应的行锁如果没有则添加表锁此
时就会从第一行数据检查到最后一行数据效率较低。 有了意向锁之后 :
客户端一在执行DML操作时会对涉及的行加行锁同时也会对该表加上意向锁。 而其他客户端在对这张表加表锁的时候会根据该表上所加的意向锁来判定是否可以成功加上表
锁而不用逐行判断行锁情况了。 1.3.4.2 分类 ①.意向共享锁(IS): 由语句select ... lock in share mode添加 。 与 表锁共享锁(read)兼容与表锁
排他锁(write)互斥。
②.意向排他锁(IX): 由insert、update、delete、select...for update添加 。与表锁共享锁(read)及排
他锁(write)都互斥意向锁之间不会互斥。
一旦事务提交了意向共享锁、意向排他锁都会自动释放。 可以通过以下SQL查看意向锁及行锁的加锁情况
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data fromperformance_schema.data_locks; 1.3.4.3 演示 ①.意向共享锁与表读锁是兼容的
由于select语句不会自动加行锁需要手动加行锁
select * from score where id 1 lock in share mode; ②.意向排他锁与表读锁、写锁都是互斥的
insert、update、delete语句会自动加行锁 1.4 行级锁 1.4.1 介绍 行级锁每次操作锁住对应的行数据。锁定力度最小发生锁冲突的概率最低并发度最高。应用
在InnoDB存储引擎中。
InnoDB的数据是基于索引组织的行锁是通过对索引上的索引项加锁来实现的而不是对记录加
的锁。对于行级锁主要分为以下三类
①.行锁Record Lock锁定单个行记录的锁防止其他事务对此行进行update和delete。
在RC、RR隔离级别下都支持。 ②.间隙锁Gap Lock锁定索引记录间隙不含该记录确保索引记录间隙不变防止其他
事务在这个间隙进行insert产生幻读。在RR隔离级别下都支持。 ③.临键锁Next-Key Lock行锁和间隙锁组合同时锁住数据并锁住数据前面的间隙Gap。
在RR隔离级别下支持。 1.4.2 行锁 共享锁S允许一个事务去读一行阻止其他事务获得相同数据集的排它锁.就是允许一个事务
select另外事务可以获得相同数据的共享锁但是不能获得相同数据集的排他锁。 排他锁X允许获取排他锁的事务更新数据阻止其他事务获得相同数据集的共享锁和排他
锁。 两种行锁的兼容情况如下: 常见的SQL语句在执行时所加的行锁如下 1.4.3 演示 ①.针对唯一索引进行检索时对已存在的记录进行等值匹配时将会自动优化为行锁。
②.InnoDB的行锁是针对于索引加的锁不通过索引条件检索数据那么InnoDB将对表中的所有记录加锁此时 就会升级为表锁。 可以通过以下SQL查看意向锁及行锁的加锁情况
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data fromperformance_schema.data_locks; 加共享锁共享锁与共享锁之间兼容。共享锁与排他锁之间互斥。 排它锁与排他锁之间互斥 无索引行锁升级为表锁 注意这里面经常会手动开启事务的原因是为了演示效果如果是自动开启事务会自动提交事务
会把锁给释放因此看不出效果但是原理还是一样的. 1.4.4 间隙锁临键锁 默认情况下InnoDB在 REPEATABLE READ事务隔离级别运行InnoDB使用 next-key 锁进行搜
索和索引扫描以防止幻读。
①.索引上的等值查询(唯一索引)给不存在的记录加锁时, 优化为间隙锁 。 ②.索引上的等值查询(非唯一普通索引)向右遍历时最后一个值不满足查询需求时next-key lock
退化为间隙锁。
③.索引上的范围查询(唯一索引)--会访问到不满足条件的第一个值为止。
注意间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存一个事务采用的间隙锁不会阻
止另一个事务在同一间隙上采用间隙锁。 1.4.4.1 示例演示 ①.索引上的等值查询(唯一索引)给不存在的记录加锁时, 优化为间隙锁 ②.索引上的等值查询(非唯一普通索引)向右遍历时最后一个值不满足查询需求时next-key lock
退化为间隙锁。
介绍分析一下 我们知道InnoDB的B树索引叶子节点是有序的双向链表。 假如我们要根据这个二级索引查询
值为18的数据并加上共享锁我们是只锁定18这一行就可以了吗
并不是因为是非唯一索引这个结构中可能有多个18的存在所以在加锁时会继续往后找找
到一个不满足条件的值当前案例中也就是29。此时会对18加临键锁并对29之前的间隙加锁 这里的临键锁(行锁间隙锁)还会锁住age3的行并且还会锁住主键为1-3之间的间隙 ③.索引上的范围查询(唯一索引)--会访问到不满足条件的第一个值为止 所以数据库数据在加锁是就是将19加了行锁25的临键锁包含25及25之前的间隙正无穷
的临键锁(正无穷及之前的间隙)。 麒麟而非淇淋不是干货不制作https://blog.csdn.net/2301_77358195
