Mysql-锁

简介

在MySQL中，锁机制是为了保证数据的一致性和完整性，防止多个事务同时操作同一数据时出现冲突。

全局锁

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

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

示例

加锁

FLUSH TABLES WITH READ LOCK;

使用mysqldump工具备份

mysqldump --all-databases > all_databases.sql

解锁

UNLOCK TABLES;

以上示例存在问题

如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆。

如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志(binlog)，会导致主从延迟。

在InnoDB引擎中，我们可以在备份时加上参数--single-transaction 参数来完成不加锁的一致性数据备份。

演示

single-transaction 的作用

一致性备份

在启动备份时，--single-transaction 会启动一个新的事务（START TRANSACTION）。

事务内的所有查询会基于事务开始时的数据库状态，这样可以确保备份期间数据的一致性。

不锁定表：

与 FLUSH TABLES WITH READ LOCK 不同，--single-transaction 不会锁定表，从而允许其他事务继续对数据库进行读写操作。这样可以减少对数据库正常操作的影响。

避免长时间锁定：

对于大数据库，备份可能需要较长时间。使用 --single-transaction 可以避免长时间的表锁定，减少对应用程序的影响。

使用 mysqldump 工具进行备份时，可以使用 --single-transaction 参数：

mysqldump --single-transaction -u username -p database_name > backup.sql

事务支持：

--single-transaction 适用于支持事务的存储引擎（如InnoDB）。如果数据库中有使用非事务存储引擎的表（如MyISAM），这些表的数据在备份过程中可能会发生变化，从而无法保证完全一致的备份。

长时间查询：

在备份过程中，如果有长时间运行的查询，可能会导致备份文件的大小增加，因为 mysqldump 会保留备份期间修改的数据版本，直到备份完成。

备份视图：

使用 --single-transaction 进行备份时，如果数据库中包含视图，可能会出现视图定义和数据不一致的情况。为了避免这种情况，可以结合使用 --lock-tables=false 参数。

结合其他参数

可以结合其他参数使用，以优化备份过程：

--quick：快速备份大表，逐行从服务器读取数据。

--lock-tables=false：明确禁用表锁定。

mysqldump --single-transaction --quick --lock-tables=false -u username -p database_name > backup.sql

表级锁

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

表级锁，主要分为以下三类：

表锁：使用LOCK TABLES ... READ/WRITE命令来锁定表。读取锁（READ LOCK）允许其他事务读取数据，但不允许修改。写入锁（WRITE LOCK）则完全阻止其他事务的读写操作。

元数据锁（MDL）：当执行DDL操作（如ALTER TABLE）时，会自动获取元数据锁，以防止表结构在操作过程中被其他事务改变。

意向锁：意向锁是一种特殊的表级锁，用于表示事务打算在表的某些行上加锁。主要有以下两种：

意向共享锁（IS锁）：表示事务打算在某些行上加共享锁。

意向排他锁（IX锁）：表示事务打算在某些行上加排他锁。

表锁分为两类:

表共享读锁(read lock)：

多个事务可以同时获取共享读锁：多个事务可以并发读取数据，而不相互阻塞。

阻止写操作：当一个事务持有共享读锁时，其他事务无法获取排它写锁进行写操作。

排它写锁(write lock)：

独占访问权：当一个事务获取了排它写锁时，其他任何事务都无法获取任何类型的锁（包括共享读锁和其他排它写锁）。

阻止读写操作：排它写锁不仅阻止其他事务的写操作，也阻止其他事务的读操作，确保对数据的独占访问。

演示 表共享读锁

演示 排它写锁

元数据锁(meta data lock，MDL)

MySQL中的元数据锁（Metadata Lock, MDL）是用于保护数据库对象（如表、视图、存储过程等）的元数据的锁。这种锁机制是在MySQL 5.5引入的，用于确保对元数据的并发访问时的一致性和完整性。

