Innodb中的锁

最新推荐文章于 2024-03-19 10:58:38 发布
最新推荐文章于 2024-03-19 10:58:38 发布
阅读量305 收藏 3
点赞数 3
分类专栏： 数据库 文章标签： 锁 死锁 Innodb mysql java
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/gogokongyin/article/details/110197584
版权
         锁是计算机协调多个进程或多线程并发访问某一资源的同步机制，数据库设计到数据的安全性，所以锁的使用是不可避免的。   

1、锁分类

  • 悲观锁-Pessimistic lock
    • 全局锁：flush table with read lock;使用全局锁会锁住整个数据库，使其处于只读状态；
    • 表锁： lock table和 意向锁(Intention Locks) MataData Lock ，意向锁不用显示调用；
    • 行锁(Record-lock)
    • 间隙锁( gap locks)
    • 临键锁( next-key lock) ，由行锁和间隙锁组成；
  • 乐观锁- Optimistic Lock
    • 自旋cas机制；可通过version版本和时间戳来判断；

2、全局锁

2.1 全局锁： flush tables with lock：全局锁主要是对整个数据库的实例加锁，一个库被全局锁锁住后处于只读状态，以下动作将会被禁止：
  • DML：data manipulation language 对数据的增查改删更新操作；
  • DDL:   data definition language ,包括对表的修改，增加字段删除字段；
使用场景：全库逻辑备份。如果没有全局锁，那么系统备份得到的数据不是一个逻辑时间点的，这个视图的逻辑是不一致的，数据备份会出现问题。
 

3、表锁

3.1 lock tables 表锁

  • 加锁：lock table tableName  read／write;
  • 解锁：unlock table：
使用场景 ：在没有行锁这种粒度锁的时候，处理并发的时候就会使用表级锁； 
lock table car write；
select *　from car;
unlock tables；

特点：

  • (1). 可以主动unlock tables释放，在客户端断开连接的时候自动释放；
  • (2). lock tables除了限制别的线程的读写外，也限定了本线程接下来的操作对象；
例如线程执行： 
lock tables t1 read, t2 write; //除了阻塞其他线程外，自己本身也只能读t1，读写t2，不能写t1.

3.2 MDL-意向锁

Mate Data Lock， 意向锁 -也称为 元数据锁 ，在mysql5.5版本中引入了MDL，引擎自己维护，用户不用手动操作。当对一个表做增查改删的时候+MDL读锁，当要对表结果做变更的时候，+MDL写锁。
  • 意向共享锁：如果一张表上面至少有一个意向共享锁，说明有其他的事务给其中的某些数据行加上了共享锁；
  • 意向排他锁：如果一张表上面至少有一个意向排他锁，说明有其他的事务给其中的某些数据行加上了排他锁；
作用：
（1）是防止DDL和MDL之间冲突，保证DDL-(数据定义语言)和DML-(操纵语言：增查改删)的数据一致性；
  • MDL的（增查改删）读锁和 DDL之间不互斥；
  • MDL的写锁和DDL的写锁修改表结构之间互斥；在对表结构进行修改时，加MDL锁，另外线程只能等待。
（2）提高加锁的效率；
  • 加x锁的前提是：表中没有任何锁，包括行锁的存在，意向锁避免了加行锁时检查锁状态的全表行锁扫描，先加意向锁后加x锁，它相当于一个标记，提高了加锁的效率；

4、行锁【Innodb】

4.1、行锁实现原理

  • 行锁是存储引擎innodb自己设计的，不是所有的存储引擎都支持，比如myisam就不支持；
  • 行锁是基于索引实现的，所以没有建立索引的更新将会扫描全表，使用的是表锁；通过锁住索引实现行及锁。
  • 如果select …for update没走索引，就会锁表，innodb内部是全表根据主键索引逐行扫描，逐行加锁，最后统一释放。
使用注意点： 没有索引或者索引失效时，InnoDB 的行锁变表锁。

4.2、两阶段锁协议

行锁是需要的时候加上的，不是不需要了就立即释放，在commit后才释放，释放时机如下：
  • 1. 执行commit语句或者rollback；
  • 2. 退出数据库；
  • 3. 客户端断开连接；

4.3、行锁的使用优化

由于行锁的等到事务提交后才释放，不是用完就释放，所以把有冲突的抢锁操作尽量放到事务操作的最后一步进行。如何理解： “尽量把临界资源的锁往后放”，实例如下：
实例场景：支付订单中一般流程较长，步骤较多，比如商品库存就是一临界资源，容易产生竞争，可以将扣减库存放在订单事务操作的最后一步，让事务在获取锁的等待时间尽量短，提高并发度。

