首页 > 数据库 >mysql 锁机制

mysql 锁机制

时间:2023-05-17 15:45:41浏览次数:33  
标签:-- update 死锁 mysql 机制 where id select

表锁:InnoDB 支持行锁,不代表着就不支持表锁

  • 表锁的 S 和 X 锁,MyISAM 的锁(不建议在 InnoDB 中使用)

    -- 查看当前有表锁的表
    show open tables where in_use > 0;
    
    -- 给表加 S 锁(所有事务都能读,所有事务都不能写)
    lock tables table_name read;
    
    -- 给表加 X 锁(拿到锁的事务可写可读,别的事务不能读也不能写)
    lock tables table_name write;
    
    -- 释放锁
    unlock tables;
    
  • 意向锁

    • 如果 T1 给一行数据加了 X 锁,T2 想给表加锁?只能等 T1 结束 T2 才能成功。具体做法是:遍历每行数据看是否有 X 锁,如果都没有就给表加锁,如果有就阻塞
    • 如果表数据量太大,这样性能就不高了,于是有了意向锁
    • 如果有行锁,自动给表添加对应的锁
    • 意义: 做表锁的时候不需要挨行去扫描行锁
  • 自增锁:维护自增主键

  • 元数据锁

    • 也叫 MDL 锁,是个排他锁,alter 表结构的时候如果不持有 mdl 锁就会阻塞
    • 意义:保证查询修改时和表结构是一致的
    • 查询数据时,也会给表上意向锁,这时更新表结构就会阻塞,因为 mdl 锁是排他锁,意向锁没释放是不能获取到元数据锁的

行锁( record lock )

  • 记录锁,存储引擎层实现,InnoDB才有
  • 并发能力强,但是维护锁的消耗变大,会出现死锁
  • 也有 S 和 X 锁,如果根据非索引列更新,会升级为表锁
  • 增删改会自动加 X 锁,查询自动加 S 锁(查询也能手动指定为 X 锁)
  • 一行记录有了 S 锁,别的事务还是能获取 S 锁,但是不能获取 X 锁
  • 一行记录有了 X 锁,别的数据不能获取 S 和 X 锁
  • commit; 提交事务之后会释放锁

间隙锁( gap lock)

  • 是一种特殊的行数,自然也是 X 锁(排他锁)

  • 删除和更新是普通的行数,如果是插入,这条数据原来不存在,给那行记录加锁呢?给间隙加锁!

  • 目的是避免幻影记录即解决幻读

  • 给不存在的记录加锁,这个锁就是间隙锁,该条记录的前后作为区间,开区间,锁区间不锁边界

    -- 数据库存在数据:id = 1, id = 3, id = 8;
    -- 没有的 id=4 的记录,这就是间隙锁,具体区间是值的最近的两个值之间,这里就是(3,8)
    BEGIN;
    SELECT * FROM USER WHERE ID = 4 FOR UPDATE;
    
    -- 另一个事务,插入这个区间的值会阻塞(3和8之间的都不能插入)
    BEGIN;
    INSERT INTO USER (ID, NAME) VALUES (5, 'Marry');
    
    -- 这个间隙锁的区间就是 (8, 正无穷)
    BEGIN;
    SELECT * FROM USER WHERE ID = 10 FOR UPDATE;
    

临键锁( next-key lock )

  • 间隙锁的特殊形式,包括边界

  • 间隙锁是锁区间不锁边界,临键锁就是既锁区间也锁边界

  • 也可以理解为普通的行锁+间隙锁

    -- 数据库存在数据:id = 1, id = 3, id = 8;
    -- 给 (3,8]这个区间加 X 锁,这就是临键锁
    BEGIN;
    SELECT * FROM USER WHERE ID > 3 AND ID <=8 FOR UPDATE;
    
    -- 这时也是不允许插入的
    BEGIN;
    INSERT INTO USER (ID, NAME) VALUES (5, 'Marry');
    

乐观锁悲观锁

  • 悲观锁数据库层面做的,比如扣减库存,查询和更新都加 X 锁来保证并发安全
    • where 条件要有索引,能直接定位到某条记录进行上锁
    • 如果 where 条件没有索引,就是全表扫描,知道找到目标记录,结果会把扫描的记录全部加锁!
    • 适用于写比较多的场景
  • 乐观锁是程序层面做的,两种思路
    • 版本号机制:给每条数据添加个版本,version,每次修改 version+1
      • 每当有修改数据的业务,先查询该条数据的 version,比如当前是 2
      • 做完业务后,更新数据时带上条件 where version = 2
      • 如果更新不成功,说明 version=2 的找不到数据(别的业务更新过该条数据,version 变了)
      • CAS 自旋,再次查询 version,然后更新当前 version 的数据
    • 时间戳机制:和 version 思想一致,值是时间错而已
    • 适用于读比较多的场景

