澳门新萄京官方网站-www.8455.com-澳门新萄京赌场网址

Server事务与锁,事务隔离级别详解

2019-09-11 作者:数据库网络   |   浏览(81)

一  概述

SQL 事务隔离级别

SQL 事务隔离级别

SQL 事务隔离级别

SQL 事务隔离级别

Server事务与锁,事务隔离级别详解。在数据库方面,对于非DBA的程序员来说,事务与锁是一大难点,针对该难点,本篇文章试图采用图文的方式来与大家一起探讨。

概述

Server事务与锁,事务隔离级别详解。概述

概述

概述

“浅谈SQL Server 事务与锁”这个专题共分两篇,上篇主讲事务及事务一致性问题,并简略的提及一下锁的种类和锁的控制级别。

     隔离级别用于决定如果控制并发用户如何读写数据的操作,同时对性能也有一定的影响作用。

     隔离级别用于决定如果控制并发用户如何读写数据的操作,同时对性能也有一定的影响作用。

     隔离级别用于决定如果控制并发用户如何读写数据的操作,同时对性能也有一定的影响作用。

     隔离级别用于决定如果控制并发用户如何读写数据的操作,同时对性能也有一定的影响作用。

下篇主讲SQL Server中的锁机制,锁控制级别和死锁的若干问题。

步骤

步骤

步骤

步骤

二   事务

事务隔离级别通过影响读操作来间接地影响写操作;可以在回话级别上设置事务隔离级别也可以在查询(表级别)级别上设置事务隔离级别。
事务隔离级别总共有6个隔离级别:
READ UNCOMMITTED(未提交读,读脏),相当于(NOLOCK)
READ COMMITTED(已提交读,默认级别)
REPEATABLE READ(可以重复读),相当于(HOLDLOCK)
SERIALIZABLE(可序列化)
SNAPSHOT(快照)
READ COMMITTED SNAPSHOT(已经提交读隔离)
对于前四个隔离级别:READ UNCOMMITTED<READ COMMITTED<REPEATABLE READ<SERIALIZABLE
隔离级别越高,读操作的请求锁定就越严格,锁的持有时间久越长;所以隔离级别越高,一致性就越高,并发性就越低,同时性能也相对影响越大.

事务隔离级别通过影响读操作来间接地影响写操作;可以在回话级别上设置事务隔离级别也可以在查询(表级别)级别上设置事务隔离级别。
事务隔离级别总共有6个隔离级别:
READ UNCOMMITTED(未提交读,读脏),相当于(NOLOCK)
READ COMMITTED(已提交读,默认级别)
REPEATABLE READ(可以重复读),相当于(HOLDLOCK)
SERIALIZABLE(可序列化)
SNAPSHOT(快照)
READ COMMITTED SNAPSHOT(已经提交读隔离)
对于前四个隔离级别:READ UNCOMMITTED<READ COMMITTED<REPEATABLE READ<SERIALIZABLE
隔离级别越高,读操作的请求锁定就越严格,锁的持有时间久越长;所以隔离级别越高,一致性就越高,并发性就越低,同时性能也相对影响越大.

事务隔离级别通过影响读操作来间接地影响写操作;可以在回话级别上设置事务隔离级别也可以在查询(表级别)级别上设置事务隔离级别。
事务隔离级别总共有6个隔离级别:
READ UNCOMMITTED(未提交读,读脏),相当于(NOLOCK)
READ COMMITTED(已提交读,默认级别)
REPEATABLE READ(可以重复读),相当于(HOLDLOCK)
SERIALIZABLE(可序列化)
SNAPSHOT(快照)
READ COMMITTED SNAPSHOT(已经提交读隔离)
对于前四个隔离级别:READ UNCOMMITTED<READ COMMITTED<REPEATABLE READ<SERIALIZABLE
隔离级别越高,读操作的请求锁定就越严格,锁的持有时间久越长;所以隔离级别越高,一致性就越高,并发性就越低,同时性能也相对影响越大.

事务隔离级别通过影响读操作来间接地影响写操作;可以在回话级别上设置事务隔离级别也可以在查询(表级别)级别上设置事务隔离级别。
事务隔离级别总共有6个隔离级别:
READ UNCOMMITTED(未提交读,读脏),相当于(NOLOCK)
READ COMMITTED(已提交读,默认级别)
REPEATABLE READ(可以重复读),相当于(HOLDLOCK)
SERIALIZABLE(可序列化)
SNAPSHOT(快照)
READ COMMITTED SNAPSHOT(已经提交读隔离)
对于前四个隔离级别:READ UNCOMMITTED<READ COMMITTED<REPEATABLE READ<SERIALIZABLE
隔离级别越高,读操作的请求锁定就越严格,锁的持有时间久越长;所以隔离级别越高,一致性就越高,并发性就越低,同时性能也相对影响越大.

1   何为事务

获取事务隔离级别(isolation level)

获取事务隔离级别(isolation level)

获取事务隔离级别(isolation level)

获取事务隔离级别(isolation level)


DBCC USEROPTIONS 
DBCC USEROPTIONS 
DBCC USEROPTIONS 
DBCC USEROPTIONS 

 预览众多书籍,对于事务的定义,不同文献不同作者对其虽有细微差别却大致统一,我们将其抽象概括为:

设置隔离

设置隔离

设置隔离

设置隔离

事务:指封装且执行单个或多个操作的单个工作单元,在SqlServer中,其定义表现为显示定义和隐式定义两种方式。
设置回话隔离
SET TRANSACTION ISOLATION LEVEL <ISOLATION NAME>
--注意:在设置回话隔离时(REPEATABLE READ)两个单词需要用空格间隔开,但是在表隔离中可以粘在一起(REPEATABLEREAD)

设置查询表隔离
SELECT ....FROM <TABLE> WITH (<ISOLATION NAME>) 
设置回话隔离
SET TRANSACTION ISOLATION LEVEL <ISOLATION NAME>
--注意:在设置回话隔离时(REPEATABLE READ)两个单词需要用空格间隔开,但是在表隔离中可以粘在一起(REPEATABLEREAD)

设置查询表隔离
SELECT ....FROM <TABLE> WITH (<ISOLATION NAME>) 
设置回话隔离
SET TRANSACTION ISOLATION LEVEL <ISOLATION NAME>
--注意:在设置回话隔离时(REPEATABLE READ)两个单词需要用空格间隔开,但是在表隔离中可以粘在一起(REPEATABLEREAD)

设置查询表隔离
SELECT ....FROM <TABLE> WITH (<ISOLATION NAME>) 
设置回话隔离
SET TRANSACTION ISOLATION LEVEL <ISOLATION NAME>
--注意:在设置回话隔离时(REPEATABLE READ)两个单词需要用空格间隔开,但是在表隔离中可以粘在一起(REPEATABLEREAD)

设置查询表隔离
SELECT ....FROM <TABLE> WITH (<ISOLATION NAME>) 

 基于如上的定义,我们可以将事务解剖拆分为如下几个点:

1.READ UNCOMMITTED

1.READ UNCOMMITTED

1.READ UNCOMMITTED

澳门新萄京官方网站,1.READ UNCOMMITTED

(1)事务是单个工作单元,这一定义,才使事务具有ACID属性

READ UNCOMMITTED:未提交读,读脏数据
默认的读操作:需要请求共享锁,允许其他事物读锁定的数据但不允许修改.
READ UNCOMMITTED:读操作不申请锁,运行读取未提交的修改,也就是允许读脏数据,读操作不会影响写操作请求排他锁.

READ UNCOMMITTED:未提交读,读脏数据
默认的读操作:需要请求共享锁,允许其他事物读锁定的数据但不允许修改.
READ UNCOMMITTED:读操作不申请锁,运行读取未提交的修改,也就是允许读脏数据,读操作不会影响写操作请求排他锁.

READ UNCOMMITTED:未提交读,读脏数据
默认的读操作:需要请求共享锁,允许其他事物读锁定的数据但不允许修改.
READ UNCOMMITTED:读操作不申请锁,运行读取未提交的修改,也就是允许读脏数据,读操作不会影响写操作请求排他锁.

READ UNCOMMITTED:未提交读,读脏数据
默认的读操作:需要请求共享锁,允许其他事物读锁定的数据但不允许修改.
READ UNCOMMITTED:读操作不申请锁,运行读取未提交的修改,也就是允许读脏数据,读操作不会影响写操作请求排他锁.

(2)事务是封装操作的,如封装基本的CRUD操作

 创建测试数据

 创建测试数据

 创建测试数据

 创建测试数据

1  --事务
2 Begin Tran
3 SELECT * FROM UserInfo
4 INSERT INTO UserInfo VALUES('Alan_beijing',35)
5 UPDATE UserInfo SET Age=31 WHERE UserName='Alan_beijing'
6 DELETE UserInfo WHERE UserName='Alan_beijing'
7 Commit Tran

澳门新萄京官方网站 1

澳门新萄京官方网站 2

澳门新萄京官方网站 3

澳门新萄京官方网站 4

(3)事务在封装操作时,可以封装单个操作,也可以封装多个操作(封装多个操作时,应注意与批处理的区别)

IF OBJECT_ID('Orders','U') IS NOT NULL DROP TABLE Orders 
GO
CREATE TABLE Orders
(ID INT NOT NULL,
Price FLOAT NOT NULL
);
INSERT INTO Orders VALUES(10,10.00),(11,11.00),(12,12.00),(13,13.00),(14,14.00);
GO
SELECT ID,Price FROM Orders 
IF OBJECT_ID('Orders','U') IS NOT NULL DROP TABLE Orders 
GO
CREATE TABLE Orders
(ID INT NOT NULL,
Price FLOAT NOT NULL
);
INSERT INTO Orders VALUES(10,10.00),(11,11.00),(12,12.00),(13,13.00),(14,14.00);
GO
SELECT ID,Price FROM Orders 
IF OBJECT_ID('Orders','U') IS NOT NULL DROP TABLE Orders 
GO
CREATE TABLE Orders
(ID INT NOT NULL,
Price FLOAT NOT NULL
);
INSERT INTO Orders VALUES(10,10.00),(11,11.00),(12,12.00),(13,13.00),(14,14.00);
GO
SELECT ID,Price FROM Orders 
IF OBJECT_ID('Orders','U') IS NOT NULL DROP TABLE Orders 
GO
CREATE TABLE Orders
(ID INT NOT NULL,
Price FLOAT NOT NULL
);
INSERT INTO Orders VALUES(10,10.00),(11,11.00),(12,12.00),(13,13.00),(14,14.00);
GO
SELECT ID,Price FROM Orders 

