未加星标

SQLServer事务的隔离级别

字体大小 | |
[数据库(mssql) 所属分类 数据库(mssql) | 发布者 店小二05 | 时间 2016 | 作者 红领巾 ] 0人收藏点击收藏
传统隔离级别
隔离级别确定了并发用户读取或者写入的行为。读取者可以是任何选择数据的语句,默认情况下使用共享锁。写入者是任何对表进行修改的语句,并且需要一个排它锁。
SQL Server支持4个基于悲观并发控制(锁定)的传统隔离级别:READ UNCOMMITTED,READ COMMITTED, REPEATABLE READ与SERIALIZABLE。对于这4个隔离级别,隔离级别越高,读取者请求的锁就越强,并且持续时间越强。因此随着隔离级别的提高,一致性越高而并发性越低。
READ UNCOMMITTED
是可用的最低隔离级别。在该隔离级别中,读取者不需要请求共享锁。不要求共享锁的读者就不会与持有排它锁的写入者发生冲突,这意味着读取者可以读取未提交的更改(脏读)。同时也意味着读取者不会干扰排它锁的写入者,那么读取者在该隔离方式下读取数据时,写入者可以更改数据。
建立如下的连接1,并保持事务处于打开状态:
BEGIN TRAN;
UPDATE Production.Products SET unitprice += 1.00 WHERE productid = 2;
SELECT productid, unitprice FROM Production.Products WHERE productid = 2;

该事务拥有产品id为2的那个产品行的排他锁;最终显示:

productid unitprice
---------- ----------
2 20.00

那么执行连接2,设置隔离级别为READ UNCOMMITTED:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT productid, unitprice FROM Production.Products WHERE productid = 2;

显示结果如下:

productid unitprice
---------- ----------
2 20.00

尽管连接1中的事务还没有提交,但是连接2却显示已经更改但未提交的数据。

READ COMMITTED
这是默认的隔离级别,该隔离级别仅允许读取者读取已经提交的更改,那么读取者必须要获得一个共享锁来防止未提交的更改。当写入者拥有一个排它锁时,读取者的共享锁将与之冲突,此时必须等待事务结束后才能获得共享锁,从而读取数据。
如果修改上面的连接2:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT productid, unitprice FROM Production.Products WHERE productid = 2;

那么当连接1的事务没有提交时,该查询便会阻塞。但是读取者所获得共享锁的持续时间仅在与SELECT语句读取间,而不是在整个事务中都拥有共享锁。

REPEATABLE READ

如果希望确保在同一事务中的多次读取之间没有其他事务能够修改值,那么就要使用REPEATABLE READ隔离级别。此时,读取者不仅需要一个共享锁才能够读写,而且直到事务结束都持有锁。那么其它事务中任何尝试获得排它锁的写操作将在事务未提交之前被阻塞。这样确保了数据在整个事务中的一致性。

在连接1中设置REPEATABLE READ隔离级别:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRAN;
SELECT productid, unitprice FROM Production.Products WHERE productid = 2;

输出:

productid unitprice
---------- ----------
2 19.00

连接2如下:

UPDATE Production.Products SET unitprice += 1.00 WHERE productid = 2;

那么由于连接1事务未提交,连接2请求的排它锁与共享锁冲突,连接2中的update语句被阻塞。

REPEATABLE READ会造成丢失更新,比如两个事务同时读取一个值并尝试更新该值。由于在读取后双方都会保持它们的共享锁,那么对于两个事务中的任何一个更新都不会成功。从而造成死锁。

SERIALIZABLE
SERIALIZABLE这种隔离级别可以防止“幻读”。考虑这样的情况,事务锁定的行是在查询第一次运行时确定的,假如在事务进行第二次相同刷选条件下的查询时,其它事务添加了新行,而且新行位于查询筛选范围中,那么前后两次查询结果就不一致。从而产生“幻读”。将隔离级别设置为SERIALIZABLE级别,那么读取者锁定的行将包括查询筛选所限定的整个范围。这意味着读者锁定的不仅是查询筛选所限定的现有行,也包括将来的行。这样,其他事务尝试添加读取者查询筛选所限定的行将被阻塞。
连接1如下:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRAN;
SELECT productid, productname, categoryid, unitprice
FROM Production.Products WHERE categoryid = 1;

结果如下:

productid productname categoryid unitprice
---------- --------------- ---------- ----------
1 Product HHYDP 1 18.00
2 Product RECZE 1 19.00 ...
76 Product JYGFE 1 18.00

连接2如下:

INSERT INTO Production.Products(productname, supplierid, categoryid, unitprice, discontinued)
VALUES('Product ABCDE', 1, 1, 20.00, 0);

连接2的插入将被阻止。

基于行版本的隔离级别
SQL Server还支持两种基于乐观并发控制(行版本控制)的隔离级别:SNAPSHOT和READ COMMITTED SNAPSHOT。
这种隔离级别被称于基于行版本或者快照的隔离级别,SQL Server 能够在tempdb中存储之前提交的行版本。读取者不请求共享锁。
SNAPSHOT

在SNAPSHOT隔离级别下,读取者在读取数据时,它将确保获得事务启动时最近提交的可用行版本。这意味着,保证获得的是提交后的读取并且可以重复读取,以及确保获得的不是幻读。要允许事务可以以SNAPSHOT隔离级别工作,首先要在数据库级别允许此选项:

ALTER DATABASE dbname SET ALLOW_SNAPSHOT_ISOLATION ON;

在连接1中,我们运行READ COMMITTED隔离级别,更新产品2的价格,从19.00变为20.00:

BEGIN TRAN;
UPDATE Production.Products SET unitprice += 1.00 WHERE productid = 2;
SELECT productid, unitprice FROM Production.Products WHERE productid = 2;

结果显示:

productid unitprice
---------- ----------
2 20.00

连接2设置SNAPSHOT隔离级别:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN;
SELECT productid, unitprice FROM Production.Products WHERE productid = 2;

结果显示事务运行时可用的上次提交的行版本:

productid unitprice
---------- ----------
2 19.00

回到连接1,提交事务:

COMMIT TRAN;
提交事务后,价格为20.00的当期版本就变成了最新的提交版本。
在连接2中再次读取数据,并提交,此时显示的仍是19.00,因为事务还未提交,事务所保存的快照并不会发生改变:
productid unitprice
---------- ----------
2 19.00

在连接2中我们重新打开一个事务:

BEGIN TRAN;
SELECT productid, unitprice FROM Production.Products WHERE productid = 2;
COMMIT TRAN;

此时事务运行时的数据快照为20.00,故显示:

productid unitprice
---------- ----------
2 20.00
对于旧的快照版本,如果没有事务使用它们,那么清理线程会及时清除它们。
SNAPSHOT隔离级别不会产生死锁,但是可以防止更新冲突。通过储存的版本可以检测更新冲突,如果在一个事务的读取与写入之间另外一个事务修改了数据,那么这个事务将会失败。
加入连接1首先进行读数据:
BEGIN TRAN;
SELECT productid, unitprice FROM Production.Products WHERE productid = 2;

在连接2中更新数据:

BEGIN TRAN;
UPDATE Production.Products SET unitprice += 1.00 WHERE productid = 2;
COMMIT TRAN;

那么在连接1中重新更新数据,并提交事务:

UPDATE Production.Products SET unitprice = 21.00 WHERE productid = 2;
COMMIT TRAN;

那么这个事务会提交失败,因为检测到了更新冲突。错误信息如下:

消息 3960,级别 16,状态 2,第 1 行
快照隔离事务由于更新冲突而中止。您无法在数据库'AdventureWorks2012'中使用快照隔离来直接或间接访问表 'Production.Products',以便更新、删除或插入已由其他事务修改或删除的行。请重试该事务或更改 update/delete 语句的隔离级别。
READ COMMITED SNAPSHOT
READ COMMITED SNAPSHOT也是基于行版本控制的。但是它与SANPSHOT不同的是,读取者获得的是“语句”启动时可用的最后提交的行版本,而不是事务启动时可用的最后提交的行版本。这种隔离级别不会检测更新冲突,但是如果所请求资源以排它锁方式锁定时,它不会请求共享锁并且不会等待。
首先修改数据库设置,将READ COMMITED SNAPSHOT隔离级别打开:
ALTER DATABASE dbname SET READ_COMMITED_SNAPSHOT ON;
启动之后,隔离级别将隐式地更改为READ COMMITED SNAPSHOT
在连接1运行如下事务:
BEGIN TRAN;
UPDATE Production.Products SET unitprice += 1.00 WHERE productid = 2;
SELECT productid, unitprice FROM Production.Products WHERE productid = 2;

运行后将得到:

productid unitprice
---------- ----------
2 20.00

在连接2中,打开一个事务并尝试读取产品2,并使事务处于打开状态:

BEGIN TRAN;
SELECT productid, unitprice FROM Production.Products WHERE productid = 2;

运行后得到最近提交的版本:

productid unitprice
---------- ----------
2 19.00

现在在连接1中提交事务:

COMMIT TRAN;

那么此时最近提交的行版本发生了变化(20.00),如果此时在连接2中再次读取产品2的数据,并提交事务:

SELECT productid, unitprice FROM Production.Products WHERE productid = 2;
COMMIT TRAN;

此时结果如下:

productid unitprice
---------- ----------
2 20.00

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

主题: SQLRIA数据TI冲突CTISQL Server删除数据库NPS
分页:12
转载请注明
本文标题:SQLServer事务的隔离级别
本站链接:http://www.codesec.net/view/484664.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(28)