SQL 事务和视图

一  事务的属性  
 
事务具有ACID属性  
即  Atomic原子性,  Consistent一致性,  Isolated隔离性,  Durable永久性  
 
原子性  
     
     就是事务应作为一个工作单元,事务处理完成，所有的工作要么都在数据库中保存下来，要么完全  
回滚，全部不保留  
 
 
一致性  
     事务完成或者撤销后，都应该处于一致的状态  
 
隔离性  
 
     多个事务同时进行，它们之间应该互不干扰.应该防止一个事务处理其他事务也要修改的数据时，  
不合理的存取和不完整的读取数据  
 
 
永久性  
     事务提交以后，所做的工作就被永久的保存下来  
 
 
二  事务并发处理会产生的问题    
 
丢失更新  
 
       当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，会发生丢失更新问题、  
每个事务都不知道其它事务的存在。最后的更新将重写由其它事务所做的更新，这将导致数据丢失。  
 
脏读  
         当第二个事务选择其它事务正在更新的行时，会发生未确认的相关性问题。  
         第二个事务正在读取的数据还没有确认并且可能由更新此行的事务所更改。  
 
不可重复读  
 
         当第二个事务多次访问同一行而且每次读取不同的数据时，会发生不一致的分析问题。  
         不一致的分析与未确认的相关性类似，因为其它事务也是正在更改第二个事务正在读取的数据。  
         然而，在不一致的分析中，第二个事务读取的数据是由已进行了更改的事务提交的。而且，不一致的分析涉及多次（两次或更多）读取同一行，而且每次信息都由其它事务更改；因而该行被非重复读取。  
 
幻像读  
 
           当对某行执行插入或删除操作，而该行属于某个事务正在读取的行的范围时，会发生幻像读问题。  
           事务第一次读的行范围显示出其中一行已不复存在于第二次读或后续读中，因为该行已被其它事务删除。同样，由于其它事务的插入操作，事务的第二次或后续读显示有一行已不存在于原始读中。  
 
 
三  事务处理类型  
 
 
自动处理事务  
 
     系统默认每个T－SQL命令都是事务处理    由系统自动开始并提交  
 
 
隐式事务  
 
       当有大量的DDL  和DML命令执行时会自动开始，并一直保持到用户明确提交为止，切换隐式事务可以用SET  IMPLICIT_TRANSACTIONS  
       为连接设置隐性事务模式.当设置为  ON  时，SET  IMPLICIT_TRANSACTIONS  将连接设置为隐性事务模式。当设置为  OFF  时，则使连接返回到自动提交事务模式  
 
 
用户定义事务  
 
         由用户来控制事务的开始和结束    命令有:  begin  tran  commit  tran    rollback  tran  命令  
 
 
分布式事务  
         跨越多个服务器的事务称为分布式事务,sql  server  可以由DTc  microsoft  distributed  transaction  coordinator  
         来支持处理分布式事务,可以使用  BEgin  distributed  transaction  命令启动一个分布式事务处理  
 
 
 
四    事务处理的隔离级别  
 
使用SET  TRANSACTION  ISOLATION  LEVEL来控制由连接发出的所有语句的默认事务锁定行为  
 
从低到高依次是  
 
 
READ  UNCOMMITTED  
 
执行脏读或  0  级隔离锁定，这表示不发出共享锁，也不接受排它锁。当设置该选项时，可以对数据执行未提交读或脏读；在事务结束前可以更改数据内的数值，行也可以出现在数据集中或从数据集消失。该选项的作用与在事务内所有语句中的所有表上设置  NOLOCK  相同。这是四个隔离级别中限制最小的级别。  
 
举例  
 
设table1(A,B,C)  
A        B        C  
a1      b1      c1  
a2      b2      c2  
a3      b3      c3  
 
新建两个连接  
在第一个连接中执行以下语句  
select  *  from  table1  
begin  tran  
update  table1  set  c='c'  
select  *  from  table1  
waitfor  delay  '00:00:10'    --等待10秒  
rollback  tran  
select  *  from  table1  
 