(4)在SqlServer中,事务的定义分为显示定义和隐式定义两种方式

澳门新萄京官方网站 5

澳门新萄京官方网站 6

澳门新萄京官方网站 7

澳门新萄京官方网站 8

 显示定义:以Begin Tran作为开始,其中提交事务为Commit Tran,回滚事务为RollBack Tran,如我们在一个事务中插入两条操作语句

新建回话1将订单10的价格加1

新建回话1将订单10的价格加1

新建回话1将订单10的价格加1

新建回话1将订单10的价格加1

1 --显示定义事务
2 Begin Tran
3 INSERT INTO UserInfo VALUES('Alan_shanghai',30)
4 INSERT INTO UserInfo VALUES('Alan_beijing',35)
5 Commit Tran

澳门新萄京官方网站 9

澳门新萄京官方网站 10

澳门新萄京官方网站 11

澳门新萄京官方网站 12

 隐式定义:如果不显示定义事务,SQL Server 默认把每个语句当作一个事务来处理(执行完每个语句之后就自动提交事务)

BEGIN TRANSACTION
UPDATE Orders 
SET Price=Price 1
WHERE ID=10

SELECT ID,Price FROM Orders 
WHERE ID=10
BEGIN TRANSACTION
UPDATE Orders 
SET Price=Price 1
WHERE ID=10

SELECT ID,Price FROM Orders 
WHERE ID=10
BEGIN TRANSACTION
UPDATE Orders 
SET Price=Price 1
WHERE ID=10

SELECT ID,Price FROM Orders 
WHERE ID=10
BEGIN TRANSACTION
UPDATE Orders 
SET Price=Price 1
WHERE ID=10

SELECT ID,Price FROM Orders 
WHERE ID=10

2   事务的ACID属性

澳门新萄京官方网站 13

澳门新萄京官方网站 14

澳门新萄京官方网站 15

澳门新萄京官方网站 16


澳门新萄京官方网站 17

澳门新萄京官方网站 18

澳门新萄京官方网站 19

澳门新萄京官方网站 20

 事务作为单个工作单元,该定义使其具有ACID属性,ACID属性指原子性(Atomicity)、一致性(Consisitency)、隔离性(Isolation)和持久性(Durability)。

在另一个回话2中执行查询操作

在另一个回话2中执行查询操作

在另一个回话2中执行查询操作

在另一个回话2中执行查询操作

(1)原子性(Atomicity)

澳门新萄京官方网站 21

澳门新萄京官方网站 22

澳门新萄京官方网站 23

澳门新萄京官方网站 24

原子性指事务必须是原子工作单元,即对于事务的封装操作,要么全部执行,要么全都不执行。如下情况均会导致事务的撤销或回滚。。。

首先不添加隔离级别,默认是READ COMMITTED,由于数据之前的更新操作使用了排他锁,所以查询一直在等待锁释放*/
SELECT ID,Price FROM Orders 
WHERE ID=10
---将查询的隔离级别设置为READ UNCOMMITTED允许未提交读,读操作之前不请求共享锁。
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT ID,Price FROM Orders 
WHERE ID=10;
--当然也可以使用表隔离,效果是一样的
SELECT ID,Price FROM Orders WITH (NOLOCK)
WHERE ID=10
首先不添加隔离级别,默认是READ COMMITTED,由于数据之前的更新操作使用了排他锁,所以查询一直在等待锁释放*/
SELECT ID,Price FROM Orders 
WHERE ID=10
---将查询的隔离级别设置为READ UNCOMMITTED允许未提交读,读操作之前不请求共享锁。
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT ID,Price FROM Orders 
WHERE ID=10;
--当然也可以使用表隔离,效果是一样的
SELECT ID,Price FROM Orders WITH (NOLOCK)
WHERE ID=10
首先不添加隔离级别,默认是READ COMMITTED,由于数据之前的更新操作使用了排他锁,所以查询一直在等待锁释放*/
SELECT ID,Price FROM Orders 
WHERE ID=10
---将查询的隔离级别设置为READ UNCOMMITTED允许未提交读,读操作之前不请求共享锁。
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT ID,Price FROM Orders 
WHERE ID=10;
--当然也可以使用表隔离,效果是一样的
SELECT ID,Price FROM Orders WITH (NOLOCK)
WHERE ID=10
首先不添加隔离级别,默认是READ COMMITTED,由于数据之前的更新操作使用了排他锁,所以查询一直在等待锁释放*/
SELECT ID,Price FROM Orders 
WHERE ID=10
---将查询的隔离级别设置为READ UNCOMMITTED允许未提交读,读操作之前不请求共享锁。
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT ID,Price FROM Orders 
WHERE ID=10;
--当然也可以使用表隔离,效果是一样的
SELECT ID,Price FROM Orders WITH (NOLOCK)
WHERE ID=10

a.事务提交之前,系统发生故障或重新启动,SQL Server将会撤销在事务中进行的所有操作;

澳门新萄京官方网站 25

澳门新萄京官方网站 26

澳门新萄京官方网站 27

澳门新萄京官方网站 28

b.事务处理中遇到错误,SQL Server通常会自动回滚事务,但也有少数例外;

澳门新萄京官方网站 29

澳门新萄京官方网站 30

澳门新萄京官方网站 31

澳门新萄京官方网站 32

c.一些不太严重的错误不会引发事务的自动回滚,如主键冲突,锁超时等;

假设在回话1中对操作执行回滚操作,这样价格还是之前的10,但是回话2中则读取到的是回滚前的价格11,这样就属于一个读脏操作

假设在回话1中对操作执行回滚操作,这样价格还是之前的10,但是回话2中则读取到的是回滚前的价格11,这样就属于一个读脏操作

假设在回话1中对操作执行回滚操作,这样价格还是之前的10,但是回话2中则读取到的是回滚前的价格11,这样就属于一个读脏操作

假设在回话1中对操作执行回滚操作,这样价格还是之前的10,但是回话2中则读取到的是回滚前的价格11,这样就属于一个读脏操作

d.可以使用错误处理代码来捕获一些错误,并采取相应的操作,如把错误记录在日志中,再回滚事务等;

ROLLBACK TRANSACTION
ROLLBACK TRANSACTION
ROLLBACK TRANSACTION
ROLLBACK TRANSACTION

(2)一致性(Consisitency)

2.READ COMMITTED

2.READ COMMITTED

2.READ COMMITTED

2.READ COMMITTED

一致性主要指数据一致性,即主要对象是数据。从宏观上来说,指某一段时间区间,数据要保持一致性状态,从微观上来说,某个时间点数据要保持一致性状态,我们举个例子,

READ COMMITTED(已提交读)是SQL SERVER默认的隔离级别,可以避免读取未提交的数据,隔离级别比READ UNCOMMITTED未提交读的级别更高;
该隔离级别读操作之前首先申请并获得共享锁,允许其他读操作读取该锁定的数据,但是写操作必须等待锁释放,一般读操作读取完就会立刻释放共享锁。

READ COMMITTED(已提交读)是SQL SERVER默认的隔离级别,可以避免读取未提交的数据,隔离级别比READ UNCOMMITTED未提交读的级别更高;
该隔离级别读操作之前首先申请并获得共享锁,允许其他读操作读取该锁定的数据,但是写操作必须等待锁释放,一般读操作读取完就会立刻释放共享锁。

READ COMMITTED(已提交读)是SQL SERVER默认的隔离级别,可以避免读取未提交的数据,隔离级别比READ UNCOMMITTED未提交读的级别更高;
该隔离级别读操作之前首先申请并获得共享锁,允许其他读操作读取该锁定的数据,但是写操作必须等待锁释放,一般读操作读取完就会立刻释放共享锁。

READ COMMITTED(已提交读)是SQL SERVER默认的隔离级别,可以避免读取未提交的数据,隔离级别比READ UNCOMMITTED未提交读的级别更高;
该隔离级别读操作之前首先申请并获得共享锁,允许其他读操作读取该锁定的数据,但是写操作必须等待锁释放,一般读操作读取完就会立刻释放共享锁。

假若有两个事务A和B对同一张表进行操作,A向表中写数据,B向数据表中读取数据,可以猜测,B读取的数据大致有三种粗粒度可能:

新建回话1将订单10的价格加1,此时回话1的排他锁锁住了订单10的值

新建回话1将订单10的价格加1,此时回话1的排他锁锁住了订单10的值

新建回话1将订单10的价格加1,此时回话1的排他锁锁住了订单10的值

新建回话1将订单10的价格加1,此时回话1的排他锁锁住了订单10的值

第一种可能:A还没向数据表中写入数据的状态;

澳门新萄京官方网站 33

澳门新萄京官方网站 34

澳门新萄京官方网站 35

澳门新萄京官方网站 36

第二种可能:A已向数据表中写入部分数据,但还未写完的状态;

BEGIN TRANSACTION
UPDATE Orders 
SET Price=Price 1
WHERE ID=10

SELECT ID,Price FROM Orders 
WHERE ID=10
BEGIN TRANSACTION
UPDATE Orders 
SET Price=Price 1
WHERE ID=10

SELECT ID,Price FROM Orders 
WHERE ID=10
BEGIN TRANSACTION
UPDATE Orders 
SET Price=Price 1
WHERE ID=10

