当前位置: 首页>后端>正文

mysql lock table的用户 mysql dead lock

1.全局锁:(限制 DML , DDL[修改表结构])

  全局读锁: Flush tables with read lock

    Flush tables 做的是将缓存刷回硬盘,with read lock 给所有表加读锁,对于大部分 lock,当客户端连接断开的时候,锁一般会释放。

    如果在主库上使用此命令,则写业务停摆。在从库上使用此命令,则来自主库的 binlog 无法被执行,主从同步会延后

  全局只读属性:set global readonly = true

    此命令不等同于锁,而是设置数据库的全局可读性,客户端连接断开也不会使得此属性还原,慎用。

    readonly 可能用于从库,不适用于读锁

2.表级锁

  表锁:lock tables tableName read/write  /  unlock tables

    属于 lock,不是属性,也不是 latch(线程同步工具),所以客户端连接断开时,也会解锁

    并且同一线程(客户端连接),就算是自己拥有读锁,也不能进行写操作。

  MDL锁(metadata lock):

    1.增删查改时加 MDL 读锁

    2.改表结构(DDL)加写锁

  需要注意的是,MDL锁在 Mysql 的实现使用了一把锁,但是这把锁会记录两个链表

  一条链表是记录该Lock 已经被获取了的类型,称为 granted ,比如有 N 个连接请求了 MDL 读锁,那么 granted 链表就应该

  有 N 个状态为 MDL_SHARED_XXX 的节点

  还有一条链表是 waiting,表示因为和 granted 链表的任一锁类型冲突而导致需要等待加上的锁

      官方给他们的定义: 

      

mysql lock table的用户 mysql dead lock,mysql lock table的用户 mysql dead lock_链表,第1张

 

  假设一下线程要去获取锁 A, 获取类型是 type,那么该线程需要做两件事,查看自己要获取的 type 是否和

 A的两条链表的锁类型都兼容。才能获得锁。否则阻塞,并且把自己的 type 加入 waitting 链表。

 所以有一种情况:三个客户端连接分别执行 A,B,C。且按A->B->C的顺序执行。

 Transaction A: select * from t;

 Transaction B: alter table t change clomun 'x' 'a'  int null default null;

 Transaction C: select * from t;

 最终 B 和 C 会被堵住,原因就是 A 在 granted 链表中放入了 SHARED 类型的锁节点

 B获取失败,在 waitting 链表放入 EXCLUSIVE 类型的锁节点

 C检查链条链表,发现和自己的锁 waitting 链表中的 EXCLUSIVE 不兼容,所以C也被阻塞。

3.行锁:

  两阶段锁协议:连接在事务中获得的行锁,都在事务结束才会释放。而MDL写锁不会有类似现象(MDL读锁会)。

  行锁调优:涉及 竞争度激烈的行 的语句应该尽量放在 事务的后面,这样的话占有这个行的锁的时间会尽可能短,因为离事务结束更近。

       占用这个锁的时间会相对短,如此一来,这个竞争激烈的行的锁就会更快释放。

       比如说,订单交易的时候,事务中会扣除客户账户余额,和商店营收。多个客户购买的话,就会同时去竞争商店营收这一行数据。

       所以在事务中可以先改用户账户余额,再去改商店营收。或者可以将商店营收改成多个行,然后把不同用户路由到不同行上去修改

       对应营收,求总营收的时候需要将所有营收行锁读锁,然后读取。

4.间隙锁 + 行锁 = net-key lock

  间隙锁 主要为了防止幻读,同时也表明了,加锁的对象不一定是行,也可以是间隙,比如一张表有两条记录 id = 1 和 id = 5

,并且id是主键,那么在主键上的范围(1, 5)就是一个间隙,锁住了这个间隙,其他客户端连接(线程)就不能对这个间隙插入内容,但是可以对这个间隙(不存在的值)做 update 和 delete。注意,间隙锁是不包含行记录的,锁行记录的是行锁。 间隙锁锁的是插入意图,不是更新和删除意图

