SQL 存储过程(3)

批处理和错误

如：创建一个临时表，把原表中需要的数据导入临时表，将原表删除再创建新表，把数据从临时表导入新表，把临时表删除。
create table tmppart (partid int,make varchar(5))
go
inset into tmppart (partid,make)
select partid,make from part
go
drop table part
create table part(partid int,make varchar(5))
go
insert into part(partid,make)
select partid,make from tmppart
go
drop table tmppart
go
以上程序看上去完美，但实际上存在很大问题。比如第一个批处理出现语法错误，将不会创造临时表，但是后面语句删除原表仍然会执行，那

么原表就消失了，数据也并没有保存。

在批处理完成之前，产生的对象是不可访问的。
如错误：
alter table part add cost money null
select partid,cost from part
go
更改为：
alter table part add cost money null
go
select partid,cost from part
go

 

显示事务 将SQL语句分组到一个单一的事务中。分组语句中任意一个语句出现问题，所有更改都必须异常终止。
Begin Transaction  开始事务
Rollback Transaction  回滚事务
Commit Transaction  结束事务（将结果写进数据库）

例如：将租借计划已经到期的资产项目的每月租借数目设置为0，同时将租借数目总计设置为0。
create procedure prclearleaseshedule
 @intleasescheduleid int
as

begin transaction
update inventory
set lease=0
where leasescheduleid=@leasescheduleid
if @@error<>0 goto problem
update leaseschedule
set periodictotalamount=0
where scheduleid=@intleasescheduleid
if @@error<>0 goto problem
commit transaction
return 0

problem:
print'unable to eliminate lease amounts from the datebase!'
rollback transaction
return 1

事务处理体系结构
事务日志在带有.ldf的单独文件中实现。存储两种类型的记录：
·执行的逻辑操作：插入，删除，启动等（不包括查询，因为查询并不会修改内容）
·更改前后的数据映像（数据副本）

事务日志解决的问题为：
·客户端应用程序在事务结束前丢失了连接，SQL将回滚更改，以保证一致性。
·如果机器掉电，当服务恢复时，SQL将恢复该数据库，就好象没发生过异常一样，所有在问题发生之前没完成的事务都将从数据库中回滚。

嵌套事务  在嵌套事务中，除了外层的commit语句外，其他commit语句都不会保存对数据库所做的更改。只有在提交最后一次事务之后，所有

对数据库的更改才变成永久性的。在这之前，你可以回滚所有的更改。
begin transaction
...
    begin transaction
     ...
    commit transaction
...
commit transaction

保存点 只回滚部分事务的机制。
save transaction @chvinventoryid          --在数据库中保存值@chvinventoryid
rollback transaction @chvinventoryid      --回滚到刚刚保存了的值

锁定 锁级别粒度（SQL会动态的自动在某资源上获得具有合适粒度的锁）如下：
·行
·页面
·键
·键范围
·索引
·表
·数据库

锁的类型：
·共享（只读）锁
·排他（只写）锁
·更新锁
·意向锁：IS（意向共享），IX（意向排他），SIX（意向排他共享）
·模式锁：Sch-S（模式稳定）防止删除表或索引，Sch-M（模式修改）确保资源正被修改时其他事务无法访问

事务隔离级别与锁提示
使用事务隔离级别或锁提示更改SQL的默认行为，事务隔离级别在连接级别上设置锁定，锁提示在语句级别上设置锁定。
事务隔离级别为：
·可串行读（最高级别）
·可重复读（强制共享锁）
·提交读（SQL默认）
·读未提交（最低级别）
set transaction isolation level repeatable read   --设置事务隔离级别为可重复读

锁提示为：（可用在select insert update delete语句中，作用域为表）
·HOLDLOCK 将共享锁保留到事务完成，而不是在相应的表、行或数据页不再需要时就立即释放锁。HOLDLOCK 等同于 SERIALIZABLE。

·NOLOCK 不要发出共享锁，不要提供排它锁。当此选项生效时，可能会读取未提交的事务或一组在读取中间回滚的页面。有可能发生脏读,仅 

       应用于SELECT 语句。

·PAGLOCK 在通常使用单个表锁的地方采用页锁。

·READCOMMITTED 用与运行在提交读隔离级别的事务相同的锁语义执行扫描。默认情况下，SQL Server 2000 在此隔离级别上操作。