5、行锁与索引的关系分析

从一条简单的SQL语句开始，分析索引对加锁的影响： 
SQL: delete from Order where orderId = 33;

情况一：在RC级别下，id为主键索引，orderId为普通索引，忽略订单ID重复的情况；

表结构如下： 
CREATE TABLE `Order` (
  `id` int(11) NOT NULL,
  `orderId` bigint NOT NULL,
  `goods_name` varchar(45) DEFAULT NULL COMMENT '商品名称',
   PRIMARY KEY (`id`),
   KEY `orderId` (`orderId`)
) ENGINE=InnoDB

分析：此时加行锁会锁住对应条件的二级索引及主键索引的行记录；
情况二：在RC级别下，id为主键索引，orderId为唯一索引；
表结构如下： 
CREATE TABLE `Order` (
  `id` int(11) NOT NULL,
  `orderId` bigint NOT NULL,
  `goods_name` varchar(45) DEFAULT NULL COMMENT '商品名称',
   PRIMARY KEY (`id`),
   UNIQUE KEY `orderId_UNIQUE` (`orderId`),
) ENGINE=InnoDB

分析：由于id是唯一索引时，where会走orderIde列的索引进行过滤，在找到orderId=33的记录后对唯一索引orderId=33的记录加X锁。
同时会回表查询主键索引orderId=33的数据，并对orderId=33的数据也加上X锁。此时依然加行锁，锁住唯一索引及主键索引对应的行记录
 
情况三：在RC级别下，只有id主键索引；
表结构如下： 
CREATE TABLE `Order` (
  `id` int(11) NOT NULL,
  `orderId` bigint NOT NULL,
  `goods_name` varchar(45) DEFAULT NULL COMMENT '商品名称',
   PRIMARY KEY (`id`),
) ENGINE=InnoDB

分析：从上面分析的行锁的锁原理可知，因为没有索引，所以走的是全表扫描，此时没有行锁，会对主键索引上每一条记录施加X锁，会锁住整个的表数据；
思考： 为什么会对所有记录施加X锁，而不是表锁或者说符合条件的数据加X锁呢？
这是由于InnoDB的实现决定的，由于没有索引， 无法在存储引擎层过滤（执行计划里的Using Where），所以存储引擎对每一条数据加锁后返回给Sql Server进行过滤，因为Innodb的行锁是通过索引来实现的。

5.1、行锁使用的注意事项

  • （1）在不通过索引条件查询的时候，或没有索引的情况下，InnoDB只能使用表锁。
  • （2）由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。
  • （3）当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。
  • （4）即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。
 
对于在RR级别下，对数据的写操作不仅会锁住行还会锁住相邻值之间的间隙；也就是会出现 gap 锁 和 Next-key lock。

6、三种细粒度锁的实现

  • record lock-行锁：
  • gap lock-间隙锁：
  • Next-Key lock-临键锁：行锁和间隙锁共同实现的锁；

6.1、行锁Record lock

实现：通过索引实现，锁住一行记录，锁粒度最小，并发度高。
使用条件：等值查询；

6.2、使用间隙锁Gap Lock

使用条件：查询一个不存在的值，锁定就是该值左右的区间，就是间隙锁。
  • 间隙锁与间隙锁之间是不冲突的，跟间隙锁冲突的是： 往间隙之间插入一条数据的这个操作 。

6.3、临键锁 Next-key Lock

临键锁锁住的是什么？
使用条件：范围查询，包含等值和区间
  • 锁住的是下一个记录的左开右闭的区间；
  • 解决了RR级别下的数据幻读；

7、小结

Mysql的行锁和表锁区别：
  • 表级锁： 锁住整张表。    开销小，加锁快；不会出现死锁；锁粒度最大，发生锁冲突的概率最高，并发度低；
  • 行级锁： 锁住一行数据。开销大，加锁慢；容易出现死锁；锁粒度最小，发生锁冲突的概率最低，并发度高；
行锁的实现：
  • 如果有索引，那么会先扫描索引文件，查询到主键id，通过索引锁定行记录实现行锁；
  • 如果没有索引，就会锁住全表的数据；
行锁的出现大大提高了数据库的并发度，当然实现相对复杂，并且产生死锁的概率也提升了，使用时需要注意。

 

   OK---盛年不再来，一日难再晨。
 
 
水滴石穿，积少成多。学习笔记，内容简单，用于复习，梳理巩固。
 
