锁的初衷 - 处理并发问题
当并发访问出现的时候,数据库需要合理地控制资源的访问规则。锁就是用来实现访问规则的重要数据结构。
根据加锁的范围,分为三种:全局锁、表级锁、行锁(Innodb支持)。
全局锁
添加全局读锁: flush tables with read lock
执行完该命令后会让库处于只读状态,其他线程以下的语句会被阻塞:增删改、建表、修表和更新类事务提交。
全局锁的使用场景: 全库逻辑备份。 - 把整库每个表都select出来存成文本。 步骤1: FLWRL 步骤2: 执行备份操作 mysqldump -u -p -all-databases > .sql 步骤3: 释放全局读锁
注意事项: 1.执行全库逻辑备份时,所有数据库操作都被暂停,以避免在数据过程中发生变化。 2.非常耗时 3. FTWRL会阻塞备库binlog同步，同时会阻塞更新等语句。 4. 也要考虑大量更新操作过来一直阻塞，MySQL线程爆了的风险。set gloabl readonly=true，只能针对非super用户只读，binlog可以同步。set global super_read_only=true，针对所有用户只读，并且阻塞binlog同步。
- mysqldump的--single-transaction参数能够在支持事务的引擎（如InnoDB）上无锁备份数据，保持一致性。但对于不支持事务的引擎（如MyISAM），需要使用FTWRL来锁定所有表，确保备份一致性。因此，DBA通常推荐使用InnoDB等支持事务的存储引擎。
全库只读不用set global readonly=true
- 需要判断readonly的值会被用来做其他逻辑的。
- 异常处理机制上有差异。执行FTWRL,由于客户端发生异常断开,MYSQL会自动释放这个全局锁，整个库可以正常更新状态。而将库设置为readonly之后,如果客户端发生异常,则数据库一直readonly处于不可写状态,风险较高。
表级锁
- 表锁
- 元数据锁(meta data lock,MDL)
表锁的语法是 lock tables … read/write。与 FTWRL 类似，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。
- 需要注意，lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。 举个例子, 如果在某个线程 A 中执行 lock tables t1 read, t2 write; 这个语句，则其他线程写 t1、读写 t2 的语句都会被阻塞。同时，线程 A 在执行 unlock tables 之前，也只能执行读 t1、读写 t2 的操作。连写 t1 都不允许，自然也不能访问其他表。
INNODB存储引擎
InnoDB存储引擎确实以索引作为锁定单位，主要使用行锁（row-level locks）来提供高并发性。当执行更新（UPDATE）、删除（DELETE）或查询（SELECT）操作时，InnoDB会尝试锁定涉及到的行。如果这些操作能够通过索引快速定位到特定的行，那么InnoDB就会使用行锁，这样可以锁定尽量少的数据，从而允许其他事务并发地访问数据库中的其他部分。
然而，如果更新或删除操作的条件没有使用索引，或者无法有效利用索引（例如，全表扫描），InnoDB可能无法获取行锁，因为它无法精确地定位到需要锁定的具体行。在这种情况下，为了保持数据的一致性，InnoDB可能会采取更为激进的锁定策略，即降级为表级锁（table-level lock）。表级锁会锁定整个表，阻止其他事务对表中的任何数据进行修改，直到锁被释放。
这种锁的降级行为通常发生在以下情况：
全表扫描：当查询条件无法有效使用索引时，数据库可能需要进行全表扫描，这时可能会使用表级锁。
非索引列的条件：如果WHERE子句中的条件涉及非索引列，InnoDB可能无法使用行锁。
隐式锁升级：在某些情况下，即使使用了索引，由于事务的隔离级别或其他锁的存在，InnoDB也可能自动升级锁的粒度。
MDL锁 - 保持读写的正确性
事务中的 MDL 锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。
MDL 会直到事务提交才释放，在做表结构变更的时候，你一定要小心不要导致锁住线上查询和更新。
安全地给小表加字段
1、解决长事务，事务不提交，就会一直占着 MDL 锁。要考虑先暂停 DDL，或者 kill 掉这个长事务。 2、比较理想的机制是，在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者 DBA 再通过重试命令重复这个过程。