mysql 添加字段或者添加表直接锁表 mysql 加字段锁表方案

转载

mob64ca140d96d9 2024-03-11 09:51:59

文章标签 mysql 加字段慢读锁字段数据 文章分类 MySQL 数据库

概述

MySQL的锁分类：

从逻辑架构分为服务器层锁和存储引擎层锁。
服务器层锁有：全局读锁、表级锁，其中表级锁又包含表锁和元数据锁。
存储引擎层锁：行锁。
从对数据操作的类型(读/写)分为共享锁(读锁)和排他锁(写锁)
从对数据操作的粒度划分为表锁、页锁和行锁

服务器层锁

mysql 添加字段或者添加表直接锁表 mysql 加字段锁表方案_读锁

1、全局读锁

全局读锁就是对

整个数据库实例加读锁，让其只对外提供查询功能。

MySQL 提供了一个加全局读锁的方法，命令是：

flush tables with read lock;//加锁，以下简称FTWRLUNLOCK TABLES;//解锁

当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句(数据的增删改)、数据定义语句(包括建表、修改表结构等)和更新类事务的提交语句。但是若使用该命令前，某些表存在写锁，则全局读锁会阻塞等待写锁释放才会加锁。

全局锁的典型使用场景是，做

全库逻辑备份，拿到当前一致性数据。在备份过程中整个库完全处于只读状态，也就是把整库每个表都 select 出来存成文本。

下面是验证全局锁示例：

//加速flush tables with read lock;//可以正常查询select * from user;//SQL错误(1223)：Can't execute the query because you have a conflicting read lock update user set user_name='rose' where user_id=5;//解锁unlock tables;//正常操作update user set user_name='rose' where user_id=5;

说到获取一致性数据，后面会介绍事务以及隔离级别、MVCC等知识点。在该知识点中，我们可以通过

可重复读隔离级别+MVCC特性，可以做到一致性数据备份且对外还能继续提供服务。此方案下，可以使用官方自带的逻辑备份工具 mysqldump。当 mysqldump 使用参数single-transaction的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。

既然上面的方案这么好，为什么MySQL还要提供全局读锁呢？

原因是下面的备份方案强依赖于储存引擎是否支持事务，也就是说目前就InnoDB引擎可以，而MyISAM就不可以。

既然要全库只读，为什么不使用 set global readonly=true 的方式呢？确实 readonly 方式也可以让全库进入只读状态，但我还是会建议你用 FTWRL 方式，主要有两个原因：

一是，在有些系统中，readonly 的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此，修改 global 变量的方式影响面更大，我不建议你使用。
二是，在异常处理机制上有差异。如果执行 FTWRL 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高。
readonly 对super权限无效

业务的更新不只是增删改数据(DML)，还有可能是加字段等修改表结构的操作 (DDL)。不论是哪种方法，一个库被全局锁上以后，你要对里面任何一个表做加字段操作，都是会被锁住的。但是，即使没有被全局锁住，加字段也不是就能一帆风顺的，因为你还会碰到接下来我们要介绍的表级锁。

mysql 添加字段或者添加表直接锁表 mysql 加字段锁表方案_读锁

2、表级锁之表锁

(1)概念

显式使用表锁的语法是：

lock tables … read/write;//加读锁或写锁  unlock tables;//释放锁

注：一般客户端断开连接也会释放锁。若存在还是处于连接中，则查到当前的线程id后，使用kill id命令杀死线程。表锁中的读写锁特性总结：

Lock Tables....READ不会阻塞其他线程对表数据的读取，会阻塞其他线程对数据变更。
Lock Tables....READ不允许对表进行更新操作(新增、删除也不行)，并且不允许访问未被锁住的表。
Lock Tables....WRITE会阻塞其他线程对数据读和写。
Lock Tables....WRITE允许对被锁住的表进行增删改查，但不允许对其他表进行访问。

(2)示例演示

下面通过一个示例演示表锁中的读、写锁：

