事物&索引&视图

虽然学过SQLServer，但是在脑海里总是觉得很乱，并且有很多疑问和盲点，如今走入工作岗位，那么这些关于自己知识和技术的漏洞是不应该存在了，至少学一种语言或是技术，最起码要知道是什么、做什么、有什么用、什么时候用、怎么用。如果连这一点都不是很清楚的话那么我不敢相信你做出来的东西有多好，有多强。以前在学习的时候，总觉得这些理论知识，自己知道就行了，不必太在意，会操作，能使用就行了，可是如今我不这么认为了，可能这些很基础的理论知识学起来很抽象，很枯燥，但是他们真的很有用。作为一名技术人员，没有清晰透彻的理论作为基础，那么在技术这条道路上是走不远，飞不高的。。。最近我在业余时间抽空复习自己所学的东西，找到一些不错的资料，在此分享给正在学习中的朋友们，我相信这些资源对你们的学习是很有用的。好了，废话有点多了，咱们开始吧。声明：一下内容是我转载的一些内容，稍作些许加工，以下有地址来源。。。

SQL教程: 事务

我们知道各种子查询的用法，包括简单子查询、IN子查询和EXISTS子查询。除此之外，我们在实际开发中还会用到一些比较特殊的高级查询，包括事务、索引和视图。

例如，银行转账问题：假定资金从账户A转到账户B，至少需要两步，即账户A的资金减少，然后账户B的资金相应增加。在进行资金转账时，系统必须保证：这些步骤是一个整体，如果其间任一步骤失败，则将撤销对这两个账户数据所做的任何修改，这时就需要使用事务处理。事务是指一个工作单元，该单元可以包含多个步骤来完成所需的任务。一个事务作为一个整体，要么成功，要么失败。

正如汉语字典中的汉字按页存放一样，SQL Server中的数据记录也是按页存放的，每页容量一般为4KB。为了加快查找的速度，汉语字（词）典一般都有按拼音、笔画、偏旁部首等排序的目录（索引），我们可以选择按拼音或笔画查找，快速查找到需要的字（词）。同理，SQL Server允许用户在表中创建索引，指定按某列预先排序，从而大大提高查询速度。

同一星球，用望远镜从不同的角度或方位观看，将看到星球的不同位置，从而得到不同的结果。同一张员工信息表数据，因为公司保密的原因，可能要求不同权限的人员看到不同的员工信息。例如：财务人员只能查看员工的姓名、工资、奖金等；技术部经理只能查看员工的姓名，职称、技能等；人事部经理只能查看员工的姓名、工作经历和发展方向等；总经理当然可以全部查看。如何更加安全、直观地显示数据结果呢？SQL Server中允许用户创建视图，在同一原始数据表的基础上，为不同的用户选择不同的列，从而达到不同用户的需求。

下面我们将详细讨论事务、索引和视图的具体使用。

事务(Transaction)是单个的工作单元。如果某一事务成功，则在该事务中进行的所有数据更改均会提交，成为数据库中的永久组成部分。如果事务遇到错误且必须取消或回滚，则所有数据更改均被清除。

一、为什么需要事务

一般来说，只要是同一银行（例如都是农行），一般都支持账户间直接转账。我们来看看上述提及的转账问题，假定张三的账户直接转账1000元到李四的账户，就需要创建账户表，存放用户的账户信息，T-SQL语句如示例1：

/*--举例：为什么需要事务--*/
--同一银行，如都是农行的帐号，可以直接转账
/*－--------------建表-----------------*/
USE stuDB
GO
 
--创建农行帐户表bank
IF EXISTS(SELECT * FROM sysobjects WHERE name='bank')
   DROP TABLE bank
GO
 
CREATE TABLE bank
(
    customerName CHAR(10), --顾客姓名
    currentMoney MONEY     --当前余额
)
GO
 
/*---添加约束：根据银行规定，帐户余额不能少于1元，除非销户----*/
ALTER TABLE bank
  ADD CONSTRAINT CK_currentMoney CHECK(currentMoney>=1)
GO
 