SELECT ID,Price FROM Orders 
WHERE ID=10
BEGIN TRANSACTION
UPDATE Orders 
SET Price=Price 1
WHERE ID=10

SELECT ID,Price FROM Orders 
WHERE ID=10

第三种可能:A已向数据表中写完数据;

澳门新萄京官方网站 37

澳门新萄京官方网站 38

澳门新萄京官方网站 39

澳门新萄京官方网站 40

如此,造成了事务的不一致性。

澳门新萄京官方网站 41

澳门新萄京官方网站 42

澳门新萄京官方网站 43

澳门新萄京官方网站 44

关于事务一致性,可能会发生 丢失更新,脏读,不可重复读和幻读等问题,下文会详细论述这些事务一致性问题。

在回话2中执行查询,将隔离级别设置为READ COMMITTED

在回话2中执行查询,将隔离级别设置为READ COMMITTED

在回话2中执行查询,将隔离级别设置为READ COMMITTED

在回话2中执行查询,将隔离级别设置为READ COMMITTED

(3)隔离性(Isolation)

澳门新萄京官方网站 45

澳门新萄京官方网站 46

澳门新萄京官方网站 47

澳门新萄京官方网站 48

隔离性指当两个及其以上事务对同一边界资源进行操作时,要控制好每个事务的边界,控制好数据访问机制,确保事务只能访问处于期望的一致性级别下的数据。

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT ID,Price FROM Orders 
WHERE ID=10
---由于READ COMMITTED需要申请获得共享锁,而锁与回话1的排他锁冲突,回话被堵塞,

----在回话1中执行事务提交
COMMIT TRANSACTION
/*由于回话1事务提交,释放了订单10的排他锁,此时回话2申请共享锁成功查到到订单10的价格为修改后的价格11,READ COMMITTED由于是已提交读隔离级别,所以不会读脏数据.
但是由于READ COMMITTED读操作一完成就立即释放共享锁,读操作不会在一个事务过程中保持共享锁,也就是说在一个事务的的两个查询过程之间有另一个回话对数据资源进行了更改,会导致一个事务的两次查询得到的结果不一致,这种现象称之为不可重复读.*/
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT ID,Price FROM Orders 
WHERE ID=10
---由于READ COMMITTED需要申请获得共享锁,而锁与回话1的排他锁冲突,回话被堵塞,

----在回话1中执行事务提交
COMMIT TRANSACTION
/*由于回话1事务提交,释放了订单10的排他锁,此时回话2申请共享锁成功查到到订单10的价格为修改后的价格11,READ COMMITTED由于是已提交读隔离级别,所以不会读脏数据.
但是由于READ COMMITTED读操作一完成就立即释放共享锁,读操作不会在一个事务过程中保持共享锁,也就是说在一个事务的的两个查询过程之间有另一个回话对数据资源进行了更改,会导致一个事务的两次查询得到的结果不一致,这种现象称之为不可重复读.*/
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT ID,Price FROM Orders 
WHERE ID=10
---由于READ COMMITTED需要申请获得共享锁,而锁与回话1的排他锁冲突,回话被堵塞,

----在回话1中执行事务提交
COMMIT TRANSACTION
/*由于回话1事务提交,释放了订单10的排他锁,此时回话2申请共享锁成功查到到订单10的价格为修改后的价格11,READ COMMITTED由于是已提交读隔离级别,所以不会读脏数据.
*/
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT ID,Price FROM Orders 
WHERE ID=10
---由于READ COMMITTED需要申请获得共享锁,而锁与回话1的排他锁冲突,回话被堵塞,

----在回话1中执行事务提交
COMMIT TRANSACTION
/*由于回话1事务提交,释放了订单10的排他锁,此时回话2申请共享锁成功查到到订单10的价格为修改后的价格11,READ COMMITTED由于是已提交读隔离级别,所以不会读脏数据.
*/

在SQL Server中,一般采用锁机制来控制,下文中,我们会详细论述。

澳门新萄京官方网站 49

澳门新萄京官方网站 50

澳门新萄京官方网站 51

澳门新萄京官方网站 52

(4)持久性(Durability)

重置数据

重置数据

重置数据

重置数据

 我们对数据表进行操作时,一般会按照先后顺序执行如下两步:

UPDATE Orders 
SET Price=10
WHERE ID=10
UPDATE Orders 
SET Price=10
WHERE ID=10
UPDATE Orders 
SET Price=10
WHERE ID=10
UPDATE Orders 
SET Price=10
WHERE ID=10

第一步:将对数据表操作写入到磁盘上数据库的事务日志中(持久还到磁盘事务日志中);

3.REPEATABLE READ

3.REPEATABLE READ

注意:但是由于READ COMMITTED读操作一完成就立即释放共享锁,读操作不会在一个事务过程中保持共享锁,也就是说在一个事务的的两个查询过程之间有另一个回话对数据资源进行了更改,会导致一个事务的两次查询得到的结果不一致,这种现象称之为不可重复读.

注意:但是由于READ COMMITTED读操作一完成就立即释放共享锁,读操作不会在一个事务过程中保持共享锁,也就是说在一个事务的的两个查询过程之间有另一个回话对数据资源进行了更改,会导致一个事务的两次查询得到的结果不一致,这种现象称之为不可重复读.

第二步:完成第一步后,再将对数据表操作写入到磁盘上数据库的数据分区中(持久化到磁盘上数据库分区中);

REPEATABLE READ(可重复读):保证在一个事务中的两个读操作之间,其他的事务不能修改当前事务读取的数据,该级别事务获取数据前必须先获得共享锁同时获得的共享锁不立即释放一直保持共享锁至事务完成,所以此隔离级别查询完并提交事务很重要。

REPEATABLE READ(可重复读):保证在一个事务中的两个读操作之间,其他的事务不能修改当前事务读取的数据,该级别事务获取数据前必须先获得共享锁同时获得的共享锁不立即释放一直保持共享锁至事务完成,所以此隔离级别查询完并提交事务很重要。

3.REPEATABLE READ

3.REPEATABLE READ

关于如上两步,我们来想想可能发生的问题:

在回话1中执行查询订单10,将回话级别设置为REPEATABLE READ

在回话1中执行查询订单10,将回话级别设置为REPEATABLE READ

REPEATABLE READ(可重复读):保证在一个事务中的两个读操作之间,其他的事务不能修改当前事务读取的数据,该级别事务获取数据前必须先获得共享锁同时获得的共享锁不立即释放一直保持共享锁至事务完成,所以此隔离级别查询完并提交事务很重要。

REPEATABLE READ(可重复读):保证在一个事务中的两个读操作之间,其他的事务不能修改当前事务读取的数据,该级别事务获取数据前必须先获得共享锁同时获得的共享锁不立即释放一直保持共享锁至事务完成,所以此隔离级别查询完并提交事务很重要。

问题1:完成如上第一步之前,系统发生故障(如系统异常,系统重启),数据库引擎会怎么做?

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
SELECT ID,Price FROM Orders 
WHERE ID=10
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
SELECT ID,Price FROM Orders 
WHERE ID=10

在回话1中执行查询订单10,将回话级别设置为REPEATABLE READ

在回话1中执行查询订单10,将回话级别设置为REPEATABLE READ

 由于未完成第一步,提交指令还未记录到磁盘的事务日志中,此时事务并未持久化,系统发生故障后,SQL Server

新建回话2修改订单10的价格

新建回话2修改订单10的价格

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
SELECT ID,Price FROM Orders 
WHERE ID=10
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
SELECT ID,Price FROM Orders 
WHERE ID=10

会检查每个数据库的事务日志,进行恢复处理(恢复处理一般分为重做阶段和撤销阶段),此时的恢复处理为重做阶段,即提交指令还未记录到磁盘的事务日志中,

UPDATE Orders 
SET Price=Price 1
WHERE ID=10
---由于回话1的隔离级别REPEATABLE READ申请的共享锁一直要保持到事务结束,所以回话2无法获取排他锁,处于等待状态
UPDATE Orders 
SET Price=Price 1
WHERE ID=10
---由于回话1的隔离级别REPEATABLE READ申请的共享锁一直要保持到事务结束,所以回话2无法获取排他锁,处于等待状态

新建回话2修改订单10的价格

新建回话2修改订单10的价格

数据库引擎会撤销这些事务所做的所有修改,这个过程也成为回滚。

在回话1中执行下面语句,然后提交事务

在回话1中执行下面语句,然后提交事务

UPDATE Orders 
SET Price=Price 1
WHERE ID=10
---由于回话1的隔离级别REPEATABLE READ申请的共享锁一直要保持到事务结束,所以回话2无法获取排他锁,处于等待状态
UPDATE Orders 
SET Price=Price 1
WHERE ID=10
---由于回话1的隔离级别REPEATABLE READ申请的共享锁一直要保持到事务结束,所以回话2无法获取排他锁,处于等待状态

问题2:完成如上第一步但还未完成第二步,系统发生故障(如系统异常,系统重启),数据库引擎会怎么做?

SELECT ID,Price FROM Orders 
WHERE ID=10
COMMIT TRANSACTION
SELECT ID,Price FROM Orders 
WHERE ID=10
COMMIT TRANSACTION

在回话1中执行下面语句,然后提交事务

在回话1中执行下面语句,然后提交事务

 完成第一步后,提交指令已记录到磁盘的事务日志中,无论数据操作是否被写入到磁盘的数据分区,此时事务已持久化,系统发生故障后,SQL Server

澳门新萄京官方网站 53

澳门新萄京官方网站 54

SELECT ID,Price FROM Orders 
WHERE ID=10
COMMIT TRANSACTION
SELECT ID,Price FROM Orders 
WHERE ID=10
COMMIT TRANSACTION

会检查每个数据库的事务日志,进行恢复处理(恢复处理一般分为重做阶段和撤销阶段),此时的恢复处理为重做阶段,即由于数据修改还没有运用到数据分区的事务,

