【MySQL】锁

1. 锁的分类

Mysql中的锁，按照锁的粒度分，分为以下三类：

• 1.全局锁：锁定数据库中的所有表。
• 2.表级锁：每次操作锁住整张表。
• 3.行级锁：每次操作锁住对应的行数据。

2.全局锁 ===>锁定数据库中的所有表

2.1 介绍

介绍：全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都会被阻塞。

典型的使用场景就是做全库的逻辑备份，对所有表进行锁定，从而获取一致性视图，保证数据的完整性。

全局锁的操作需要进行以下步骤：

1. 在需要加锁的MySQL实例上运行以下命令，打开全局锁：

FLUSH TABLES WITH READ LOCK;

这条命令将会强制所有正在运行的写操作等待，并将数据库的所有表锁住，直到释放锁。在锁定期间所有的读操作都可以继续进行。

2. 在锁定期间进行备份或数据迁移等操作。

3. 在结束操作后，需要释放全局锁。可以运行以下命令：

UNLOCK TABLES;

插入成功
这条命令将会释放所有被锁定的表，允许写操作继续进行。

需要注意的是，一旦全局锁被打开，所有的写操作都会被阻塞，因此在生产环境中应该避免长时间打开全局锁。此外，在使用全局锁时，也需要注意MySQL的版本号和数据文件的大小，过大的数据文件会导致锁定时间过长，并可能导致MySQL实例的崩溃。

2.2 MySQL备份命令

MySQL备份数据库的命令是mysqldump，它是MySQL自带的备份工具，可以用来备份单个数据库或者整个MySQL实例的所有数据库。以下是备份单个数据库的命令：

mysqldump -u username -p password database_name > backup_file.sql

其中，username是MySQL的用户名，password是用户的密码，database_name是要备份的数据库名称，backup_file.sql是备份的文件名。

如果需要备份整个MySQL实例的所有数据库，可以使用如下命令：

mysqldump -u username -p password --all-databases > backup_file.sql

备份文件是一个纯文本文件，可以使用任何文本编辑器打开和编辑。以下是演示一个备份MySQL数据库的示例：

1. 打开终端或命令提示符窗口。

2. 输入以下命令，备份一个名为sample_db的MySQL数据库，并将备份文件保存为backup.sql：

mysqldump -u root -p sample_db > backup.sql

3. 稍等片刻，备份完成后，可以使用任何文本编辑器来查看备份文件。

完整的备份文件应该包含所有表和数据，可以用来恢复原始数据库。请注意，备份文件可能会非常大，因此建议进行定期备份，并将备份文件存储在安全的位置。此外，备份文件也可以压缩，以节省磁盘空间。

！！！ ======>>> 全局锁实现数据备份

2.3 一个备份的实践操作

下面会阻塞，因为加了全局锁

备份数据库

备份后的内容

释放锁

2.4 全局锁存在的问题

加参数 --single-transaction

2. 表级锁===>每次操作锁住整张表

===> 表级锁课程
表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低，应用正在MyISAM、InnoDB、BDB等存储引擎中。

对于表级锁，主要分成以下三类：

1. 表锁
2. 元数据锁(meta data Lock, MDL)
3. 意向锁

2.1 表锁

对于表锁，又可以分为两类。

1. 表共享读锁 (read Lock)
2. 表独占写锁 (write Lock)

语法：

1. 加锁：lock tables 表名 read/write (可以同时一起加多个表)
2. 释放锁：unlock tables / 客户端断开连接 （前两是这两个二选一，都可以释放锁）

2.1.1 读锁 演示demo

表锁示意图

对score表加读锁

读取是没有问题的

执行更细语句

在另一个客户端中，可以读取，但是不能做其他操作，除非一个客户端释放锁，图中是客户端1先加锁，然后客户端2可以读，但是update的时候一直阻塞，直到客户端1中释放锁，客户端2才执行成功。

2.1.2 写锁

写锁在当前客户端既可以读也可以写，但是其他客户端既不能写也不能读。

下面红色部分表示不能操作

演示
第一个客户端对于写锁既可以读，可以写

下面第二个客户端不能读不能写，会一直阻塞，除非另一个客户端释放锁

Java实现读写锁

在 Java 中，可以通过使用 java.sql.Connection 类中提供的方法来实现对表的锁定操作。下面是怎样在 Java 中进行表级锁的读写与写锁的实现：

1. 实现表级读锁

实现对表的读锁非常简单。只需在 Connection 对象上调用 setTransactionIsolation() 方法来设置隔离级别，并将其设置为 Connection.TRANSACTION_READ_COMMITTED。代码示例：

Connection con = ...;
con.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);

这将设置事务的隔离级别为“读已提交”，这意味着事务可以读取并锁定其他事务已经提交的行。注意，这里的锁将被其他事务共享。

2. 实现表级写锁

实现对表的写锁与读锁类似，但需要将隔离级别设置为 Connection.TRANSACTION_SERIALIZABLE。代码示例：

