阅读 52

MySQL 学习笔记-01

MySQL的全局锁和表级锁

1. 全局锁

1.1 什么是全局锁?

对整个数据库实例加锁。

1.2 如何加全局锁?

MySQL提供加全局锁的方法:Flush tables with read lock(FTWRL) 这个命令可以使整个库处于只读状态。使用该命令后,数据更新语句, 数据定义语句和更新类事务的提交语句等操作都会被阻塞。

1.3 全局锁的使用场景?

全库逻辑备份,数据库引擎不支持事务,例如MyISAM

使用风险:

1.如果在主库备份,在备份期间不能更新,业务受阻。

2.如果在从库备份,备份期间不能执行主库同步的binlog,导致主从延迟。

如果数据库引擎支持事务,MySQL官方自带的备份逻辑是mysqldump。当mysqldump使用参数-single-transaction 的时候, 导数据之前就会启动一个事务,来确保拿到一致性视图。由于MVCC的支持,这个过程中数据时可以更新的。 但是前提是引擎要支持事务。single-transaction 方法适用于所有的表使用事务引擎的库。如果有的表使用了不支持 事务的引擎,那么备份只能通过FTWRL方法。这往往是DBA要求业务开发人员使用InnoDB代替MyISAM的原因之一。

1.4 备份保证全库只读就行了,为什么不用 set global readonly = true 的方式呢?

确实这个方式也可以让全库进入只读状态,但还是建议用FTWRL。主要有两个原因:

1.在有些系统中,readonly的值会被用来做其他逻辑,例如判断这个库是主库还是从库。 修改global变量的方式影响面更大。

2.异常处理机制上不同,FTWRL命令之后如果客户端发生异常断开,MySQL会自动释放这个全局锁。 而将整个库设置为readonly后,如果客户端发生异常,数据库会一直保持这个状态,导致库长时间处于不可写状态,风险高。

2.表级锁

2.1 什么是表级锁?

MySQL里面的表级锁有两种:一种是表锁,一种是元数据锁(meta data lock,MDL)。 表锁提供了加表锁的语法,元数据锁不需要显示使用,在访问一个表的时候会被自动加上。 元数据锁的作用是保证读写的正确性,保证读写的时候不能修改表结构。当对一个表进行 增删改查操作的时候,会加MDL读锁,当对表结构修改的时候,会加MDL写锁。

2.2 如何加表锁,语法是什么?

表锁的语法是 lock tables ... read/write。与FTWRL类似,可以使用unlock tables主动释放, 也可以在客户端断开连接时释放。lock tables 的语法除了会限制别的线程的读写外,也会限制 本线程的操作对象。

2.3 为什么有时候我给一张小表加个字段,导致整个库挂了?

sessionA执行:select * from user limit 1;事务未提交

sessionB执行:alter table user add companyId int;(block)

sessionC执行:select * from user limit 1;(block)

分析:

sessionA执行语句需要获取读锁,正常执行,事务未提交,读锁未释放

sessionB执行语句需要获取写锁,sessionA MDL 读锁未释放,被sessionA阻塞

此时,sessionB阻塞倒不如何影响,但是之后所有需要在表user上申请读锁的请求都会被阻塞, 所有对表的增删改查操作都需要先申请 MDL 读锁,就都被锁住,等于这个表现在完全不可读写了。

2.4 如何安全的给小表加锁

首先我们要解决长事务,事务不提交,就会一直占着 MDL 锁。在 MySQL 的 information_schema 库的 innodb_trx 表中,你可以查到当前执行中的事务。如果你要做 DDL 变更的表刚好有长事务在执行,要考虑先暂停 DDL,或者 kill 掉这个长事务。但考虑一下这个场景。

如果你要变更的表是一个热点表,虽然数据量不大,但是上面的请求很频繁,而你不得不加个字段,你该怎么做呢?这时候 kill 可能未必管用,因为新的请求马上就来了。比较理想的机制是,在 alter table 语句里面设定等待时间,如果在这个指定的等待时间里面能够拿到 MDL 写锁最好,拿不到也不要阻塞后面的业务语句,先放弃。之后开发人员或者 DBA 再通过重试命令重复这个过程。

笔记来自MySQL实战45讲