/*--插入测试数据：张三开户，开户金额为800 ；李四开户，开户金额1 ---*/
INSERT INTO bank(customerName,currentMoney) VALUES('张三',1000)
INSERT INTO bank(customerName,currentMoney) VALUES('李四',1)
GO
--查看结果
SELECT * FROM bank
 
GO
 
/*--转帐测试：张三希望通过转账，直接汇钱给李四1000元--*/
--我们可能会这样这样写代码
--张三的帐户少1000元，李四的帐户多1000元
UPDATE bank SET currentMoney=currentMoney-1000
     WHERE customerName='张三'
UPDATE bank SET currentMoney=currentMoney+1000
     WHERE customerName='李四'
GO
--再次查看结果，结果发现了什么严重的错误?如何解决呢？
SELECT * FROM bank
GO

 上面代码的输出结果如图1：

图1 张三、李四的账户信息

注意：目前两个账户的余额总和为：1000+1=1001元。

现在开始模拟实现转账：从张三的账户直接转账IOOO元到李四的账户。可以使用UPDATE语句修改张三的账户和李四的账户，张三的账户减少1OOO元，李四的账户增加1000元。WANGYEXX.COM

显然，转账后的余额总和应保持不变，仍为1001元。

T-SQL实现如示例2所示。

示例2：

/*--转账测试：张三转账1000元给李四--*/
--我们可能会这样这样编写语句
--张三的账户少1000元，李四的账户多1000元
UPDATE bank SET currentMoney=currentMoney-1000
     WHERE customerName='张三'
UPDATE bank SET currentMoney=currentMoney+1000
     WHERE customerName='李四'
GO
--再次查看转账后的结果。
SELECT * FROM bank
GO

 

上述语句的输出结果如图2所示。

输出的结果是张三的账户没有减少，还是1000元，但李四的账户却多了1000元，转账后两个账户的余额总和变为1000+1001=2001元，银行的钱凭空多出1000元！

 

为什么会这样呢？让我们一起分析出现如此错误的原因。

 

查看SQL Server给出的错误提示，显示UPDATE语句有错，执行时违反了CK_currentMoney约束，即余额不能少于1元。目前有两条UPDATE语句，哪条语句导致了此错误呢？显然是修改张三账户的UPDATE语句。因为张三的账户原有余额1000元，减少1000元后即为0元，违反了上述约束，所以终止执行，余额保持不变，仍为1000元。遗憾的是，后面的语句并没有中断执行，修改李四账户的UPDATE语句继续执行，李四的账户增加了1000元，变为1001元。所以两人账户的余额最终出现了图2所示的结果。

 

如何解决呢？使用事务，转账过程就是一个事务，它需要两条UPDATE语句来完成，这两条语句是一个整体。如果其中任何一条出现错误，则整个转账业务也应取消，两个账户中的余额应恢复到原来的数据，从而确保转账前和转账后的余额总和不变，即都是1001元。

二、什么是事务

事务是一种机制、一个操作序列，它包含了一组数据库操作命令，并且所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行，因此事务是一个不可分割的工作逻辑单元，在数据库系统上执行并发操作时事务是作为最小的控制单元来使用的。它特别适用于多用户同时操作的数据库系统。例如，航空公司的订票系统、银行、保险公司以及证券交易系统等。

事务是作为单个逻辑工作单元执行的一系列操作。一个逻辑工作单元必须有4个属性，即原子性（Atomicity）、一致性(Consistency)、隔离性(Isolation)及持久性（Durability），这些特性通常简称为ACID。

(1) 原子性（Atomicity）：事务是一个完整的操作。事务的各元素是不可分的（原子的）。事务中的所有元素必须作为一个整体提交或回滚。如果事务中的任何元素失败，则整个事务将失败。

再次以银行转账事务为例，如果该事务提交了，则这两个账户的数据将会更新。如果由于某种原因，事务在成功更新这两个账户之前终止，则不会更新这两个账户余额，并且会撤销对任何账户余额的修改。事务不能部分提交。

(2) 一致性（Consistency）：当事务完成时，数据必须处于一致状态。也就是说，在事务开始之前，数据库中存储的数据处于一致状态。在正在进行的事务中，数据可能处于不一致的状态，例如，数据可能有部分修改。然而，当事务成功完成时，数据必须再次回到己知的一致状态。通过事务对数据所做的修改不能损坏数据，或者说事务不能使数据存储处于不稳定的状态。

