数据库隔离之 read-committed

Read Committed是mysql默认的transaction isolation level. 当transaction 使用这个isolation level, select 查询只能看见 提交了的数据；它看不到并发的其他transaction未提交的数据和在查询当中提交的数据。Select看到的是一个数据库快照，这个快照发生在查询开始的那一瞬间。如果在同一个transaction，select能看到上调sql语句更新的数据，即使他还没有提交。同一个transaction中，连续的两条select可能看到不同的数据，如果并发的其他transaction有提交数据。

sex	name
male	李雷
female	韩梅梅

Read committed transaction1	Read committed transaction2
Begin:	Begin:
Select * from user where sex=’male’; 查出：李雷
	Insert into user (‘Jack’,  ‘male’)
	Commit; 提交以后，transaction1 可见Jack。不提交就不可以，所以叫read committed
Select * from user where sex=’male’; 查出：李雷和Jack

 

UPDATE, DELETE, SELECT FOR UPDATE, SELECT FOR SHARE查询数据的规则和select是一样的：通过查询前的快照。查出来这个数据可能已经被另一个还没有提交的transaction改了。这时会停下来，等那个transaction提交。如果那个transaction回滚了，当前的update操作可以使用之前查出来的数据。如果那个transaction 提交了，新版本的数据和老版本相比，会出现有的可能被删除了或者是被修改了。 Where语句会被重新执行，看新版本的数据是否还符合查询条件。SELECT FOR UPDATE 和 SELECT FOR SHARE得到更新后的、带行锁的数据。

Read committed transaction1	Read committed transaction2
Begin:	Begin:
	Update user set name=’Dear’+name where sex=’male’
Update user set name=’Mr.’+name where sex=’male’; 查出行后，因为同样的行已经被transaction2修改，transaction1会阻塞，等待transaction2 commit。 等transaction2 commit后，transaction1会重新对刚才查出来的行执行一次where语句，过滤掉不再符合条件的行。请注意，只会对刚才查出来的行执行where语句，不会对此时的user表。	Commit; 假设在transaction1阻塞后执行的commit。
以上逻辑同样适用于delete，select for update 和 select for share

 

带有ON CONFLICT DO UPDATE 的INSERT和其他命令类似。在read-committed mode里，insert操作或者insert或者update。如果有源自并发transaction的冲突，insert会变成update，即使更新空数据。

Read committed transaction1	Read committed transaction2
Begin:	Begin:
	Insert into user (‘Jack’,  ‘male’)
Insert into user (‘Jack’,  ‘male’) 发现transaction2插入同样数据。transaction1阻塞 等待transaction2提交，然后修改目标行	Commit; 假设在transaction1阻塞后执行的commit。
Commit;
以上逻辑适用于insert on conflict do update. 和insert on conflict do nothing稍有不同

 

在READ COMMITTED MODE, insert on conflict do nothing 会取消操作，如果另一个transaction 已经插入了该条数据，即使该transaction还没有提交。

 

在read committed mode下，更新操作可能看到数据不一致的情况：在当前transaction中，对于将要更新的记录，更新sql命令可以看到并发transaction提交的这部分数据，其余的数据是看不到的。所以read-committed mode 不适合有复杂查询条件的情况。

 

Website有两条记录hits分别等于9和10

BEGIN;

Update website set hits=hits+1;

Delete from website where hits=10

COMMIT;

Begin;
	Begin;
Update website set hits=hits+1;
	Delete from website where hits=10; hits=10的记录有新版本，阻塞，等待Update提交
Commit;
	Commit; hits=10的记录, hits变成了11，不符合delete的查询条件。另外，该delete语句并看不到后来hits从9变成10那条记录，因为只会关心之前查出来的数据的版本问题。这个例子演示的数据不一致的场景

 

在read-committed 模式下，每个sql命令执行前都会获得一个快照。在Transaction A中，其他的transaction提交了，Transaction A接下来的sql命令就会看到刚刚那个transaction提交的数据。其实问题的关键在于一个sql命令能不能看到一致的数据。

 

Read-committed mode提供部分数据一致，适用于大多应用。有复杂查询和更新语句的系统，需要更严格执行数据一致性的isolation level。

Read committed transaction1	Read committed transaction2
Begin:	Begin:
	UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345;
UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345; 发现transaction2操作同样数据。transaction1阻塞 等待transaction2提交，拿到transaction2提交的数据，然后修改目标行	Commit; 假设在transaction1阻塞后执行的commit。
Commit;