只有在可重复读隔离级别的情况下,才可能出现幻读的情况,幻读指的是当前事务重复读取的情况下,下一次读取读取到了上一次读取不存在的行。如果是之前读取的了的某一行的内容变了,严格来说不算幻读。在可重复读的情况下,应该叫做脏读。

  间隙锁的加锁规则:

  1. 当使用 update,delete,或者带 for update 或者 lock in share mode 语句时会加锁, 且加锁的单位是 next-key lock

  2. 只有访问到的对象会加锁,此处对象可以是单单辅助索引或者带有数据行的聚簇索引,如果是访问不存在的行,也就是访问间隙的话,就只会加上间隙锁(辅助索引和聚簇索引都一样,就算是辅助索引,因为是访问两个存在行中的不存在行,所以访问到右边的存在行就会停下来,根据索引等值查询优化,不满足条件的右边行的行锁被排出 next-key lock ,所以只剩下间隙锁)

唯一索引(注意不只是聚簇索引)的等值查询会使原本要加的 next-key lock 退化 行锁

非唯一索引 的等值查询会扫描(只有非唯一索引上的等值查询会扫描,因为唯一索引是不能重复的)到最后一个不满足条件时停下,并且最后一个不满足条件的行造成的 next-key lock 会退化成 间隙锁

suprenum

但是还是会遍历到不满足条件为止,这导致多加了一个 next-key lock,比如假设有 id(主键) = 10, 15, 20 的行,条件本来是 where id > 10 and id <= 15,那么按理说加的锁是

(10,  15],因为已经找到 id = 15 的了,接下去不可能再有 id <= 15 的行,但是还是会继续遍历到 20,不满足条件,停止。所以最后加的锁是 (10, 15] 和 (15, 20]

  7.正序 加锁的范围:

等值查询

    2. 对于 col > x and col < y 是从 x 这一行向右范围查询,直到遇到 col >= y

    3. 对于 col >=x and col <= y 和 7.1 一样,只不过遇到 col > y 才停止

    4. 对于 col > x and col <= y 和 7.2 一样,从x 这一行向右范围查询,直到遇到 col > y 等行停止

  8.倒序 加锁的范围

    1. 对于 col >= x and col < y by desc 是从 y 开始向左范围查询,直到遇到 col < x

    2. 对于 col > x and col < y by desc和 8.1 一样,从y开始向左范围查询,直到遇到 col <= x

by desc,从 y 的等值查询开始,向左范围查询,直到遇到 col < x

    4. 对于 col > x and col <= y by desc,从 y 的等值查询开始,向左范围查询,直到遇到 col <= x

间隙锁本身是一种读锁

先加间隙锁,再加行锁。所以如果有线程 A 先持有行锁,线程 B 再去持有间隙锁且要求A的行锁,线程A再去要求B持有的间隙锁,会造成死锁。

    假设只有 id = 1, c = 123 , a = 345 这一行,且c是索引

    A : update table set a = a + 1 where c = 123; (持有 (-max, 123] , (123, suprenum] 这两个next-key lock))

    B : update table set a = a + 2 where c = 123; (加上了 (-max,123) 这个间隙锁,并且堵在了 c = 123 这一行的行锁上)

    A : insert into tables values (2, 100, 666); (请求 B 持有的 (-max,123) 导致死锁)

限制扫描行数,减少访问对象,减少锁的锁定对象 比如非唯一索引c上 有 10 12 15 三个值

    如果:

    A: update table set a = a + 1 where c > 10 limit 1(只会加(10,12]的next-key lock)

    B: insert into table values (123, 13, 123) 仍然能成功(c = 13) 

间隙锁依赖于两条记录之间

    比如非唯一索引c有四条记录 A, B ,C, D 

    那么存在三个间隙锁 (A, B) , (B , C),(C , D)

    倘若现已加 (C, D] next-key lock ,那么删除 C 这一行的时候,(C,D] 会变成 (B, D]

    倘若使用 update table set c = m  where c = A     ( B < m < D)

    执行此语句的线程将被 block,因为上述语句相当于在 被间隙锁锁住的 (B,D) 中插入 c = m 的记录

    再删除 c = A 的记录

 

插入带来的锁

插入不会带来间隙锁 或 next-key lock 但是会在插入的记录上加上排他锁(主键或唯一索引和辅助索引上),所以事务插入一条记录之后,其他事务插入同样的记录不行(辅助索引上值相同也不行)。但是可以在间隙插入事务。

 

意向表锁:

当要加表锁的时候,会先给表加意向排他锁。

加表锁的时候,会先加意向排他锁,如果有其他事务加了意向排他锁,则会阻塞。

免去了加表锁的时候一行行遍历查看是否有行锁的情况。

 




https://www.xamrdz.com/backend/3rs1963576.html

相关文章: