1-概念
DDL(操作字段):
操作数据库、表、列等(这些对象进行操作),使用的关键字:CREATE、 ALTER、 DROP。
DML(操作数据)
是对表中的数据进行增、删、改的操作。不要与DDL混淆了。 INSERT 、UPDATE、 DELETE。
两段锁
数据库遵循的是两段锁协议,将事务分成两个阶段,加锁阶段和解锁阶段(所以叫两段锁)
加锁阶段:
在该阶段可以进行加锁操作。
在对任何数据进行读操作之前要申请并获得S锁(共享锁,其它事务可以继续加共享锁,但不能加排它锁),
在进行写操作之前要申请并获得X锁(排它锁,其它事务不能再获得任何锁)。
加锁不成功,则事务进入等待状态,直到加锁成功才继续执行。
解锁阶段:
当事务释放了一个封锁以后,事务进入解锁阶段,在该阶段只能进行解锁操作不能再进行加锁操作。
2-锁的级别分类 (表级锁, 行级锁, 页面锁)
表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。
MyISAM和MEMORY存储引擎, InnoDB存储引擎
行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。
InnoDB存储引擎的默认锁
页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般 .
主要是BerkeleyDB存储引擎。
2-1 行级锁
注意:
(1)-行锁必须有索引才能实现,否则会自动锁全表,那么就不是行锁, 是表锁。
(2)- 两个事务不能锁同一个索引
S锁(共享锁/读锁): shared lock
又称读锁,
若事务T对数据对象A加上S锁,则事务T可以读A但不能修改A,
其他事务只能再对A加S锁,而不能加X锁,
直到T释放A上的S锁。
这保证了其他事务可以读A,但在T释放A上的S锁之前不能对A做任何修改。
上共享锁的写法:lock in share mode
例如: select math from zje where math>60 lock in share mode;
X锁(排他锁) exclusive lock
又称写锁。
若事务T对数据对象A加上X锁,事务T可以读A也可以修改A,
其他事务不能再对A加任何锁(包括共享锁和排他锁),直到T释放A上的锁。
(之所以执行update,delete,insert进入等待状态, 是以为这些语句都会自动给涉及到的数据加上排他锁)
但可以直接通过select …from…查询数据,因为普通查询没有任何锁机制
上排它锁的写法:for update
例如:select math from zje where math >60 for update;
第一步: 执行窗口1前两行,
第二步: 执行窗口2, 程序waiting.,,,,,,,
第三步: 执行窗口1, 最后一行, 窗口2执行出现结果
2-2-表级锁(表锁/元数据锁Meta_data lock,MDL)
表锁:
表锁的语法是 lock tables … read/write。
可以用unlock tables主动释放锁,
也可以在客户端断开的时候自动释放。
需要注意,lock tables语法除了会限制别的线程的读写外,也限定了本线程接下来的操作对象。
举个例子,
如果在某个线程A中执行lock tables t1 read, t2 write; 这个语句,
则其他线程写t1、读写t2的语句都会被阻塞。
同时,线程A在执行unlock tables之前,也只能执行读t1、读写t2的操作。
连写t1都不允许,自然也不能访问其他表。
在还没有出现更细粒度的锁的时候,表锁是最常用的处理并发的方式。
而对于InnoDB这种支持 行锁的引擎,一般不使用lock tables命令来控制并发,毕竟锁住整个表的影响面还是太大。
MDL(metadata lock)
MDL不需要显式使用,在访问一个表的时候会被自动加上。
MDL的作用是,保证读写的正确性。
如果一个查询正在遍历一个表中的数据,而执行期间另一个线程对这个表结构做变更,
删了一列,那么查询线程拿到的结果跟表结构对不上,肯定是不行的。
因此,在MySQL 5.5版本中引入了MDL,
当对一个表做增删改查操作的时候,加MDL读锁;
当要对表做结构变更操作的时候,加MDL写锁。
读锁之间不互斥,因此你可以有多个线程同时对一张表增删改查。
读写锁之间、写锁之间是互斥的,用来保证变更表结构操作的安全性。
因此,如果有两个线程要同时给一个表加字段,其中一个要等另一个执行完才能开始执行。
虽然MDL锁是系统默认会加的,但却是你不能忽略的一个机制。
比如下面这个例子,我经常看到有人掉到这个坑里:
给一个小表加个字段,导致整个库挂了。
你肯定知道,给一个表加字段,或者修改字段,或者加索引,需要扫描全表的数据。
在对大表操作的时候,你肯定会特别小心,以免对线上服务造成影响。
而实际上,即使是小表,操作不慎也会出问题。
我们来看一下下面的操作序列,假设表t是一个小表(备注:这里的实验环境是MySQL 5.6)。
- 我们可以看到session A先启动,这时候会对表t加一个MDL读锁。
- 由于sessionB需要的也是MDL读锁,因此可以正常执行。
- 之后session C会被blocked,是因为sessionA的MDL读锁还没有释放,而session C需要MDL写锁,因此只能被阻塞。
- 如果只有sessionC自己被阻塞还没什么关系,但是之后所有要在表t上新申请MDL读锁的请求也会被session C阻塞。
- 前面我们说了,所有对表的增删改查操作都需要先申请MDL读锁,就都被 锁住,等于这个表现在完全不可读写了。
如何安全地给小表加字段?
1: kill事务
首先我们要解决长事务,事务不提交,就会一直占着MDL锁。
在MySQL的information_schema 库的 innodb_trx 表中,你可以查到当前执行中的事务。
SELECT CONCAT_WS('','kill',' ',t.trx_mysql_thread_id,';')a FROM information_schema.INNODB_TRX t;
如果你要做DDL变更的表刚好有长事务在执行,要考虑先暂停DDL,或者kill掉这个长事务。
但考虑一下这个场景。如果你要变更的表是一个热点表,虽然数据量不大,但是上面的请求很频繁,而你不得不加个字段,你该怎么做呢?
这时候kill可能未必管用,因为新的请求马上就来了。
比较理想的机制是,以下2种
2: 手动修改数据库的超时时间: lock_wait_timeout 参数
将参数修改为30s, 默认是31536000s(1年…)
3: 在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 ...
大表添加字段
大多数的alter table操作都本质:
lock — copy to new table — rename — unlock的过程,
如果数据很多, 锁表时间会很长
# 创建新表
CREATE TABLE main_table_new LIKE main_table;
# 新表添加字段
ALTER TABLE main_table_new ADD COLUMN location varchar(256);
# 旧表数据添加至新表(这里可以在使用触发器, 然后旧表数据分批处理)
INSERT INTO main_table_new (fields_in_main_table) SELECT * FROM main_table;
# 重新命名新表
RENAME TABLE main_table TO main_table_old, main_table_new TO main_table;
# 删除旧表
DROP TABLE main_table_old;