事务  执行语句 Connetion1 A

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

--阶段2 UPDATE  myProduct SET     price = price + 1 WHERE   id = 1;    SELECT  id ,         price FROM    dbo.myProduct WHERE   id = 1;    --阶段3 UPDATE  myProduct SET     price = price + 5 WHERE   id = 1;    SELECT  id ,         price FROM    dbo.myProduct WHERE   id = 1;    --阶段4 COMMIT TRAN

　　

Connection2 B

1 2 3 4 5 6 7 8 9

--在阶段2执行之后 SET TRAN ISOLATION LEVEL READ UNCOMMITTED BEGIN TRAN; SELECT  id ,         price FROM    dbo.myProduct WHERE   id = 1   COMMIT TRAN;

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

两个事务的流程图：

 

 

阶段1：Price=10,事务A对myProduct表请求排他锁

阶段2：事务A对myProduct表使用了排他锁，更新price = price + 1，然后事务A查询price的价格: price=11。事务B不请求任何锁，事务B在A更新Price之后进行查询，price=11

阶段3：事务A更新price = price + 5，然后事务A查询price的价格，price = 16。事务B查询price的价格: price=16

阶段4：事务A释放排他锁

阶段5：事务A中查询price的价格:price = 16。事务B查询price的价格: price=16

大家可以看到事务B有两种结果，这就是“未提交读 （READ UNCOMMITTED）”隔离级别的含义：

（1）读操作可以读取未提交的修改（也称为脏读）。

（2）读操作不会妨碍写操作请求排他锁，其他事务正在进行读操作时，写操作可以同时对这些数据进行修改。

（3）事务A进行了多次修改，事务B在不同阶段进行查询时可能会有不同的结果。

 

★ 2.已提交读（READ COMMITTED）（默认值）

打开两个查询窗口，Connetion1,connection2

Step1: 执行Connection1的SQL 语句

Step2: 执行Connection2的SQL 语句

执行语句		执行语句
Connetion1	A	1 2 UPDATE dbo.myProduct SET price = price + 1 WHERE id=1 SELECT * FROM dbo.myProduct WHERE id =1
Connection2	B	1 2 SET TRANSACTION ISOLATION LEVEL READ COMMITTED SELECT * FROM dbo.myProduct WHERE id = 1

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

两个事务的流程图：

 

阶段1：Price=10,事务A对myProduct表请求排他锁

阶段2：事务A对myProduct表使用了排他锁，更新price = price + 1，然后事务A查询price的价格: price=11。然后事务B请求共享锁进行读操作，查询price，

　　由于在当前隔离级别下，事务A的排他锁和事务B的共享锁存在冲突，所以事务B需要等待事务A释放排他锁后才能读取数据。

阶段3：事务A提交事务（COMMIT TRAN）

阶段4：事务A提交完事务后，释放排他锁

阶段5：事务B获得了共享锁，进行读操作，price=11

“已提交读 （READ UNCOMMITTED）”隔离级别的含义：

（1）必须获得共享锁才能进行读操作，其他事务如果对该资源持有排他锁，则共享锁必须等待排他锁释放。

（2）读操作不能读取未提交的修改，读操作读取到的数据是提交过的修改。

（3）读操作不会在事务持续期间内保留共享锁，其他事务可以在两个都操作之间更改数据资源，读操作因而可能每次得到不同的取值。这种现象称为“不可重复读”

 

★ 3.可重复读（REPEATABLE READ）

打开两个查询窗口，Connetion1,connection2

Step1: 执行Connection1的SQL 语句

Step2: 执行Connection2的SQL 语句

 

执行语句	事务	执行语句
Connetion1	A	1 2 UPDATE dbo.myProduct SET price = price + 1 WHERE id=1 SELECT * FROM dbo.myOrder WHERE id =1
Connection2	B	1 2 UPDATE dbo.myOrder SET customer = ‘ddd‘ WHERE id = 1 SELECT * FROM dbo.myProduct WHERE id = 1

 

 

 

 

 

 

两个事务的流程图：

 

阶段1：Price=10,事务A对myProduct表请求共享锁

阶段2：事务A对myProduct表使用了共享锁，事务A查询price的价格: price=10，事务A一直持有共享锁直到事务A完成为止。然后事务B请求排他锁进行写操作price=price+1，

由于在当前隔离级别下，事务A的共享锁和事务B请求的排他锁存在冲突，所以事务B需要等待事务A释放共享锁后才能修改数据。

阶段3：事务A查询price, price=10, 说明事务B的更新操作被阻塞了，更新操作没有被执行。然后事务A提交事务（COMMIT TRAN）

阶段4：事务A提交完事务后，释放共享锁

阶段5：事务B获得了排他锁，进行写操作，price=11

“可重复读 （REPEATABLE READ）”隔离级别的含义：

（1）必须获得共享锁才能进行读操作，获得的共享锁将一直保持直到事务完成之止。

（2）在获得共享锁的事务完成之前，没有其他事务能够获得排他锁修改这一数据资源，这样可以保证实现可重复的读取。

（3）两个事务在第一次读操作之后都将保留它们获得的共享锁，所以任何一个事务都不能获得为了更新数据而需要的排他锁，这种情况将会导致死锁（deadlock），不过却避免了更新冲突。

★ 4.可序列化（SERIALIZABLE）