·READPAST 跳过锁定行。此选项导致事务跳过由其它事务锁定的行（这些行平常会显示在结果集内），而不是阻塞该事务，使其等待其它事务

           释放在这些行上的锁。READPAST锁提示仅适用于运行在提交读隔离级别的事务，并且只在行级锁之后读取。仅用于 SELECT 语句。
 
·READUNCOMMITTED 等同于 NOLOCK。
 
·REPEATABLEREAD 用与运行在可重复读隔离级别的事务相同的锁语义执行扫描。
 
·ROWLOCK 使用行级锁，而不使用粒度更粗的页级锁和表级锁。

·SERIALIZABLE 用与运行在可串行读隔离级别的事务相同的锁语义执行扫描。等同于 HOLDLOCK。

·TABLOCK 使用表锁代替粒度更细的行级锁或页级锁。在语句结束前，SQL Server 一直持有该锁。但是，如果同时指定 HOLDLOCK，那么在事 

        务结束之前，锁将被一直持有。

·TABLOCKX 使用表的排它锁。该锁可以防止其它事务读取或更新表，并在语句或事务结束前一直持有。
 
·UPDLOCK 读取表时使用更新锁，而不使用共享锁，并将锁一直保留到语句或事务的结束。UPDLOCK 的优点是允许您读取数据（不阻塞其它事 

        务）并在以后更新数据，同时确保自从上次读取数据后数据没有被更改。

·XLOCK 使用排它锁并一直保持到由语句处理的所有数据上的事务结束时。可以使用 PAGLOCK 或 TABLOCK 指定该锁，这种情况下排它锁适用 

      于适当级别的粒度。

select *
from inventory with (holdlock)
where inventoryid=@intinventoryid
---------------------------------------------
update inventory with (tablockx,holdlock)
set statusid=4
where statusid=@intstatusid

总结
    简单的说，使用粒度更小的锁，比如行级锁，有助于提高系统的并发度，但是会增大锁的开销；而使用粒度更粗的锁，比如表级锁，有助

于提高单个进程的性能，但是会损害系统的并发度。
　　而使用锁定级别更高的锁，比如排他锁，能够提高事务的隔离级别；而降低锁定级别，比如不使用锁，能够提高系统的性能，但是会破坏

事务的完整性。

 

分布式事务

BEGIN DISTRIBUTED TRANSACTION
指定一个由 Microsoft 分布式事务处理协调器 (MS DTC) 管理的 Transact-SQL 分布式事务的起始。

语法
BEGIN DISTRIBUTED TRAN [ SACTION ]
[ transaction_name | @tran_name_variable ]

参数
transaction_name
是用户定义的事务名，用于跟踪 MS DTC 实用工具中的分布式事务。 transaction_name 必须符合标识符规则，但是仅使用头 32 个字符

@tran_name_variable
是用户定义的一个变量名，它含有一个事务名，该事务名用于跟踪 MS DTC 实用工具中的分布式事务。必须用 char、varchar、nchar 或

nvarchar 数据类型声明该变量。

注释
执行BEGIN DISTRIBUTED TRANSACTION 语句的服务器是事务创建人，并且控制事务的完成

当连接发出后续 COMMIT TRANSACTION 或 ROLLBACK TRANSACTION 语句时，
主控服务器请求 MS DTC 在所涉及的服务器间管理分布式事务的完成。
有两个方法可将远程 SQL 服务器登记在一个分布式事务中：

分布式事务中已登记的连接执行一个远程存储过程调用，该调用引用一个远程服务器。
分布式事务中已登记的连接执行一个分布式查询，该查询引用一个远程服务器。

示例
本例在本地和远程数据库上更新作者的姓。本地和远程数据库将同时提交或同时回滚本事务。

说明
当前的SQL Server 上必须安装 MS DTC.

USE pubs
GO
BEGIN DISTRIBUTED TRANSACTION
UPDATE authors
SET au_lname = 'McDonald' WHERE au_id = '409-56-7008'     --本地
EXECUTE link_Server_T.pubs.dbo.changeauth_lname '409-56-7008','McDonald'     --远程数据库
commit transaction
GONote:
如果需要连接远程DB,如果是linkServer 方式连接的话，一定要修该linkServer的 RPC 选项置为 True。