从Innodb的锁到事务隔离级别

个人感觉，应该先理解锁再来看事物隔离级别，因为事务的四种隔离级别实际上就是数据库厂商利用锁的机制，为我们这些程序员提供的四套总的并行读数据的策略。但是网上看到的文章都是隔离级别说完再说锁，也不知道这俩有什么关系，所以边写边学记录一下。

0. 先记录几个调试常用的命令，创建个测试表。

# 查看自动提交是否开启
show variables like 'autocommit';
# 打开/关闭自动提交
set autocommit = 0;
# 查看当前的事物隔离级别
select @@session.transaction_isolation;
select @@global.transaction_isolation;
# 设置事务隔离级别 read uncommitted/read committed/REPEATABLE READ/serializable
set session transaction isolation level REPEATABLE READ;
# 查看当前的事务 
select * from information_schema.innodb_trx;
# 查询锁的情况
select * from performance_schema.data_locks;
# 查询锁的等待情况
select * from performance_schema.data_lock_waits;

# 创建测试表
CREATE TABLE `account` (
  `id` int NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `account` double DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
INSERT INTO account (id,name,account) VALUES (1,'a',1000.0),(2,'b',1000.0),(3,'c',1000.0);

1. 观察一下innodb在事务执行过程中是怎么加锁的

begin;
UPDATE account a SET a.account = a.account + 100 WHERE id = 1;
# 查下现在加锁的情况，结果如下
select * from performance_schema.data_locks;

ENGINE	ENGINE_LOCK_ID	ENGINE_TRANSACTION_ID	THREAD_ID	EVENT_ID	OBJECT_SCHEMA	OBJECT_NAME	PARTITION_NAME	SUBPARTITION_NAME	INDEX_NAME	OBJECT_INSTANCE_BEGIN	LOCK_TYPE	LOCK_MODE	LOCK_STATUS	LOCK_DATA
INNODB	281473336931112:1067:281473231968896	3893	71	72	local_test	account				281473231968896	TABLE	IX	GRANTED
INNODB	281473336931112:2:4:2:281473231965984	3893	71	72	local_test	account			PRIMARY	281473231965984	RECORD	X,REC_NOT_GAP	GRANTED	1

用这种办法去分析各种文章出现的情况

记录几篇文章，最好的还是直接看官方文档，建议看完再往下看
mysql锁文档
MVCC
MVCC2

2. 一个例子看懂锁和MVCC

# session 1
	begin;
	select * from account;
+----+------+---------+
| id | name | account |
+----+------+---------+
|  1 | a    |    1000 |
|  2 | b    |    1000 |
|  3 | c    |    1000 |
+----+------+---------+
# 这里的select是快照读，是MVCC实现的原理可以看上面的文章

# session 2
	begin;
	insert into account (id,name,account) value (4,'d',1000);
Query OK, 1 row affected (0.00 sec) 
	commit;

# session 1
	select * from account;
+----+------+---------+
| id | name | account |
+----+------+---------+
|  1 | a    |    1000 |
|  2 | b    |    1000 |
|  3 | c    |    1000 |
+----+------+---------+

# 在session 2 commit前查一下当前的事务
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
4405	RUNNING	2021-12-05 02:08:50			3	21			0	1	2	1128	1	1	0	REPEATABLE READ	1	1		0	0	0	0
562948627931136	RUNNING	2021-12-05 01:53:33			0	11			0	0	0	1128	0	0	0	REPEATABLE READ	1	1		0	0	0	0

现在session1在select后表没有发生改变是因为，在session1中进行select后，id为4的这条记录在undolog中的DB_TRX_ID（session2更新的，现在是4405）和当前事务的事务id不同（现在是562948627931136，不知道为啥和网上说的递增不一样，可能mysql8以后又有改动了）。
快照读就是这个时候判断的，因为事务id不同所以不会使用，会使用当前行的DB_ROLL_PTR找到undolog的原始数据，再select出来。

# session 1
	update account a SET a.account = a.account+100 where id = 4;
	select * from account;
+----+------+---------+
| id | name | account |
+----+------+---------+
|  1 | a    |    1000 |
|  2 | b    |    1000 |
|  3 | c    |    1000 |
|  4 | d    |    1100 |
+----+------+---------+
Query OK, 0 rows affected (0.00 sec)

这时发生了幻读。这是因为session1的的事务id和undolog中的DB_TRX_ID是相同的，所以select就能查到id=4的这条记录了。

还有一种情况就是session2进行了删除操作，如下

# session 2
	begin;
	delete from account where id =4;
Query OK, 1 row affected (0.00 sec) 
	commit;

现在session1进行update时不可能改到之前那条id=4的记录的（通过db_row_id区分），所以之前那条记录的事务id会保持为session2的事务id。

	update account a set a.account = a.account+100 where id = 4;
Query OK, 0 rows affected (0.00 sec)

2.1 RR隔离级别怎么解决幻读

间隙锁
这个例子中有个前提，唯一索引只有在值存在时才是行锁，值不存在的话，还是会变成间隙锁