mysql 表级锁_MySQL表级锁

表级锁介绍

由MySQL SQL layer层实现

MySQL的表级锁有两种：

⼀种是表锁。

⼀种是元数据锁(meta data lock，MDL)。

MySQL 实现的表级锁定的争⽤状态变量：

mysql> show status like 'table%';

table_locks_immediate：产⽣表级锁定的次数；

table_locks_waited：出现表级锁定争⽤⽽发⽣等待的次数；

表锁介绍

表锁有两种表现形式：

表共享读锁(Table Read Lock)

表独占写锁(Table Write Lock)

⼿动增加表锁：

lock table 表名称 read(write),表名称2 read(write)，其他;

查看表锁情况：

show open tables;

删除表锁：

unlock tables;

表锁演示

环境准备

--新建表

CREATE TABLE mylock (

id int(11) NOT NULL AUTO_INCREMENT,

NAME varchar(20) DEFAULT NULL,

PRIMARY KEY (id)

);

INSERT INTO mylock (id,NAME) VALUES (1, 'a');

INSERT INTO mylock (id,NAME) VALUES (2, 'b');

INSERT INTO mylock (id,NAME) VALUES (3, 'c');

INSERT INTO mylock (id,NAME) VALUES (4, 'd');

读锁演示

1、表读锁

session1(Navicat)、session2(mysql)

1)session1: lock table mylock read; -- 给mylock表加读锁

2)session1: select * from mylock; -- 可以查询

3)session1：select * from tdep; --不能访问⾮锁定表

4)session2：select * from mylock; -- 可以查询 没有锁

5)session2：update mylock set name='x' where id=2; -- 修改阻塞,⾃动加⾏写锁

6)session1：unlock tables; -- 释放表锁

7)session2：Rows matched: 1 Changed: 1 Warnings: 0 -- 修改执⾏完成

8)session1：select * from tdep; --可以访问

2、表写锁

session1(Navicat)、session2(mysql)

1)session1: lock table mylock write; -- 给mylock表加写锁

2)session1: select * from mylock; -- 可以查询

3)session1：select * from tdep; --不能访问⾮锁定表

4)session1：update mylock set name='y' where id=2; --可以执⾏

5)session2：select * from mylock; -- 查询阻塞

6)session1：unlock tables; -- 释放表锁

7)session2：4 rows in set (22.57 sec) -- 查询执⾏完成

8)session1：select * from tdep; --可以访问

元数据锁介绍

MDL不需要显式使⽤，在访问⼀个表的时候会被⾃动加上。MDL的作⽤是，保证读写的正确性。你可以想象⼀下，如果⼀个查询正在遍历⼀个表中的数据，⽽执⾏期间另⼀个线程对这个表结构做变更，删了⼀列，那么查询线程拿到的结果跟表结构对不上，肯定是不⾏的。

因此，在 MySQL 5.5 版本中引⼊了 MDL，当对⼀个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。

读锁之间不互斥，因此你可以有多个线程同时对⼀张表增删改查。

读写锁之间、写锁之间是互斥的，⽤来保证变更表结构操作的安全性。因此，如果有两个线程要同时给⼀个表加字段，其中⼀个要等另⼀个执⾏完才能开始执⾏。

元数据锁演示

session1(Navicat)、session2(mysql)

1、session1: begin;--开启事务

select * from mylock;--加MDL读锁

2、session2: alter table mylock add f int; -- 修改阻塞

3、session1：commit; --提交事务 或者 rollback 释放读锁

4、session2：Query OK, 0 rows affected (38.67 sec) --修改完成Records: 0 Duplicates: 0 Warnings: 0

我们可以看到 session A 先启动，这时候会对表 t 加⼀个 MDL 读锁。由于 session B 需要的也是 MDL读锁，因此可以正常执⾏。

之后 session C 会被 blocked，是因为 session A 的 MDL 读锁还没有释放，⽽ session C 需要 MDL 写锁，因此只能被阻塞。

如果只有 session C ⾃⼰被阻塞还没什么关系，但是之后所有要在表 t 上新申请 MDL 读锁的请求也会被 session C 阻塞。前⾯我们说了，所有对表的增删改查操作都需要先申请 MDL 读锁，就都被锁住，等于这个表现在完全不可读写了。

你现在应该知道了，事务中的 MDL 锁，在语句执⾏开始时申请，但是语句结束后并不会⻢上释放，⽽会等到整个事务提交后再释放。~~~~