在第二个连接中执行以下语句  
SET  TRANSACTION  ISOLATION  LEVEL  READ  UNCOMMITTED  
print  '脏读'  
select  *  from  table1  
if  @@rowcount>0  
begin  
 waitfor  delay  '00:00:10'    
 print  '不重复读'  
 select  *  from  table1  
end  
 
第二个连接的结果  
 
脏读  
A        B        C  
a1      b1      c  
a2      b2      c  
a3      b3      c  
 
'不重复读'  
A        B        C  
a1      b1      c1  
a2      b2      c2  
a3      b3      c3  
 
 
 
READ  COMMITTED  
 
指定在读取数据时控制共享锁以避免脏读，但数据可在事务结束前更改，从而产生不可重复读取或幻像数据。该选项是  SQL  Server  的默认值。  
 
 
在第一个连接中执行以下语句  
SET  TRANSACTION  ISOLATION  LEVEL  READ  COMMITTED  
begin  tran  
print  '初始'  
select  *  from  table1  
waitfor  delay  '00:00:10'    --等待10秒  
print  '不重复读'  
select  *  from  table1  
rollback  tran  
 
 
在第二个连接中执行以下语句  
SET  TRANSACTION  ISOLATION  LEVEL  READ  COMMITTED  
 
update  table1  set  c='c'  
     
 
第一个连接的结果  
 
初始  
A        B        C  
a1      b1      c1  
a2      b2      c2  
a3      b3      c3  
 
不重复读  
A        B        C  
a1      b1      c  
a2      b2      c  
a3      b3      c  
 
 
 
REPEATABLE  READ  
 
锁定查询中使用的所有数据以防止其他用户更新数据，但是其他用户可以将新的幻像行插入数据集，且幻像行包括在当前事务的后续读取中。因为并发低于默认隔离级别，所以应只在必要时才使用该选项。  
 
 
在第一个连接中执行以下语句  
SET  TRANSACTION  ISOLATION  LEVEL  REPEATABLE  READ  
begin  tran  
print  '初始'  
select  *  from  table1  
waitfor  delay  '00:00:10'    --等待10秒  
print  '幻像读'  
select  *  from  table1  
rollback  tran  
 
 
在第二个连接中执行以下语句  
SET  TRANSACTION  ISOLATION  LEVEL  REPEATABLE  READ  
insert    table1  select  'a4','b4','c4'  
 
 
第一个连接的结果  
 
初始  
A        B        C  
a1      b1      c1  
a2      b2      c2  
a3      b3      c3  
 
幻像读  
A        B        C  
a1      b1      c1  
a2      b2      c2  
a3      b3      c3  
a4      b4      c4  
 
 
SERIALIZABLE  
 
在数据集上放置一个范围锁，以防止其他用户在事务完成之前更新数据集或将行插入数据集内。这是四个隔离级别中限制最大的级别。因为并发级别较低，所以应只在必要时才使用该选项。该选项的作用与在事务内所有  SELECT  语句中的所有表上设置  HOLDLOCK  相同。  
 
 
在第一个连接中执行以下语句  
SET  TRANSACTION  ISOLATION  LEVEL  SERIALIZABLE  
begin  tran  
print  '初始'  
select  *  from  table1  
waitfor  delay  '00:00:10'    --等待10秒  
print  '没有变化'  
select  *  from  table1  
rollback  tran  
 
 
在第二个连接中执行以下语句  
SET  TRANSACTION  ISOLATION  LEVEL  SERIALIZABLE  
insert    table1  select  'a4','b4','c4'  
 
 
第一个连接的结果  
 
初始  
A        B        C  
a1      b1      c1  
a2      b2      c2  
a3      b3      c3  
 
没有变化  
A        B        C  
a1      b1      c1  
a2      b2      c2  
a3      b3      c3  
 
 
五  事务处理嵌套的语法和对@@TRANCOUNT的影响  
 
BEGIN  TRAN    @@TRANCOUNT＋1  
COMMIT  TRAN  @@TRANCOUNT－1  

ROLLBACK  TR  