元数据锁的特性

自动管理：元数据锁由MySQL自动管理，不需要用户显式地进行控制。

保护元数据：确保当一个事务在访问或修改一个数据库对象的元数据时，其他事务不会同时修改该对象的元数据。

类型：元数据锁可以是读锁或写锁。

元数据锁的使用场景

读取元数据：当一个事务正在读取一个表的元数据时，其他事务不能对该表的元数据进行修改。

修改元数据：当一个事务正在修改一个表的元数据时，其他事务不能读取或修改该表的元数据。

元数据锁的常见操作

以下是一些常见的会触发元数据锁的操作：

DDL操作：如CREATE TABLE、DROP TABLE、ALTER TABLE等，这些操作会获取写锁。

DML操作：如SELECT、INSERT、UPDATE、DELETE等，这些操作会获取读锁。

演示 当一个事务正在读取一个表的元数据时，其他事务不能对该表的元数据进行修改

要查看当前的元数据锁，可以查询 performance_schema.metadata_locks 表。下面是如何查看元数据锁的详细示例。

启用 Performance Schema

首先，确保 performance_schema 是启用的。你可以在 MySQL 配置文件（my.cnf）中添加或确认以下配置：

[mysqld]
performance_schema = ON

如果 performance_schema 没有启用，可以通过以下命令在运行时启用（仅在 MySQL 8.0 及以上版本）：

SET GLOBAL performance_schema = ON;

查询元数据锁：

SELECT * FROM performance_schema.metadata_locks;

OBJECT_TYPE：对象类型（如 TABLE）

OBJECT_SCHEMA：对象所属的数据库

OBJECT_NAME：对象名称（如表名）

LOCK_TYPE：锁类型（如 SHARED_READ, SHARED_WRITE, EXCLUSIVE）

LOCK_DURATION：锁持续时间（如 TRANSACTION, STATEMENT, EXPLICIT）

LOCK_STATUS：锁状态（如 GRANTED, PENDING）

OWNER_THREAD_ID：持有锁的线程ID

OWNER_EVENT_ID：持有锁的事件ID

意向锁（Intention Lock）

是表级锁的一种，用于提高多粒度锁定的效率和避免死锁。意向锁本身并不阻止具体的行级操作，但它们帮助InnoDB存储引擎了解事务对表的锁定意图，从而避免在获取行级锁时发生冲突。

为什么需要意向锁

意向锁的主要目的是为了协调行级锁和表级锁的使用，使得InnoDB能够在不必检查每一行锁的情况下，实现高效的锁冲突检测和管理。

工作原理

当一个事务请求行级锁时，InnoDB会首先请求相应的意向锁。意向锁的存在告诉其他事务，该表中的某些行可能已经被锁定，这样可以避免整个表的锁定冲突。例如：

当一个事务请求对某行加共享锁（S锁）时，InnoDB会首先请求该表的意向共享锁（IS锁）。

当一个事务请求对某行加排他锁（X锁）时，InnoDB会首先请求该表的意向排他锁（IX锁）。

意向共享锁(I5):由语句select..........lock in share mode添加。

意向排他锁(IX):由insert、update、delete、select..for update 添加。

意向共享锁(IS):与表锁共享锁(read)兼容，与表锁排它锁(write)互斥。

意向排他锁(IX):与表锁共享锁(read)及排它锁(write)都互斥。意向锁之间不会互斥。

查看意向锁情况

SELECT object_schema,object_name, index_name, lock_type, lock_mode,lock_data FROM performance_schema.data_locks;

演示 窗口2堵塞状态

窗口2堵塞状态

行级锁

MySQL 中的行级锁（row-level locking）是指对单行记录加锁，以实现更高的并发性和数据的一致性。行级锁允许多个事务同时读写不同的行，从而提高数据库的性能和吞吐量。

共享锁（S 锁，Share Lock）：

允许多个事务同时读取一行记录，但不允许修改。

