SqlServer注意事项总结,事务隔离级别详解

日期:2019-10-21编辑作者:计算机网络

本篇文章主要介绍SqlServer使用时的注意事项。

SQL 事务隔离级别

想成为一个高级程序员,数据库的使用是必须要会的。而数据库的使用纯熟程度,也侧面反映了一个开发的水平。

概述

下面介绍SqlServer在使用和设计的过程中需要注意的事项。

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

SqlServer注意事项

步骤

Sql事务启动语句

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

开始事务:BEGIN TRANSACTION

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

提交事务:COMMIT TRANSACTION

DBCC USEROPTIONS 

回滚事务:ROLLBACK TRANSACTION

设置隔离

相关注意事项

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

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

保持事务简短,事务越短,越不可能造成阻塞。

1.READ UNCOMMITTED

在事务中尽量避免使用循环while和游标,以及避免采用访问大量行的语句。

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

事务中不要要求用户输入。

 创建测试数据

在启动事务前完成所有的计算和查询等操作。

图片 1

避免同一事务中交错读取和更新。可以使用表变量预先存储数据。即存储过程中查询与更新使用两个事务实现。

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 

超时会让事务不执行回滚,超时后如果客户端关闭连接sqlserver自动回滚事务。如果不关闭,将造成数据丢失,而其他事务将在这个未关闭的连接上执行,造成资源锁定,甚至服务器停止响应。

图片 2

避免超时后还可打开事务 SET XACT_ABORT ON统计信息可以优化查询速度,统计信息准确可以避免查询扫描,直接进行索引查找。

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

sp_updatestats可以更新统计信息到最新。

图片 3

低内存会导致未被客户端连接的查询计划被清除。

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

SELECT ID,Price FROM Orders 
WHERE ID=10

修改表结构,修改索引后,查询计划会被清除,可以再修改后运行几遍查询。

图片 4

DDL DML交错和查询内部SET选项将重新编译查询计划。

图片 5

order by 影响查询速度。

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

where中使用函数则会调用筛选器进行扫描,扫描表要尽量避免。

图片 6

updlock和holdlock同时使用可以在早期锁定后面需要更新的资源,维护资源完整性,避免冲突。

首先不添加隔离级别,默认是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

如果不需要使用临时表的统计信息来进行大数据查询,表变量是更好的选择。

图片 7

事务使用注意事项

图片 8

设置事务隔离级别(未提交读,读脏),相当于(NOLOCK) 的语句:

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

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

ROLLBACK TRANSACTION

隔离级别描述如下:

2.READ COMMITTED

1.READ UNCOMMITTED

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

READ UNCOMMITTED:未提交读,读脏数据。

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

默认的读操作:需要请求共享锁,允许其他事物读锁定的数据但不允许修改。

图片 9

READ UNCOMMITTED:读操作不申请锁,允许读取未提交的修改,也就是允许读脏数据,读操作不会影响写操作请求排他锁。

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

SELECT ID,Price FROM Orders 
WHERE ID=10

2.READ COMMITTED

图片 10

READ COMMITTED(已提交读)是SQL SERVER默认的隔离级别,可以避免读取未提交的数据,隔离级别比READ UNCOMMITTED未提交读的级别更高;

图片 11

该隔离级别读操作之前首先申请并获得共享锁,允许其他读操作读取该锁定的数据,但是写操作必须等待锁释放,一般读操作读取完就会立刻释放共享锁。

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

3.REPEATABLE READ

图片 12

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

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读操作一完成就立即释放共享锁,读操作不会在一个事务过程中保持共享锁,也就是说在一个事务的的两个查询过程之间有另一个回话对数据资源进行了更改,会导致一个事务的两次查询得到的结果不一致,这种现象称之为不可重复读.*/

4.SERIALIZABLE

图片 13

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

重置数据

5.SNAPSHOT

UPDATE Orders 
SET Price=10
WHERE ID=10

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

3.REPEATABLE READ

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

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

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

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

5.1SNAPSHOT

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

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

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

5.2READ COMMITTED SNAPSHOT

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

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

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

SqlServer【锁】注意事项

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

一、页锁实例

图片 14

T1: select * from table (paglock)
T2: update table set column1='hello' where id>10

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

说明
T1执行时,会先对第一页加锁,读完第一页后,释放锁,再对第二页加锁,依此类推。假设前10行记录恰好是一页(当然,一般不可能一页只有10行记录),那么T1执行到第一页查询时,并不会阻塞T2的更新。

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


 4.SERIALIZABLE

二、行锁实例

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

T1: select * from table (rowlock)
T2: update table set column1='hello' where id=10

图片 15

说明
T1执行时,对每行加共享锁,读取,然后释放,再对下一行加锁;T2执行时,会对id=10的那一行试图加锁,只要该行没有被T1加上行锁,T2就可以顺利执行update操作。

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

图片 16

三、整表锁实例

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

T1: select * from table (tablock)
T2: update table set column1='hello' where id = 10

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

说明
T1执行,对整个表加共享锁。 T1必须完全查询完,T2才可以允许加锁,并开始更新。

图片 17


在回话2中执行插入操作

婚前最后一篇博文,希望婚后的自己还能坚持更新。

INSERT INTO Orders VALUES(15,15.00,1)

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

注:此文章为原创,欢迎转载,请在文章页面明显位置给出此文链接!
若您觉得这篇文章还不错,请点击下右下角的【推荐】,非常感谢!
如果您觉得这篇文章对您有所帮助,那就不妨支付宝小小打赏一下吧。 

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

图片 18

图片 19

 

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

重新插入测试数据

图片 20

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

图片 21

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

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

图片 22

在回话2中执行插入操作

INSERT INTO Orders VALUES(15,15.00,1)

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

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

图片 23

两次执行的查询结果相同

 

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

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

5.SNAPSHOT

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

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

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

5.1SNAPSHOT

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

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

ALTER DATABASE TEST SET ALLOW_SNAPSHOT_ISOLATION ON;

重置测试数据

图片 24

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

图片 25

图片 26

在回话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的隔离级别*/

图片 27

5.2READ COMMITTED SNAPSHOT

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

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

图片 28

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隔离级别在同一事务中两次查询的结果不一致.

图片 29

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

ALTER DATABASE TEST SET ALLOW_SNAPSHOT_ISOLATION OFF;

ALTER DATABASE TEST SET READ_COMMITTED_SNAPSHOT OFF;

 

 

总结

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

 

转自:

本文由今晚最快开奖现场直播发布于计算机网络,转载请注明出处:SqlServer注意事项总结,事务隔离级别详解

关键词:

Sever数据常见难点,跨越服务器务器备份表

exec sp_configure 'show advanced options',1 reconfigure exec sp_configure 'Ad Hoc Distributed Queries',1 reconfigure SELECT * into T_System_Organizatio...

详细>>

处理上百万条的数据库如何提高处理查询速度,

**1、 对查询进行优化,应尽量制止全表扫描,首先应思量在where 及 order by 涉及的列上构建目录。  php 管理上百万条...

详细>>

计算两个日期之间的工作日数,获取指定日期为

转自:http://www.maomao365.com/?p=6771 /**//**//**//// summary ///总括多少个日子之间的办事日数,(星期6,星期日,不算工作日)...

详细>>

时间戳转换为日期

DATEADD(s, Timestamp + 8*3600, '1970-01-01 00:00:00') 在那之中Timestamp为十个人的时日戳,+8*3600是猎取中华夏族民共和国新加坡时...

详细>>