回话1的两次查询得到的结果一致,前面的两个隔离级别无法得到一致的数据,此时事务已提交同时释放共享锁,回话2申请排他锁成功,对行执行更新

回话1的两次查询得到的结果一致,前面的两个隔离级别无法得到一致的数据,此时事务已提交同时释放共享锁,回话2申请排他锁成功,对行执行更新

澳门新萄京官方网站 55

澳门新萄京官方网站 56

数据库引擎会重做这些事务所做的所有修改,这个过程也成为前滚。

REPEATABLE READ隔离级别保证一个事务中的两次查询到的结果一致,同时保证了丢失更新
丢失更新:两个事务同时读取了同一个值然后基于最初的值进行计算,接着再更新,就会导致两个事务的更新相互覆盖。
例如酒店订房例子,两个人同时预定同一酒店的房间,首先两个人同时查询到还有一间房间可以预定,然后两个人同时提交预定操作,事务1执行number=1-0,同时事务2也执行number=1-0最后修改number=0,这就导致两个人其中一个人的操作被另一个人所覆盖,REPEATABLE READ隔离级别就能避免这种丢失更新的现象,当事务1查询房间时事务就一直保持共享锁直到事务提交,而不是像前面的几个隔离级别查询完就是否共享锁,就能避免其他事务获取排他锁。

REPEATABLE READ隔离级别保证一个事务中的两次查询到的结果一致,同时保证了丢失更新
丢失更新:两个事务同时读取了同一个值然后基于最初的值进行计算,接着再更新,就会导致两个事务的更新相互覆盖。
例如酒店订房例子,两个人同时预定同一酒店的房间,首先两个人同时查询到还有一间房间可以预定,然后两个人同时提交预定操作,事务1执行number=1-0,同时事务2也执行number=1-0最后修改number=0,这就导致两个人其中一个人的操作被另一个人所覆盖,REPEATABLE READ隔离级别就能避免这种丢失更新的现象,当事务1查询房间时事务就一直保持共享锁直到事务提交,而不是像前面的几个隔离级别查询完就是否共享锁,就能避免其他事务获取排他锁。

回话1的两次查询得到的结果一致,前面的两个隔离级别无法得到一致的数据,此时事务已提交同时释放共享锁,回话2申请排他锁成功,对行执行更新

回话1的两次查询得到的结果一致,前面的两个隔离级别无法得到一致的数据,此时事务已提交同时释放共享锁,回话2申请排他锁成功,对行执行更新

 

 4.SERIALIZABLE

 4.SERIALIZABLE

REPEATABLE READ隔离级别保证一个事务中的两次查询到的结果一致,同时保证了丢失更新
丢失更新:两个事务同时读取了同一个值然后基于最初的值进行计算,接着再更新,就会导致两个事务的更新相互覆盖。
例如酒店订房例子,两个人同时预定同一酒店的房间,首先两个人同时查询到还有一间房间可以预定,然后两个人同时提交预定操作,事务1执行number=1-0,同时事务2也执行number=1-0最后修改number=0,这就导致两个人其中一个人的操作被另一个人所覆盖,REPEATABLE READ隔离级别就能避免这种丢失更新的现象,当事务1查询房间时事务就一直保持共享锁直到事务提交,而不是像前面的几个隔离级别查询完就释放共享锁,就能避免其他事务获取排他锁。

REPEATABLE READ隔离级别保证一个事务中的两次查询到的结果一致,同时保证了丢失更新
丢失更新:两个事务同时读取了同一个值然后基于最初的值进行计算,接着再更新,就会导致两个事务的更新相互覆盖。
例如酒店订房例子,两个人同时预定同一酒店的房间,首先两个人同时查询到还有一间房间可以预定,然后两个人同时提交预定操作,事务1执行number=1-0,同时事务2也执行number=1-0最后修改number=0,这就导致两个人其中一个人的操作被另一个人所覆盖,REPEATABLE READ隔离级别就能避免这种丢失更新的现象,当事务1查询房间时事务就一直保持共享锁直到事务提交,而不是像前面的几个隔离级别查询完就释放共享锁,就能避免其他事务获取排他锁。

三   事务的隔离级别和隔离级别产生的一致性问题

SERIALIZABLE(可序列化),对于前面的REPEATABLE READ能保证事务可重复读,但是事务只锁定查询第一次运行时获取的数据资源(数据行),而不能锁定查询结果之外的行,就是原本不存在于数据表中的数据。因此在一个事务中当第一个查询和第二个查询过程之间,有其他事务执行插入操作且插入数据满足第一次查询读取过滤的条件时,那么在第二次查询的结果中就会存在这些新插入的数据,使两次查询结果不一致,这种读操作称之为幻读。
为了避免幻读需要将隔离级别设置为SERIALIZABLE

SERIALIZABLE(可序列化),对于前面的REPEATABLE READ能保证事务可重复读,但是事务只锁定查询第一次运行时获取的数据资源(数据行),而不能锁定查询结果之外的行,就是原本不存在于数据表中的数据。因此在一个事务中当第一个查询和第二个查询过程之间,有其他事务执行插入操作且插入数据满足第一次查询读取过滤的条件时,那么在第二次查询的结果中就会存在这些新插入的数据,使两次查询结果不一致,这种读操作称之为幻读。
为了避免幻读需要将隔离级别设置为SERIALIZABLE

 4.SERIALIZABLE

 4.SERIALIZABLE

1   未提交读(READ UNCOMMITTED)

澳门新萄京官方网站 57

澳门新萄京官方网站 58

SERIALIZABLE(可序列化),对于前面的REPEATABLE READ能保证事务可重复读,但是事务只锁定查询第一次运行时获取的数据资源(数据行),而不能锁定查询结果之外的行,就是原本不存在于数据表中的数据。因此在一个事务中当第一个查询和第二个查询过程之间,有其他事务执行插入操作且插入数据满足第一次查询读取过滤的条件时,那么在第二次查询的结果中就会存在这些新插入的数据,使两次查询结果不一致,这种读操作称之为幻读。
为了避免幻读需要将隔离级别设置为SERIALIZABLE

SERIALIZABLE(可序列化),对于前面的REPEATABLE READ能保证事务可重复读,但是事务只锁定查询第一次运行时获取的数据资源(数据行),而不能锁定查询结果之外的行,就是原本不存在于数据表中的数据。因此在一个事务中当第一个查询和第二个查询过程之间,有其他事务执行插入操作且插入数据满足第一次查询读取过滤的条件时,那么在第二次查询的结果中就会存在这些新插入的数据,使两次查询结果不一致,这种读操作称之为幻读。
为了避免幻读需要将隔离级别设置为SERIALIZABLE


IF OBJECT_ID('Orders','U') IS NOT NULL DROP TABLE Orders 
GO
CREATE TABLE Orders
(ID INT NOT NULL PRIMARY KEY,
Price FLOAT NOT NULL,
type INT NOT NULL
);
INSERT INTO Orders VALUES(10,10.00,1),(11,11.00,1),(12,12.00,1),(13,13.00,1),(14,14.00,1);
GO
IF OBJECT_ID('Orders','U') IS NOT NULL DROP TABLE Orders 
GO
CREATE TABLE Orders
(ID INT NOT NULL PRIMARY KEY,
Price FLOAT NOT NULL,
type INT NOT NULL
);
INSERT INTO Orders VALUES(10,10.00,1),(11,11.00,1),(12,12.00,1),(13,13.00,1),(14,14.00,1);
GO

澳门新萄京官方网站 59

澳门新萄京官方网站 60

 未提交读(READ UNCOMMITTED)指读取未提交的数据,此时产生的数据不一致性,我们称为数据脏读。

澳门新萄京官方网站 61

澳门新萄京官方网站 62

IF OBJECT_ID('Orders','U') IS NOT NULL DROP TABLE Orders 
GO
CREATE TABLE Orders
(ID INT NOT NULL PRIMARY KEY,
Price FLOAT NOT NULL,
type INT NOT NULL
);
INSERT INTO Orders VALUES(10,10.00,1),(11,11.00,1),(12,12.00,1),(13,13.00,1),(14,14.00,1);
GO
IF OBJECT_ID('Orders','U') IS NOT NULL DROP TABLE Orders 
GO
CREATE TABLE Orders
(ID INT NOT NULL PRIMARY KEY,
Price FLOAT NOT NULL,
type INT NOT NULL
);
INSERT INTO Orders VALUES(10,10.00,1),(11,11.00,1),(12,12.00,1),(13,13.00,1),(14,14.00,1);
GO

1.1   未提交读为什么会产生数据脏读

在回话1中执行查询操作,并将事务隔离级别设置为REPEATABLE READ(先测试一下前面更低级别的隔离)

在回话1中执行查询操作,并将事务隔离级别设置为REPEATABLE READ(先测试一下前面更低级别的隔离)

澳门新萄京官方网站 63

澳门新萄京官方网站 64

未提交读是最低级的隔离级别,在这个隔离级别运行的事务,读操作是不需要请求共享锁的,如果读操作不需要共享锁,就不会产生与持有排它锁的事务操作发生冲突,

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION 
SELECT ID,Price,type FROM Orders
WHERE TYPE=1
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION 
SELECT ID,Price,type FROM Orders
WHERE TYPE=1

在回话1中执行查询操作,并将事务隔离级别设置为REPEATABLE READ(先测试一下前面更低级别的隔离)

在回话1中执行查询操作,并将事务隔离级别设置为REPEATABLE READ(先测试一下前面更低级别的隔离)

那么也就是说,在这个事务隔离级别,读操作可以与写操作同时进行,互不排斥,读操作可以读取写操作未提交的修改,从而造成数据的不一致性,这种情况,我们称

澳门新萄京官方网站 65

澳门新萄京官方网站 66

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION 
SELECT ID,Price,type FROM Orders
WHERE TYPE=1
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION 
SELECT ID,Price,type FROM Orders
WHERE TYPE=1

