原文:SQL点滴9—SQL Server中的事务处理以及SSIS中的内建事务
我们可以把SSIS中的整个package包含在一个事务中,但是如果在package的执行过程中有一个表需要锁定应该怎么处理呢?SSIS内建的事务处理可以解决这个问题。在此之前首先来熟悉一下SQL Server中的事务的概念。
事务
SQL Server中的事务是单个的工作单元。如果某一事务成功,则在该事务中进行的所有数据修改均会提交,成为数据库中永久的组成部分。如果事务遇到错误且必须取消或回滚,则所有的数据修改均被清除。
在SQL Server中使用事务有可能会造成一些预想不到的结果,具体来说有脏读,不可重复读和幻读三种结果。
- 脏读:脏读是指当一个事务正在访问数据库,并且对数据进行修改,而这种修改还没有提交到数据库中,另外一个事务也在访问这个数据,然后使用了这个数据。
- 不可重复读:在一个事物内多次读同一数据。在这个事务还没有结束时,另外一个事物也在访问该同一数据,那么在第一个事务两次读取之间,由于第二个事务的修改,第一个事务两次读取到的数据可能不一样。这样就发生了在一个事务内两次读取到的数据不一样,因此称为不可重复读。
- 幻读:幻读是指当事务不是独立执行时发生的一种现象,例如第一个事务对一个表中的数据进行修改,这种修改设计到表中的全部数据行。同时第二个事务也修改这个表中的数据,这个修改时向同一个表中插入一行新数据,这样第一个事务的用户发现还有一条数据没有修改,像发生了幻觉一样,因此称为幻读
在SQL Server中给事务指定一个隔离级别,这个隔离级别定义该事务与其他事务进行资源或数据更改相隔离的级别。事务隔离级别决定了是否锁定SQL Server对象,下面是SQL Server中的事务隔离级别。
- Rdad Uncommitted: 读取数据不需要等待解锁,这种方式会读到脏数据,因为读取的数据有可能是还没有更新的数据。这种隔离级别最低,会造成脏读,不可重复读和幻读的结果,并发性最高。
- Read Committed: 读取数据需要等待解锁,这样会读取到最新的被更新的数据。Read Committed不会造成脏读的问题,但是会造成不可重复的和幻读的问题。Read Committed是SQL Server的默认设置。
- Repeatable Read: 与Read Committed类似,它会锁定所读取的所有行,但是没有其他的连接可以更新或插入数据,这样如果select语句可能选择到这条新跟新或插入的数据,这条数据记录是不会出现在select结果中的。同时被选择出的数据也不能被其他连接更改,直到读取动作执行结束或者回滚结束。这种隔离级别不会造成脏读和不可重复读,但是会造成幻读。
- Serializable:和Repeatable Read类似,不过没有其他的连接可以插入或更新数据,同时如果在下次查询中任然使用这种事务隔离级别,你会得到相同的查询结果,就是说更新或新插入的数据任然不会出现在查询结果中。这种隔离级别不会造成脏读,不可重复读或幻读。
还有两种是SQL Server 2005中新添加的事务隔离级别
- 一种Read Committed级别的变异,当你把数据库的隔离级别设置成READ_COMMITTED_SNAPHOT,任何使用Read Committed级别的的事务不再需要锁定数据对象。执行语句时会得到select语句开始执行之时会得到所有最新的结果。
- SNAPSHOT:一种全新的级别SNAPSHOT,当你在任何数据库对象中设置事务的隔离级别为ALLOW_SNAPSHOT_ISOLATION时,其他事务都不会遇到共享锁,查询结果会得到所有更新之后的行。这种隔离级别不会造成脏读,不可更新读和幻读的结果
所有上述的事务处理都在tempdb数据库中一个类似版本库的数据对象中自动进行,当遇到更新未被提交的情况,数据引擎会检索这个版本库得到合适的提交结果。维护这个版本库的工作由SQL Server自动进行,不需要人为干预。
SSIS中的事务处理
SSIS中的包,容器(例如Loop,Foreach Loop,Sequence)或者一个单独的任务中都可以设置事务处理选项。事务处理选项有下面一些值
- Required-如有事务则添加,否则新添加一个
- Supported-如有有事务添加一个,没有则不添加,这是默认选项+
- NotSupported-不添加事务处理
内建的事务处理要使用Distributed Transaction Coordinator(MSDTC)服务,这个服务必须开启。MSDTC允许使用分布式事务处理,例如在一个事务中同时处理SQL Server数据库和Oracle数据库。如果没有开启这个服务会得到下面的错误提示.
Error: 0xC001401A at Transaction: The SSIS Runtime has failed to start the distributed transaction due to error 0x8004D01B "The Transaction Manager is not available.". The DTC transaction failed to start. This could occur because the MSDTC Service is not running.
注意SSIS中包中的元素的事务隔离级别是Serializable,这种级别会影响锁的持续时间。下面我们来用一个例子说明在如何package中锁定一个表
- 新建一个SequenceContainer,命名为Test Initialization。
- 这个SequenceContainer主要用来创建测试的环境,创建连个表TranQueue,TranQueueHistory,向第一个表中添加一条记录,这样模拟一个事物处理 过程。我们只是使用这个SequenceContainer来创建测试环境,所以设置它的TransactionOption选项为NotSupported在这个SequenceContainer中依次添加三个Execute SQL,依次他们的设置如下
/*命名*/
Create TranQueue Table
/*SQLstatement设置*/
IF NOT EXISTS ( SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'dbo.TranQueue') AND type in (N'U') )
BEGIN
execute('CREATE TABLE dbo.TranQueue(message nvarchar(256))')
END
/*命名*/
Populate TranQueue
/*SQLstatement设置*/
INSERT INTO dbo.TranQueue VALUES ('Test Message' + CONVERT
(NVARCHAR(23), GETDATE(), 121))
/*命名*/
Create TranQueueHistory table
/*SQLstatement设置*/
IF NOT EXISTS ( SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'dbo.TranQueueHistory') AND type in (N'U') )
BEGIN
execute('CREATE TABLE dbo.TranQueueHistory(message nvarchar(256))')
END - 创建第二个SequenceContainer,命名为Process,TransactionOption属性设置为Supported,这样就会添加事务处理。
- 在这个SequenceContainer中添加一个Execute SQL,命名为ProcessTranQueue,它的SQLStatement设置为下面的语句。这个语句的作用,模拟事务处理,删除TranQueue表中前10条数据;OUTPUT字句将删除的数据插入到TranQueueHistory表中,模拟处理结束,更新历史记录
DELETE TOP(10) dbo.TranQueue
OUTPUT DELETED.*
INTO dbo.TranQueueHistory
FROM dbo.TranQueue WITH (TABLOCKX) - 添加一个Execute SQL,命名为Placeholder for Breakpoint。这个任务不进行任何操作,只是为了在这设置一个断点然后在这里停下来让我们有时间验证是否会锁定表。
- 右击Control Flow界面添加一个变量v_SimulateFailure,类型为Int32,值为1。
- 添加一个Execute SQL命名为Simulate Failure。用它来模拟错误,设置SQLStatement为select 1/0,当pacakage执行到这里的时候会造成错误进而回滚。
- 右击Placeholder for Breakpoint和Simulate Failure之间的连线,点击Edit,设置Evaluation operation为Expression and Constraint,设置Expression为@[User::v_SimulateFailure] == 1,其他保持默认。这样之后这个自定义变量的值为1的时候才会继续往下执行。
- 执行package,会得到如图1的结果,package在断点处终止。
图1
10.打开SQL Server Management Studion,选择对应的数据库,新建一个Query,执行下面的语句,NOLOCK选项忽略锁,这个语句查询得到一条记录 Message2011-04-10 14:22:31.043,但是这条记录并没有提交
SELECT * FROM dbo.TranQueueHistory WITH (NOLOCK)
11.执行下面的语句
SELECT * FROM dbo.TranQueue
语句将阻塞在这里,语句一直停留在执行状态,不会结束。因为在Process TranQueue任务中我们使用TABLOCKX,在这里将等待任务回滚或者提交。或者可以写成这样,它任然会阻塞
DELETE TOP(10) dbo.TranQueue
INSERT INTO dbo.TranQueueHistory VALUES ('Test Message' + CONVERT(NVARCHAR(23), GETDATE(), 121))
12. 点击Continue按钮或者Debuge按钮,会看到package执行失败,执行SELECT * FROM dbo.TranQueueHistory
WITH (NOLOCK);因为执行了回滚,不会得到任何结果。SELECT * FROM dbo.TranQueue,任然有一条记录。
SELECT * FROM dbo.TranQueueHistory WITH (NOLOCK)
NOLOCK提示忽略锁,这个语句查询得到一条记录 Message2011-04-10 14:22:31.043,但是这条记录并没有提交
13. 执行下面的语句,
SELECT * FROM dbo.TranQueue
sql语句将阻塞在这里,语句一直执行。因为在Process TranQueue任务中我们使用TABLOCKX,在这里将等待任务回滚或者提交。或者可以写成这样
DELETE TOP(10) dbo.TranQueue;INSERT INTO dbo.TranQueueHistory VALUES ('Test Message' + CONVERT(NVARCHAR(23), GETDATE(), 121)),它任然会阻塞
14. 点击Continue按钮或者Debuge按钮,会看到package执行失败,执行SELECT * FROM dbo.TranQueueHistory WITH (NOLOCK);因为执行了回滚,不会得到任何结果。执行SELECT * FROM dbo.TranQueue,任然有一条记录。
如果设置变量User::v_SimulateFailure的值为0,不会执行Simulate Failure任务,就不会回滚,TranQueue中的记录会被写入到TranQueueHistory中。这里有一个很有意思的语句:
DELETE TOP(10) dbo.TranQueue
OUTPUT DELETED.*
INTO dbo.TranQueueHistory
FROM dbo.TranQueue WITH (TABLOCKX)
如果两个表的结构有一部分是是一样的,现在想把一个表的数据导入到另外一个表中,可以使用DELETE SourceTable OUTPUT DELETE.*/DELETE.Column1,DELETE.Column2... INTO DestinationTable FROM SourceTable,这样第一个表中的数据会被“剪切”到第二个表中。
SQL点滴9—SQL Server中的事务处理以及SSIS中的内建事务的更多相关文章
-
SQL点滴12—SQL Server备份还原数据库中的小把戏
原文:SQL点滴12-SQL Server备份还原数据库中的小把戏 备份数据库时出现一个不太了解的错误 ,错误信息“is formatted to support 1 media families, ...
-
sql点滴38—SQL Server 2008和SQL Server 2008 R2导出数据的选项略有不同
原文:sql点滴38—SQL Server 2008和SQL Server 2008 R2导出数据的选项略有不同 说明: 以前要将一个表中的数据导出为脚本,只有用存储过程.现在在SQL Server ...
-
SQL点滴33—SQL中的字符串操作
原文:SQL点滴33-SQL中的字符串操作 计算字符串长度len()用来计算字符串的长度 select sname ,len(sname) from student 字符串转换为大.小写lower() ...
-
SQL点滴34—SQL中的大小写
原文:SQL点滴34-SQL中的大小写 默认情况下,SQL Server不区分大小写,如果数据表TEST的TNAME列中有数据“abcd”和“Abcd”, 如果使用查询语句:select * from ...
-
SQL点滴30—SQL中常用的函数
原文:SQL点滴30-SQL中常用的函数 该文章转载自http://www.cnblogs.com/jiajiayuan/archive/2011/06/16/2082488.html 别人的总结,很 ...
-
SQL点滴35—SQL语句中的exists
原文:SQL点滴35-SQL语句中的exists 比如在Northwind数据库中有一个查询为 SELECT c.CustomerId,CompanyName FROM Customers c WHE ...
-
SQL点滴31—SQL语句中@@IDENTITY和@@ROWCOUNT区别
原文:SQL点滴31-SQL语句中@@IDENTITY和@@ROWCOUNT区别 SQL语句中@@IDENTITY和@@ROWCOUNT区别 在一条 INSERT.SELECT INTO 或大容量复制 ...
-
SQL点滴16—SQL分页语句总结
原文:SQL点滴16-SQL分页语句总结 今天对分页语句做一个简单的总结,他们大同小异的,只要理解其中一个其他的就很好理解了. 使用top选项 *from Orders orderid from Or ...
-
[置顶] Ajax程序:处理异步调用中的异常(使用Asp.Net Ajax内建的异常处理方法)
无论在Window应用程序,还是Web应用程序以对用户友好的方式显示运行时的异常都是很有必要,尤其对于可能有很多不确定因素导致异常的Web应用程序;在传统的Web开发中,处理异常的方式——设计专门一个 ...
随机推荐
-
crontab介绍
1.Cron的启动与关闭 由于Cron是Linux的内置服务,可以用以下的方法启动.关闭这个服务: /sbin/service crond start //启动服务/sbin/se ...
-
MVC3.0 项目升级到 MVC4.0
按照 http://www.asp.net/whitepapers/mvc4-release-notes#_Toc303253806 的步骤 第一步:修改web.config 注意,默认的MVC3网站 ...
-
数据库 SQL语法二
聚合函数 -SUM([DISTINCT] FIELDNAME) 求指定列之和,[DISTINCT]选项表示剔除重复记录 例如:SELECT SUM(age) FROM TABLE1; SELECT S ...
-
django internal search
最近改进了项目中的站内搜索的功能,增加了全文索引,提升了搜索速度.因为项目框架是django,所以采用django+haystack+pyelasticsearch+elasticsearch的方式实 ...
-
【转】Android中EditText中的InputType类型含义与如何定义
原文网址:http://www.crifan.com/summary_android_edittext_inputtype_values_and_meaning_definition/ 经过一些And ...
-
Spring JDBC 随笔
Spring 框架,借助 JdbcTemplate 类来简化 java 访问 database. 完成一个增查改删(CRUD)的基本功能,借助下面 5 个角色来共同来完成. 1. object cla ...
-
oracle的exp和imp命令的使用【转载】
oracle的exp和imp命令的使用 我们通常要对ORACLE的数据进行导入,导出,在没有expdb,impdb以前(10G以前),我们都是使用exp 导出,imp命令来实现导入. 打开 ...
-
【Learning】带花树——一般图最大匹配
一般图最大匹配--带花树 问题 给定一个图,求该图的最大匹配.即找到最多的边,使得每个点至多属于一条边. 这个问题的退化版本就是二分图最大匹配. 由于二分图中不存在奇环,偶环对最大匹配并无 ...
-
集合总结三(HashMap的实现原理)
一.概述 二话不说,一上来就点开源码,发现里面有一段介绍如下: Hash table based implementation of the Map interface. This implement ...
-
00008 - crontab定时执行任务命令详解
linux 系统则是由 cron (crond) 这个系统服务来控制的.Linux 系统上面原本就有非常多的计划性工作,因此这个系统服务是默认启动的.另 外, 由于使用者自己也可以设置计划任务,所以, ...