1 MySQL事务
1.1 事务的四大特性
事务具有ACID四大特性，分别是原子性（Atomicity），一致性（Consistency），隔离性（Isolation），持久性（Duration）。
- 原子性（Atomicity）：事务的原子性是指事务必须是一个原子的操作序列单元。事务中包含的各项操作在一次执行过程中，要么都成功，要么都失败。任何一项操作都会导致整个事务的失败，同时其它已经被执行的操作都将被撤销并回滚，只有所有的操作全部成功，整个事务才算是成功完成。
- 一致性（Consistency）：事务的一致性是指事务的执行不能破坏数据库数据的完整性和一致性，一个事务在执行前和执行后，数据库都必须处于一致性状态。
- 隔离性（Isolation）：事务的隔离性是指在并发环境中，并发的事务之间是相互隔离的，一个事务的执行不能被其它事务干扰。
- 持久性（Duration）：事务的持久性是指事务一旦提交后，数据库中的数据必须被永久的保存下来。即使服务器系统崩溃或服务器宕机等故障。只要数据库重新启动，那么一定能够将其恢复到事务成功结束后的状态。事务在执行过程中，DML语句不会更改底层硬盘文件中的数据，只是在内存中将历史操作记录下来，只有在事务提交成功的时候，才会修改底层硬盘文件中的数据。
1.2 事务的开启方式
通过 start transaction 或 begin 即可开启事务，通过 commit 提交事务，通过 rollback 回滚事务。
-
开启事务并提交事务：
begin; ... 事务操作 commit; -
开启事务并回滚事务：
start transaction; ... 事务操作 rollback;
1.3 事务的自动提交
事务的 autocommit 是 session 级别的，即修改了当前连接的 autocommit，对其它连接没有影响。默认情况下 autocommit = on。
事务可以设置为自动提交，也可以设置为手动提交，通过以下SQL语句即可设置：
-- 查看是否开启事务自动提交
show variables like 'autocommit';
-- 设置开启事务自动提交
set autocommit = on;
-- 设置关闭事务自动提交
set autocommit = off;
1.4 查看事务的信息
通过以下SQL即可查看当前存在的事务，以及事务的状态等信息：
SELECT * FROM information_schema.INNODB_TRX;
查看事务的示例如下所示：
mysql> SELECT * FROM information_schema.INNODB_TRX;
+--------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+---------------------+
| trx_id | trx_state | trx_started | trx_requested_lock_id | trx_wait_started | trx_weight | trx_mysql_thread_id | trx_query | trx_operation_state | trx_tables_in_use | trx_tables_locked | trx_lock_structs | trx_lock_memory_bytes | trx_rows_locked | trx_rows_modified | trx_concurrency_tickets | trx_isolation_level | trx_unique_checks | trx_foreign_key_checks | trx_last_foreign_key_error | trx_adaptive_hash_latched | trx_adaptive_hash_timeout | trx_is_read_only | trx_autocommit_non_locking | trx_schedule_weight |
+--------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+---------------------+
| 17281 | RUNNING | 2022-10-17 08:29:18 | NULL | NULL | 2 | 14 | NULL | NULL | 0 | 1 | 2 | 1128 | 1 | 0 | 0 | REPEATABLE READ | 1 | 1 | NULL | 0 | 0 | 0 | 0 | NULL |
+--------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+---------------------+
1 row in set (0.00 sec)
2 事务的隔离级别
2.1 事务的四种隔离级别
SQL标准定义了四种隔离级别，MySQL的默认隔离级别是可重复读：
| 隔离级别 | 含义 | 脏读 | 不可重复读 | 幻读 | 说明 |
|---|---|---|---|---|---|
| READ_UNCOMMITTED | 读未提交 | 有 | 有 | 有 | 生产环境一般会禁用 |
| READ_COMMITTED | 读已提交 | 无 | 有 | 有 | |
| REPEATABLE_READ | 可重复读 | 无 | 无 | 有 | MySQL默认隔离级别 |
| SERIALIZABLE | 串行化读 | 无 | 无 | 无 |
2.2 查看并设置隔离级别
查看隔离级别：
-- 查看全局的隔离级别
show global variables like 'transaction_isolation';
-- 查看当前会话的隔离级别
show variables like 'transaction_isolation';
设置隔离级别：
-- 设置全局的隔离级别
set global transaction isolation level repeatable read;
set global transaction isolation level read committed;
set global transaction isolation level read uncommitted;
-- 设置会话的隔离级别
set session transaction isolation level repeatable read;
set session transaction isolation level read committed;
set session transaction isolation level read uncommitted;
2.3 隔离级别存在的问题
2.3.1 脏读
脏读是指一个事务读到了另一个事务已修改但还没有提交的数据。
只有在 READ_UNCOMMITTED 隔离级别的情况下才有可能会出现脏读。解决脏读的方法是把隔离级别设置为 READ_COMMITTED 或 REPEATABLE_READ 或 SERIALIZABLE。
例如，下面示例中，事务B在T3时刻读到了事务A已修改但还没有提交的数据，所以事务B读到的是脏数据。
T3时刻事务A和事务B的执行结果如下图所示：
2.3.2 不可重复读
不可重复读是指在一个事务中，相同的查询语句前后两次查询到的数据不一致。不可重复读是因为在两次查询期间，其它事务修改了数据。
只有在 READ_UNCOMMITTED 、READ_COMMITTED 隔离级别的情况下才有可能会出现不可重复读。解决不可重复读的方法是把隔离级别设置为 REPEATABLE_READ 或 SERIALIZABLE。
例如，下面示例中，事务A在T2时刻和T5时刻读取到的数据不一致，因为事务B在T3时刻修改了数据，所以事务A发生了不可重复读。
T2时刻事务A和事务B的执行结果如下图所示：
T5时刻事务A和事务B的执行结果如下图所示：
2.3.3 幻读
幻读是指在一个事务中，相同的查询语句前后两次查询到的数据量不一致。幻读是因为在两次查询期间，其它事务新增或删除了数据。
只有在 READ_UNCOMMITTED 、READ_COMMITTED 和 REPEATABLE_READ 隔离级别的情况下才有可能会出现幻读。解决幻读的方法是把隔离级别设置为 SERIALIZABLE。
例如，下面示例中，事务A在T2时刻和T5时刻读取到的数据量不一致，因为事务B在T3时刻新增了数据，所以事务A发生了幻读。
T2时刻事务A和事务B的执行结果如下图所示：
T5时刻事务A和事务B的执行结果如下图所示：
3 MySQL锁
3.1 锁分类和冲突
3.1.1 锁的分类
MySQL支持InnoDB和MyISAM等存储引擎，其中InnoDB支持事务，同时支持表级锁和行级锁（默认），而MyISAM不支持事务，且只支持表级锁。如果需要使用MySQL的事务特性，则需要使用InnoDB存储引擎，所以本文中所涉及的锁都是InnoDB支持的锁。
InnoDB支持多种锁，而各种锁都有自己的特性及优缺点，对主要的几种锁的分类如下图所示：
从锁的机制来划分，包括乐观锁和悲观锁。其中乐观锁是用户自己实现的锁，而不是MySQL实现的锁，例如用户可以基于MVCC实现乐观锁。MySQL实现的锁都是悲观锁。
从锁的特性来划分，包括共享锁和排它锁。如果一个事务获得的共享锁，则其它事务仍然可以获得共享锁，但不能获得排它锁；如果一个事务获得了排它锁，则其它事务既不能获得共享锁，也不能获得排它锁。即共享锁之间是兼容的，而共享锁和排它锁之间是冲突的。
| 共享锁 | 排它锁 | |
|---|---|---|
| 共享锁 | 兼容 | 冲突 |
| 排它锁 | 冲突 | 冲突 |
从锁的级别来划分，包括表级锁和行级锁。表级锁是加在数据表上的锁，而行级锁是加在数据行上的锁。
| 锁级别 | 优点 | 缺点 |
|---|---|---|
| 表级锁 | 加锁开销小、加锁快、无死锁问题 | 锁的粒度大，发生锁冲突的概率高，并发处理能力低 |
| 行级锁 | 锁的粒度小，发生锁冲突的概率低，并发处理能力强 | 加锁开销大，加锁慢，有死锁问题 |
从锁的类型来划分，包括表锁、意向锁、记录锁、间隙锁、临键锁、插入意向锁等。
3.1.2 锁的冲突
| 意向共享锁 (IS) | 意向排它锁 (IX) | 共享锁 (S) | 排它锁 (X) | 读锁 | 写锁 | |
|---|---|---|---|---|---|---|
| 意向共享锁 (IS) | 兼容 | 兼容 | 兼容 | 冲突 | 兼容 | 冲突 |
| 意向排它锁 (IX) | 兼容 | 兼容 | 冲突 | 冲突 | 冲突 | 冲突 |
| 共享锁 (S) | 兼容 | 冲突 | 兼容 | 冲突 | 兼容 | 冲突 |
| 排它锁 (X) | 冲突 | 冲突 | 冲突 | 冲突 | 冲突 | 冲突 |
| 读锁 | 兼容 | 冲突 | 兼容 | 冲突 | 兼容 | 冲突 |
| 写锁 | 冲突 | 冲突 | 冲突 | 冲突 | 冲突 | 冲突 |
3.2 表级锁详解
3.2.1 表锁（读锁、写锁）
表锁是表级锁中的一种，分为读锁和写锁，读锁与读锁是兼容的，但是读锁与写锁是冲突的。
表锁需要手动的显示加锁，通过以下SQL即可在表上加表锁或释放表锁：
-- 在表上加读锁
lock tables 表名 read;
-- 在表上加写锁
lock tables 表名 write;
-- 在表1上加读锁，在表2上加写锁
lock tables 表名1 read 表名2 write;
-- 释放所有的读锁和写锁
unlock tables;
3.2.2 意向锁（意向共享锁、意向排它锁）
意向锁是表级锁中的一种，分为意向共享锁（IS）和意向排它锁（IX），意向锁由InnoDB自动获取，不需要用户手动获取，并且不会和行级锁发生冲突。事务在对表中的数据行加共享锁之前，会先获得该表的意向共享锁，在对表中的数据行加排它锁之前，会先获得该表的意向排它锁；事务提交或回滚后，意向锁才会自动释放。
通过以下SQL即可在表上加意向锁：
-- 先自动给表加意向共享锁，然后给数据行加共享锁
select ... lock in share mode;
-- 先自动给表加意向排它锁，然后给数据行加排它锁
select ... for update;
-
案例一：意向锁作用案例
意向锁主要解决如下问题：当一个事务想要在表A上添加表级锁或行级锁时，不需要检查表A上的行级锁，而是检查表A上的意向锁即可，如果冲突则阻塞事务。
首先，事务A执行如下语句：
begin; select * from t_member where id = 1 for update;事务A会在表t_member上加意向排它锁（表级锁），并且在id = 1这行数据上加排它锁（行级锁）。事务A会加锁成功。
然后，事务B执行如下语句：
lock tables t_member read;事务B想在表t_member上加读锁（表级锁），但是检测到事务A已经在表t_member上加了意向排它锁且未释放，所以事务B会被阻塞，直到事务A提交事务并释放锁，事务B才能成功在表t_member上加读锁。
执行结果如下图所示：
从上面的执行结果可知，事务A执行成功，但在提交事务之前，事务B一直处于阻塞状态。当事务A执行commit语句提交事务并释放了锁之后，事务B才会加读锁成功，执行结果如下图所示：
总结：从上面的案例中可以发现，如果存在意向锁，事务B就不需要检查表t_member的每行是否存在排它锁，这可以提高事务效率。
-
案例二：意向锁不影响事务并发性案例
意向共享锁和意向排它锁是相互兼容的，并且表级的意向锁不会和行级锁发生冲突。正是由于这个特性，意向锁不会对多个事务在不同数据行的加锁产生影响，也就不会影响事务的并发性。
首先，事务A执行如下语句：
begin; select * from t_member where id = 1 for update;事务A会在表t_member上加意向排它锁（表级锁），并且在id = 1这行数据上加排它锁（行级锁）。事务A会加锁成功。
然后，事务B执行如下语句：
begin; select * from t_member where id = 2 for update;事务B会在表t_member上加意向排它锁（表级锁），并且在id = 2这行数据上加排它锁（行级锁）。事务B会加锁成功。
执行结果如下图所示：
总结：从上面的案例可以发现，事务A和事务B都在表t_member上加了意向排它锁，但由于意向锁的特性，这两个事务加排它锁都不会受到影响。但如果事务A上加的不是意向锁，而是表锁，那么事务B将被阻塞。
3.3 行级锁详解
3.3.1 行锁（共享锁、排它锁）
InnoDB实现了两种标准的行锁，分别是共享锁和排它锁。
共享锁（S）：是事务并发读取某一行记录所需要持有的锁。如果一个事务对数据R添加共享锁后，其他事务仍可以在数据R上立即获得共享锁，这种情况称为锁兼容。但如果其他事务想要在数据R上添加排它锁则会处于等待状态，这种情况称为锁冲突。
排它锁（X）：是事务并发更新或删除某一行记录所需要持有的锁。如果一个事务对数据R添加排它锁后，其他事务就不可以在数据R上立即获得共享锁和排它锁。
尽管共享锁和排它锁是行锁，但并不是具体的某一种锁，而主要是表明锁的特性和锁的级别，包括记录锁、间隙锁、临键锁等具体类型的锁。事务在请求共享锁或排它锁时，获取到的结果可能是记录锁，也可能是间隙锁，也可能是临键锁，这取决于数据库的隔离级别以及查询的数据是否存在。
通过以下方式可以在数据行上加共享锁和排它锁：
-- 手动在数据行上加共享锁
select ... lock in share mode;
-- 手动在数据行上加排它锁
select ... for update;
-- 自动在数据行上加排它锁
insert ...
-- 自动在数据行上加排它锁
update ...
-- 自动在数据行上加排它锁
delete ...
3.3.2 记录锁
记录锁（Record Lock）：记录锁是行级锁中的一种，它锁定的是单行数据记录，并且锁定的是索引项而不是数据记录。记录锁既可能是共享锁，也可能是排它锁，这取决于加的锁的特性。
使用 =、in 这些等值查询条件时，会给数据行加记录锁。记录锁示例如下：
首先，事务A执行如下语句：
begin;
select * from t_member where id = 2 for update;
事务A会在表t_member上加意向排它锁（表级锁），并且在id = 2这行数据上加排它锁的记录锁（行级锁）。事务A会加锁成功。
然后，事务B执行如下语句：
begin;
update t_member set member_name = '刘聪' where id = 2;
事务B会在表t_member上加意向排它锁（表级锁），并且在id = 2这行数据记录上加排它锁的记录锁（行级锁），并修改id = 2这行数据。但由于事务A先在id = 2的数据行上加了排它锁的记录锁，所以事务B会一直阻塞，直到锁等待超时，或者直到事务A提交事务并释放了锁。
执行结果如下图所示：
总结：从上面的案例可以发现，事务A确实在id = 2的记录上成功添加了排它锁的记录锁，而事务B在id = 2的记录上添加排它锁的记录锁时等待锁超时。
3.3.3 间隙锁
间隙锁（Gap Lock）：间隙锁是行级锁中的一种，它锁定的是一个区间范围的数据记录，这个区间范围是一个开区间，即左右边界都不包含，并且锁定的是索引项而不是数据记录。间隙锁即可能是共享锁，也可能是排它锁，这取决于加的锁的特性。
间隙锁是不互斥的，即两个事务可以同时持有包含共同间隙的间隙锁。这里的共同间隙包括两种场景：其一是两个间隙锁的间隙区间完全一样；其二是一个间隙锁包含的间隙区间是另一个间隙锁包含间隙区间的子集。
间隙锁是为了解决幻读，只有在REPEATE READ隔离级别下才有间隙锁，而在READ UNCOMMITED和READ COMMITED隔离级别下没有间隙锁。
使用 >、>=、<、<=、between 这些范围查询条件时，会给数据行加间隙锁。间隙锁示例如下：
首先，事务A执行如下语句：
begin;
select * from t_member where id between 2 and 5 for update;
事务A会在表t_member上加意向排它锁（表级锁），并且在id为 2~5 的这些行数据上加间隙锁（行级锁）。事务A会加锁成功。
然后，事务B执行如下语句：
begin;
update t_member set member_name = '李明' where id = 1;
update t_member set member_name = '刘聪' where id = 2;
update t_member set member_name = '张爱家' where id = 5;
update t_member set member_name = '孙辉辉' where id = 6;
事务B会在表t_member上意向排它锁（表级锁），并依次在id = 1、2、5、6的数据记录上加记录锁（行级锁），并修改该行数据。但由于事务A先在id 为 2~5 的数据行上加了间隙锁，所以事务B在修改id = 2 和 5的记录时会一直阻塞，直到锁等待超时，而在修改id = 1 和 6的记录时会成功。
执行结果如下图所示：
总结：从上面的案例可以发现，事务A确实在id 为 2~5 的记录上成功添加了间隙锁，而事务B在id = 2和5的记录上添加记录锁时等待锁超时，事务B在id = 1和6的记录上添加记录锁成功。
3.3.4 临键锁
临键锁（Next-Key Lock）：临键锁是行级锁中的一种，它锁定的也是一个区间范围的数据记录，这个区间范围是一个左开右闭的区间，即不包含左边界，但包含右边界，并且锁定的是索引项而不是数据记录。临键锁既可能是共享锁，也可能是排它锁，这取决于加的锁的特性。临键锁可以理解为是间隙锁 + 记录锁。
默认情况下，InnoDB在 REPEATABLE READ 隔离级别运行，使用 Next-Key Lock进行搜索和索引扫描，以防止幻读。如果使用 select … lock in share mode 或 select … for update 语句，InnoDB会使用 Next-Key Lock 进行搜索和索引扫描；但如果使用普通的 select 语句，那么InnoDB将是快照读，不会使用任何锁，因而无法防止幻读；
InnoDB在 READ UNCOMMITED 和 READ COMMITED 隔离级别时，即便使用 select … lock in share mode 或 select … for update 语句，也无法防止幻读，因为这两种隔离级别下只会有记录锁，而不会有间隙锁和临键锁。
3.3.5 插入意向锁
插入意向锁（Insert Intention Lock）：插入意向锁是一种间隙锁，是在插入一行之前由insert操作设置的一种锁，主要是为了解决幻读。
3.4 加锁方式和示例
3.4.1 加锁的方式
对于INSETT、UPDATE、DELETE等类型的语句，MySQL会自动加锁，而对于SELECT类型的语句，则需要用户手动加锁，具体如下：
| SQL | 表级锁类型 | 行级锁类型 | 加锁说明 | 释放锁说明 |
|---|---|---|---|---|
| SELECT … | 不加锁 | 不加锁 | 基于快照读，所以不加锁 | |
| SELECT … LOCK IN SHARE MODE | 意向共享锁 | 共享锁 | 手动加锁：需加LOCK IN SHARE MODE | 提交或回滚事务后释放锁 |
| SELECT … FOR UPDATE | 意向排它锁 | 排它锁 | 手动加锁：需加FOR UPDATE | 提交或回滚事务后释放锁 |
| INSERT … | 意向排它锁 | 排它锁 | 自动加锁 | 提交或回滚事务后释放锁 |
| UPDATE … | 意向排它锁 | 排它锁 | 自动加锁 | 提交或回滚事务后释放锁 |
| DELETE … | 意向排它锁 | 排它锁 | 自动加锁 | 提交或回滚事务后释放锁 |
| LOCK TABLES 表名 READ | 读锁 | 不加锁 | 手动加锁 | 执行UNLOCK TABLES后释放锁 |
| LOCK TABLES 表名 WRITE | 写锁 | 不加锁 | 手动加锁 | 执行UNLOCK TABLES后释放锁 |
InnoDB的行级锁是针对索引项加的锁，而不是针对记录加的锁，并且该索引不能失效，否则都会从行级锁自动降级为表锁。如果对非索引项加行级锁并修改数据，则行级锁会自动降级为表锁，从而导致其它修改类型的事务均都于等待状态。
3.4.2 查看锁信息
通过以下SQL语句可以查看当前存在的意向锁和行级锁信息，但不能查看表锁信息：
SELECT ENGINE, ENGINE_LOCK_ID, ENGINE_TRANSACTION_ID, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_DATA
FROM performance_schema.data_locks
ORDER BY ENGINE_LOCK_ID ASC;
执行结果示例如下：
mysql> SELECT ENGINE, ENGINE_LOCK_ID, ENGINE_TRANSACTION_ID, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_DATA
-> FROM performance_schema.data_locks
-> ORDER BY ENGINE_LOCK_ID ASC;
+--------+-----------------------------------+-----------------------+---------------+-------------+------------+-----------+---------------+-----------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_DATA |
+--------+-----------------------------------+-----------------------+---------------+-------------+------------+-----------+---------------+-----------+
| INNODB | 2672211924864:1070:2672217558040 | 284147188635520 | member | t_member | NULL | TABLE | IS | NULL |
| INNODB | 2672211924864:9:4:5:2672201433456 | 284147188635520 | member | t_member | PRIMARY | RECORD | S,REC_NOT_GAP | 5 |
| INNODB | 2672211924864:9:6:5:2672201433112 | 284147188635520 | member | t_member | age_INDEX | RECORD | S | 15, 5 |
| INNODB | 2672211924864:9:6:6:2672201433800 | 284147188635520 | member | t_member | age_INDEX | RECORD | S,GAP | 16, 6 |
+--------+-----------------------------------+-----------------------+---------------+-------------+------------+-----------+---------------+-----------+
4 rows in set (0.00 sec)
mysql> SELECT ENGINE, ENGINE_LOCK_ID, ENGINE_TRANSACTION_ID, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_DATA
-> FROM performance_schema.data_locks
-> ORDER BY ENGINE_LOCK_ID ASC;
+--------+-----------------------------------+-----------------------+---------------+-------------+------------+-----------+---------------+-----------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_DATA |
+--------+-----------------------------------+-----------------------+---------------+-------------+------------+-----------+---------------+-----------+
| INNODB | 2672211924864:1070:2672217558040 | 17212 | member | t_member | NULL | TABLE | IX | NULL |
| INNODB | 2672211924864:9:4:5:2672201433456 | 17212 | member | t_member | PRIMARY | RECORD | X,REC_NOT_GAP | 5 |
| INNODB | 2672211924864:9:6:5:2672201433112 | 17212 | member | t_member | age_INDEX | RECORD | X | 15, 5 |
| INNODB | 2672211924864:9:6:6:2672201433800 | 17212 | member | t_member | age_INDEX | RECORD | X,GAP | 16, 6 |
+--------+-----------------------------------+-----------------------+---------------+-------------+------------+-----------+---------------+-----------+
4 rows in set (0.00 sec)
结果字段说明如下：
- ENGINE：存储引擎类型
- ENGINE_LOCK_ID：存储引擎的锁ID
- ENGINE_TRANSACTION_ID：存储引擎的事务ID
- OBJECT_SCHEMA：数据库名
- OBJECT_NAME：数据表名
- INDEX_NAME：索引名称，
- LOCK_TYPE：锁的级别，TABLE为表级锁，RECORD为记录锁
- LOCK_MODE：锁的类型，S为共享间隙锁，S,REC_NOT_GAP为共享记录锁，S,GAP为共享间隙锁，X为排它间隙锁，X,REC_NOT_GAP为排它记录锁，X,GAP为排它间隙锁
- LOCK_DATA：锁对应的索引项的值
3.4.3 加读锁或写锁示例
通过以下命令就可以加读锁或写锁，以及释放锁：
-- 在t_member表上加写锁
lock tables t_member write;
-- 在t_member表上加读锁
lock tables t_member read;
-- 释放所有的读锁和写锁
unlock tables;
例如：事务A先在表t_member上加写锁，然后在事务A释放写锁之前，事务B再在表t_member上加读锁，则事务B加读锁会被阻塞，执行结果如下图所示：
当事务A释放了写锁之后，事务B加读锁才会成功，执行结果如下图所示：
可以发现，通过 lock tables t_member write; 这个语句会在t_member表上加写锁。同样通过 lock tables t_member read; 这个语句会在t_member表上加读锁。通过 unlock tables; 这个语句会释放表锁。
3.4.4 加共享锁和意向共享锁示例
通过以下命令就可以加共享锁和加意向共享锁：
begin;
select * from t_member where id = 1 lock in share mode;
commit;
命令执行结果如下图所示：
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t_member where id = 1 lock in share mode;
+----+-----------+-------------+-----+-------------+
| id | member_no | member_name | age | phone |
+----+-----------+-------------+-----+-------------+
| 1 | 1001 | 李明 | 11 | 18934528794 |
+----+-----------+-------------+-----+-------------+
1 row in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
在执行commit前查看锁信息如下：
mysql> SELECT ENGINE, ENGINE_LOCK_ID, ENGINE_TRANSACTION_ID, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_DATA
-> FROM performance_schema.data_locks
-> ORDER BY ENGINE_LOCK_ID ASC;
+--------+-----------------------------------+-----------------------+---------------+-------------+------------+-----------+---------------+-----------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_DATA |
+--------+-----------------------------------+-----------------------+---------------+-------------+------------+-----------+---------------+-----------+
| INNODB | 2672211924864:1070:2672217558040 | 284147188635520 | member | t_member | NULL | TABLE | IS | NULL |
| INNODB | 2672211924864:9:4:2:2672201433112 | 284147188635520 | member | t_member | PRIMARY | RECORD | S,REC_NOT_GAP | 1 |
+--------+-----------------------------------+-----------------------+---------------+-------------+------------+-----------+---------------+-----------+
2 rows in set (0.00 sec)
在执行commit后查看锁信息如下：
mysql> SELECT ENGINE, ENGINE_LOCK_ID, ENGINE_TRANSACTION_ID, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_DATA
-> FROM performance_schema.data_locks
-> ORDER BY ENGINE_LOCK_ID ASC;
Empty set (0.00 sec)
可以发现，通过 select * from t_member where id = 1 lock in share mode; 这个语句，会在t_member表上加意向共享锁（IS），并且在t_member表的id=1的记录上加共享锁（S），且共享锁的类型为记录锁（REC_NOT_GAP）。而且当执行commit提交事务后，意向共享锁（IS）和共享锁（S）都成功释放了。
3.4.5 加排它锁和意向排它锁示例
通过以下命令就可以加排它锁和意向排它锁：
begin;
select * from t_member where id = 1 for update;
commit;
命令执行结果如下图所示：
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t_member where id = 1 for update;
+----+-----------+-------------+-----+-------------+
| id | member_no | member_name | age | phone |
+----+-----------+-------------+-----+-------------+
| 1 | 1001 | 李明 | 11 | 18934528794 |
+----+-----------+-------------+-----+-------------+
1 row in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
在执行commit前查看锁信息如下：
mysql> SELECT ENGINE, ENGINE_LOCK_ID, ENGINE_TRANSACTION_ID, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_DATA
-> FROM performance_schema.data_locks
-> ORDER BY ENGINE_LOCK_ID ASC;
+--------+-----------------------------------+-----------------------+---------------+-------------+------------+-----------+---------------+-----------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_DATA |
+--------+-----------------------------------+-----------------------+---------------+-------------+------------+-----------+---------------+-----------+
| INNODB | 2672211924864:1070:2672217558040 | 17219 | member | t_member | NULL | TABLE | IX | NULL |
| INNODB | 2672211924864:9:4:2:2672201433112 | 17219 | member | t_member 
最低0.47元/天 解锁文章
扫一扫
458
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