数据脏读。

在回话2中执行插入操作

在回话2中执行插入操作

澳门新萄京官方网站 67

澳门新萄京官方网站 68

1.2   图解数据脏读

INSERT INTO Orders VALUES(15,15.00,1)
INSERT INTO Orders VALUES(15,15.00,1)

在回话2中执行插入操作

在回话2中执行插入操作

 澳门新萄京官方网站 69

返回回话1重新执行查询操作并提交事务

返回回话1重新执行查询操作并提交事务

INSERT INTO Orders VALUES(15,15.00,1)
INSERT INTO Orders VALUES(15,15.00,1)

1.3   SQL演示数据脏读

SELECT ID,Price,type FROM Orders
WHERE TYPE=1
COMMIT TRANSACTION
SELECT ID,Price,type FROM Orders
WHERE TYPE=1
COMMIT TRANSACTION

返回回话1重新执行查询操作并提交事务

返回回话1重新执行查询操作并提交事务

 澳门新萄京官方网站 70

澳门新萄京官方网站 71

澳门新萄京官方网站 72

SELECT ID,Price,type FROM Orders
WHERE TYPE=1
COMMIT TRANSACTION
SELECT ID,Price,type FROM Orders
WHERE TYPE=1
COMMIT TRANSACTION

 

结果回话1中第二次查询到的数据包含了回话2新插入的数据,两次查询结果不一致(验证之前的隔离级别不能保证幻读)

结果回话1中第二次查询到的数据包含了回话2新插入的数据,两次查询结果不一致(验证之前的隔离级别不能保证幻读)

澳门新萄京官方网站 73

澳门新萄京官方网站 74

2   已提交读(READ COMMITTED)

重新插入测试数据

重新插入测试数据

结果回话1中第二次查询到的数据包含了回话2新插入的数据,两次查询结果不一致(验证之前的隔离级别不能保证幻读)

结果回话1中第二次查询到的数据包含了回话2新插入的数据,两次查询结果不一致(验证之前的隔离级别不能保证幻读)


澳门新萄京官方网站 75

澳门新萄京官方网站 76

重新插入测试数据

重新插入测试数据

 已提交读(READ COMMITTED)指只能读取已提交事务的数据,是防止数据脏读的最低隔离级别,也是SQL Server默认的隔离级别,它要求读操作必须获得共享锁后

IF OBJECT_ID('Orders','U') IS NOT NULL DROP TABLE Orders 
GO
CREATE TABLE Orders
(ID INT NOT NULL PRIMARY KEY,
Price FLOAT NOT NULL,
type INT NOT NULL
);
INSERT INTO Orders VALUES(10,10.00,1),(11,11.00,1),(12,12.00,1),(13,13.00,1),(14,14.00,1);
GO
IF OBJECT_ID('Orders','U') IS NOT NULL DROP TABLE Orders 
GO
CREATE TABLE Orders
(ID INT NOT NULL PRIMARY KEY,
Price FLOAT NOT NULL,
type INT NOT NULL
);
INSERT INTO Orders VALUES(10,10.00,1),(11,11.00,1),(12,12.00,1),(13,13.00,1),(14,14.00,1);
GO

澳门新萄京官方网站 77

澳门新萄京官方网站 78

才能进行操作,防止读取未提交的修改,虽然已提交读能防止产生数据脏读,但却不可避免不可重复读数据一致性问题。

澳门新萄京官方网站 79

澳门新萄京官方网站 80

IF OBJECT_ID('Orders','U') IS NOT NULL DROP TABLE Orders 
GO
CREATE TABLE Orders
(ID INT NOT NULL PRIMARY KEY,
Price FLOAT NOT NULL,
type INT NOT NULL
);
INSERT INTO Orders VALUES(10,10.00,1),(11,11.00,1),(12,12.00,1),(13,13.00,1),(14,14.00,1);
GO
IF OBJECT_ID('Orders','U') IS NOT NULL DROP TABLE Orders 
GO
CREATE TABLE Orders
(ID INT NOT NULL PRIMARY KEY,
Price FLOAT NOT NULL,
type INT NOT NULL
);
INSERT INTO Orders VALUES(10,10.00,1),(11,11.00,1),(12,12.00,1),(13,13.00,1),(14,14.00,1);
GO

2.1   为什么已提交读能够防止数据脏读

接下来将回话级别设置为SERIALIZABLE,在回话1中执行查询操作,并将事务隔离级别设置为SERIALIZABLE

接下来将回话级别设置为SERIALIZABLE,在回话1中执行查询操作,并将事务隔离级别设置为SERIALIZABLE

澳门新萄京官方网站 81

澳门新萄京官方网站 82

已提交读只允许读取事务已提交的数据,它要求读操作必须获得共享锁才能尽心操作,而读操作的共享锁与写操作的排他锁是互斥的,两者互斥会发生冲突,所以读操作

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION 
SELECT ID,Price,type FROM Orders
WHERE TYPE=1
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION 
SELECT ID,Price,type FROM Orders
WHERE TYPE=1

接下来将回话级别设置为SERIALIZABLE,在回话1中执行查询操作,并将事务隔离级别设置为SERIALIZABLE

接下来将回话级别设置为SERIALIZABLE,在回话1中执行查询操作,并将事务隔离级别设置为SERIALIZABLE

在读取数据时,必须等待写操作完成后,才能获取共享锁,然后才能读取数据,此时读取的数据是已经提交结束的数据,因此就防止了数据脏读的问题。

澳门新萄京官方网站 83

澳门新萄京官方网站 84

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION 
SELECT ID,Price,type FROM Orders
WHERE TYPE=1
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION 
SELECT ID,Price,type FROM Orders
WHERE TYPE=1

2.2   SQL演示已提交读

在回话2中执行插入操作

在回话2中执行插入操作

澳门新萄京官方网站 85

澳门新萄京官方网站 86

澳门新萄京官方网站 87

INSERT INTO Orders VALUES(15,15.00,1)
INSERT INTO Orders VALUES(15,15.00,1)

在回话2中执行插入操作

在回话2中执行插入操作

2.3   为什么已提交读会产生不可重复读问题

返回回话1重新执行查询操作并提交事务

返回回话1重新执行查询操作并提交事务

INSERT INTO Orders VALUES(15,15.00,1)
INSERT INTO Orders VALUES(15,15.00,1)

我们知道,虽然已提交读能获得共享锁,然而,读操作一完成,就会立即释放资源上的共享锁(该操作不会在事务持续期间一致保留共享锁),如此就会产生一个问题,

SELECT ID,Price,type FROM Orders
WHERE TYPE=1
COMMIT TRANSACTION
SELECT ID,Price,type FROM Orders
WHERE TYPE=1
COMMIT TRANSACTION

返回回话1重新执行查询操作并提交事务

返回回话1重新执行查询操作并提交事务

即在一个事务处理内部对相同数据资源读操作之间,没有共享锁会锁定该资源,导致其他事务可以在两个读操作之间更改数据资源,读操作因而可能每次得到不同的

澳门新萄京官方网站 88

澳门新萄京官方网站 89

SELECT ID,Price,type FROM Orders
WHERE TYPE=1
COMMIT TRANSACTION
SELECT ID,Price,type FROM Orders
WHERE TYPE=1
COMMIT TRANSACTION

取值,这种现象称为数据的不可重复读。

两次执行的查询结果相同

两次执行的查询结果相同

澳门新萄京官方网站 90

澳门新萄京官方网站 91

2.4   图解不可重复读

 

 

两次执行的查询结果相同

两次执行的查询结果相同

澳门新萄京官方网站 92

重置所有打开回话的默认隔离级别

重置所有打开回话的默认隔离级别

 

 

3  可重复读(REPEATABLE READ)

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

重置所有打开回话的默认隔离级别

重置所有打开回话的默认隔离级别


5.SNAPSHOT

5.SNAPSHOT

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

 为了防止不可重复读现象,SQL Sever中采用隔离级别升级的方式,即将已提交读升级为可重复读。在可重复读隔离级别下,事务中的读操作不仅能获得共享锁,

SNAPSHOT快照:SNAPSHOT和READ COMMITTED SNAPSHOT两种隔离(可以把事务已经提交的行的上一版本保存在TEMPDB数据库中)
SNAPSHOT隔离级别在逻辑上与SERIALIZABLE类似
READ COMMITTED SNAPSHOT隔离级别在逻辑上与 READ COMMITTED类似
不过在快照隔离级别下读操作不需要申请获得共享锁,所以即便是数据已经存在排他锁也不影响读操作。而且仍然可以得到和SERIALIZABLE与READ COMMITTED隔离级别类似的一致性;如果目前版本与预期的版本不一致,读操作可以从TEMPDB中获取预期的版本。

SNAPSHOT快照:SNAPSHOT和READ COMMITTED SNAPSHOT两种隔离(可以把事务已经提交的行的上一版本保存在TEMPDB数据库中)
SNAPSHOT隔离级别在逻辑上与SERIALIZABLE类似
READ COMMITTED SNAPSHOT隔离级别在逻辑上与 READ COMMITTED类似
不过在快照隔离级别下读操作不需要申请获得共享锁,所以即便是数据已经存在排他锁也不影响读操作。而且仍然可以得到和SERIALIZABLE与READ COMMITTED隔离级别类似的一致性;如果目前版本与预期的版本不一致,读操作可以从TEMPDB中获取预期的版本。

5.SNAPSHOT

5.SNAPSHOT

而且获得的共享锁一直保持到事务完成为止, 在该事务完成之前,其他事务不可能获得排他锁来修改这一数据,如此,便实现了可重复读,防止了不可重复读造

如果启用任何一种基于快照的隔离级别,DELETE和UPDATE语句在做出修改前都会把行的当前版本复制到TEMPDB中,而INSERT语句不需要在TEMPDB中进行版本控制,因为此时还没有行的旧数据

