数据库 - 事务 - 隔离级别 - 汇总

站长

2023年03月27日 23:13 · 阅读数 72

事务 - 隔离级别 - 汇总

SQL 标准定义了四个隔离级别

READ-UNCOMMITTED(读取未提交数据)

READ UNCOMMITTED:未提交读，指定语句可以读取已由其他事务修改但尚未提交的行。该选项为默认设置。

原理：

事务对当前读取的数据不加锁；
事务对数据更新前添加行级共享锁，直到事务结束才释放。

可能发生的情况：

事务1读取某些数据记录时，事务2也能对这些记录进行读取、更新；当事务2对这些记录进行更新时，事务1再次读取记录，能读到事务2对该记录的修改版本，即使更新尚未提交。

简单地理解就是：

允许事务同时读数据
允许一个事务读取数据同时另外一个事务修改数据

/// w ##class(M.M100).IsolationLevel("66")
ClassMethod IsolationLevel(age)
{
	w " tran before ：" _ $system.SQL.GetIsolationMode(),!
	&sql(set transaction isolation level read uncommitted)
	w " tran after ：" _ $system.SQL.GetIsolationMode(),!
	
	&sql(start transaction)

	
	&sql(select 
			MT_Age into :oldAge
		from 
			M_T.Person 
		where 
			%ID = 1
	)
	
	w oldAge,!
	
	b ;11
	
	&sql(
		update M_T.Person
		set 
			MT_Age = :age
		where
			ID = 1
	)
	
	b ;33
}

/// w ##class(M.M100).IsolationLevel1()
ClassMethod IsolationLevel1()
{
	w " tran before ：" _ $system.SQL.GetIsolationMode(),!
	&sql(set transaction isolation level read uncommitted)
	w " tran after ：" _ $system.SQL.GetIsolationMode(),!
	
	ts
	
	&sql(select 
			MT_Age into :age
		from 
			M_T.Person 
		where 
			%ID = 1
	)
	
	b ;22
}

执行顺序：

先执行方法 IsolationLevel 查看 age = 20 ，此时还没有更新数据。
执行方法 IsolationLevel1，因为没有更新数据所以查到 age = 20。
在方法 IsolationLevel 输入 g 执行更新sql语句，将 age 更新为 66.
再次执行方法 IsolationLevel1，因为数据已经更改所以脏读数据 age = 66。

数据库 - 事务 - 隔离级别 - 汇总

READ-COMMITTED(读取已提交数据)

READ COMMITTED:已提交读，指定语句不能读取已由其他事务修改但尚未提交的数据，这样可以避免脏读。

原理：

事务对当前被读取的数据加行级共享锁（当读到时才加锁），一旦读完该行，立即释放该行级共享锁；
事务在更新某数据的瞬间（就是发生更新的瞬间），必须先对其加行级排他锁，直到事务结束才释放。

可能发生的情况：

事务1更新某行记录时，事务2不能对这行记录做更新，直到事务1结束。

简单地理解就是：

允许事务同时读数据
必须一个事务读取完数据后，另外一个事务才能修改该数据
必须等更新数据的事务执行完成后，才能对执行其他的读取或者修改该数据的事务

/// w ##class(M.M100).IsolationLevel2("66")
ClassMethod IsolationLevel2(age)
{
	w " tran before ：" _ $system.SQL.GetIsolationMode(),!
	&sql(set transaction isolation level read committed)
	w " tran after ：" _ $system.SQL.GetIsolationMode(),!
	
	&sql(start transaction)

	
	&sql(select 
			MT_Age into :oldAge
		from 
			M_T.Person 
		where 
			%ID = 1
	)
	
	w oldAge,!
	
	b ;11
	
	&sql(
		update M_T.Person
		set 
			MT_Age = :age
		where
			ID = 1
	)
	
	b ;33
}

/// w ##class(M.M100).IsolationLevel1()
ClassMethod IsolationLevel3()
{
	w " tran before ：" _ $system.SQL.GetIsolationMode(),!
	&sql(set transaction isolation level read committed)
	w " tran after ：" _ $system.SQL.GetIsolationMode(),!
	
	ts
	
	&sql(select 
			MT_Age into :age
		from 
			M_T.Person 
		where 
			%ID = 1
	)
	
	b ;22
}

执行顺序：

先执行方法 IsolationLevel 查看 age = 20 ，此时还没有更新数据。
执行方法 IsolationLevel1，因为没有更新数据所以查到 age = 20。
在方法 IsolationLevel 输入 g 执行更新sql语句，将 age 更新为 66.
再次执行方法 IsolationLevel1，因为设置隔离界别为read committed，所以超时退出。提示加共享锁失败。

数据库 - 事务 - 隔离级别 - 汇总

REPEATABLE-READ(可重复读)

REPEATABLE READ:可重复读，指定语句不能读取已由其他事务修改但尚未提交的行，并且指定其他任何事务都不能在当前事务完成之前修改由当前事务读取的数据。

原理：

事务在读取某数据的瞬间（就是开始读取的瞬间），必须先对其加行级共享锁，直到事务结束才释放；
事务在更新某数据的瞬间（就是发生更新的瞬间），必须先对其加行级排他锁，直到事务结束才释放。

可能发生的情况：

事务1读取某行记录时，事务2也能对这行记录进行读取、更新；当事务2对该记录进行更新时，事务1再次读取该记录，读到的仍然是第一次读取的那个版本。
事务1更新某行记录时，事务2不能对这行记录做更新，直到事务1结束。

