mysql数据库锁有几种类型？ mysql中有哪些类型的锁

terry 2年前 (2023-09-30) 阅读数 40 #Mysql

文章标签 Mysql

本文内容列表：

1、mysql中的锁有哪些？
2.深入理解MySQL数据库中不同的锁（总结）
3. 以下哪项不是 MySQL 数据库的锁级别？

mysql中有哪些锁

MySQL中有哪些锁？

数据库中的锁最初是为了解决并发问题而设计的。由于是多个用户共享的资源，当发生并发访问时，数据库必须合理控制访问资源的规则。实现这些访问规则时，锁是重要的数据。

锁的分类

根据锁范围的不同，MySQL 中的锁可以分为三类：全局锁、表级锁和行锁。

全局锁

全局锁旨在锁定整个数据库实例。 MySQL提供了添加全局读锁的方法。命令为：

清除带读锁的表 (FTWRL)

当需要整个数据库实例时您可以在库处于只读状态时使用此命令。之后，其他线程的数据更新语句（添加、删除、修改）、数据定义语句（创建表、更改表结构）以及更新事务的提交语句都会被阻塞。

全局锁的用例

全局锁的最终用例，其中对数据库进行完整逻辑备份。也就是说，选择整个库中的每个表，然后将其另存为文本。

如果整个库都是只读的会怎样？如果备份到主数据库，在备份期间将无法进行任何更新，业务基本停止。如果在从库上进行备份，则从库将无法执行备份时从主库同步的binlog，从而导致从库延迟。既然你希望整个库是只读的，为什么不使用 set global readonly=true 呢？

只读方式也可以将整个库变成只读状态，但我还是推荐使用FTWRL方式，主要有两个原因：

首先，在某些系统中，只读值用于其他逻辑，例如确定库是主库还是备库。所以改变全局变量的方式影响比较大，不建议使用。其次，异常处理机制存在差异。如果执行FTWRL命令后客户端异常断开，MySQL会自动释放全局锁，使整个库恢复到可以正常更新的状态。将整个库设置为只读后，如果客户端出现异常，数据库将保持只读状态。这样会导致整个库长期处于不可写状态，风险较大。表级锁

MySQL 中的表级锁有两种类型的锁：一种是表锁，另一种是元数据锁（metadatalok，MDL）。表锁语法为：

锁定表...读/写

与 FTWRL 一样，您可以使用解锁表来主动释放锁，或者在客户端断开连接时自动释放。需要注意的是，锁表的语法不仅限制了其他线程的读写，也限制了本线程后续的操作对象。

表级 MDL 锁定

不需要显式使用 MDL。访问表时会自动添加它。 MDL保证读写的正确性。也就是说，当请求数据时，没有其他线程会访问这个表。实施结构性变革。

哪些操作会添加MDL锁定？

MDL 在 MySQL 5.5 版本中引入。对表进行增删改查时，添加MDL读锁；当您对表进行结构更改时，您将添加 MDL 写锁定。

读锁不是互斥的，因此多个线程可以同时对表进行增删改查。读/写锁和写锁是互斥的，用于保证表结构修改操作的安全性。如果两个线程想要同时向表中添加字段，其中一个线程必须等待另一个线程完成执行。更改表结构时要注意什么？

在表中添加字段、更改字段或添加索引时，必须扫描全表数据。在为大餐桌提供服务时，必须特别小心，不要影响在线服务。即使是一个小表，如果操作不仔细，也会出现问题，导致整个库线程混乱。

例如

让我们看看以下操作顺序。假设表t是一个小表。

image

会话 A 首先开始。此时，表t上就加了一个MDL读锁。由于会话B也需要MDL读锁，因此可以正常运行。会话C被阻塞是因为会话A的MDL读锁没有释放，而会话C需要MDL写锁，所以只能被阻塞，并且读写锁是互斥的。如果只是会话C本身被阻塞也没关系，但是后续所有请求表t的MDL读锁的请求也会被会话C阻塞。前面我们说过，所有的增删改查操作都会被阻塞.对表必须首先请求MDL读锁，然后将它们全部锁定，意味着该表现在完全不可读写。

如果某个表有频繁的查询语句，并且客户端有重试机制，这意味着超时后启动新的会话并发出新的请求，这个库的线程很快就会满。事务中的MDL锁在指令执行开始时请求，但不会在指令结束后立即释放，而是在整个事务提交后释放。

如何解决改变表结构的问题

理想的机制是在'alter table'语句中设置等待时间。最好能在指定的等待时间内获得MDL写锁。如果没有，则无法获得。不要阻塞接下来的业务语句，先放弃。

ALTER TABLE tbl_name NOWAIT 添加列... ALTER TABLE tbl_name WAIT 添加N列...