再次以银行转账事务为例。在事务开始之前，所有账户余额的总额处于一致状态。在事务进行的过程中，一个账户余额减少，而另一个账户余额尚未修改。因此，所有账户余额的总额处于不一致状态。事务完成以后，账户余额的总额再次恢复一致状态。

(3) 隔离性（Isolation）：对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务。修改数据的事务可以在另一个使用相同数据的事务开始之前访问这些数据，或者在另一个使用相同数据的事务结束之后访问这些数据。另外，当事务修改数据时，如果任何其他进程正在同时使用相同的数据，则直到该事务成功提交之后，对数据的修改才能生效。张三和李四之间的转账以及王五和赵二之间，永远是相互独立的。

(4) 持久性（Durability）：事务完成之后，它对于系统的影响是永久性的。该修改即使出现系统故障，也将一直保持。

三、如何创建事务

Transact-SQL使用下列语句来管理事务。

● 开始事务：BEGIN TRANSACTION。

● 提交事务：COIVIIVIIT TRANSACTION。

● 回滚（撤销）事务：ROLLBACK TRANSACTION。

事务的分类有以下3种。

● 显式事务：用BEGIN TRANSACTION明确指定事务的开始。

● 隐式事务：通过设置SET IMPLICIT TRANSACTIONS ON语句，将隐式事务模式设置为打开。当以隐式事务操作时，SQL Server将在提交或回滚事务后自动启动新事务。无法描述事务的开始，只需提交或回滚每个事务。

● 自动提交事务：这是SQL Server的默认模式，它将每条单独的T-SQL语句视为一个事务。如果成功执行，则自动提交。如果错误，则自动回滚。

实际开发中最常用的就是显式事务，它明确地指定事务的开始边界。

判断T-SQL语句是否有错，将使用到曾讲过的全局变量@@ERROR，它用来判断当前T-SQL语句执行是否有错误，若有错误则返回非零值。下面我们应用显式事务来解决上述转账问题，T-SQL语句如示例3所示。

示例3：

 

USE stuDB
GO
--恢复原来的数据
--UPDATE bank SET currentMoney=currentMoney-1000 WHERE customerName='李四'
SET NOCOUNT ON --不显示受影响的行数信息
print '查看转帐事务前的余额'
SELECT * FROM bank
GO
 
/*--开始事务（指定事务从此处开始，后续的T-SQL语句都是一个整体--*/
BEGIN TRANSACTION
/*--定义变量，用于累计事务执行过程中的错误--*/
DECLARE @errorSum INT 
SET @errorSum=0  --初始化为0，即无错误
 
/*--转帐：张三的帐户少1000元，李四的帐户多1000元*/
UPDATE bank SET currentMoney=currentMoney-800 WHERE customerName='张三'
SET @errorSum=@errorSum+@@error  --累计是否有错误
UPDATE bank SET currentMoney=currentMoney+800 WHERE customerName='李四'
SET @errorSum=@errorSum+@@error  --累计是否有错误
 
print '查看转帐事务过程中的余额'
SELECT * FROM bank
 
/*--根据是否有错误，确定事务是提交还是撤销---*/
IF @errorSum<>0  --如果有错误
  BEGIN
    print '交易失败，回滚事务'
    ROLLBACK TRANSACTION
  END
ELSE
  BEGIN
    print '交易成功，提交事务，写入硬盘，永久的保存'
    COMMIT TRANSACTION
  END
GO
 
print '查看转帐事务后的余额'
SELECT * FROM bank
GO

 

图3 事务处理：交易失败的情况

示例3中，我们将转账金额设置为1000元，因张三的账户余额为0，违反了约束而有错。如果我们修改转账金额为800元，则结果如图4所示。

图3 事务处理：交易成功的情况

 

说明：现实中银行的开户、转账问题比上述处理要更加复杂，如后续的项目案例所示。

转载请注明原文地址:http://www.wangyexx.com/db/sqlbasic/1460.html