MySQL之锁-表级锁_mysql lock-tables

 

 

MySQL之锁-表级锁

1.表级锁分类

表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM、InnoDB、BDB等存储引擎中。
对于表级锁，主要分为以下三类：
表锁
元数据锁（meta data lock，MDL）
意向锁
注：接下来的都是以InnoDB里的表级锁来进行讲解

2.表锁

2.1分类

对于表锁，分为两类：
表共享读锁（read lock）
表独占写锁（write lock）

2.2表共享读锁

2.2.1特点

左侧为客户端一，对指定表加了读锁：
客户端一可以进行读锁，但客户端一不能进行写入操作；
不会影响右侧客户端二的读，但是会阻塞右侧客户端的写

2.2.2加读锁

lock tables 表名... read; --...是指可以对多张表加锁

2.3表独占写锁

2.3.1特点

左侧为客户端一，对指定表加了写锁：
客户端一既可以读也可以进行写；
会阻塞右侧客户端的读和写

2.3.2加写锁

lock tables 表名... write; 

2.2.3释放锁

unlock tables ;  --释放锁, 客户端断开连接

3.元数据锁

3.1概念

meta data lock , 元数据锁，简写MDL。
MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与DDL冲突，保证读写的正确性。
这里的元数据，大家可以简单理解为就是一张表的表结构。 也就是说，某一张表涉及到未提交的事务时，是不能够修改这张表的表结构的。
在MySQL5.5中引入了MDL，当对一张表进行增删改查的时候，加MDL读锁(共享)；当对表结构进行变更操作的时候，加MDL写锁(排他)。

3.2分类

常见的SQL操作时，所添加的元数据锁：
SHARED_READ / SHARED_WRITE : 元数据共享锁(即MDL中的读锁与写锁)
EXCLUSIVE：元数据排他锁(也算MDL的写锁)

下表的第一行指：当我们为某一张表加读锁或者写锁的时候 ，会自动帮我们加上对应的元数据锁；
下表第二行：当我们执行select等时候，会自动帮我们加上SHARED_READ。lock in share mode 是指共享锁
下表第四行：alter 是指要去修改表结构了

3.3查看元数据锁

以下sql语句查询的实际上是系统表performance_schema.metadata_locks，在metadata_locks表中记录了我们当前数据库实例的元数据锁

select object_type,object_schema,object_name,lock_type,lock_duration from
performance_schema.metadata_locks ;

3.4操作讲解

左窗口开启事务，并进行查询操作,会加上SHARED_READ共享锁

右窗口开启事务，也可以进行查询操作，会加上SHARED_READ共享锁

右窗口进行更新操作，并再次查询，会加上SHARED_WRITE 共享锁；
以上的操作都会成功的原因是:共享锁之间都是兼容的，所以两个客户端怎么执行都可以

左窗口与右窗口分别进行提交

左窗口再次进行开启事务，在左窗口进行查询发现数据已经更改，自动加上shared_READ共享读锁,

在右窗口进行修改表结构，添加一个java字段，即添加一列，但是发现点击回车后，一直处于阻塞状态，光标一直在闪；
阻塞的原因是:在我们之前把左窗口进行开启事务后，会自动为我们加上shared_READ共享读锁，此时共享读锁会与另外一个EXCLUSIVE进行互斥，所以会一直堵塞，会一直阻塞直到左窗口的事务提交后才会发生解锁

把左窗口的事务进行提交，然后会发现右窗口的锁发生解锁,此时加上了EXCLUSIVE锁

此时在右窗口再进行查询，发现查询成功

在右窗口进行查看当前的锁，发现只有一个共享读锁shared_READ

左窗口开启事务

右窗口再进行查看当前的锁，发现没有变化，得出结论是仅仅开启事务的话，不会对锁有任何的改变

左窗口进行查询操作

右窗口再次查询当前锁

右窗口开启事务，右窗口进行update，然后查看当前锁，发现有三个，即说明共享锁之间是兼容的

右窗口进行修改表结构，想要删掉java字段，发现堵塞了

左窗口提交事务，立马发现右窗口锁的解开