简单地理解就是：

允许事务同时读数据必须等读取数据的事务执行完成后，才能对执行其他的修改该数据的事务必须等更新数据的事务执行完成后，才能对执行其他的读取或者修改该数据的事务

IRIS 不支持此级别，需要手动实现。

数据库 - 事务 - 隔离级别 - 汇总

/// w ##class(M.M100).IsolationLevel6()
ClassMethod IsolationLevel6()
{
	w " tran before ：" _ $system.SQL.GetIsolationMode(),!
	&sql(set transaction isolation level read committed)
	w " tran after ：" _ $system.SQL.GetIsolationMode(),!
	
	&sql(start transaction)
    
    #; 不加共享锁可重复读。
	l ^M.T.PersonD(1)#"S"
	
	&sql(select 
			MT_Age into :oldAge
		from 
			M_T.Person 
		where 
			%ID = 1
	)
	
	w oldAge,!
	
	b ;11
	

	&sql(select 
			MT_Age into :newAge
		from 
			M_T.Person 
		where 
			%ID = 1
	)
	
	w newAge,!
	
	b ;33
}

/// w ##class(M.M100).IsolationLevel7(67)
ClassMethod IsolationLevel7(age)
{
	w " tran before ：" _ $system.SQL.GetIsolationMode(),!
	&sql(set transaction isolation level read committed)
	w " tran after ：" _ $system.SQL.GetIsolationMode(),!
	
	ts
	
	&sql(
		update M_T.Person
		set 
			MT_Age = :age
		where
			ID = 1
	)
	
	b ;22
}

先执行方法 IsolationLeve6 查看 age = 67 , 给ID为1的数据加共享锁。
执行方法 IsolationLevel7，由于设置了隔离级别 read committed，会加独占锁，因为ID 1 加了共享锁。所以加锁超时。

数据库 - 事务 - 隔离级别 - 汇总

SNAPSHOT:快照

SNAPSHOT:快照，事务只能识别在其开始之前提交的数据修改。在当前事务中执行的语句将看不到在当前事务开始以后由其他事务所做的数据修改，就如同事务中的语句获得了已提交数据的快照，因为该数据在事务开始时就存在了。

IRIS 不支持此级别，无法实现。

SERIALIZABLE(可串行化)

SERIALIZABLE:可串行化，事务串行化执行，隔离级别最高，牺牲了系统的并发性。可以解决并发事务的所有问题。

IRIS 不支持此级别，需要手动实现。

实现原理：

需要加范围锁(range-locks：给SELECT 的查询中使用一个“WHERE”子句描述范围加锁）。
事务在读取某数据的瞬间（就是开始读取的瞬间），必须先对其加表级共享锁，直到事务结束才释放；
事务在更新某数据的瞬间（就是发生更新的瞬间），必须先对其加表级排他锁，直到事务结束才释放。

简单地理解就是：

所有的事务必须等上一个事务执行完成后才开始执行

/// w ##class(M.M100).IsolationLevel8()
ClassMethod IsolationLevel8()
{
	w " tran before ：" _ $system.SQL.GetIsolationMode(),!
	&sql(set transaction isolation level read committed)
	w " tran after ：" _ $system.SQL.GetIsolationMode(),!
	
	&sql(start transaction)
	
	#; 不加共享锁可重复读。
	l +^M.T.PersonD:3
	
	&sql(select 
			count(*) into :count
		from 
			M_T.Person 
	
	)
	
	w count,!
	
	b ;11
	

	&sql(select 
			count(*) into :count
		from 
			M_T.Person 
	
	)
	
	w count,!
	
	b ;33
}

/// w ##class(M.M100).IsolationLevel9()
ClassMethod IsolationLevel9()
{
	w " tran before ：" _ $system.SQL.GetIsolationMode(),!
	&sql(set transaction isolation level read committed)
	w " tran after ：" _ $system.SQL.GetIsolationMode(),!
	
	
	
	&sql(
		insert M_T.Person
		(
			 MT_Age, MT_Name, MT_No
		)
		values 
		(
			 18, "yx", "987654"
		)
	)
	
	b ;22
}

先执行方法 IsolationLeve8 查询Count为 20 给整个表加上共享锁。
执行方法 IsolationLevel9，插入一条数据。因为insert时会自动加上排他锁，因为之前已经共享锁表。所以锁超时，退出。

数据库 - 事务 - 隔离级别 - 汇总

注意：如果只查询数据(SELECT语句)，可以使用SET TRANSACTION来建立隔离级别。不需要START TRANSACTION。

所有的事务必须等上一个事务执行完成后才开始执行。

数据库隔离级别总结

隔离级别	READ-UNCOMMITTED	READ-COMMITTED	Repeatable Read
脏读	Y
不可重复读	Y	Y
幻读	Y	Y	Y
默认	IRIS、Caché	Oracel、SQL Server	MySQL
并发性能	最高		最低

实现事务隔离级别时加锁原理总结

共享锁(S锁):假设事务T1对数据A加上共享锁，那么事务T2可以读数据A，不能修改数据A。
排他锁(X锁):假设事务T1对数据A加上共享锁，那么事务T2不能读数据A，不能修改数据A。

	write lock	read lock	range lock
READ-UNCOMMITTED	X	X	X
READ-COMMITTED	Y	X	X
Repeatable Read	Y	Y	X
SERIALIZABLE	Y	Y	Y

转载自:https://juejin.cn/post/7161588538054541343