如果启用任何一种基于快照的隔离级别,DELETE和UPDATE语句在做出修改前都会把行的当前版本复制到TEMPDB中,而INSERT语句不需要在TEMPDB中进行版本控制,因为此时还没有行的旧数据

SNAPSHOT快照:SNAPSHOT和READ COMMITTED SNAPSHOT两种隔离(可以把事务已经提交的行的上一版本保存在TEMPDB数据库中)
SNAPSHOT隔离级别在逻辑上与SERIALIZABLE类似
READ COMMITTED SNAPSHOT隔离级别在逻辑上与 READ COMMITTED类似
不过在快照隔离级别下读操作不需要申请获得共享锁,所以即便是数据已经存在排他锁也不影响读操作。而且仍然可以得到和SERIALIZABLE与READ COMMITTED隔离级别类似的一致性;如果目前版本与预期的版本不一致,读操作可以从TEMPDB中获取预期的版本。

SNAPSHOT快照:SNAPSHOT和READ COMMITTED SNAPSHOT两种隔离(可以把事务已经提交的行的上一版本保存在TEMPDB数据库中)
SNAPSHOT隔离级别在逻辑上与SERIALIZABLE类似
READ COMMITTED SNAPSHOT隔离级别在逻辑上与 READ COMMITTED类似
不过在快照隔离级别下读操作不需要申请获得共享锁,所以即便是数据已经存在排他锁也不影响读操作。而且仍然可以得到和SERIALIZABLE与READ COMMITTED隔离级别类似的一致性;如果目前版本与预期的版本不一致,读操作可以从TEMPDB中获取预期的版本。

成的数据不一致性。可重复读不仅能解决不可重复读数据不一致性问题,还能解决丢失更新问题。然而,可重复读也存在问题,那就是死锁和幻读等问题。

无论启用哪种基于快照的隔离级别都会对更新和删除操作产生性能的负面影响,但是有利于提高读操作的性能因为读操作不需要获取共享锁;

无论启用哪种基于快照的隔离级别都会对更新和删除操作产生性能的负面影响,但是有利于提高读操作的性能因为读操作不需要获取共享锁;

如果启用任何一种基于快照的隔离级别,DELETE和UPDATE语句在做出修改前都会把行的当前版本复制到TEMPDB中,而INSERT语句不需要在TEMPDB中进行版本控制,因为此时还没有行的旧数据

如果启用任何一种基于快照的隔离级别,DELETE和UPDATE语句在做出修改前都会把行的当前版本复制到TEMPDB中,而INSERT语句不需要在TEMPDB中进行版本控制,因为此时还没有行的旧数据

3.1 SQL演示可重复读

5.1SNAPSHOT

5.1SNAPSHOT

无论启用哪种基于快照的隔离级别都会对更新和删除操作产生性能的负面影响,但是有利于提高读操作的性能因为读操作不需要获取共享锁;

无论启用哪种基于快照的隔离级别都会对更新和删除操作产生性能的负面影响,但是有利于提高读操作的性能因为读操作不需要获取共享锁;

 澳门新萄京官方网站 93

SNAPSHOT 在SNAPSHOT隔离级别下,当读取数据时可以保证操作读取的行是事务开始时可用的最后提交版本
同时SNAPSHOT隔离级别也满足前面的已提交读,可重复读,不幻读;该隔离级别实用的不是共享锁,而是行版本控制
使用SNAPSHOT隔离级别首先需要在数据库级别上设置相关选项

SNAPSHOT 在SNAPSHOT隔离级别下,当读取数据时可以保证操作读取的行是事务开始时可用的最后提交版本
同时SNAPSHOT隔离级别也满足前面的已提交读,可重复读,不幻读;该隔离级别实用的不是共享锁,而是行版本控制
使用SNAPSHOT隔离级别首先需要在数据库级别上设置相关选项

5.1SNAPSHOT

5.1SNAPSHOT

3.2  何为丢失更新?

在打开的所有查询窗口中执行以下操作

在打开的所有查询窗口中执行以下操作

SNAPSHOT 在SNAPSHOT隔离级别下,当读取数据时可以保证操作读取的行是事务开始时可用的最后提交版本
同时SNAPSHOT隔离级别也满足前面的已提交读,可重复读,不幻读;该隔离级别实用的不是共享锁,而是行版本控制
使用SNAPSHOT隔离级别首先需要在数据库级别上设置相关选项

SNAPSHOT 在SNAPSHOT隔离级别下,当读取数据时可以保证操作读取的行是事务开始时可用的最后提交版本
同时SNAPSHOT隔离级别也满足前面的已提交读,可重复读,不幻读;该隔离级别实用的不是共享锁,而是行版本控制
使用SNAPSHOT隔离级别首先需要在数据库级别上设置相关选项

在比可重复读低的隔离级别中,两个事务在读取数据之后就不再持有该资源的任何锁,此时,两个事务都能更新这个值,

ALTER DATABASE TEST SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE TEST SET ALLOW_SNAPSHOT_ISOLATION ON;

在打开的所有查询窗口中执行以下操作

在打开的所有查询窗口中执行以下操作

从而发生最后事务更新的值覆盖前面事务更新的值,从而造成数据的丢失,这称为丢失更新。

重置测试数据

重置测试数据

ALTER DATABASE TEST SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE TEST SET ALLOW_SNAPSHOT_ISOLATION ON;

3.3  图解丢失更新

澳门新萄京官方网站 94

澳门新萄京官方网站 95

重置测试数据

重置测试数据

澳门新萄京官方网站 96

IF OBJECT_ID('Orders','U') IS NOT NULL DROP TABLE Orders 
GO
CREATE TABLE Orders
(ID INT NOT NULL PRIMARY KEY,
Price FLOAT NOT NULL,
type INT NOT NULL
);
INSERT INTO Orders VALUES(10,10.00,1),(11,11.00,1),(12,12.00,1),(13,13.00,1),(14,14.00,1);
GO
IF OBJECT_ID('Orders','U') IS NOT NULL DROP TABLE Orders 
GO
CREATE TABLE Orders
(ID INT NOT NULL PRIMARY KEY,
Price FLOAT NOT NULL,
type INT NOT NULL
);
INSERT INTO Orders VALUES(10,10.00,1),(11,11.00,1),(12,12.00,1),(13,13.00,1),(14,14.00,1);
GO

澳门新萄京官方网站 97

澳门新萄京官方网站 98

4   可序列化(SERIALIZABLE)

澳门新萄京官方网站 99

澳门新萄京官方网站 100

IF OBJECT_ID('Orders','U') IS NOT NULL DROP TABLE Orders 
GO
CREATE TABLE Orders
(ID INT NOT NULL PRIMARY KEY,
Price FLOAT NOT NULL,
type INT NOT NULL
);
INSERT INTO Orders VALUES(10,10.00,1),(11,11.00,1),(12,12.00,1),(13,13.00,1),(14,14.00,1);
GO
IF OBJECT_ID('Orders','U') IS NOT NULL DROP TABLE Orders 
GO
CREATE TABLE Orders
(ID INT NOT NULL PRIMARY KEY,
Price FLOAT NOT NULL,
type INT NOT NULL
);
INSERT INTO Orders VALUES(10,10.00,1),(11,11.00,1),(12,12.00,1),(13,13.00,1),(14,14.00,1);
GO

澳门新萄京官方网站 101

澳门新萄京官方网站 102

澳门新萄京官方网站 103

澳门新萄京官方网站 104

4.1   何为幻读?

在回话1中打开事务,将订单10的价格加1,并查询跟新后的价格
BEGIN TRANSACTION
UPDATE Orders 
SET Price=Price 1
WHERE ID=10

SELECT ID,Price,type FROM Orders
WHERE ID=10
---查询到更新后的价格为11

---在回话2中将隔离级别设置为SNAPSHOT,并打开事务(此时查询也不会因为回话1的排他锁而等待,依然可以查询到数据)
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRANSACTION
SELECT ID,Price,type FROM Orders
WHERE ID=10

---查询到的结果还是回话1修改前的价格,由于回话1在默认的READ COMMITTED隔离级别下运行,SQL SERVER必须在更新前把行的一个副本复制到TEMPDB数据库中
--在SNAPSHOT级别启动事务会请求行版本

---现在在回话1中执行提交事务,此时订单10的价格为11
COMMIT TRANSACTION

---再次在回话二中查询订单10的价格并提交事务,结果还是10,因为事务要保证两次查询的结果相同

SELECT ID,Price,type FROM Orders
WHERE ID=10

COMMIT TRANSACTION

---此时如果在回话2中重新打开一个事务,查询到的订单10的价格则是11
BEGIN TRANSACTION
SELECT ID,Price,type FROM Orders
WHERE ID=10

COMMIT TRANSACTION

/*SNAPSHOT隔离级别保证操作读取的行是事务开始时可用的最后已提交版本,由于回话1的事务未提交,所以订单10的最后提交版本还是修改前的价格10,所以回话2读取到的价格是回话2事务开始前的已提交版本价格10,当回话1提交事务后,回话2重新新建一个事务此时事务开启前的价格已经是11了,所以查询到的价格是11,同时SNAPSHOT隔离级别还能保证SERIALIZABLE的隔离级别*/
在回话1中打开事务,将订单10的价格加1,并查询跟新后的价格
BEGIN TRANSACTION
UPDATE Orders 
SET Price=Price 1
WHERE ID=10

SELECT ID,Price,type FROM Orders
WHERE ID=10
---查询到更新后的价格为11

---在回话2中将隔离级别设置为SNAPSHOT,并打开事务(此时查询也不会因为回话1的排他锁而等待,依然可以查询到数据)
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRANSACTION
SELECT ID,Price,type FROM Orders
WHERE ID=10

---查询到的结果还是回话1修改前的价格,由于回话1在默认的READ COMMITTED隔离级别下运行,SQL SERVER必须在更新前把行的一个副本复制到TEMPDB数据库中
--在SNAPSHOT级别启动事务会请求行版本