打开两个查询窗口，Connetion1,connection2
Step1: 执行Connection1的SQL 语句
Step2: 执行Connection2的SQL 语句

 

执行语句	事务	执行语句
Connetion1	A	1 2 3 BEGIN TRANSACTION SET TRANSACTION ISOLATION LEVEL SERIALIZABLE SELECT * FROM dbo.myProduct WHERE id = 1
Connection2	B	1 INSERT INTO dbo.myProduct(id, price) VALUES (1, 20)

 

 

 

 

 

 

 

两个事务的流程图：

 

 

阶段1：Price=10,事务A对myProduct表请求共享锁

阶段2：事务A对myProduct表使用了共享锁，事务A查询id=1的price的价格:1行记录，price=10，事务A一直持有共享锁直到事务A完成为止。然后事务B请求排他锁进行插入操作id=1,price=20，

　　由于在当前隔离级别下，事务B试图增加能够满足事务A的读操作的查询搜索条件的新行，所以事务A的共享锁和事务B请求的排他锁存在冲突，事务B需要等待事务A释放共享锁后才能插入数据。

阶段3：事务A查询出id=1的数据只有1行，说明事务B的插入操作被阻塞了，插入操作没有被执行。然后事务A提交事务（COMMIT TRAN）

阶段4：事务A提交完事务后，释放共享锁

阶段5：事务B获得了排他锁，进行插入操作，插入成功，查询出id=1的数据有两条

 

“可序列化（SERIALIZABLE）”隔离级别的含义：

（1）必须获得共享锁才能进行读操作，获得的共享锁将一直保持直到事务完成之止。

（2）在获得共享锁的事务完成之前，没有其他事务能够获得排他锁修改这一数据资源，且当其他事务增加能够满足当前事务的读操作的查询搜索条件的新行时，其他事务将会被阻塞，直到当前事务完成然后释放共享锁，其他事务才能获得排他锁进行插入操作。

（3）事务中的读操作在任何情况下读取到的数据是一致的，不会出现幻影行。

（4）范围锁：读操作锁定满足查询搜索条件的整个范围的锁

 

5.隔离级别总结

 

隔离级别	是否读取未提交的行	是否不可重复读	是否丢失更新	是否幻读	共享锁持续时间	是否持有范围锁
未提交读 READ UNCOMMITTED	Y	Y	Y	Y	当前语句	N
已提交读 READ COMMITTED	Y	Y	Y	Y	当前语句	N
可重复读REPEATABLE READ	N	N	N	Y	事务开始到事务完成	N
可序列化SERIALZABLE	N	N	N	N	事务开始到事务完成	Y

 

 

回到顶部

 

回到顶部

四.死锁

死锁是指一种进程之间互相永久阻塞的状态，可能涉及两个或更多的进程。

打开两个查询窗口，Connetion1,connection2

Step1: 执行Connection1的SQL 语句

Step2: 执行Connection2的SQL 语句

 

执行语句	事务	执行语句
Connetion1	A	1 2 3 4 SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRAN UPDATE dbo.myProduct SET price = price + 1 WHERE id=1 SELECT * FROM dbo.myOrder WHERE id =1
Connection2	B	1 2 3 4 SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRAN UPDATE dbo.myOrder SET customer = ‘ddd‘ WHERE id = 1 SELECT * FROM dbo.myProduct WHERE id = 1

 

 

 

 

 

 

 

两个事务的流程图：

阶段1：Price=10,事务A对myProduct表请求排他锁。Customer = aaa,事务B对myOrder请求排他锁

阶段2：事务A对myProduct表使用了排他锁，更新price = price + 1。然后事务B对myOrder表使用了排他锁，更新customer=ddd。

阶段3：事务A查询myOrder表，对myOrder表请求共享锁，因为事务A的请求的共享锁与事务B的排他锁冲突，所以事务A被阻塞。然后事务B查询myProduct表，对myProduct表请求共享锁，因为事务B的请求的共享锁与事务A的排他锁冲突，所以事务B被阻塞。

阶段4：事务A等待事务B的排他锁释放，事务B等待事务A的排他锁释放，导致死锁。事务A和事务B都被阻塞了。

阶段5：SQL Server在几秒之内检测到死锁，会选择一个事务作为死锁的牺牲品，终止这个事务，并回滚这个事务所做的操作。在这个例子中，事务A被终止，提示信息：事务(进程 ID 53)与另一个进程被死锁在 锁 资源上，并且已被选作死锁牺牲品。请重新运行该事务。

“死锁 （Dead Lock）”的一些注意事项：

（1）如果两个事务没有设置死锁优先级，且两个事务进行的工作量也差不多一样时，任何一个事务都由可能被终止。

（2）解除死锁要付出一定的系统开销，因为这个过程会涉及撤销已经执行过的处理。

（3）事务处理的时间时间越长，持有锁的时间就越长，死锁的可能性也就越大，应该尽可能保持事务简短，把逻辑上可以不属于同一个工作单元的操作移到事务以外。

（4）上面的例子中，事务A和事务B以相反顺序访问资源，所以发生了死锁。如果两个事务按同样的顺序来访问资源，则不会发生这种类型的死锁。在不改变程序的逻辑情况下，可以通过交换顺序来解决死锁的问题。

30分钟全面解析-SQL事务+隔离级别+阻塞+死锁

标签：