mysql数据库之表级锁

约定不等于承诺〃 2023-10-11 21:03 18阅读 0赞

表级锁，每次操作锁住整张表。锁定粒度大，发生所冲突的概率最高，并发度最低。应用在myisam、innodb、bdb等存储引擎中。

一、表级锁分类。

1、表锁

2、元数据锁（meta data lock，MDL）

3、意向锁

二、表锁。

分类

1、表共享读锁（read lock）

![eba40d6207cf4412906798c4c1e3820b.png][]

释放前

![0936ca6ee4ea454ebce03232132c2793.png][]

释放后

![42de3cd462c944498174738fec21ba95.png][]

2、表独占写锁（write lock）

![f29849d015014f71a9080476ceb16e43.png][]

释放前

![3f7f742233ad488bbf594fc88da600b7.png][]

释放后

![c1759c308c5745c3bbe5af35cf96b881.png][]

语法

1、加锁：lock tables 表名... read/write。

读锁，可以读，不可以写

![c5831cefadb54a498e07b1610b2481ad.png][]

写锁，本地客户端可以写可以读，其他客户端不能读不能写，释放后可以

![f29849d015014f71a9080476ceb16e43.png][]

2、释放锁：unlock tables 或者客户端断开连接。

三、元数据锁（meta data lock，MDL）。

MDL加锁过程是系统自动控制，无需显示使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与DDL冲突，保证读写的正确性。

在mysql5.5中引入了MDL，当对一张表进行增删改查的时候，家MDL读锁（共享锁），当对表结构进行变更操作的时候，家MDL写锁（排他锁）。

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td>&nbsp;</td> 
   <td style="width:164px;">对应SQL</td> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
   <td style="width:242px;">锁类型</td> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
   <td style="width:209px;">说明</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td style="width:164px;">lock tables xxx read/write</td> 
   <td style="width:242px;">shared_read_only/shared_no_read_write</td> 
   <td style="width:209px;"></td> 
  </tr> 
  <tr> 
   <td style="width:164px;">select/select... lock in share mode</td> 
   <td style="width:242px;">shared_read</td> 
   <td style="width:209px;">与shared_read/shared_write兼容，与exclusive互斥</td> 
  </tr> 
  <tr> 
   <td style="width:164px;">insert、update、delete、select... for update</td> 
   <td style="width:242px;">shared_write</td> 
   <td style="width:209px;">与shared_read/shared_write兼容，与exclusive互斥</td> 
  </tr> 
  <tr> 
   <td style="width:164px;">alter table ...</td> 
   <td style="width:242px;"></td> 
   <td style="width:209px;">与其他的MDL互斥</td> 
  </tr> 
 </tbody> 
</table>

1、共享锁，可以读可以写

![189aeeae7c36469294787b36231a9f9e.png][]

2、修改表结构，未提交

![d3134ad881c1482092d497a6e7a2357c.png][]

3、提交后可更新表结构

![d94ea0b9b4f04ece9c7a6ed18f75ddb1.png][]

4、查看元数据锁

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

查询![1b74aa14524b49349b2e59b0bb20d4e3.png][]

只有执行增删改查的时候，才会有对应的元数据锁，alter table改表结构的时候阻塞，提交事务即可执行。

![53a2f3749d6042559a1e027fdd8f233b.png][]

四、意向锁。

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

1、演示。

没有意向锁

![8cb24a2f21844e06a8624185c2e7ff03.png][]

有意向锁

![d7f4b6d3db394780a492baa9f0603fb2.png][]

2、分类。

意向共享锁（IS）：由语句select ... lock in share mode添加。

意向排他锁（IX）：由insert、update、delete、select ... for update添加。

3、兼容性。

意向共享锁（IS）：与表锁共享锁（read）兼容，与表锁排他锁（write）互斥。

意向排他锁（IX）：与表锁共享锁（read）和表锁排他锁（write）都互斥，意向锁之间不会互斥。