---现在在回话1中执行提交事务,此时订单10的价格为11
COMMIT TRANSACTION

---再次在回话二中查询订单10的价格并提交事务,结果还是10,因为事务要保证两次查询的结果相同

SELECT ID,Price,type FROM Orders
WHERE ID=10

COMMIT TRANSACTION

---此时如果在回话2中重新打开一个事务,查询到的订单10的价格则是11
BEGIN TRANSACTION
SELECT ID,Price,type FROM Orders
WHERE ID=10

COMMIT TRANSACTION

/*SNAPSHOT隔离级别保证操作读取的行是事务开始时可用的最后已提交版本,由于回话1的事务未提交,所以订单10的最后提交版本还是修改前的价格10,所以回话2读取到的价格是回话2事务开始前的已提交版本价格10,当回话1提交事务后,回话2重新新建一个事务此时事务开启前的价格已经是11了,所以查询到的价格是11,同时SNAPSHOT隔离级别还能保证SERIALIZABLE的隔离级别*/

澳门新萄京官方网站 105

澳门新萄京官方网站 106

我们知道,在可重复读隔离级别下,读事务持有的共享锁一直保持到该事务完成为止,但是事务只锁定查询第一次运行时找到的那些数据资源(如,行),

澳门新萄京官方网站 107

澳门新萄京官方网站 108

在回话1中打开事务,将订单10的价格加1,并查询跟新后的价格
BEGIN TRANSACTION
UPDATE Orders 
SET Price=Price 1
WHERE ID=10

SELECT ID,Price,type FROM Orders
WHERE ID=10
---查询到更新后的价格为11

---在回话2中将隔离级别设置为SNAPSHOT,并打开事务(此时查询也不会因为回话1的排他锁而等待,依然可以查询到数据)
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRANSACTION
SELECT ID,Price,type FROM Orders
WHERE ID=10

---查询到的结果还是回话1修改前的价格,由于回话1在默认的READ COMMITTED隔离级别下运行,SQL SERVER必须在更新前把行的一个副本复制到TEMPDB数据库中
--在SNAPSHOT级别启动事务会请求行版本

---现在在回话1中执行提交事务,此时订单10的价格为11
COMMIT TRANSACTION

---再次在回话二中查询订单10的价格并提交事务,结果还是10,因为事务要保证两次查询的结果相同

SELECT ID,Price,type FROM Orders
WHERE ID=10

COMMIT TRANSACTION

---此时如果在回话2中重新打开一个事务,查询到的订单10的价格则是11
BEGIN TRANSACTION
SELECT ID,Price,type FROM Orders
WHERE ID=10

COMMIT TRANSACTION

/*SNAPSHOT隔离级别保证操作读取的行是事务开始时可用的最后已提交版本,由于回话1的事务未提交,所以订单10的最后提交版本还是修改前的价格10,所以回话2读取到的价格是回话2事务开始前的已提交版本价格10,当回话1提交事务后,回话2重新新建一个事务此时事务开启前的价格已经是11了,所以查询到的价格是11,同时SNAPSHOT隔离级别还能保证SERIALIZABLE的隔离级别*/
在回话1中打开事务,将订单10的价格加1,并查询跟新后的价格
BEGIN TRANSACTION
UPDATE Orders 
SET Price=Price 1
WHERE ID=10

SELECT ID,Price,type FROM Orders
WHERE ID=10
---查询到更新后的价格为11

---在回话2中将隔离级别设置为SNAPSHOT,并打开事务(此时查询也不会因为回话1的排他锁而等待,依然可以查询到数据)
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRANSACTION
SELECT ID,Price,type FROM Orders
WHERE ID=10

---查询到的结果还是回话1修改前的价格,由于回话1在默认的READ COMMITTED隔离级别下运行,SQL SERVER必须在更新前把行的一个副本复制到TEMPDB数据库中
--在SNAPSHOT级别启动事务会请求行版本

---现在在回话1中执行提交事务,此时订单10的价格为11
COMMIT TRANSACTION

---再次在回话二中查询订单10的价格并提交事务,结果还是10,因为事务要保证两次查询的结果相同

SELECT ID,Price,type FROM Orders
WHERE ID=10

COMMIT TRANSACTION

---此时如果在回话2中重新打开一个事务,查询到的订单10的价格则是11
BEGIN TRANSACTION
SELECT ID,Price,type FROM Orders
WHERE ID=10

COMMIT TRANSACTION

/*SNAPSHOT隔离级别保证操作读取的行是事务开始时可用的最后已提交版本,由于回话1的事务未提交,所以订单10的最后提交版本还是修改前的价格10,所以回话2读取到的价格是回话2事务开始前的已提交版本价格10,当回话1提交事务后,回话2重新新建一个事务此时事务开启前的价格已经是11了,所以查询到的价格是11,同时SNAPSHOT隔离级别还能保证SERIALIZABLE的隔离级别*/

而不会锁定查询结果范围以外的其他行(其实,控制事务时,有数据库架构级别,表,页和行等)。因此,在同一事务中进行第二次读取之前,若其他事

5.2READ COMMITTED SNAPSHOT

5.2READ COMMITTED SNAPSHOT

澳门新萄京官方网站 109

澳门新萄京官方网站 110

务插入新行,并且新行能满足读操作的查询过滤条件,那么这些新行也会出现在第二次读操作返回的结果中,这些新行称为幻影子,也叫做幻读。

READ COMMITTED SNAPSHOT也是基于行版本控制,但是READ COMMITTED SNAPSHOT的隔离级别是读操作之前的最后已提交版本,而不是事务前的已提交版本,有点类似前面的READ COMMITTED能保证已提交读,但是不能保证可重复读,不能避免幻读,但是又比 READ COMMITTED隔离级别多出了不需要获取共享锁就可以读取数据

READ COMMITTED SNAPSHOT也是基于行版本控制,但是READ COMMITTED SNAPSHOT的隔离级别是读操作之前的最后已提交版本,而不是事务前的已提交版本,有点类似前面的READ COMMITTED能保证已提交读,但是不能保证可重复读,不能避免幻读,但是又比 READ COMMITTED隔离级别多出了不需要获取共享锁就可以读取数据

5.2READ COMMITTED SNAPSHOT

5.2READ COMMITTED SNAPSHOT

4.2  图解幻读

要启用READ COMMITTED SNAPSHOT隔离级别同样需要修改数据库选项,在回话1,回话2中执行以下操作(执行下面的操作当前连接必须是数据库的唯一连接,可以通过查询已连接当前数据库的进程,然后KILL掉那些进程,然后再执行该操作,否则可能无法执行成功)

要启用READ COMMITTED SNAPSHOT隔离级别同样需要修改数据库选项,在回话1,回话2中执行以下操作(执行下面的操作当前连接必须是数据库的唯一连接,可以通过查询已连接当前数据库的进程,然后KILL掉那些进程,然后再执行该操作,否则可能无法执行成功)

READ COMMITTED SNAPSHOT也是基于行版本控制,但是READ COMMITTED SNAPSHOT的隔离级别是读操作之前的最后已提交版本,而不是事务前的已提交版本,有点类似前面的READ COMMITTED能保证已提交读,但是不能保证可重复读,不能避免幻读,但是又比 READ COMMITTED隔离级别多出了不需要获取共享锁就可以读取数据

READ COMMITTED SNAPSHOT也是基于行版本控制,但是READ COMMITTED SNAPSHOT的隔离级别是读操作之前的最后已提交版本,而不是事务前的已提交版本,有点类似前面的READ COMMITTED能保证已提交读,但是不能保证可重复读,不能避免幻读,但是又比 READ COMMITTED隔离级别多出了不需要获取共享锁就可以读取数据

澳门新萄京官方网站 111

澳门新萄京官方网站 112

澳门新萄京官方网站 113

要启用READ COMMITTED SNAPSHOT隔离级别同样需要修改数据库选项,在回话1,回话2中执行以下操作(执行下面的操作当前连接必须是数据库的唯一连接,可以通过查询已连接当前数据库的进程,然后KILL掉那些进程,然后再执行该操作,否则可能无法执行成功)

要启用READ COMMITTED SNAPSHOT隔离级别同样需要修改数据库选项,在回话1,回话2中执行以下操作(执行下面的操作当前连接必须是数据库的唯一连接,可以通过查询已连接当前数据库的进程,然后KILL掉那些进程,然后再执行该操作,否则可能无法执行成功)

 4.3  如何解决幻读?

ALTER DATABASE TEST SET READ_COMMITTED_SNAPSHOT ON

IF OBJECT_ID('Orders','U') IS NOT NULL DROP TABLE Orders 
GO
CREATE TABLE Orders
(ID INT NOT NULL PRIMARY KEY,
Price FLOAT NOT NULL,
type INT NOT NULL
);
INSERT INTO Orders VALUES(10,10.00,1),(11,11.00,1),(12,12.00,1),(13,13.00,1),(14,14.00,1);
GO

-----在回话1中打开事务,将订单10的价格加1,并查询跟新后的价格,并保持事务一直处于打开状态
BEGIN TRANSACTION
UPDATE Orders 
SET Price=Price 1
WHERE ID=10

--查询到的价格是11
SELECT ID,Price,type FROM Orders
WHERE ID=10

---在回话2中打开事务查询订单10并一直保持事务处于打开状态(此时由于回话1还未提交事务,所以回话2中查询到的还是回话1执行事务之前保存的行版本)
BEGIN TRANSACTION
SELECT ID,Price,type FROM Orders
WHERE ID=10
--查询到的价格还是10

---在回话1中提交事务
COMMIT TRANSACTION 

---在回话2中再次执行查询订单10的价格,并提交事务
SELECT ID,Price,type FROM Orders
WHERE ID=10
COMMIT TRANSACTION 
--此时的价格为回话1修改后的价格11,而不是事务之前已提交版本的价格,也就是READ COMMITTED SNAPSHOT隔离级别在同一事务中两次查询的结果不一致.
ALTER DATABASE TEST SET READ_COMMITTED_SNAPSHOT ON