CREATE TABLE `test_product` (  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,  `code` varchar(255) DEFAULT NULL,  `name` varchar(255) DEFAULT NULL,  `price` decimal(10,2) DEFAULT NULL,  `quantity` int(11) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;CREATE TABLE `test_user` (  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,  `name` varchar(255) DEFAULT NULL,  `age` int(3) DEFAULT NULL,  `gender` int(1) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;INSERT INTO `test_user` (`id`, `name`, `age`, `gender`) VALUES ('1', '张三', '16', '1');INSERT INTO `test_user` (`id`, `name`, `age`, `gender`) VALUES ('2', '李四', '18', '1');INSERT INTO `test_product` (`id`, `code`, `name`, `price`, `quantity`) VALUES ('1', 'S001', '产品1号', '100.00', '200');INSERT INTO `test_product` (`id`, `code`, `name`, `price`, `quantity`) VALUES ('2', 'S001', '产品2号', '200.00', '200');INSERT INTO `locktest`.`test_product` (`code`, `name`, `price`, `quantity`) VALUES ('S003', '产品3号', '300.00', 300);INSERT INTO `locktest`.`test_product` (`code`, `name`, `price`, `quantity`) VALUES ('S004', '产品4号', '400.00', 400);INSERT INTO `locktest`.`test_product` (`code`, `name`, `price`, `quantity`) VALUES ('S005', '产品5号', '500.00', 500);

会话1	会话2
LOCK TABLES test_product READ;
update test_product set price=250 where id=2;(错误)
select * from test_user;(错误)
	select * from test_product;(正常查询)
	update test_product set price=250 where id=2;(阻塞)
UNLOCK TABLES;
	更新成功

会话1	会话2	会话3
LOCK TABLES test_product WRITE;
select * from test_product ;(正常)
select * from test_user;(错误)
	SELECT * FROM test_product;(阻塞)
		update test_product set price=250 where id=2;(阻塞)
unlock tables;
	正常查询	正常更新

mysql 添加字段或者添加表直接锁表 mysql 加字段锁表方案_读锁

3、表级锁之元数据锁

(1)概念

元数据锁是 MDL(metadata lock)。 MDL 不需要显式使用，在访问一个表的时候会被自动加上。 MDL 的作用是防止DDL和DML并发的冲突，保证读写的正确性。在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁(共享锁)；当要对表做结构变更操作的时候，加 MDL 写锁(排他锁)。读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。 (2)示例演示下面这个示例展示如何对一张表加字段，导致整个库挂了。

会话1	会话2	会话3	会话4
begin;
select * from t limit 1;
	select * from t limit 1;
		alter table t add f int;(阻塞)
			select * from t limit 1;(阻塞)

分析上表流程：

开启事务
session A 先启动，这时候会对表 t 加一个 MDL 读锁。
由于 session B 需要的也是 MDL 读锁，因此可以正常执行。
之后 session C 会被 blocked，是因为 session A 的 MDL 读锁还没有释放，而 sessionC 需要 MDL 写锁，因此只能被阻塞。
如果只有 session C 自己被阻塞还没什么关系，但是之后所有要在表 t 上新申请 MDL 读锁的请求也会被 session C 阻塞。前面我们说了，所有对表的增删改查操作都需要先申请MDL 读锁，就都被锁住，等于这个表现在完全不可读写了。

此时，该表上有大量的请求，那么后面来的请求线程将全部被阻塞，导致整个库线程池中线程被用完，直接导致库挂了。事务中的 MDL 锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。

那如何安全地给小表加字段？

首先我们要解决长事务，事务不提交，就会一直占着 MDL 锁。在 MySQL 的information_schema 库的 innodb_trx 表中，你可以查到当前执行中的事务。如果你要做 DDL 变更的表刚好有长事务在执行，要考虑先暂停 DDL，或者 kill 掉这个长事务。但考虑一下这个场景。如果你要变更的表是一个热点表，虽然数据量不大，但是上面的请求很频繁，而你不得不加个字段，你该怎么做呢？这时候 kill 可能未必管用，因为新的请求马上就来了。比较理想的机制是，在 alter table语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者 DBA 再通过重试命令重复这个过程。 MariaDB 已经合并了 AliSQL 的这个功能，所以这两个开源分支目前都支持 DDL NOWAIT/WAIT n 这个语法。

ALTER TABLE tbl_name NOWAIT add column ...ALTER TABLE tbl_name WAIT N add column ...

当然，MySQL 5.6+版本提供online DDL特性，使得在修改schema的时候支持DDL执行期间DML语句的并行操作，提高数据库的吞吐量。

InnoDB引擎默认使用的是行锁，其行锁的实现具体有共享锁和排他锁

mysql 添加字段或者添加表直接锁表 mysql 加字段锁表方案_读锁

1、共享和排他锁--Shared and Exclusive Locks

(1)概念

共享锁(读锁或S锁)指的是多个事务对同一个数据

可以共享访问，但是不能操作修改；排他锁(写锁或X锁)指的是某事务获取了排它锁，其余事务就必须等待排他锁释放，只有获取到排他锁才有权限对数据进行读取和修改。显式的添加共享锁、排他锁命令：

//加共享锁select .... lock in share mode;//释锁commit/rollback;//对select语句手动加排他锁select ... for update;

注：delete /update/insert 语句默认加上排他锁

(2)示例演示

表及数据准备如下，隔离级别采用MySQL默认的。

create database study_lock;CREATE TABLE `t` ( `id` int(11) NOT NULL, `c` int(11) DEFAULT NULL, `d` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `c` (`c`) ) ENGINE=InnoDB;  insert into t values(0,0,0),(5,5,5), (10,10,10),(15,15,15),(20,20,20),(25,25,25);

示例1--测试共享锁特性：

会话1	会话2
begin;	begin;
select * from t where id=5 lock in share mode;
	select * from t where id=5 ;(正常)
	update tset c=c+1 where id=5;(阻塞)
rollback;
	commit;

示例2--测试Innodb行锁是如何实现的

1)字段d没有索引

会话1	会话2
begin;	begin;
select * from t where d=0 for update;
	select * from t where d=0 for update;(阻塞)
	select * from t where d=5 for update;(阻塞)
commit;
	commit;

3)字段id条件是主键索引

会话1	会话2
begin;	begin;
select * from t where id=0 for update;(有结果)
	select * from t where id=0 for update;(阻塞)
	select * from t where id=5 for update;(正常)
commit;
	commit;

注：当会话1没有查询到数据时，时候会话2将正常执行。

2)字段c辅助索引

会话1	会话2
begin;	begin;
select * from t where c=0 for update;(有结果)
	select * from t where c=0 for update;(阻塞)
	select * from t where id=5 for update;(阻塞)
commit;
	commit;