4.意向锁

4.1概念

为了避免DML在执行时，加的行锁与表锁的冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查

4.2分类

意向共享锁(IS): 由语句select … lock in share mode添加 。 与表锁中的读锁(read)兼容，与表锁中的写锁(write)互斥。
意向排他锁(IX): 由insert、update、delete、select…for update添加 。与表锁中的读锁(read)及表锁中的写锁(write)都互斥，意向锁之间不会互斥。
注：一旦事务提交了，意向共享锁、意向排他锁，都会自动释放

4.3查看意向锁与行锁

这次命令查看的表与之前元数据查看表的不一样，元数据查看的表是metadata_locks ，这次看的是data

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from
performance_schema.data_locks;

4.4没有意向锁

假如没有意向锁，客户端一对表加了行锁后，客户端二如何给表加表锁呢，来通过示意图简单分析一下:

首先客户端一，开启一个事务，然后执行DML操作，更新id为3的这条记录，id是主键，执行update语句是根据主键更新，此时在默认sql的隔离界别之下，自动的帮我们对id为3的这一行加上行锁，即在执行DML语句时，会对涉及到的行加行锁

当客户端二，想对这张表加表锁时，会检查当前表是否有对应的行锁，如果没有，则添加表锁，此时就会从第一行数据，检查到最后一行数据，效率较低(及加表锁时必须得对行锁进行检查，否则会发生冲突)

4.5有意向锁

有了意向锁之后 :

客户端一，在执行DML操作时，会对涉及的行加行锁，同时也会对该表加上意向锁

而其他客户端，在对这张表加表锁的时候，会根据该表上所加的意向锁来判定是否可以成功加表锁，如果是兼容的则直接加锁，而不用逐行判断行锁情况了；不兼容则不加，会处于阻塞，一直阻塞到客户端一的事务提交，把行锁、意向锁提交之后，才进行解锁。

客户端一开启一个事务，然后执行DML操作，更新id为3的这条记录，id是主键，执行update语句是根据主键更新，此时在默认sql的隔离界别之下，自动的帮我们对id为3的这一行加上行锁，即在执行DML语句时，会对涉及到的行加行锁；然后紧接着会主动对这张表加上意向锁；
客户端二来执行加表锁，对这张表加表锁的时候，会根据该表上所加的意向锁来判定是否可以成功加表锁，而不用逐行判断行锁情况了

4.6操作讲解(意向共享锁与表锁)

在select后面加上 lock in share mode:
即加上行锁的共享锁，同时为score这张表加上意向共享锁(IS)

注：在不修改表结构的情况下，讨论元数据没有任何意义，所以忽略元数据锁

在客户端二查看当前表,发现被加上了IS意向共享锁、id为1的行锁
recode是指行锁

在客户端二为table表加上读锁，发现加锁成功了
成功原因是：读锁与意向共享锁是兼容的

在客户端二解锁;
然后在客户端二为table表加上写锁，发现加锁没成功
成功原因是：写锁与意向共享锁是不兼容的

客户端1把事务进行提交，事务一旦提交，行锁与意向共享锁都被释放，然后客户端二就可以加写锁成功

4.7操作讲解(意向排他锁与表锁)

客户端一开启事务，然后执行update语句，当我们执行update语句后：
会自动为我们加上行锁，与此同时也会为这张表加上意向排他锁

客户端二再次查看当前锁情况：
此时id为1的数据依然有一个行锁，此时的行锁是一个排他锁。表锁加的是一个意向排他锁

客户端二对表加上读锁，发现失败:
在加锁的时候，InnoDB引擎会区判定这张表的意向锁的情况，InnoDB引擎检测到有一个意向排它锁，产生互斥，则失败

客户端一事务提交

读锁立马添加成功;

客户端二解锁，然后如果加写锁得话会失败的，也不会成功

5.附

5.1SHARED_READ_ONLY(在事务中时)

5.2SHARED_READ_ONLY(不在事务中时)

5.3事务提交前如果表锁没有解锁，事务提交后表锁仍会存在

5.4SHARED_NO_READ_WRITE(在事务中时)