死锁

  • 同一张表(行锁)下的死锁

    session1 session2
    begin; begin;
    id = 1 加 X 锁
    select * from user where id=1 for update;
    id = 2 加 X 锁
    select * from user where id=2 for update;
    阻塞,等待 session2 释放
    select * from user where id=2 for update;
    阻塞,等待 session1 释放
    select * from user where id=1 for update;
    commit; commit;
  • 不同表下的死锁

    session1 session2
    begin; begin;
    select * from tableA where id=1 for update; select * from tableB where id=1 for update;
    select * from tableB where id=1 for update;
    select * from tableA where id=1 for update;
    commit; commit;
  • 间隙锁下的死锁(当前数据库存在id = 1, id = 3, id = 9 三条记录)

    session1 session2
    begin; begin;
    (3,9) 间隙锁
    select * from user where id = 5 for update;
    再加一个 (3,9) 间隙锁
    select * from user where id = 6 for update;
    插入数据,id = 7
    insert into user (id, name) values (7, 'Bob');
    插入数据,id = 8
    insert into user (id, name) values (8, 'Milk');
    commit; commit;
  • 死锁解决方案

    • 等待,直到超时。时间由参数 innodb_lock_wait_timeout 决定,默认 50s(show VARIABLES like 'innodb_lock_wait_timeout';)
    • 回滚 undo 最小的事务。是否死锁根据每个事务再等待那个事务结束,画一个图,如果能形成一个环,那么就是死锁。这时也能知道哪个事务做的操作最小,回滚它来释放锁
  • 避免死锁思路

    • 优化 sql,比如给 name = 'Marry' 的记录加锁,name 涉及成索引列(扫描的行记录都会加锁,如果能根据索引只定位一条记录,那么扫描的行就是一条)
    • 避免大事务,或者拆为小事务
    • 乐观锁
    • 较低隔离级别,提升锁粒度

标签:--,update,死锁,mysql,机制,where,id,select
From: https://www.cnblogs.com/hangychn/p/17408953.html

相关文章

  • mysql 底层数据存储结构
    内存和磁盘每次交互都是完整的页,数据页里面存放的是行(不仅仅是数据库的数据行,还有行格式等)页(16k,计算机与内存的最小单位)的上层单位还有区(一个区存放64个页,64*16k=1024k,刚好1M),区上面是段(一个或多个区组成),段上面是表空间(一个或多个段组成)行格式showtablestatuslike't_u......
  • docker 部署nacos单机版并配置mysql支持
    1.配置mysql数据库选用mysql5.7表名为nacos_config,附上建表SQLSETNAMESutf8mb4;SETFOREIGN_KEY_CHECKS=0;--------------------------------Tablestructureforconfig_info------------------------------DROPTABLEIFEXISTS`config_info`;CREATETABLE......
  • springboot中使用application.properties配置mysql和sqlserver
    1.使用依赖*mysql:<dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId></dependency>*sqlserver:<dependency><groupId>com.microsoft.sqlserver</groupId><art......
  • mysql 存储引擎和索引
    存储引擎引擎特性文件InnoDB默认,支持事务,支持外键,支持行锁和表锁.frm文件存储表结构.ibd文件存储数据和索引MyISAM不支持事务,不支持外键,只支持表锁不支持行锁专门维护了一个常量保存每个表的总记录数(count很快)MyISAM强调的是性能,所以性能上优于InnoDB,但安全......
  • 玩转MYSQL数据库之--视图详解
    前言从今天开始本系列文章就带各位小伙伴学习数据库技术。数据库技术是Java开发中必不可少的一部分知识内容。也是非常重要的技术。本系列教程由浅入深, 全面讲解数据库体系。 非常适合零基础的小伙伴来学习。全文大约【1297】字,不说废话,只讲可以让你学到技术、明白原理的纯......
  • 关于使用Serilog配置MySql数据库和appsettings的问题
    1、项目使用dtonet6WebApi。2、Nuget包:用来访问mysql数据库Pomelo.EntityFrameworkCore.MySqlSerilog日志Serilog配合dotnetSerilog.AspNetCore读取环境变量配置Serilog.Settings.ConfigurationSerilog读取MySqlSerilog.Sinks.MySQL输出到控制台中Serilog.Sinks.Co......
  • 面试官:MySQL 日期时间类型怎么选?
    构建数据库写程序避免不了使用日期和时间,对于数据库来说,有多种日期时间字段可供选择,如timestamp和datetime以及使用int来存储unixtimestamp。不仅新手,包括一些有经验的程序员还是比较迷茫,究竟我该用哪种类型来存储日期时间呢?那我们就一步一步来分析他们的特点,这样我们根据......
  • mysql 基础知识
    --_在mysql中是表示任一字符,下面查的是名字等于*Aaaa的SELECT*FROMUSERWHERENAME='_Aaaa';--如果就要查名字是_Aaaa的人需要使用转移字符,把_当成普通字符SELECT*FROMUSERWHERENAME='\_Aaaa';--或者自定义字符SELECT*FROMUSERWHERENAME='$_Aaaa'......
  • 8张图带你全面了解kafka的核心机制
    前言kafka是目前企业中很常用的消息队列产品,可以用于削峰、解耦、异步通信。特别是在大数据领域中应用尤为广泛,主要得益于它的高吞吐量、低延迟,在我们公司的解决方案中也有用到。既然kafka在企业中如此重要,那么本文就通过几张图带大家全面认识一下kafka,现在我们不妨带入kafka设计......
  • 一个好用的命令行mysql客户端 mycli
    官网:https://www.mycli.net/支持自动补全,高亮提示,非常好用;查看表结构\dtsys_menu;有点遗憾的是没有展示注释信息,不可也可以用其它语句查看:showfullcolumnsfromsys_menu;showcreatetablesys_menu;查询结果导出\Tcsv;\o~/export.csv;SELECT*FROMt_t......