Connection con = ...;
con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);

这将设置事务的隔离级别为“串行化”，这意味着事务可以锁定整个表，防止其他事务读取或写入该表的行。需要注意的是，在使用表级写锁时应该尽可能的避免锁定的时间过长，以避免对性能产生影响。

3. 实现获取表级锁

要获取表级锁，需要调用 java.sql.Statement 对象中的 execute() 方法来执行一条锁定语句。以下是一个获取写锁的示例代码：

Connection con = ...;
Statement stmt = con.createStatement();
stmt.execute("LOCK TABLE table_name WRITE");

其中，table_name 是要锁定的表的名称。执行这个语句后，将会锁定整个表，防止其他事务在此表上进行读和写操作。

需要注意的是，表级锁可能会对数据库的性能产生影响，因此应该尽量避免使用。如果有必要实现锁定，建议使用行级锁或其他更细粒度的锁定机制。

2.2 元数据锁MDL 【避免DML与DDL冲突，保证读写的正确性】

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

另一种说法： 元数据锁是一种数据库锁定机制，用于保护数据库中的元数据（例如表结构和索引）。元数据是描述数据库对象的数据，它们通常存储在系统表中。在执行某些数据库操作时，系统需要对元数据进行访问和更新，这可能会干扰其他用户的操作。
元数据锁可以防止并发用户同时修改元数据，从而避免数据不一致的问题。当一个用户正在修改元数据时，系统会将元数据对象加锁，以防止其他用户同时修改该对象。一旦锁定，其他用户将无法对元数据进行任何修改，直到释放锁定。
通常，元数据锁是由数据库管理系统自行管理的。这就意味着，用户不需要手动设置元数据锁。但是，了解元数据锁的概念对于理解并发访问数据库的行为和问题是很重要的。

在Mysql5.5中引入了DML，当对一张表进行增删改查的时候，加DML读锁(共享)；当对表结构进行变更操作的时候，加DML写锁(排他锁)。

2.2.1 元数据锁演示

在客户端1中开启事物，并查询score可以正常查询，在客户端2中开启事物，同样查询score也是可以正常查询的。在客户端2中执行update语句也是没有问题的。

两边的事务都提交，在客户端1开启事物并查询score，在客户端2提交事务执行ALTER语句，发现客户端2会阻塞。会一直阻塞到客户端1commit之前当客户端1执行commit客户端2则执行不会再阻塞。

原因分析：select语句执行后会为表加上MDL读锁,而ALTER会加上排他锁。所以客户端2会处于阻塞状态。

2.2.2 查看数据库表当中的元数据锁

当客户端1执行命令后，在客户端2再次查询

2.3 意向锁

======>意向锁课程

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

说明：左侧线程A在执行的时候，先开启事物，然后执行update并会在这一行加上行锁，然后对该表加上意向锁，线程B要对这个表加上表锁，线程B要通过意向锁来决定能不能加表锁 (图1)。

							<center>图1</center>

如果线程A加的意向锁与线程B要加的表锁是兼容的，就直接加表锁，如果不兼容就阻塞，等待A线程释放锁。

2.3.1 意向锁的种类

1.意向共享锁(IS)：由语句select...lock in share mode添加
2.意向排他锁(IX)：由insert、update、delete、select.. for update 添加

2.3.2 兼容关系

2.3.3 意向锁测试

======>课程

在客户端1中开启事物，并且在select语句后面lock in share mode，会加上这一行的行的共享锁，同时为score这张表加上意向共享锁。

在下图中可以看到，客户端2，为id为1的加上了行锁(RECORD)，为score这张表加上了意向共享锁(IS)

在客户端2中为表score加上读锁，成功。原因：意向锁IS与读锁兼容

---

客户端执行这个语句时，update会自动加上行锁，同时也会为这张表加上意向锁(IX)。客户端2加表锁会阻塞，知道客户端1提交事务才会执行lock table。

3.行级锁

介绍：行级锁，每次操作锁会锁住对应的行数据，锁的粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中。

======>行锁
// TODO

N. DML 语句和 DDL 语句区别

DML 语句和 DDL 语句区别：

1. DML 是数据库操作语言（Data Manipulation Language）的缩写，是指对数据库中表记录的操作，主要包括表记录的插入、更新、删除和查询，是开发人员日常使用最频繁的操作。
2. DDL （Data Definition Language）是数据定义语言的缩写，简单来说，就是对数据库内部的对象进行创建、删除、修改，的操作语言。它和 DML 语言的最大区别是 DML 只是对表内部数据的操作，而不涉及到表的定义、结构的修改，更不会涉及到其他对象。DDL 语句更多的被数据库管理员（DBA）所使用，一般的开发人员很少使用。

附录

1.MySQL数据库锁部分
2.Innodb中的事务隔离级别和锁的关系
3.大厂面试指北–事务隔离&锁
4.路途博客