1)数据库为什么要设计锁?
-
处理并发问题,因为数据库是共享资源
2)根据加锁的范围,mysql里面的锁有哪几种?
-
全局锁
-
表锁
-
行锁
3)全局锁
what?
-
对整个数据库实例加锁
why?
-
优点:防止在备份期间有业务修改数据库的数据,导致问题的出现。
how?
-
让整个库只读的命令:Flush tables with read lock
-
释放锁:unlock tables
应用场景?
-
做全库逻辑备份 ,把整库每个表都 select 出来存成文本。
3.1)全局读锁存在哪些问题?
-
如果你在主库上备份,那么在备份期间都不能执行更新,业务基本上就得停摆;
-
如果你在从库上备份,那么备份期间从库不能执行主库同步过来的 binlog,会导致主从延迟。
3.2)既然我们全局读锁备份时业务停摆,那有没有什么解决方案,让业务不停摆又能让备份的逻辑一致?
-
mysql官方逻辑备份工具mysqldump。使用的时候加上参数–single-transaction。导数据之前就会启动一个事务,来确保拿到一致性视图。而由于 MVCC(多版本并发控制) 的支持,这个过程中数据是可以正常更新的。
3.3)既然mysqldump做备份这么好用,那干嘛还要FTWRL命令?
-
mysqldump是需要引擎有事务支持的,而mysql自带的引擎MYISAM是不支持事务的。
3.4)既然备份过程中要全库只读,那为什么不可以用set global readonly=true命令呢?
-
有些系统中,readonly 的值会被用来做其他逻辑,比如用来判断一个库是主库还是备库。
-
在异常处理机制上有差异
-
FTWRL 命令执行后客户端异常断开,MySQL 会自动释放这个全局锁,业务可正常运行。
-
readonly执行后客户端发生异常则数据库会呆呆的保持只读状态
-
4)表级锁
what?
-
锁的范围是表,有两种
-
表锁
-
元数据锁(meta data lock,MDL)。
-
why?
-
没有出现更细粒度的锁的时候,表锁是最常用的处理并发的方式
how?
-
表锁的语法:lock tables … read/write
-
释放表锁:unlock tables/客户端断开自动释放
-
元数据锁是自动挡,在访问一个表的时候被自动加上。mysql5.5版本之中引入的。
-
对表内容增删改查时:加MDL读锁
-
对表结构变更时:加MDL写锁
-
4.1)为什么对表内容增删改查加读锁而对表结构变更时加MDL写锁?
-
读锁之间不互斥,因此可以有多个线程同时对一张表增删改查
-
读写锁之间、写锁之间是互斥的,用来保证变更表结构操作的安全性。因此,如果有两个线程要同时给一个表加字段,其中一个要等另一个执行完才能开始执行。
4.2)InnoDB引擎为什么不用表锁Lock tables来控制并发?
-
InnoDB引擎有粒度更细的行锁,当然用行锁。
4.3)因为MDL锁机制我们修改一个小表而导致整个库挂了的教训?
AB是更新表内容,所以会自动加上MDL读锁,他们可以并发执行的。而我们的C是改变表结构,需要加上MDL写锁,但是此时MDL正在忙AB的事,没空理C,C就阻塞了,然后D是读表内容,申请MDL读锁,但是要在C基础上。也跟着遭罪被阻塞,后面一直遭罪堵着,就是因为C。
也就是说我们现在t这张小表因为C这个害群之马不能读了,假如我几万个用户要来读这张表,这个库的线程很快就爆满挂掉。
4.4)事务中的MDL锁有什么特点?
-
语句执行开始时申请,但是语句结束后并不会马上释放,而是要等到事务结束。
4.5)如何安全地给小表加字段?
-
首先我们要解决长事务,事务不提交,MDL锁不释放。先去information_schema 库的innodb_trx 表中看看有没有长事务正在执行,要么让它先执行完,或者干掉kill它。那我就可以正常给小表加字段。
4.6)假如一个小表频繁被用户用着,那我怎么加一个必须加的字段?
-
这个时候kill也没用,因为下一个请求马上就来,杀不完的。
-
在 alter table 语句里面设定等待时间,在这个时间内如果能见缝插针拿到MDL锁最好,拿不到就放弃,业务优先。之后再通过重试命令重复这个过程。
4.7)在mysql中4.6的操作是怎样实现的?
-
MariaDB 已经合并了 AliSQL 的这个功能,所以这两个开源分支目前都支持 DDL NOWAIT/WAIT n 这个语法。
ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ...
4.8)假如你发现你的应用程序里有 lock tables 这样的语句,有哪些可能的情况?
-
你的系统现在还在用 MyISAM 这类不支持事务的引擎,那要安排升级换引擎
-
你的引擎升级了,但是代码还没升级。最后业务开发就是把 lock tables 和 unlock tables 改成 begin 和 commit,问题就解决了。
4.8.1)为什么把lock tables 和 unlock tables 改成 begin 和 commit问题就解决了?
-
原本需要用到表锁的场景都可以直接用事务替代,因为事务自动添加 MDL 锁,MDL 锁能够区别对待表结构的修改和普通增删改查,粒度更细。