mysql的锁机制最全详解

根据加锁的粒度区分

• 全局锁
• 表级锁
• 行锁
  • 记录锁
  • 间隙锁
  • 临键锁

根据加锁的场景

• 乐观锁
• 悲观锁

全局锁

锁对象是:整个数据库实例

Flush tables with read lock (FTWRL)-会让整个库处于只读状态

使用场景: 做全库逻辑备份

全库逻辑备份

为什么要进行全局锁才能进行数据备份呢?

就比如售卖,我一张表记录发货,一张表记录扣款.结果我在备份发货记录表.这个时候有人买东西了,只扣款了但是没有发货记录.这个显然是不行的

官方自带的逻辑备份工具是mysqldump。当mysqldump使用参数–single-transaction的时候，导 数据之前就会启动一个事务，来确保拿到一致性视图。而由于MVCC的支持，这个过程中数据是 可以正常更新的。但是这个是基于事务的基础上的,针对myisam数据引擎就不可用,那么就有可能出现有的表不是基于innoDB的数据引擎

当然,如果全部都是innodb的数据引擎表,那么,还是使用默认的mysqldump增加参数–single-transaction来进行全局逻辑备份的好

FTWRL和set global readonly=true的区别

1. readonly会在别的逻辑中参与使用(不同系统不一样)
2. ftwrl可以在客户端链接断开时,自动释放锁.防止造成死锁问题

表级锁

命令:lock table {tableName} read/write(write比read权限大,能write当然能read),unlock table解锁

锁住的资源只允许当前的线程可以执行对应的操作.且当前线程只能对锁住的表进行对应的操作

例如:lock table t1 read,则当前线程只能读不能写,其他线程不能读不能写

MDL锁

不需要显式使用,在访问表时自动加上(为了防止表结构变更带来的问题)

在对一张表进行增删改查时上MDL读锁,在对一张表的结构进行变更时上MDL写锁

• MDL读锁(共享锁),锁之间不互斥.所以可以允许多个线程进行同时的增删改查
• MDL写锁(排它锁),这个锁和其他读写锁都互斥.也就是当前数据变更或者查询或者结构变更,都必须等其他的MDL写锁释放后才能执行

行锁

行锁是引擎层,各个引擎自己实现的(MyISAM不支持行锁,所以该引擎只能一次进行一个线程的update操作)

在事务中:行锁会在需要使用某一行或多行数据时加上,但是所有的行锁都会在该事务提交才会释放也就是说,别的线程需要访问改行数据,就需要等待线程的事务提交之后才能访问 举例:

线程A执行以下操作
begin;
update t1 set a=1 where id=1;

update t2 set b=2 where id=2;

commit

这个时候线程A分别对t1的id=1上锁和t2的id=2上锁.如果此时线程B访问t1的id=1是无法访问的,即使第一条语句已经执行完成了
线程B只有在线程A进行了commit操作之后才能获取其中的数据
复制代码

所以,对于我们来说需要注意的点就是:在进行事务操作时,如果update没有顺序操作,那么就尽量将访问最多的那条语句最后执行(因为上锁是顺序上的,但是释放锁是一起释放的)

死锁

很多情况都回引起死锁,大部分都是针对数据库操作有问题才会导致.比如

线程A和线程B都针对id=1和id=2进行修改并开启事务

线程A先修改了id=1导致id=1被线程A上锁

线程B修改了id=2导致id=2被县城B上锁

此时线程A要等待id=2释放锁后执行对id=2的操作
而线程B要等待id=1释放锁后怼id=1的操作

从而达到了一个循环死锁的情况
复制代码

处理这种问题有两个策略:

1. 直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout(默认50s)来设置。这个不能设置太短,如果不是死锁呢?
2. 发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事 务得以继续执行。将参数innodb_deadlock_detect设置为on(默认就是on)，表示开启这个逻辑。

记录锁

属于行锁的一种情况

针对的是事务在加锁后锁住的某一条记录信息

触发情况:查询条件精准命中且命中的条件字段是唯一的 例如:update t1 set name="张三" where id=12138

作用:记录在被当前事务管理时,加上锁之后不会被其他事务获取产生“重复读”和“数据脏读”的问题

间隙锁

属于行锁的一种情况

间隙的意思就是between中的数据

image.png

在主键索引id中有多个数据未填充,这个时候如果两个线程A和B,A在查询0-10之间的数据,而B在往id=3插入数据,就会造成数据脏读的问题

image.png

所以在进行between等范围查找的是事务时候,会加间隙锁进行约束

临键锁

临键锁会把查询出来的记录锁住，同时也会把该范围查询内的所有间隙空间也会锁住，再之它会把相邻的下一个区间也会锁住。 (临就是相邻的意思)

乐观锁和悲观锁