做购物网站的引言,国内电商运营是做什么的,国际新闻最新10条,竞价页面网站做优化文章目录 1. 锁概述2. 全局锁2.1 全局锁的必要性2.2 语法2.3 全局锁的特点 3. 表级锁3.1 表锁3.2 元数据锁3.3 意向锁3.4 自增锁 4. 行级锁4.1 介绍4.2 记录锁4.3 间隙锁4.4 临键锁 1. 锁概述
锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中#xff0c;除传… 文章目录 1. 锁概述2. 全局锁2.1 全局锁的必要性2.2 语法2.3 全局锁的特点 3. 表级锁3.1 表锁3.2 元数据锁3.3 意向锁3.4 自增锁 4. 行级锁4.1 介绍4.2 记录锁4.3 间隙锁4.4 临键锁 1. 锁概述
锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中除传统的计算资源CPU、RAM、I/O的争用以外数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说锁对数据库而言显得尤其重要也更加复杂。
MySQL中的锁按照锁的粒度分分为以下三类
全局锁锁定数据库中的所有表。表级锁每次操作锁住整张表。行级锁每次操作锁住对应的行数据。
2. 全局锁
2.1 全局锁的必要性
全局锁就是对整个数据库实例加锁加锁后整个实例就处于只读状态后续的DML的写语句DDL语句已及更新操作的事务提交语句都将被阻塞。
其典型的使用场景是做全库的逻辑备份对所有的表进行锁定从而获取一致性视图保证数据的完整性。为什么全库逻辑备份就需要加全局锁呢
我们分别来对加与不加全局锁的情况进行分析。 不加全局锁 假设在数据库中存在这样三张表: tb_stock 库存表tb_order 订单表tb_orderlog 订单日志表。 假设我们备份数据库时进行如下的操作 在进行数据备份时先备份了tb_stock库存表。 然后接下来在业务系统中执行了下单操作扣减库存生成订单更新tb_stock表插入tb_order表。 然后再执行备份 tb_order表的逻辑。 业务中执行插入订单日志操作。 最后又备份了tb_orderlog表。 此时备份出来的数据是存在问题的。因为备份出来的数据tb_stock表与tb_order表的数据不一致(有最新操作的订单信息,但是库存数没减)。 加了全局锁 对数据库进行进行逻辑备份之前先对整个数据库加上全局锁一旦加了全局锁之后其他的DDL、DML全部都处于阻塞状态但是可以执行DQL语句也就是处于只读状态而数据备份就是查询操作。那么数据在进行逻辑备份的过程中数据库中的数据就是不会发生变化的这样就保证了数据的一致性和完整性。
2.2 语法 加全局锁 flush tables with read lock ;数据备份 mysqldump -uroot –p1234 study study.sql数据备份的相关指令在后面MySQL管理章节还会详细讲解。 释放锁 unlock tables ;2.3 全局锁的特点
数据库中加全局锁是一个比较重的操作存在以下问题
如果在主库上备份那么在备份期间都不能执行更新业务基本上就得停摆。如果在从库上备份那么在备份期间从库不能执行主库同步过来的二进制日志binlog会导致主从延迟。
在InnoDB引擎中我们可以在备份时加上参数 --single-transaction 参数来完成不加锁的一致性数据备份。
mysqldump --single-transaction -uroot –p123456 study study.sql3. 表级锁
表级锁每次操作锁住整张表。锁定粒度大发生锁冲突的概率最高并发度最低。应用在MyISAM、InnoDB、BDB等存储引擎中。
对于表级锁主要分为以下三类
表锁元数据锁meta data lockMDL意向锁自增锁
3.1 表锁
对于表锁分为两类
表共享读锁read lock表独占写锁write lock
其语法如下
锁的状态语法加锁lock tables 表名... read/write释放锁unlock tables 或 直接退出客户端 读锁 左侧为客户端一对指定表加了读锁不会影响右侧客户端二的读但是会阻塞右侧客户端的写。 写锁 左侧为客户端一对指定表加了写锁会阻塞右侧客户端的读和写。
需要注意的是表锁除了会限制别的线程的读写外也会限制本线程接下来的读写操作。也就是说如果本线程对表加了「共享表锁」那么本线程接下来如果要对表执行写操作的语句是会被阻塞的当然其他线程对表进行写操作时也会被阻塞直到锁被释放。 读锁不会阻塞其他客户端的读但是会阻塞写。写锁既会阻塞其他客户端的读又会阻塞其他客户端的写。 也就是说如果本线程对学生表加了「共享表锁」那么本线程接下来如果要对学生表执行写操作的语句是会被阻塞的当然其他线程对学生表进行写操作时也会被阻塞直到锁被释放。
3.2 元数据锁
meta data lock , 元数据锁简写MDL。MDL加锁过程是系统自动控制无需显式使用在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性在表上有活动事务的时候不可以对元数据进行写入操作。为了避免DML与DDL冲突保证读写的正确性。
这里的元数据大家可以简单理解为就是一张表的表结构。 也就是说某一张表涉及到未提交的事务时是不能够修改这张表的表结构的。
在MySQL5.5中引入了MDL当对一张表进行增删改查的时候加MDL读锁(共享)当对表结构进行变更操作的时候加MDL写锁(排他)。
常见的SQL操作时所添加的元数据锁
对应SQL锁类型说明lock tables xxx read / writeSHARED_READ_ONLY / SHARED_NO_READ_WRITEselect 、select ... lock in share modeSHARED_READ与SHARED_READ、SHARED_WRITE兼容与EXCLUSIVE互斥insert 、update、delete、select ... for updateSHARED_WRITE与SHARED_READ、SHARED_WRITE兼容与EXCLUSIVE互斥alter table ...EXCLUSIVE与其他的MDL都互斥
当执行SELECT、INSERT、UPDATE、DELETE等语句时添加的是元数据共享锁SHARED_READ / SHARED_WRITE之间是兼容的。当执行SELECT语句时添加的是元数据共享锁SHARED_READ会阻塞元数据排他锁EXCLUSIVE之间是互斥的。
我们可以通过下面的SQL语句来查看数据库中的元数据锁的情况
select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks ;3.3 意向锁
为了避免DML在执行时加的行锁与表锁的冲突在InnoDB中引入了意向锁使得表锁不用检查每行数据是否加锁使用意向锁来减少表锁的检查。假如没有意向锁客户端一对表加了行锁后客户端二如何给表加表锁呢来通过示意图简单分析一下有无意向锁的两种情况。意向锁的目的是为了快速判断表里是否有记录被加锁。 没有意向锁 首先客户端一开启一个事务然后执行DML操作在执行DML语句时会对涉及到的行加行锁。 当客户端二想对这张表加表锁时会检查当前表是否有对应的行锁如果没有则添加表锁此时就会从第一行数据检查到最后一行数据效率较低。 存在意向锁 有了意向锁之后客户端一在执行DML操作时会对涉及的行加行锁同时也会对该表加上意向锁。 而其他客户端在对这张表加表锁的时候会根据该表上所加的意向锁来判定是否可以成功加表锁而不用逐行判断行锁情况了。
意向锁分为下面两类
意向共享锁(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 from performance_schema.data_locks;注意 意向共享锁与表读锁是兼容的意向排他锁与表锁的读锁、写锁都是互斥的 3.4 自增锁
自增锁听名字就知道在表的主键设置为自动增加的时候会上的锁。表里的主键通常都会设置成自增的这是通过对主键字段声明 AUTO_INCREMENT 属性实现的。之后可以在插入数据时可以不指定主键的值数据库会自动给主键赋值递增的值这主要是通过 AUTO-INC 锁实现的。
AUTO-INC 锁是特殊的表锁机制锁不是在一个事务提交后才释放而是在执行完插入语句后就会立即释放。在插入数据时会加一个表级别的 AUTO-INC 锁然后为被 AUTO_INCREMENT 修饰的字段赋值递增的值等插入语句执行完成后才会把 AUTO-INC 锁释放掉。
一个事务在持有 AUTO-INC 锁的过程中其他事务的如果要向该表插入语句都会被阻塞从而保证插入数据时被 AUTO_INCREMENT 修饰的字段的值是连续递增的。但是 AUTO-INC 锁再对大量数据进行插入的时候会影响插入性能因为另一个事务中的插入会被阻塞。
因此MySQL的InnoDB引擎提供了一种轻量级的自增锁。该轻量级的自增锁在插入数据的时候会为被AUTO_INCREMENT 修饰的字段加上轻量级锁然后给该字段赋值一个自增的值就把这个轻量级锁释放了而不需要等待整个插入语句执行完后才释放锁。
可以使用 show variables like %innodb_autoinc_lock_mode%; 语句查看控制自增锁的变量的值该变量的值有012三种。
innodb_autoinc_lock_mode 0采用 AUTO-INC 锁语句执行结束后才释放锁innodb_autoinc_lock_mode 2采用轻量级锁申请自增主键后就释放锁并不需要等语句执行后才释放innodb_autoinc_lock_mode 1对于普通的insert语句自增锁在申请之后就马上释放类似 insert … select 这样的批量插入数据的语句自增锁还是要等语句结束后才被释放。 需要注意的是 虽然 innodb_autoinc_lock_mode 2 是性能最高的一种方式但是其搭配 binlog 的 statement 格式一起使用的时候在主从复制中会发生数据不一致问题当使用 innodb_autoinc_lock_mode 2 时需要将 binlog_format 的格式设置为 row 这样既能提升并发性又不会出现数据一致性问题 4. 行级锁
4.1 介绍
行级锁每次操作锁住对应的行数据。锁定粒度最小发生锁冲突的概率最低并发度最高。应用在InnoDB存储引擎中。
nnoDB实现了以下两种类型的行锁
共享锁S允许一个事务去读一行阻止其他事务获得相同数据集的排它锁。排他锁X允许获取排他锁的事务更新数据阻止其他事务获得相同数据集的共享锁和排他锁。
行锁之中只有共享锁与共享锁之间能够兼容其余组合都不兼容
InnoDB的数据是基于索引组织的行锁是通过对索引上的索引项加锁来实现的而不是对记录加的锁。对于行级锁主要分为以下三类 记录锁Record Lock锁定单个行记录的锁防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。 间隙锁Gap Lock锁定索引记录间隙不含该记录确保索引记录间隙不变防止其他事务在这个间隙进行insert产生幻读。在RR隔离级别下都支持。 临键锁Next-Key Lock行锁和间隙锁组合同时锁住数据并锁住数据前面的间隙Gap。在RR隔离级别下支持。
4.2 记录锁
记录锁其实很好理解对表中的记录加锁叫做记录锁简称行锁。比如
SELECT * FROM test WHERE id1 FOR UPDATE;它会在 id1 的记录上加上记录锁以阻止其他事务插入更新删除 id1 这一行。
需要注意的是
id 列必须为唯一索引列或主键列否则上述语句加的锁就会变成临键锁(有关临键锁下面会讲)。同时查询语句必须为精准匹配不能为 、、like等否则也会退化成临键锁。记录锁是锁住记录锁住索引记录而不是真正的数据记录.记录锁也是排它(X)锁,所以会阻塞其他事务对其插入、更新、删除。如果不通过索引条件检索数据那么InnoDB将对表中的所有记录加锁此时 就会升级为表锁。
4.3 间隙锁
间隙锁只存在于可重复读隔离级别目的是为了解决可重复读隔离级别下幻读的现象。
间隙锁定是对索引记录之间的间隙的锁定或者是对第一个或最后一个索引记录之前的间隙的锁定。间隙可能跨越单个索引值多个索引值甚至为空。使用间隙锁锁住的是一个区间而不仅仅是这个区间中的每一条数据。
间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。
接下来我们间隙锁的不同情况进行分析。假设创建有如下的数据表
CREATE TABLE gap_test (id INT(11) NOT NULL,name VARCHAR(50) NULL DEFAULT NULL,age INT(11) NULL DEFAULT NULL,PRIMARY KEY (id)
)
ENGINEInnoDB;insert into gap_test(id,name,age)values(2,海月,20);
insert into gap_test(id,name,age)values(7,吕布,30);
insert into gap_test(id,name,age)values(10,马可,28);
insert into gap_test(id,name,age)values(15,曹操,40);唯一索引的范围查询 我们查询的动作如下 事务1
select * from gap_test where id 7 for update;事务2
insert into gap_test(id,name,age)values(1,亚连,20); -- 阻塞
insert into gap_test(id,name,age)values(5,亚连,20);-- 阻塞
insert into gap_test(id,name,age)values(8,亚连,20); -- 成功说明在区间 (-无穷大, 7] 之间是存在间隙锁的。 查询唯一索引中不存在的数据 我们查询的动作如下 事务1
select * from gap_test where id 5 for update;
事务2
insert into gap_test(id,name,age)values(4,亚连,20); -- 阻塞
insert into gap_test(id,name,age)values(1,亚连,20); -- 成功
insert into gap_test(id,name,age)values(9,亚连,20); -- 成功说明在区间 (2, 7] 之间是存在间隙锁的。 普通索引的范围查询 我们对上面的 age 字段建立一个索引 CREATE INDEX idx_gap_age ON gap_test(age);然后我们的查询如下 事务1
select * from gap_test where age 30 for update;事务2
insert into gap_test(id,name,age)values(20,赔钱虎,20); -- 阻塞
insert into gap_test(id,name,age)values(21,赔钱虎,31); -- 阻塞
insert into gap_test(id,name,age)values(22,赔钱虎,60); -- 成功
insert into gap_test(id,name,age)values(23,赔钱虎,40); -- 成功
insert into gap_test(id,name,age)values(24,赔钱虎,39); -- 阻塞我们发现一样会进行阻塞不过其与主键的间隙锁不同的是其阻塞的范围包含的是 [20,30] 以及 [30,40] 即将30这个索引的左边和右边都上了间隙锁不过如果条件改为 age 30 则不会上锁右边的区间。 查询普通索引中不存在的数据 我们查询的动作如下 事务1
select * from gap_test where age 26 for update;
事务2
insert into gap_test(id,name,age)values(30,亚连,28); -- 成功
insert into gap_test(id,name,age)values(31,亚连,20); -- 阻塞
insert into gap_test(id,name,age)values(32,亚连,31); -- 成功说明在区间 (20, 28) 之间是存在间隙锁的并且其还对 20 这个索引值加上了记录锁为什么还需要加上一个记录锁呢这种情况就是下面我们要将的临键锁了。
4.4 临键锁
临键锁是记录锁与间隙锁的组合它的封锁范围既包含索引记录又包含索引区间。默认情况下InnoDB在 REPEATABLE READ事务隔离级别运行InnoDB使用 next-key 锁进行搜索和索引扫描以防止幻读。
InnoDB执行行级锁定的方式是当它搜索或扫描表索引时会在遇到的索引记录上设置共享或互斥锁。因此行级锁实际上是索引记录锁。索引记录上的临键锁定也会影响该索引记录之前的“间隙”。即临键锁定是索引记录锁定加上索引记录之前的间隙上的间隙锁定。如果一个会话R在索引中的记录上具有共享或排他锁 则另一会话不能R在索引顺序之前的间隙中插入新的索引记录 。
需要注意的是 临键锁的主要目的也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC临键锁则也会失效。 索引上的等值查询(唯一索引)给不存在的记录加锁时, 优化为间隙锁 。 索引上的等值查询(非唯一普通索引)向右遍历时最后一个值不满足查询需求时next-key lock 退化为间隙锁。 我们知道InnoDB的B树索引叶子节点是有序的双向链表。 假如我们要根据这个二级索引查询值为18的数据并加上共享锁我们是只锁定18这一行就可以了吗 并不是因为是非唯一索引这个结构中可能有多个18的存在所以在加锁时会继续往后找找到一个不满足条件的值当前案例中也就是29。此时会对18加临键锁并对29之前的间隙加锁。 索引上的范围查询(唯一索引)——会访问到不满足条件的第一个值为止。 针对唯一索引进行检索时对已存在的记录进行等值匹配时将会自动优化为记录锁。 文章转载自： http://www.morning.dxqfh.cn.gov.cn.dxqfh.cn http://www.morning.mgkcz.cn.gov.cn.mgkcz.cn http://www.morning.tkkjl.cn.gov.cn.tkkjl.cn http://www.morning.c7512.cn.gov.cn.c7512.cn http://www.morning.kggxj.cn.gov.cn.kggxj.cn http://www.morning.wlgpz.cn.gov.cn.wlgpz.cn http://www.morning.lpqgq.cn.gov.cn.lpqgq.cn http://www.morning.qlpyn.cn.gov.cn.qlpyn.cn http://www.morning.rrms.cn.gov.cn.rrms.cn http://www.morning.rsnn.cn.gov.cn.rsnn.cn http://www.morning.ktrh.cn.gov.cn.ktrh.cn http://www.morning.rddlz.cn.gov.cn.rddlz.cn http://www.morning.rrxnz.cn.gov.cn.rrxnz.cn http://www.morning.qcsbs.cn.gov.cn.qcsbs.cn http://www.morning.mwqbp.cn.gov.cn.mwqbp.cn http://www.morning.fcrw.cn.gov.cn.fcrw.cn http://www.morning.fdrch.cn.gov.cn.fdrch.cn http://www.morning.qnwyf.cn.gov.cn.qnwyf.cn http://www.morning.srbmc.cn.gov.cn.srbmc.cn http://www.morning.nynpf.cn.gov.cn.nynpf.cn http://www.morning.dwfxl.cn.gov.cn.dwfxl.cn http://www.morning.pzpj.cn.gov.cn.pzpj.cn http://www.morning.lwjlj.cn.gov.cn.lwjlj.cn http://www.morning.fldk.cn.gov.cn.fldk.cn http://www.morning.tjsxx.cn.gov.cn.tjsxx.cn http://www.morning.hhqtq.cn.gov.cn.hhqtq.cn http://www.morning.cnprt.cn.gov.cn.cnprt.cn http://www.morning.rbbgh.cn.gov.cn.rbbgh.cn http://www.morning.dmcqy.cn.gov.cn.dmcqy.cn http://www.morning.mwrxz.cn.gov.cn.mwrxz.cn http://www.morning.msbct.cn.gov.cn.msbct.cn http://www.morning.hhxkl.cn.gov.cn.hhxkl.cn http://www.morning.mbpfk.cn.gov.cn.mbpfk.cn http://www.morning.hqxyt.cn.gov.cn.hqxyt.cn http://www.morning.gsrh.cn.gov.cn.gsrh.cn http://www.morning.ybgcn.cn.gov.cn.ybgcn.cn http://www.morning.qbgdy.cn.gov.cn.qbgdy.cn http://www.morning.hwnnm.cn.gov.cn.hwnnm.cn http://www.morning.bygyd.cn.gov.cn.bygyd.cn http://www.morning.kmprl.cn.gov.cn.kmprl.cn http://www.morning.fxpyt.cn.gov.cn.fxpyt.cn http://www.morning.zylrk.cn.gov.cn.zylrk.cn http://www.morning.ybgyz.cn.gov.cn.ybgyz.cn http://www.morning.gslz.com.cn.gov.cn.gslz.com.cn http://www.morning.ctrkh.cn.gov.cn.ctrkh.cn http://www.morning.gzzxlp.com.gov.cn.gzzxlp.com http://www.morning.gdgylp.com.gov.cn.gdgylp.com http://www.morning.kxnjg.cn.gov.cn.kxnjg.cn http://www.morning.wqwbj.cn.gov.cn.wqwbj.cn http://www.morning.gdgylp.com.gov.cn.gdgylp.com http://www.morning.mhnrx.cn.gov.cn.mhnrx.cn http://www.morning.wlqll.cn.gov.cn.wlqll.cn http://www.morning.rnhh.cn.gov.cn.rnhh.cn http://www.morning.tmjhy.cn.gov.cn.tmjhy.cn http://www.morning.bksbx.cn.gov.cn.bksbx.cn http://www.morning.kwcnf.cn.gov.cn.kwcnf.cn http://www.morning.tdwjj.cn.gov.cn.tdwjj.cn http://www.morning.yqqgp.cn.gov.cn.yqqgp.cn http://www.morning.nyqzz.cn.gov.cn.nyqzz.cn http://www.morning.ltpzr.cn.gov.cn.ltpzr.cn http://www.morning.ygmw.cn.gov.cn.ygmw.cn http://www.morning.bpmnh.cn.gov.cn.bpmnh.cn http://www.morning.gmrxh.cn.gov.cn.gmrxh.cn http://www.morning.dsncg.cn.gov.cn.dsncg.cn http://www.morning.qzglh.cn.gov.cn.qzglh.cn http://www.morning.qnqt.cn.gov.cn.qnqt.cn http://www.morning.nrftd.cn.gov.cn.nrftd.cn http://www.morning.gwdmj.cn.gov.cn.gwdmj.cn http://www.morning.ldhbs.cn.gov.cn.ldhbs.cn http://www.morning.mphfn.cn.gov.cn.mphfn.cn http://www.morning.trrrm.cn.gov.cn.trrrm.cn http://www.morning.dfdhx.cn.gov.cn.dfdhx.cn http://www.morning.jpqmq.cn.gov.cn.jpqmq.cn http://www.morning.mnjwj.cn.gov.cn.mnjwj.cn http://www.morning.nlwrg.cn.gov.cn.nlwrg.cn http://www.morning.xkjrs.cn.gov.cn.xkjrs.cn http://www.morning.rgtp.cn.gov.cn.rgtp.cn http://www.morning.bqmsm.cn.gov.cn.bqmsm.cn http://www.morning.nqyzg.cn.gov.cn.nqyzg.cn http://www.morning.sdecsd.cn.gov.cn.sdecsd.cn