IF OBJECT_ID('Orders','U') IS NOT NULL DROP TABLE Orders 
GO
CREATE TABLE Orders
(ID INT NOT NULL PRIMARY KEY,
Price FLOAT NOT NULL,
type INT NOT NULL
);
INSERT INTO Orders VALUES(10,10.00,1),(11,11.00,1),(12,12.00,1),(13,13.00,1),(14,14.00,1);
GO

-----在回话1中打开事务,将订单10的价格加1,并查询跟新后的价格,并保持事务一直处于打开状态
BEGIN TRANSACTION
UPDATE Orders 
SET Price=Price 1
WHERE ID=10

--查询到的价格是11
SELECT ID,Price,type FROM Orders
WHERE ID=10

---在回话2中打开事务查询订单10并一直保持事务处于打开状态(此时由于回话1还未提交事务,所以回话2中查询到的还是回话1执行事务之前保存的行版本)
BEGIN TRANSACTION
SELECT ID,Price,type FROM Orders
WHERE ID=10
--查询到的价格还是10

---在回话1中提交事务
COMMIT TRANSACTION 

---在回话2中再次执行查询订单10的价格,并提交事务
SELECT ID,Price,type FROM Orders
WHERE ID=10
COMMIT TRANSACTION 
--此时的价格为回话1修改后的价格11,而不是事务之前已提交版本的价格,也就是READ COMMITTED SNAPSHOT隔离级别在同一事务中两次查询的结果不一致.

澳门新萄京官方网站 114

澳门新萄京官方网站 115

SQL SERVER中,更高级别的可序列化(SERIALIZABLE)能够解决该问题。

澳门新萄京官方网站 116

澳门新萄京官方网站 117

ALTER DATABASE TEST SET READ_COMMITTED_SNAPSHOT ON

IF OBJECT_ID('Orders','U') IS NOT NULL DROP TABLE Orders 
GO
CREATE TABLE Orders
(ID INT NOT NULL PRIMARY KEY,
Price FLOAT NOT NULL,
type INT NOT NULL
);
INSERT INTO Orders VALUES(10,10.00,1),(11,11.00,1),(12,12.00,1),(13,13.00,1),(14,14.00,1);
GO

-----在回话1中打开事务,将订单10的价格加1,并查询跟新后的价格,并保持事务一直处于打开状态
BEGIN TRANSACTION
UPDATE Orders 
SET Price=Price 1
WHERE ID=10

--查询到的价格是11
SELECT ID,Price,type FROM Orders
WHERE ID=10

---在回话2中打开事务查询订单10并一直保持事务处于打开状态(此时由于回话1还未提交事务,所以回话2中查询到的还是回话1执行事务之前保存的行版本)
BEGIN TRANSACTION
SELECT ID,Price,type FROM Orders
WHERE ID=10
--查询到的价格还是10

---在回话1中提交事务
COMMIT TRANSACTION 

---在回话2中再次执行查询订单10的价格,并提交事务
SELECT ID,Price,type FROM Orders
WHERE ID=10
COMMIT TRANSACTION 
--此时的价格为回话1修改后的价格11,而不是事务之前已提交版本的价格,也就是READ COMMITTED SNAPSHOT隔离级别在同一事务中两次查询的结果不一致.
ALTER DATABASE TEST SET READ_COMMITTED_SNAPSHOT ON

IF OBJECT_ID('Orders','U') IS NOT NULL DROP TABLE Orders 
GO
CREATE TABLE Orders
(ID INT NOT NULL PRIMARY KEY,
Price FLOAT NOT NULL,
type INT NOT NULL
);
INSERT INTO Orders VALUES(10,10.00,1),(11,11.00,1),(12,12.00,1),(13,13.00,1),(14,14.00,1);
GO

-----在回话1中打开事务,将订单10的价格加1,并查询跟新后的价格,并保持事务一直处于打开状态
BEGIN TRANSACTION
UPDATE Orders 
SET Price=Price 1
WHERE ID=10

--查询到的价格是11
SELECT ID,Price,type FROM Orders
WHERE ID=10

---在回话2中打开事务查询订单10并一直保持事务处于打开状态(此时由于回话1还未提交事务,所以回话2中查询到的还是回话1执行事务之前保存的行版本)
BEGIN TRANSACTION
SELECT ID,Price,type FROM Orders
WHERE ID=10
--查询到的价格还是10

---在回话1中提交事务
COMMIT TRANSACTION 

---在回话2中再次执行查询订单10的价格,并提交事务
SELECT ID,Price,type FROM Orders
WHERE ID=10
COMMIT TRANSACTION 
--此时的价格为回话1修改后的价格11,而不是事务之前已提交版本的价格,也就是READ COMMITTED SNAPSHOT隔离级别在同一事务中两次查询的结果不一致.

4.4  何为可序列化(SERIALIZABLE)?

关闭所有连接,然后打开一个新的连接,禁用之前设置的数据库快照隔离级别选项

关闭所有连接,然后打开一个新的连接,禁用之前设置的数据库快照隔离级别选项

澳门新萄京官方网站 118

澳门新萄京官方网站 119

大多数时候,可序列化(SERIALIZABLE)隔离级别的处理方式和可重复都得处理方式是类似的,只不过,可序列化(SERIALIZABLE)隔离级别

ALTER DATABASE TEST SET ALLOW_SNAPSHOT_ISOLATION OFF;

ALTER DATABASE TEST SET READ_COMMITTED_SNAPSHOT OFF;
ALTER DATABASE TEST SET ALLOW_SNAPSHOT_ISOLATION OFF;

ALTER DATABASE TEST SET READ_COMMITTED_SNAPSHOT OFF;

关闭所有连接,然后打开一个新的连接,禁用之前设置的数据库快照隔离级别选项

关闭所有连接,然后打开一个新的连接,禁用之前设置的数据库快照隔离级别选项

增加了一个新的内容——逻辑上,这个隔离级别会让读操作锁定满足查询搜索条件的键的整范围,这就意味着读操作不仅锁定了满足查询搜索

 

 

ALTER DATABASE TEST SET ALLOW_SNAPSHOT_ISOLATION OFF;

ALTER DATABASE TEST SET READ_COMMITTED_SNAPSHOT OFF;
ALTER DATABASE TEST SET ALLOW_SNAPSHOT_ISOLATION OFF;

ALTER DATABASE TEST SET READ_COMMITTED_SNAPSHOT OFF;

条件的现有的那些行,还锁定了未来可能满足查询搜索条件的行。

 

 

 

 

5   SNAPSHOT

总结

总结

总结

总结


   理解了事务隔离级别有助于理解事务的死锁。

   理解了事务隔离级别有助于理解事务的死锁。

   理解了事务隔离级别有助于理解事务的死锁。

   理解了事务隔离级别有助于理解事务的死锁。

 略。

 

 

四    事务的隔离级别总结

转自:

转自:

下表总结了每种隔离级别与逻辑一致性问题,检测冲突和行版本控制之间关系

澳门新萄京官方网站 120

 

五   锁定

1  两种并发控制模型


关于并发控制模型,主要有两种,即悲观控制模型和乐观控制模型。

澳门新萄京官方网站 121

(1)悲观控制模型: 该模型假设总是存在多个事务对同一资源操作(读/写),即假定冲突总是会发生。在SQL Server中,采用事务

隔离级别来控制(也可叫做采用锁来控制)。一般在事务发生冲突前进行控制,也叫事前控制;

(2)乐观控制模型:该模型与悲观控制模型是对立的,即该模型总是假设系统中并不存在或较少存在多个事务对同一资源操作(读/写)

,即假定冲突是不会发生的或很少发生的。在SQL Server中,采用行版本控制来处理。一般在事务发生冲突后进行控制,也叫事后

控制;

2 何为锁定及锁定的种类


2.1  何为锁定

锁定,指在并发操作时,确保数据的一致性所采用的一种手段。在SQL Server中,采用锁机制与事务隔离级别来控制数据的一致性,

2.2 锁定的种类

常用的四大类锁包括:共享锁,意向锁,更新锁和排他锁。

 澳门新萄京官方网站 122

(1)共享锁:在SQL SERVER中,当事务要读取数据时,需要获取共享锁。

(2)意向锁:在SQL SERVER中,准确来说,意向锁并不是一种独立的锁,其主要作用在于获取锁的控制粒度(如,页,表,行等)。

(3)更新锁:在SQL SERVER中,准确来说,更新锁并不是一种独立的锁,而是由共享锁和排它锁组成的混合锁,其隔离级别高于共享锁,

低于排他锁,更新锁能够预防锁升级而产生的死锁。

(4)排它锁:在SQL SERVER中,当事务要写数据、更细数据和删除数据时,需要获取排他锁。

3 锁的控制粒度


 在SQL SERVER中,锁可以控制表,页和行等资源。

澳门新萄京官方网站 123

 

六  参考文献

【01】Microsoft  SqlServer 2008技术内幕:T-SQL 语言基础

【02】Microsoft  SqlServer 2008技术内幕:T-SQL 查询

 

七 服务区

有喜欢的朋友,可以看一下,不喜欢的的朋友,勿喷,谢谢!!

澳门新萄京官方网站 124

 

八  版权区

  • 感谢您的阅读,若有不足之处,欢迎指教,共同学习、共同进步。
  • 博主网址:
  • 极少部分文章利用读书、参考、引用、抄袭、复制和粘贴等多种方式整合而成的,大部分为原创。
  • 如您喜欢,麻烦推荐一下;如您有新想法,欢迎提出,邮箱:2016177728@qq.com。
  • 可以转载该博客,但必须著名博客来源。

本文由澳门新萄京官方网站发布于数据库网络,转载请注明出处:Server事务与锁,事务隔离级别详解

关键词: