标签: SQL SEERVER/MSSQL SERVER/SQL/事务隔离级别选项/设置数据库事务级别 SQL 事务隔离级别 概述 隔离级别用于决定如果控制并发用户如何读写数据的操作,同时对性能也有一定的影响作用。 步骤 事务隔离级别通过影响读操作来间接地影响写操作;可以在回话级别上设置事务隔离级别也可以在查询(表级别)级别上设置事务隔离级别。 获取事务隔离级别(isolation level) 设置隔离 设置回话隔离SET TRANSACTION ISOLATION LEVEL ISOLATION NAME>--注意:在设置回话隔离时(REPEATABLE READ)两个单词需要用空格间隔开,但是在表隔离中可以粘在一起(REPEATABLEREAD)设置查询表隔离SELECT ....FROM TABLE> WITH (ISOLATION NAME>) 1.READ UNCOMMITTED READ UNCOMMITTED:未提交读,读脏数据 创建测试数据 IF OBJECT_ID('Orders','U') IS NOT NULL DROP TABLE Orders GOCREATE 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);GOSELECT ID,Price FROM Orders 新建回话1将订单10的价格加1 在另一个回话2中执行查询操作 首先不添加隔离级别,默认是READ COMMITTED,由于数据之前的更新操作使用了排他锁,所以查询一直在等待锁释放*/SELECT ID,Price FROM Orders WHERE ID=10---将查询的隔离级别设置为READ UNCOMMITTED允许未提交读,读操作之前不请求共享锁。SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDSELECT ID,Price FROM Orders WHERE ID=10;--当然也可以使用表隔离,效果是一样的SELECT ID,Price FROM Orders WITH (NOLOCK)WHERE ID=10 假设在回话1中对操作执行回滚操作,这样价格还是之前的10,但是回话2中则读取到的是回滚前的价格11,这样就属于一个读脏操作 2.READ COMMITTED READ COMMITTED(已提交读)是SQL SERVER默认的隔离级别,可以避免读取未提交的数据,隔离级别比READ UNCOMMITTED未提交读的级别更高; 新建回话1将订单10的价格加1,此时回话1的排他锁锁住了订单10的值 在回话2中执行查询,将隔离级别设置为READ COMMITTED SET TRANSACTION ISOLATION LEVEL READ COMMITTEDSELECT ID,Price FROM Orders WHERE ID=10---由于READ COMMITTED需要申请获得共享锁,而锁与回话1的排他锁冲突,回话被堵塞,----在回话1中执行事务提交COMMIT TRANSACTION/*由于回话1事务提交,释放了订单10的排他锁,此时回话2申请共享锁成功查到到订单10的价格为修改后的价格11,READ COMMITTED由于是已提交读隔离级别,所以不会读脏数据.*/ 重置数据 注意:但是由于READ COMMITTED读操作一完成就立即释放共享锁,读操作不会在一个事务过程中保持共享锁,也就是说在一个事务的的两个查询过程之间有另一个回话对数据资源进行了更改,会导致一个事务的两次查询得到的结果不一致,这种现象称之为不可重复读. 3.REPEATABLE READ REPEATABLE READ(可重复读):保证在一个事务中的两个读操作之间,其他的事务不能修改当前事务读取的数据,该级别事务获取数据前必须先获得共享锁同时获得的共享锁不立即释放一直保持共享锁至事务完成,所以此隔离级别查询完并提交事务很重要。 在回话1中执行查询订单10,将回话级别设置为REPEATABLE READ SET TRANSACTION ISOLATION LEVEL REPEATABLE READBEGIN TRANSACTIONSELECT ID,Price FROM Orders WHERE ID=10 新建回话2修改订单10的价格 UPDATE Orders SET Price=Price+1WHERE ID=10---由于回话1的隔离级别REPEATABLE READ申请的共享锁一直要保持到事务结束,所以回话2无法获取排他锁,处于等待状态 在回话1中执行下面语句,然后提交事务 回话1的两次查询得到的结果一致,前面的两个隔离级别无法得到一致的数据,此时事务已提交同时释放共享锁,回话2申请排他锁成功,对行执行更新 REPEATABLE READ隔离级别保证一个事务中的两次查询到的结果一致,同时保证了丢失更新 4.SERIALIZABLE SERIALIZABLE(可序列化),对于前面的REPEATABLE READ能保证事务可重复读,但是事务只锁定查询第一次运行时获取的数据资源(数据行),而不能锁定查询结果之外的行,就是原本不存在于数据表中的数据。因此在一个事务中当第一个查询和第二个查询过程之间,有其他事务执行插入操作且插入数据满足第一次查询读取过滤的条件时,那么在第二次查询的结果中就会存在这些新插入的数据,使两次查询结果不一致,这种读操作称之为幻读。 IF OBJECT_ID('Orders','U') IS NOT NULL DROP TABLE Orders GOCREATE 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中执行查询操作,并将事务隔离级别设置为REPEATABLE READ(先测试一下前面更低级别的隔离) SET TRANSACTION ISOLATION LEVEL REPEATABLE READBEGIN TRANSACTION SELECT ID,Price,type FROM OrdersWHERE TYPE=1 在回话2中执行插入操作 返回回话1重新执行查询操作并提交事务 结果回话1中第二次查询到的数据包含了回话2新插入的数据,两次查询结果不一致(验证之前的隔离级别不能保证幻读) 重新插入测试数据 IF OBJECT_ID('Orders','U') IS NOT NULL DROP TABLE Orders GOCREATE 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 接下来将回话级别设置为SERIALIZABLE,在回话1中执行查询操作,并将事务隔离级别设置为SERIALIZABLE SET TRANSACTION ISOLATION LEVEL SERIALIZABLEBEGIN TRANSACTION SELECT ID,Price,type FROM OrdersWHERE TYPE=1 在回话2中执行插入操作 返回回话1重新执行查询操作并提交事务 两次执行的查询结果相同
重置所有打开回话的默认隔离级别 5.SNAPSHOT SNAPSHOT快照:SNAPSHOT和READ COMMITTED SNAPSHOT两种隔离(可以把事务已经提交的行的上一版本保存在TEMPDB数据库中) 如果启用任何一种基于快照的隔离级别,DELETE和UPDATE语句在做出修改前都会把行的当前版本复制到TEMPDB中,而INSERT语句不需要在TEMPDB中进行版本控制,因为此时还没有行的旧数据 无论启用哪种基于快照的隔离级别都会对更新和删除操作产生性能的负面影响,但是有利于提高读操作的性能因为读操作不需要获取共享锁; 5.1SNAPSHOT SNAPSHOT 在SNAPSHOT隔离级别下,当读取数据时可以保证操作读取的行是事务开始时可用的最后提交版本 在打开的所有查询窗口中执行以下操作 重置测试数据 IF OBJECT_ID('Orders','U') IS NOT NULL DROP TABLE Orders GOCREATE 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 TRANSACTIONUPDATE Orders SET Price=Price+1WHERE ID=10SELECT ID,Price,type FROM OrdersWHERE ID=10---查询到更新后的价格为11---在回话2中将隔离级别设置为SNAPSHOT,并打开事务(此时查询也不会因为回话1的排他锁而等待,依然可以查询到数据)SET TRANSACTION ISOLATION LEVEL SNAPSHOTBEGIN TRANSACTIONSELECT ID,Price,type FROM OrdersWHERE ID=10---查询到的结果还是回话1修改前的价格,由于回话1在默认的READ COMMITTED隔离级别下运行,SQL SERVER必须在更新前把行的一个副本复制到TEMPDB数据库中--在SNAPSHOT级别启动事务会请求行版本---现在在回话1中执行提交事务,此时订单10的价格为11COMMIT TRANSACTION---再次在回话二中查询订单10的价格并提交事务,结果还是10,因为事务要保证两次查询的结果相同SELECT ID,Price,type FROM OrdersWHERE ID=10COMMIT TRANSACTION---此时如果在回话2中重新打开一个事务,查询到的订单10的价格则是11BEGIN TRANSACTIONSELECT ID,Price,type FROM OrdersWHERE ID=10COMMIT TRANSACTION/*SNAPSHOT隔离级别保证操作读取的行是事务开始时可用的最后已提交版本,由于回话1的事务未提交,所以订单10的最后提交版本还是修改前的价格10,所以回话2读取到的价格是回话2事务开始前的已提交版本价格10,当回话1提交事务后,回话2重新新建一个事务此时事务开启前的价格已经是11了,所以查询到的价格是11,同时SNAPSHOT隔离级别还能保证SERIALIZABLE的隔离级别*/ 5.2READ COMMITTED SNAPSHOT READ COMMITTED SNAPSHOT也是基于行版本控制,但是READ COMMITTED SNAPSHOT的隔离级别是读操作之前的最后已提交版本,而不是事务前的已提交版本,有点类似前面的READ COMMITTED能保证已提交读,但是不能保证可重复读,不能避免幻读,但是又比 READ COMMITTED隔离级别多出了不需要获取共享锁就可以读取数据 ALTER DATABASE TEST SET READ_COMMITTED_SNAPSHOT ONIF OBJECT_ID('Orders','U') IS NOT NULL DROP TABLE Orders GOCREATE 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 TRANSACTIONUPDATE Orders SET Price=Price+1WHERE ID=10--查询到的价格是11SELECT ID,Price,type FROM OrdersWHERE ID=10---在回话2中打开事务查询订单10并一直保持事务处于打开状态(此时由于回话1还未提交事务,所以回话2中查询到的还是回话1执行事务之前保存的行版本)BEGIN TRANSACTIONSELECT ID,Price,type FROM OrdersWHERE ID=10--查询到的价格还是10---在回话1中提交事务COMMIT TRANSACTION ---在回话2中再次执行查询订单10的价格,并提交事务SELECT ID,Price,type FROM OrdersWHERE ID=10COMMIT TRANSACTION --此时的价格为回话1修改后的价格11,而不是事务之前已提交版本的价格,也就是READ COMMITTED SNAPSHOT隔离级别在同一事务中两次查询的结果不一致. 关闭所有连接,然后打开一个新的连接,禁用之前设置的数据库快照隔离级别选项 ALTER DATABASE TEST SET ALLOW_SNAPSHOT_ISOLATION OFF;ALTER DATABASE TEST SET READ_COMMITTED_SNAPSHOT OFF;
总结 理解了事务隔离级别有助于理解事务的死锁。
|
|
来自: chinablue488 > 《待分类》