##参考资料，
《Innodb存储引擎》
 
 
戎·码一生
关注
  • 3
    点赞
  • 踩
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql索引
qq_45610772的博客
09-19 685
索引 1：什么是索引？ 索引其实就是一种为了加速对数据表中的数据行的检索从而创建的分散存储的数据结构(也可以打个比喻：就是书中的目录) 注意： 1： 一般关系型数据库的中的索引是 硬盘级索引，就是说索引是存放在硬盘中的 2：如果程序访问我们数据库中的数据行数如果是几万的数据的规模的话，其实我们无需在意索引，但是当数据规模达到几千万上亿的情况下我们一般就会使用到索引 2：索引的作用： 索引在数据库中的作用其实是类似于目录在书本中的作用，用来提高查找信息的速度，用索引查找信息的时候，无需对整张表进行扫描，可
Oracle11g新增数据时出现了，表中索引或索引的分区处于不可用状态
皮鱼的世界
01-11 546
详细参考：https://blog.csdn.net/ocean20/article/details/7325609 一般处理以下3步即可： -- 查询相关表的索引  select index_name,index_type,tablespace_name,table_type,status from user_indexes  where index_name='your_index_n...
Oracle中组合索引的使用详解
kobejayandy的专栏
06-27 805
在Oracle中可以创建组合索引，即同时包含两个或两个以上列的索引。在组合索引的使用方面，Oracle有以下特点：     1、 当使用基于规则的优化器（RBO）时，只有当组合索引的前导列出现在SQL语句的where子句中时，才会使用到该索引；     2、 在使用Oracle9i之前的基于成本的优化器（CBO）时， 只有当组合索引的前导列出现在SQL语句的where子句中时，才可能会使用到该
MyISAM 和 InnoDB存储引擎(二者和表结构有关系)
weixin_44824389的博客
09-26 138
MyIsAm ：是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的 也就是说： InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值； MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。 ...
关于 oracle 语法中 where 1=1 的写法心得
南柯
02-01 1249
where 1=1 对性能的影响 很多时候，使用where 1=1 可以很方便的解决我们的问题，但是这样很可能会造成非常大的性能损失，因为添加使用了 “where 1=1 ”的过滤条件以后数据库系统就无法上使用索引等查询优化策略，数据库系统将会被迫 对每行数据进行扫描（也即是全表扫描） 以比较此行是否满足过滤条件，当表中的数据量较大时候 查询速度会非常慢。 因此如果对性能有比较高的要求就不要使用这种简便的方式了。 ...
MySQL中InnoDB的间隙锁问题
09-10
主要介绍了MySQL中InnoDB的间隙锁问题,提醒用户注意死锁情况,需要的朋友可以参考下
MySQL InnoDB中的锁机制深入讲解
09-09
在行级锁中，InnoDB使用了两种基本的锁类型：共享锁（Shared Locks，也称为S锁）和排他锁（Exclusive Locks，也称为X锁）。共享锁允许一个事务读取一行，而排他锁允许事务读取和修改一行。多个事务可以同时持有同一...
innodb锁
04-20
**innodb锁**是MySQL数据库InnoDB存储引擎中的一种关键机制，用于管理并发事务并确保数据的一致性和完整性。在多用户环境下，InnoDB通过锁来实现事务的隔离，防止脏读、不可重复读和幻读等并发问题。这篇博客文章...
MySQL InnoDB之事务与锁详解
09-11
在InnoDB中，不同的SQL操作对应不同的锁类型： - `SELECT ... LOCK IN SHARE MODE`：获取共享锁，允许多个事务读取同一数据，但不允许其他事务修改。 - `SELECT ... FOR UPDATE`：获取更新锁，锁定行，防止其他事务...
Oracle 不使用索引，索引失效的几种情况
soo的专栏
01-27 826
1, 2,单独的>, 3,like "%_" 百分号在前. 4,表没分析. 5,单独引用复合索引里非第一位置的索引列. 6,字符型字段为数字时在where条件里不添加引号. 7,对索引列进行运算.需要建立函数索引. 8,not in ,not exist. 9,当变量采用的是times变量，而表的字段采用的是date变量时.或相反情况。 10, 索引失效。 11,基于cost成
Phoenix全局索引
02-25
索引本质 • 全局索引是Phoenix二级索引的一种 • 全局索引的本质是通过一张单独的HBase存储索引数据，用空间换时间加速查询 • 查询路径 • 如果查询的列都在索引表中, 只需要查询一次索引表 • 如果查询的列只有部分在索引表中，需要回查数据表 • 功能扩展 • 全局函数索引 • 全局覆盖索引
SQL中不建议使用 where 1=1 的说法，是错误的
weixin_44602591的博客
11-15 1619
SQL中不建议使用 where 1=1 的原因 where1=1不影响SQL性能，也不会导致索引失效。因为SQL优化器已经将where1=1过滤了。 e1=1过滤了。
Oracle和Mysql数据库使用Where 1=1会使索引失效吗？
最新发布
输入技术、输出想法
03-19 1165
索引全扫描过程简述：索引全扫描是指扫描目标索引所有叶子块的索引行，但不意思着需要扫描所有的分支块，索引全扫描时只需要访问必要的分支块，然后定位到位于改索引最左边的叶子块的第一行索引行，就可以利用改索引叶子块之间的双向指针链表，从左往右依次顺序扫描所有的叶子块的索引行。全表扫描是采用多数据块一起扫的，并不是一个个数据库扫的，然后我们经常说全表扫描慢是针对数据量很多的情况，数据量少的话，全表扫描并不慢的，不过随着数据量越多，高水位线也就越高，也就是说需要扫描的数据库越多，自然扫描所需要的IO越多，时间也越多。
MySQL数据库的全局锁、表级锁和行级锁的全面深度解析
m0_73311735的博客
08-18 2111
详细介绍了MySQL数据库中的全局锁、表级锁和行级锁的概念、使用方式，以及相关特性。
where 1=1
兴趣是最好的老师
06-09 885
不少程序员老司机都会告诉我们，别使用 where 1=1，因为存在性能问题。但实际真的是这样吗？ 也有不少看过极客时间《MySQL实战45讲》的朋友回答错误。这虽然是一个小知识，但是很多人都没有思考过这个问题。今天我们来一探究竟。 在很多时候，我们面临着拼接 SQL 的业务。而拼接 SQL 一般需要用 AND 连接，而 where 后面第一个条件是不能写 AND 的，所以，往往在拼接 SQL 时要注意我的查询条件是不是 where 后面的第一个查询条件。如果是，则去掉 AND，如果不是则可以拼接上 AN
能使 Oracle 索引失效的七大限制条件
lijia900924的专栏
01-07 856
【IT168 技术】Oracle 索引的目标是避免全表扫描，提高查询效率，但有些时候却适得其反。例如一张表中有上百万条数据，对某个字段加了索引，但是查询时性能并没有什么提高，这可能是 oracle 索引失效造成的。oracle 索引有一些限制条件，如果你违反了这些索引限制条件，那么即使你已经加了索引，oracle还是会执行一次全表扫描，查询的性能不会比不加索引有所提高，反而可能由于数据库维护索引的
高性能的Mysql读书笔记系列之五（创建高性能的索引）
jdk_wangtaida的博客
09-21 1890
前言： 正文： 总结：
mysql联合索引详解
热门推荐
weixin_42845574的博客
03-30 3万+
比较简单的是单列索引（b+tree）。遇到多条件查询时，不可避免会使用到多列索引。联合索引又叫复合索引。 b+tree结构如下： 每一个磁盘块在mysql中是一个页，页大小是固定的，mysql innodb的默认的页大小是16k，每个索引会分配在页上的数量是由字段的大小决定。当字段值的长度越长，每一页上的数量就会越少，因此在一定数据量的情况下，索引的深度会越深，影响索引的查找效率。 对于复合索引...
mysql视图索引生效吗_mysql索引左侧原则，你真的了解吗？
weixin_39958248的博客
12-05 509
前言写这篇文章源自一位杠精同事提了个问题,左侧原则跟where条件顺序有无关系?我想了想,好像是有关系的!不敢确定,但是自己又懒得动手测试,于是发起ETC自动抬杠功能,强行杠了一拨,结果杠输了,接下来即是动手验证..预习执行计划实践咱们先申明前置条件,创建表如下:创建复合索引如下注意哦,索引使用的BTree:我们先来一个提问,看如下两条sql,我们花5秒时间思考下,会走索引吗?sql-1 根据co...
Innodb中的锁类型
04-24
InnoDB中有两种类型的锁：共享锁和排他锁。共享锁（Shared Locks）也称为读锁，当事务需要读取数据时，可以给数据加上共享锁。多个事务可以同时持有同一份数据的共享锁，但是如果一个事务持有了共享锁，则其他事务就不能给该数据加上排他锁。排他锁（Exclusive Locks）也称为写锁，当事务需要修改数据时，需要给数据加上排他锁，其他事务就不能给该数据加上任何类型的锁。只有当持有共享锁的事务释放锁之后，才能给该数据加上排他锁。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值