视图实验方法
情况1：如果仅仅是同登录用户的登录Id有关（这里的Id是后台SQL Server数据库的登录帐号，也就是上面的ClerkId就是这个登录帐号），那么我么可以简单地使用以下方式来实现：
CREATE VIEW v_ClerkDailyBusiness
AS
SELECT a.* FROM DailyBusiness a, Master.dbo.sysproceeses b
WHERE a.ClerkId = b.loginname and b.spid = @@spid.

但这种情况一般比较少。另外，有一个缺点就是只能实现有限的上下文相关，灵活性不够。
情况2：许多数据库应用系统一般都只建立少数几个数据库登录帐号，对于应用系统的权限，一般有程序进行控制，那么根据登录帐号来实现，就显得力不从心，因此，我们必须采取其他方法来实现。
SQL Server提供了一个设置连接上下文的功能，即SET CONTEXT_INFO可以为当前的数据库连接设置最大达到128字节的上下文信息，并可以通过查询sysprocesses表获取当前的上下文信息。因此，我们可以初步设想通过SET CONTEXT_INFO设置一些上下文信息，然后再将上下文信息作为上下文相关视图的查询条件，既可以实现。
但是，这里有两个需要解决的问题：
1． 什么时候设置上下文信息
2． 取出上下文信息，并作为查询的条件时，由于需要进行数据转换，免不了使用系统函数，SQL Server就无法对该查询进行优化，如果数据量较大，因此会遇到性能问题。
对于第一个问题，如果是跟用户相关的视图，那么用户登录的时候是最好的设置时机。写上一个用于登录的存储过程，一方面验证用户的口令等信息，另一方面可以调用SET CONTEXT_INFO设置用户的登录Id(这里的Id是数据库应用系统的Id，而不是SQL Server的登录帐号)。
如果是像财务如选择帐套之类的操作，我们可以在选择帐套时，设置当前的上下文为当前的帐套。
由于SET CONTEXT_INFO允许设置最大达到128字节的信息，因此，我们可以进行组合，包含多个上下文信息，如前2个字节为用户Id，后两个字节为帐套号，再接下去是指定的日期等等。
对于第二个问题：首先我们分析一下产生性能问题的原因。
假设我们设置了用户Id上下文，下面是建立视图的脚本如下：
CREATE VIEW v_DailyClerkBusiness
AS
SELECT a.* FROM DailyBusiness a, MASTER.DBO.SysProceeses b
WHERE a.ClerkId = Convert(CHAR(20), b.context_info) 
AND b.spid = @@spId
GO
在做这个查询的时候，因为用到了函数，SQL Server 就无法决定使用DailyBusiness的索引，因此一旦数据量比较大的时候，就会产生严重的性能问题。
那么，解决问题的关键是在做查询的时候，再条件字句中不要使用到Convert之类的影响到SQL Server无法进行查询优化的任何函数。
还好，SQL Server 2000为我们提供了用户函数，并且让我们可以通过用户函数返回一个表变量。因此，我们可以先建立一个进行查询的用户函数，然后再创建以该用户函数为数据源的视图,脚本如下：
CREATE FUNCTION dbo.fn_DailyClerkBusiness()
RETURNS @retVal TABLE
( ClerkId CHAR(20),
OccurDate DATETIME,
……
)
AS
BEGIN
DECLARE @sClerkId CHAR(20)
SELECT @sClerkId = CONVERT(CHAR(20), context_info) 
FROM MASTER.dbo.sysprocesses 
WHERE spid = @@spid

INSERT INTO @retVal
SELECT ClerkId, OccurDate, … 
FROM DailyBusiness 
WHERE ClerkId = @sClerkId
RETURN
END 
GO
CREATE VIEW v_ClerkDailyBusiness
AS
SELECT * FROM dbo.fn_ClerkDailyBusiness()
GO

通过这种方式，虽然SQL Server在调用用户函数的时候将数据集放到了临时表中，但是由于SQL Server能够对该查询进行优化，因此较之第一种方式还是能大大提高查询的性能。当然，凡事有利必有弊，后者只能作为只读视图，不能进行更新操