16、MYSQL锁机制

锁的类型

在 MySQL 数据库中，有两种基本类型的锁：共享锁（Shared Lock）和排他锁（Exclusive Lock）。

共享锁（Shared Lock）也称读锁，简称 S 锁。在同一时间多个事务都可以持有该锁，而且持有共享锁的事务之间可以并发执行，即读锁不阻塞读锁。但是如果一个事务持有了共享锁，那么其他事务就不能获得该行的排他锁，只能等待该共享锁释放。

排他锁（Exclusive Lock）也称写锁，简称 X 锁。在同一时间内只能有一个事务持有该锁，持有排他锁的事务既可以读取也可以修改该行数据，其他任何事务都不能再获得该行的共享锁和排他锁，直到该排他锁被释放。

除了以上的共享锁和排他锁之外，MySQL 还提供了另外两种锁：

意向共享锁（Intention Shared Lock）：简称 IS 锁，事务在获得共享锁之前必须先获得该锁。

意向排他锁（Intention Exclusive Lock）：简称 IX 锁，事务在获得排他锁之前必须先获得该锁。

分类：

隐式锁：由存储引擎自动施加锁

显式锁：用户手动请求

MySQL 中的锁分为表锁和行锁：

表锁是对整张表加锁，适用于大量批量的操作，如表的重建、全表备份等，通过 LOCK TABLE 和 UNLOCK TABLES 语句实现。

行锁是针对数据表中的行记录的锁，适用于并发高的场景，只锁定所需操作的行，其他事务可以访问表的其他数据行，通过 SELECT ... FOR UPDATE 和 SELECT ... LOCK IN SHARE MODE 语句实现。

在实际应用中，如果使用不当，锁会成为 MySQL 中的性能瓶颈，甚至可能导致死锁等问题。因此，在使用锁的时候需要谨慎考虑，根据实际需求选取合适的锁类型和锁定粒度，避免锁冲突和死锁等问题的出现。

锁冲突

在多用户并发访问数据库时，如果多个用户同时请求对同一数据进行修改，就会产生锁冲突问题。锁冲突是指在一个事务中，如果要访问一个已经被加锁的资源，那么就需要等待这个锁被释放，从而导致事务等待，降低了数据库的性能。

锁冲突一般分为两种类型：共享锁和排他锁。共享锁（Shared Lock）又称读锁，是一种共享锁定机制，多个事务可以同时持有共享锁，且不会阻止其他事务获得共享锁，用于保证并发读取时的数据一致性。排他锁（Exclusive Lock）又称写锁，是一种互斥锁定机制，一旦一个事务获取了排他锁，其他事务就无法获得共享锁和排他锁，用于保证事务操作的原子性。

锁冲突产生的原理主要是由于多个事务同时对同一资源进行访问和修改引起的。当一个事务访问某个资源时，它会对该资源加锁，以避免其他事务同时修改该资源，导致数据不一致。如果多个事务同时对同一资源进行访问并且持有了不同类型的锁（共享锁和排他锁），则可能会产生锁冲突，从而导致其中某些事务阻塞或回滚。这时就需要通过锁机制来解决锁冲突问题，保证数据的一致性和完整性。

假设现在有两个事务 A 和 B，它们同时访问了同一个数据项 X。
事务 A 执行了一个 SELECT 语句，获取了数据项 X 的共享锁，但是还没有释放该锁；
事务 B 试图执行一个 UPDATE 语句来更新数据项 X，它需要获取 X 的排他锁，但是由于事务 A 持有 X 的共享锁，因此锁冲突发生了。
在这种情况下，事务 B 无法立即获取到需要的锁，它只能等待事务 A 释放 X 的共享锁。如果事务 A 一直不释放锁，那么事务 B 就会一直阻塞在这里，直到出现死锁。

当多个事务同时请求对同一资源进行加锁时，就会产生锁冲突。如果请求的锁类型不同，那么不会产生锁冲突。如果请求的锁类型相同，就需要根据数据库的锁机制来判断是否产生了锁冲突。MySQL的InnoDB存储引擎提供了行级锁和表级锁两种锁定机制，行级锁可以有效地避免锁冲突问题，提高了数据库的并发性能。

在使用锁的过程中，需要注意以下几点：

1、加锁的范围要尽可能小，这样可以减小锁定的资源，提高并发性能。

2、在同一事务中，不要跨越太多的代码块进行锁定，否则会增加锁冲突的概率，影响性能。

3、避免长时间持有锁，应该尽快释放锁定的资源，避免造成死锁和长时间等待的情况。

4、在设计数据库时，需要合理地使用索引，避免全表扫描等操作，降低锁冲突的概率。

显示使用锁

加锁

LOCK TABLES tbl_name [[AS] alias] lock_type [, tbl_name [[AS] alias]
lock_type] ...
lock_type:
READ
WRITE

要显示MySQL数据库中正在使用的锁，可以使用以下命令：

SHOW OPEN TABLES WHERE In_use > 0;

这将显示当前正在使用的表及其被锁定的数量。如果某个表被锁定，将会显示 "In_use" 列大于 0。另外，如果 "Name_locked" 列也大于 0，表示表的名称也被锁定。

锁冲突产生，执行写入过程，因执行了读锁，一直在等待释放

解锁

unlock tables

查询时加写或读锁

SELECT clause [FOR UPDATE | LOCK IN SHARE MODE]
加锁可以在事务中使用 SELECT ... FOR UPDATE 或 SELECT ... LOCK IN SHARE MODE 语句实现。其中，SELECT ... FOR UPDATE 语句会给选中的数据行加上排他锁，而 SELECT ... LOCK IN SHARE MODE 则会给选中的数据行加上共享锁。

对行加锁通常使用 SELECT ... FOR UPDATE 或 SELECT ... FOR SHARE 语句来实现。

SELECT ... FOR UPDATE 语句用于加排它锁，防止其他事务修改或删除所选的行。使用此语句时，所选行的锁在事务提交或回滚之前一直保持不变。

例如，以下语句会选取 orders 表中 id=1 的行并对其加排它锁：

SELECT * FROM orders WHERE id = 1 FOR UPDATE;

SELECT ... FOR SHARE 语句用于加共享锁，防止其他事务修改所选的行，但允许其他事务继续查询该行。使用此语句时，所选行的锁在事务提交或回滚之前一直保持不变。

例如，以下语句会选取 orders 表中 id=1 的行并对其加共享锁：

SELECT * FROM orders WHERE id = 1 FOR SHARE;

需要注意的是，SELECT ... FOR UPDATE 和 SELECT ... FOR SHARE 语句都会立即获得锁，如果不能获得锁则会阻塞直到锁可用或等待超时。此外，这些语句只能用于 InnoDB 存储引擎，MyISAM 存储引擎不支持行级锁定。

关闭正在打开的表（清除查询缓存），通常在备份前加全局读锁

FLUSH TABLES [tb_name[,...]] [WITH READ LOCK]