在MySql中,可以将锁分为全局锁 表级锁 行锁,接下来分别介绍这几种锁
1.全局锁:就是对整个数据库实例加锁,MySql提供了一个全局读锁的方法,命令是Flush tables with read lock(FTWRL) 当想要整个数据库处于只读的状态的时候,可以使用如上的命令,会阻塞其他线程的一些sql语句,数据更新语句(数据库增删改) 数据定义语句(建表,修改表结构)和更新类事务的提交语句
其作用主要是为了全局的逻辑备份,将整个库的每个表select出来存成文本
之前的时候,有一种使用方法,使用FTWRL确保不会有其他的线程对数据库进行更新,让整个库做备份
但是,这很危险
(1)如果在主库上备份,备份期间就不能进行任何更新,业务就得停摆
(2)如果在备库上备份,备份期间就不能执行主库同步来的binlog,导致主从延迟
(3)但是如果备份的时候不进行FTWRL;,可能导致备份中,备份的数据不一致,也就是所谓的视图不一致性
但是现在的备份工具,利用了事务的隔离级别中的可重复度,可以在任务开始的时候创建一个视图,然后在事务执行的过程中,这个视图是具有一致性的
比如,MySql官方自带的逻辑备份工具mysqlDump,当mysqlDump使用 -single-transaction时候,会在导出数据的时候启动一个事务,来确保拿到一致性视图,这个过程中数据是可以正常更新的,但是需要考虑一个问题,对于可重复读这个隔离级别,有些数据库引擎是不支持的,那就只能使用FTWRL命令了
除此外,set global readonly = true,也可以让全库进入只读的状态,但是不会有人在备份的时候使用的
因为在互为主备的数据库集群中,往往是通过readOnly是否等于true来判断是否为主库
而且,使用全局锁时候如果连接断开了,释放这个锁,但是设置readOnly则不会自动设置回去
2.表级锁
分为表锁和元数据锁
表锁的语法是lock tables… read/write
就是简单的锁着这个表,让其他的线程无法进行读写
元数据锁MDL,在访问一张表的时候会自动的加上,保证读写的正确性
而且这个元数据锁,分为了读写锁
读锁直接不互斥,操作就是对同一个表进行增删改查工作
写锁则是对一个表进行相关的DDL的时候才会加
读写之间互斥,写锁之间互斥,如果上了写锁,就必须等待完成
或者尝试获取写锁,也必须等待其他完成
同样其可能带来线程的阻塞问题,那么如何安全的给小表加上字段呢?
可以在使用读写锁的语句上,加上等待时间
使用NOWAIT或者WAIT N来进行保证获取时间
alter table user NOWAIT add column
alter table user wait n add column
那么,在全局备份中,如果主表传来一个DDL语句,会怎么样
实际上看这个DDL语句会在备份过程中的几个关键语句列出
| Q1:SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Q2:START TRANSACTION WITH CONSISTENT SNAPSHOT； /* other tables */ Q3:SAVEPOINT sp; /* 时刻 1 */ Q4:show create table `t1`; /* 时刻 2 */ Q5:SELECT * FROM `t1`; /* 时刻 3 */ Q6:ROLLBACK TO SAVEPOINT sp; /* 时刻 4 */ /* other tables */ |
Q1是设置事务
Q2启动事务
Q3,设置保存点
Q4,拿到了表结构
Q5,正式的导数据
Q6,回滚到保存点
如果在Q4到达了,说明没有任何的影响
如果Q5到达,会检测到表的结构被改过了,会报Table defintion has changed
如果在时刻1 时刻2之间到达了,mysql的备份会占着MDL读锁,等待到Q6完成了,然后主从延迟备份
如果在时刻4之后,就更加不会出现问题,因为拿到了DDL之前的表数据
3.行锁
首先说,行锁是引擎层的每个引擎自己实现的,但并非所有的引擎都支持行锁,比如MyISAM引擎就不支持行锁,不支持行锁意味的并发控制只能使用表锁,使用表锁就代表着性能的低下,这也是为何MyISAM被InnoDB替代的原因之一
减少锁的粒度,就可以提高并发的能力
首先看两个事务的执行过程,,一个事务A,一个事务B
事务A 和 事务B 更新了同一行,那么同一时间只能有一个事务能够完成更新
事务A在执行两个update语句的时候,会持有两个记录的行锁,直到commit之后才会释放
在InnoDB事务中,行锁是在需要的时候进行加上,在事务结束之后才会释放,这就是两阶段锁协议
根据这个协议,我们可以尽可能的去优化锁这种结构了,也就是如果事务中需要锁多个行,可能造成锁冲突,这就需要我们将尽可能的造成锁冲突的语句放在后面
假设有一个购物语句
1.进行顾客的账户扣款
2.进行银行的账户加款
3.记录一条交易日志
最好就是将银行的账户扣款放在后面,因为银行的账户是一个共享的账户
这就是尽可能的减少锁的等待时间,提高了并发度
但是,尽管如此,共享数据的锁还可能导致死锁和死锁检测
常见的如上,a事务等待b事务的2id的释放锁,b事务等待a事务的1id释放锁
进入了死锁状态,出现了死锁的状态之后 可以有两种处理策略
1.进入等待,直到达到超时时间,可以通过设置 innodb_lock_wait_timeout来设置
2.发生死锁检测,在发现死锁之后,回滚死锁中的某一个事务,让其他的事务继续执行,可以通过设置innodb_deadlock_detect设置为on,来开启这个逻辑
但是通常不会使用第一种逻辑,因为第一种手段,会让默认的waitTime达到50s,想想50S的死锁,太可怕了
通常使用第二种的策略,进行主动的死锁检测,进行快速的处理死锁问题
但是使用死锁检测往往会带来大量CPU消耗,
在确定这个业务不会出现死锁的时候,不如将死锁检测关掉
或者通过控制并发度,比如限制只有10个线程在更新,这种的死锁检测的CPU消耗就很低了
但是这种方法并不可行,因为通常一个应用,有上百个客户端,那样并发数并不可能少
对于银行的业务,可以考虑将整个的账户余额改为十条数据的总和,这样更新的话会变得很简单,但是如果出现了扣款逻辑,需要考虑为0的情况
innodb行级锁通过锁索引来进行实现的,如果一个表的某个字段没有索引的话,会可能导致表级锁的出现,可以通过加入limit 1来避免表级锁