【MYSQL】锁

锁文章目录锁全局锁表级锁表锁元数据锁意向锁行级锁平时操作数据库时如果多人同时修改同一份数据很容易出现数据错乱MySQL 里的各类锁就是用来避免这种问题的。不少人分不清全局锁、表锁、行锁的区别写 SQL 时经常碰到查询卡住、更新被阻塞的情况。这份笔记从大范围到小范围把所有常用锁的用法、适用场景都整理清楚方便大家理解区分全局锁锁住整个 MySQL 实例所有库所有表属于全局只读锁。规则所有客户端只能查不能增删改、不能 DDL持有锁的会话自身可以正常读写其他会话写操作会阻塞直到解锁。使用场景—备份数据1.加锁flash tables with read lock;2.备份mysqldump -u用户名-p密码 要拷贝的表 拷贝到的地方3.解锁unlock tables;表级锁表锁对于表锁分为两类读锁多个会话可同时加读锁互相不影响查询任何会话不能增删改该表写操作阻塞。语法lock table 表名 read;unlock tables;写锁只有持有锁的会话能查、改表数据其他会话不管读、写全部阻塞。语法lock table 表名 write;unlock tables;元数据锁MDL 元数据锁每张表自带作用对象是表结构信息表名、字段、索引不是行 / 表数据。只要事务访问一张表SELECT/INSERT/UPDATE事务全程持有 MDL事务结束才释放。两种元素锁共享元数据锁读MDL普通 DML增删改查申请共享元数据锁多个事务可以同时持有互不冲突2. 排他元数据锁写MDLDDL 语句ALTER、DROP、TRUNCATE、RENAME申请排他元数据锁当有共享数据锁时此时提交DDL语句DDL语句将会阻塞直到前面申请的共享数据锁释放后DDL语句才能执行同时如果后面有共享数据锁后面的DML语句也会被阻塞只有前面的DDL语句执行完后后面的DML语句才能执行对应情况如下对应 SQL锁类型说明lock tables xxx read / writeSHARED_READ_ONLY / SHARED_NO_READ_WRITE手动表锁对应的 MDL 模式select、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 锁全部互斥意向锁意向锁是用来解决行锁和表锁之间的冲突的如果不上意向锁当一个窗口进行增删改查的时候另一个窗口并发进行表锁此时另外一个窗口要从上到下遍历是否有行锁降低了效率而上了意向锁后就不要挨个遍历行一下可以看出是否有行锁。意向锁分为两类共享意向锁IS触发SQLselect … lock in share mode;与表锁read兼容与表锁write互斥排他意向锁IX触发SQLinsert / update / delete / select … for update;与表锁read和write都互斥查看当前的所有锁SELECT object_schema,object_name,index_name,lock_type,lock_mode,lock_data FROM performance_schema.data_locks行级锁只锁某一行数据不锁整张表并发性能远高于表锁仅 InnoDB 支持MyISAM 没有行锁。依赖索引生效查询条件不走索引时行锁会退化成表锁。例子update stu set name‘lily’ where name‘mary’;此时name没有索引那么在另一个窗口想要更新一条非上面那一条的数据也是更新不了的因为此时本身只锁lily的行锁升级为表锁其他数据的更新也会被阻塞。本质是给索引条目加锁不是锁物理记录行锁分为两类共享锁读锁语法select … lock in share mode;规则多个事务可同时加 S 锁共享读持有 S 锁时其他事务不能加写锁只能读场景需要保证数据读取期间不被修改排他锁写锁语法DML 自动加insert / update / delete自动加 X 锁手动select … for update;规则一行数据只能有一个事务持有 X 锁其他事务不能读、不能改该行会阻塞等待锁释放SQL行锁类型说明INSERT …排他锁自动加锁UPDATE …排他锁自动加锁DELETE …排他锁自动加锁SELECT正常不加任何锁SELECT … LOCK IN SHARE MODE共享锁需要手动在 SELECT 之后加 LOCK IN SHARE MODESELECT … FOR UPDATE排他锁需要手动在 SELECT 之后加 FOR UPDATE行级锁下面又有两种锁间隙锁Gap定义锁住两条索引记录中间的空白区间不锁已存在的数据行只锁 “空位”。作用阻止其他事务在这个区间插入新数据解决幻读。举例表主键索引数据1、3、5、7执行update set namea where id3;匹配到 id3 这条记录间隙锁锁住两个区间(1,3)、(3,5)其他事务不能插入 2、4避免新增数据导致幻读。临键锁Next-Key Lock使用场景范围查询、、、、between索引数据1、3、5、7执行where id 5临键锁区间(-∞,1]、(1,3]、(3,5]区间内已有数据不能修改区间空位不能插入普通索引等值查询不会降级保留临键锁update t set name‘test’ where phone ‘13800’;此时的phone是普通索引前后间隙都锁住等值查询但匹配不到任何数据update t set name‘xx’ where id 4;表里无 id4 这条记录锁区间(3,5]禁止插入4不能修改区间空位不能插入普通索引等值查询不会降级保留临键锁update t set name‘test’ where phone ‘13800’;此时的phone是普通索引前后间隙都锁住等值查询但匹配不到任何数据update t set name‘xx’ where id 4;表里无 id4 这条记录锁区间(3,5]禁止插入4平时写 SQL 记住两个关键点查询条件建好索引别长时间占用表再修改表结构就能减少数据库卡死、等待的情况更好地保证数据正常读写。如果喜欢我的内容觉得我的内容有帮助到你的可以动动小手点赞收藏我们下期再见