通过命令 SELECT ... LOCK IN SHARE MODE 获得。

创建表和初始化数据

-- 创建一个示例表
CREATE TABLE example (
    id INT PRIMARY KEY,
    value VARCHAR(100)
) ENGINE=InnoDB;

-- 插入一些初始数据
INSERT INTO example (id, value) VALUES 
(1, 'Alice'), 
(2, 'Bob'), 
(3, 'Charlie'), 
(8, 'Eve'), 
(11, 'Frank');

-- 事务 1：对 id = 1 的行加共享锁
START TRANSACTION;
SELECT * FROM example WHERE id = 1 LOCK IN SHARE MODE;

-- 事务 2：尝试对 id = 1 的行加排他锁（会被阻塞）
START TRANSACTION;
UPDATE example SET value = 'Updated' WHERE id = 1;

-- 事务 1：提交事务，释放共享锁
COMMIT;

-- 事务 2：现在可以获得排他锁并完成更新

排他锁（X 锁，Exclusive Lock）：

允许一个事务读取和修改一行记录，其他事务不能访问。

通过命令 SELECT ... FOR UPDATE 或 UPDATE ... 获得。

此外，InnoDB 存储引擎在实现行级锁的过程中使用了其他一些重要的锁机制，这些机制虽然不是行级锁的直接类型，但在行级锁操作中会涉及到：

-- 事务 1：对 id = 1 的行加排他锁
START TRANSACTION;
SELECT * FROM example WHERE id = 1 FOR UPDATE;

-- 事务 2：尝试对 id = 1 的行加共享锁（会被阻塞）
START TRANSACTION;
SELECT * FROM example WHERE id = 1 LOCK IN SHARE MODE;

-- 事务 1：提交事务，释放排他锁
COMMIT;

-- 事务 2：现在可以获得共享锁并读取数据

意向锁（Intention Lock）：

表级锁，用于在行级锁之前设置，指示事务打算加锁的类型。

主要有意向共享锁（IS 锁）和意向排他锁（IX 锁）。

-- 意向锁无法直接操作，需要理解它的存在，以下是一个例子来说明其作用

-- 事务 1：对表加意向排他锁（内部操作）
START TRANSACTION;
UPDATE example SET value = 'Alice' WHERE id = 1;

-- 事务 2：尝试对表加意向共享锁（会被阻塞）
START TRANSACTION;
SELECT * FROM example LOCK IN SHARE MODE;

-- 事务 1：提交事务，释放锁
COMMIT;

-- 事务 2：现在可以获得意向共享锁并读取数据

间隙锁（Gap Lock）：

防止幻读，通过锁住索引记录之间的间隙，防止其他事务在间隙中插入新记录。

主要用于可重复读（REPEATABLE READ）隔离级别。

-- 事务 1：对 id = 3 到 id = 8 之间的间隙和记录加锁
BEGIN;
update example set value='kevin' where id=5;

-- 事务 2：尝试在锁定的间隙中插入记录（会被阻塞）
BEGIN;
INSERT INTO example (id, value) VALUES (7, 'David');

-- 事务 1：提交事务，释放锁
COMMIT;

-- 事务 2：现在可以插入记录
COMMIT;

临键锁（Next-Key Lock）：

行锁和间隙锁的组合，用于防止幻读，锁住一行记录和其前后的间隙。

记录锁（Record Lock）：

锁住单行记录，用于保证数据一致性。

-- 事务 1：对 id = 1 的记录加排他锁
START TRANSACTION;
UPDATE example SET value = 'Charlie' WHERE id = 1;

-- 事务 2：尝试读取 id = 1 的记录（会被阻塞）
START TRANSACTION;
SELECT * FROM example WHERE id = 1 LOCK IN SHARE MODE;

-- 事务 1：提交事务，释放锁
COMMIT;

-- 事务 2：现在可以读取记录
COMMIT;