4、查看意向锁及行锁的加锁情况。

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

5、演示。

为这一行加行锁，为表加意向锁，查询语句后加上lock in share mode，是为这一行加共享锁，为整张表家意向共享锁。

![8b866b65a33844388ef35c24c983d74b.png][]

IS和读锁兼容，和写锁互斥，提交后释放

![379ba482acd44d2d8dc1cf4e6debdb2e.png][]

执行update语句，自动为这一行加行锁，为表加上意向排他锁

![3a15663869f94ad780dc26ca23c4a19f.png][]

![8f43116dc2cc466e979b61c1a99735b9.png][]

互斥，提交事务后即可释放

![c2f4b31f795048d98261fab4b54c3868.png][]

主要解决innodb引擎中加的行锁和表锁的冲突问题。

[eba40d6207cf4412906798c4c1e3820b.png]: https://img-blog.csdnimg.cn/eba40d6207cf4412906798c4c1e3820b.png
[0936ca6ee4ea454ebce03232132c2793.png]: https://img-blog.csdnimg.cn/0936ca6ee4ea454ebce03232132c2793.png
[42de3cd462c944498174738fec21ba95.png]: https://img-blog.csdnimg.cn/42de3cd462c944498174738fec21ba95.png
[f29849d015014f71a9080476ceb16e43.png]: https://img-blog.csdnimg.cn/f29849d015014f71a9080476ceb16e43.png
[3f7f742233ad488bbf594fc88da600b7.png]: https://img-blog.csdnimg.cn/3f7f742233ad488bbf594fc88da600b7.png
[c1759c308c5745c3bbe5af35cf96b881.png]: https://img-blog.csdnimg.cn/c1759c308c5745c3bbe5af35cf96b881.png
[c5831cefadb54a498e07b1610b2481ad.png]: https://img-blog.csdnimg.cn/c5831cefadb54a498e07b1610b2481ad.png
[189aeeae7c36469294787b36231a9f9e.png]: https://img-blog.csdnimg.cn/189aeeae7c36469294787b36231a9f9e.png
[d3134ad881c1482092d497a6e7a2357c.png]: https://img-blog.csdnimg.cn/d3134ad881c1482092d497a6e7a2357c.png
[d94ea0b9b4f04ece9c7a6ed18f75ddb1.png]: https://img-blog.csdnimg.cn/d94ea0b9b4f04ece9c7a6ed18f75ddb1.png
[1b74aa14524b49349b2e59b0bb20d4e3.png]: https://img-blog.csdnimg.cn/1b74aa14524b49349b2e59b0bb20d4e3.png
[53a2f3749d6042559a1e027fdd8f233b.png]: https://img-blog.csdnimg.cn/53a2f3749d6042559a1e027fdd8f233b.png
[8cb24a2f21844e06a8624185c2e7ff03.png]: https://img-blog.csdnimg.cn/8cb24a2f21844e06a8624185c2e7ff03.png
[d7f4b6d3db394780a492baa9f0603fb2.png]: https://img-blog.csdnimg.cn/d7f4b6d3db394780a492baa9f0603fb2.png
[8b866b65a33844388ef35c24c983d74b.png]: https://img-blog.csdnimg.cn/8b866b65a33844388ef35c24c983d74b.png
[379ba482acd44d2d8dc1cf4e6debdb2e.png]: https://img-blog.csdnimg.cn/379ba482acd44d2d8dc1cf4e6debdb2e.png
[3a15663869f94ad780dc26ca23c4a19f.png]: https://img-blog.csdnimg.cn/3a15663869f94ad780dc26ca23c4a19f.png
[8f43116dc2cc466e979b61c1a99735b9.png]: https://img-blog.csdnimg.cn/8f43116dc2cc466e979b61c1a99735b9.png
[c2f4b31f795048d98261fab4b54c3868.png]: https://img-blog.csdnimg.cn/c2f4b31f795048d98261fab4b54c3868.png