事务1.sql

本代码简单明了

----脏读----
--事务1--(1)
use pubs
begin transaction
update jobs set job_desc = 'ACCP1' 
where job_id = 1

rollback transaction
commit transaction


----不可重复读----
--事务1(1,3)隐性事务
use pubs
set implicit_transactions on--(只执行1次)
select * from jobs where job_id = 1

rollback transaction
select @@trancount


----幻影读----
--事务1(1)
set implicit_transactions on--(只执行1次)
select * from jobs where job_desc = 'accp'
--(3)
select * from jobs where job_desc = 'accp'

rollback transaction
----以上为事务缺陷----



----事务的隔离级别----
--隔离级别3：Repeatable Read  可重复读
	  --可以防止脏读和不可重复读。
--事务1：
--设置为默认级别(任何事务类型都不能更新)

use pubs
set transaction isolation level Repeatable Read 
set implicit_transactions on--(只执行1次)
--(1)
select * from jobs where job_id = 1

--(2)
select * from jobs where job_id = 1

rollback transaction



--4、Serializable     可串行读(连续的)
--演示隔离级别3不能阻止添加数据
set transaction isolation level Repeatable Read 
set implicit_transactions on--(只执行1次)
--(1)
select * from jobs


--升高隔离级别4
set transaction isolation level Serializable 
set implicit_transactions on--(只执行1次)
--(1)
select * from jobs

commit
rollback