SQL Server~事务

一.简述事务

1. 事务：是由一系列语句构成的逻辑工作单元。事务和存储过程等批处理有一定程度上的相似之处，通常都是为了完成一定业务逻辑而将一条或者多条语句“封装”起来，使它们与其他语句之间出现一个逻辑上的边界，并形成相对独立的一个工作单元。
2. 当使用事务修改多个数据表时，如果在处理的过程中出现了某种错误，例如，系统死机或突然断电等情况，则返回结果是数据全部没有被保存。因为事务处理的结果只有两种：一种是在事务处理的过程中，如果发生了某种错误则整个事务全部回滚，使所有对数据的修改全部撤销，事务对数据库的操作是单步执行的，当遇到错误时可以随时地回滚；另一种是如果没有发生任何错误且每一步的执行都成功，则整个事务全部被提交。可以看出，有效地使用事务不但可以提高数据的安全性，还可以增强数据的处理效率。
3. 事务包含4种重要的属性，被统称为ACID（原子性、一致性、隔离性和持久性），一个事务必须通过ACID测试。
   （1）原子性（Atomic）：事务是一个整体的工作单元，事务对数据库所做的操作要么全部执行，要么全部取消。如果某条语句执行失败，则所有语句全部回滚。
   （2）一致性（ConDemoltent）：事务在完成时，必须使所有的数据都保持一致状态。在相关数据库中，所有规则都必须应用于事务的修改，以保持所有数据的完整性。如果事务成功，则所有数据将变为一个新的状态；如果事务失败，则所有数据将处于开始之前的状态。
   （3）隔离性（Isolated）：由事务所作的修改必须与其他事务所作的修改隔离。事务查看数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态的数据。
   （4）持久性（Durability）：当事务提交后，对数据库所做的修改就会永久保存下来。

二.定义显式事务

显式事务是用户自定义或用户指定的事务。可以通过BEGIN TRANSACTION、COMMIT TRANSACTION、COMMIT WORK、ROLLBACK TRANSACTION或ROLLBACK WORK事务处理语句定义显式事务。下面将简单介绍以上几种事务处理语句的语法和参数。

BEGIN TRANSACTION语句

用于启动一个事务，它标志着事务的开始。

1. 语法如下：
   BEGIN TRAN [ SACTION ] [ TRANSACTION_NAME | @TRAN_NAME_VARIABLE[ WITH MARK [ ‘DESCRIPTION’ ] ] ]
2. 参数说明：
   （1）TRANSACTION_NAME表示设定事务的名称，但是不允许标识符多于 32 个字符。
   （2）@TRAN_NAME_VARIABLE表示用户定义的、含有有效事务名称的变量名称，必须用CHAR、VARCHAR、NCHAR或NVARCHAR数据类型声明该变量。
   93WITH MARK [ ‘DESCRIPTION’ ]表示指定在日志中标记事务，DESCRIPTION 是描述该标记的字符串。

COMMIT TRANSACTION语句

用于标志一个成功的隐性事务或用户定义事务的结束。

1. 语法如下：
   COMMIT [ TRAN [ SACTION ] [ TRANSACTION_NAME | @TRAN_NAME_VARIABLE ] ]
2. 参数说明：
   （1）TRANSACTION_NAME表示此参数指定由前面的BEGIN TRANSACTION指派的事务名称，此处的事务名称仅用来帮助程序员阅读，以及指明COMMIT TRANSACTION与哪些嵌套的BEGIN TRANSACTION相关联。
   （2）@TRAN_NAME_VARIABLE表示用户定义的、含有有效事务名称的变量名称，必须用CHAR、VARCHAR、NCHAR或NVARCHAR数据类型声明该变量。

COMMIT WORK语句

用于标志事务的结束。

1. 语法如下：
   COMMIT [WORK]
   此语句的功能与COMMIT TRANSACTION相同，但COMMIT TRANSACTION接受用户定义的事务名称。

ROLLBACK TRANSACTION语句

用于将显式事务或隐性事务回滚到事务的起点或事务内的某个保存点。当执行事务的过程中发生某种错误，可以使用ROLLBACK TRANSACTION语句或ROLLBACK WORK语句，使数据库撤销在事务中所做的更改，并使数据恢复到事务开始之前的状态。

1. 语法如下：
   ROLLBACK [ TRAN [ SACTION ] [ TRANSACTION_NAME | @TRAN_NAME_VARIABLE| SAVEPOINT_NAME | @SAVEPOINT_VARIABLE ] ]
2. 参数说明：
   （1）TRANSACTION_NAME是给BEGIN TRANSACTION上的事务指派的名称。
   （2）@TRAN_NAME_VARIABLE表示用户定义的、含有有效事务名称的变量名称，必须用CHAR、VARCHAR、NCHAR 或 NVARCHAR 数据类型声明该变量。
   （3）SAVEPOINT_NAME是来自SAVE TRANSACTION语句对保存点的定义，当条件回滚只影响事务的一部分时使用SAVEPOINT_NAME。
   （4）@SAVEPOINT_VARIABLE表示用户定义的、含有有效保存点名称的变量名称。必须用CHAR、VARCHAR、NCHAR 或 NVARCHAR数据类型声明该变量。

ROLLBACK WORK语句

用于将用户定义的事务回滚到事务的起点。

1. 语法如下：
   ROLLBACK [WORK]
   此语句的功能与ROLLBACK TRANSACTION相同，除非ROLLBACK TRANSACTION接受用户定义的事务名称。

三.设置隐式事务

1. 隐性事务需要使用SET IMPLICIT_TRANSACTIONS ON语句将隐性事务模式设置为打开。在打开了隐性事务的设置开关时，执行下一条语句时自动启动一个新事务，并且每关闭一个事务时，执行下一条语句又会启动一个新事务，直到关闭了隐性事务的设置开关。
2. SQL Server的任何数据修改语句都是隐性事务，例如：ALTER TABLE、CREATE、DELETE、DROP、FETCH、GRANT、INSERT、OPEN、REVOKE、SELECT、TRUNCATE TABLE、UPDATE。这些语句都可以作为一个隐性事务的开始。如果要结束隐性事务，需要使用COMMIT TRANSACTION或ROLLBACK TRANSACTION语句来结束事务。

四.在API中控制隐式事务

用来设置隐式事务的API机制是ODBC和OLE DB。

1. ODBC
   （1）调用SQLSETCONNECTATTR函数启动隐式事务模式，其中ATTRIBUTE设置为SQL_ATTR_ AUTOCOMMIT，VALUEPTR设置为SQL_AUTOCOMMIT_OFF。
   （2）在调用SQLSETCONNECTATTR之前，连接将一直保持为隐式事务模式，其中ATTRIBUTE设置为SQL_ATTR_AUTOCOMMIT，VALUEPTR设置为SQL_AUTOCOMMIT_ON。
   （3）调用SQLENDTRAN函数提交或回滚每个事务，其中COMPLETIONTYPE设置为SQL_COMMIT或SQL_ROLLBACK。
2. OLE DB
   OLE DB没有专门用来设置隐式事务模式的方法。
   （1）调用ITRANSACTIONLOCAL::STARTTRANSACTION方法启动显式模式。
   （2）当调用ITRANSACTION::COMMIT或ITRANSACTION::ABORT方法（其中，FRETAINING设置为TRUE）时，OLE DB将完成当前的事务并进入隐式事务模式。只要ITRANSACTION::COMMIT或ITRANSACTION::ABORT中的FRETAINING设置为TRUE，那么连接就将保持隐式事务模式。
   （3）调用ITRANSACTION::COMMIT或ITRANSACTION::ABORT（其中FRETAINING设置为FALSE）停止隐式事务模式。

五.提交和回滚事务

结束事务包括“成功时提交事务”和“失败时回滚事务”两种情况，在Transact-SQL中可以使用COMMIT和ROLLBACK结束事务。
（1）COMMIT
提交事务，用在事务执行成功的情况下。COMMIT语句保证事务的所有修改都被保存，同时COMMIT语句也释放事务中使用的资源，例如，事务使用的锁。
（2）ROLLBACK
回滚事务，用于事务在执行失败的情况下，将显式事务或隐式事务回滚到事务的起点或事务内的某个保存点。

六.开始事务

当一个数据库连接启动事务时，在该连接上执行的所有Transact-SQL语句都是事务的一部分，直到事务结束。开始事务使用BEGIN TRANSACTION语句。下面将以示例的形式演示如何在SQL中使用开始事务。
例 ：
使用事务修改“Employee”表中的数据，首先使用BEGIN TRANSACTION语句启动事务“update_data”，然后修改指定条件的数据，最后使用COMMIT TRANSACTION提交事务，SQL语句及运行结果。

在例中，BEGIN TRANSACTION语句指定一个事务的开始，update_data语句为事务名称，它可由用户自定义，但必须是有效的标识符。COMMIT TRANSACTION语句指定事务的结束。
说明：
BEGIN TRANSACTION与COMMIT TRANSACTION之间的语句，可以是任何对数据库进行修改的语句。

七.结束事务

当一个事务执行完成之后要将其结束,以便释放所占用的内存资源，结束事务使用COMMIT语句。
例：使用事务在“Employee”表中添加一条记录，并使用COMMIT语句结束事务，SQL语句及运行结果。

在例中，使用了@@ERROR函数，此函数用于判断最后的Transact-SQL语句是否执行成功。此函数有两个返回值，如果此语句执行成功，则@@ERROR返回0；如果此语句产生错误，则@@ERROR返回错误号。每一个Transact-SQL语句完成时，@@ERROR的值都会改变。

八.回滚事务

使用ROLLBACK TRANSACTION语句可以将显式事务或隐式事务回滚到事务的起点或事务内的某个保存点。

1. 语法如下：
   ROLLBACK { TRAN | TRANSACTION }
   [ TRANSACTION_NAME | @TRAN_NAME_VARIABLE
   | SAVEPOINT_NAME | @SAVEPOINT_VARIABLE ]
   [ ; ]
2. 参数说明：
   （1）TRANSACTION_NAME是为BEGIN TRANSACTION上的事务分配的名称（即事务名称），它必须符合标识符规则，但只使用事务名称的前32个字符，当嵌套事务时，TRANSACTION_NAME必须是最外面的BEGIN TRANSACTION语句中的名称。
   （2）@TRAN_NAME_VARIABLE是用户定义的、包含有效事务名称的变量的名称，它必须用CHAR、VARCHAR、NCHAR 或 NVARCHAR数据类型声明变量。
   （3）SAVEPOINT_NAME是SAVE TRANSACTION语句中的SAVEPOINT_NAME（即保存点的名称），SAVEPOINT_NAME必须符合标识符规则，当条件回滚应只影响事务的一部分时，可使用SAVEPOINT_NAME。
   （4）@SAVEPOINT_VARIABLE是用户定义的、包含有效保存点名称的变量的名称，它必须用CHAR、VARCHAR、NCHAR 或 NVARCHAR数据类型声明变量。

在ROLLBACK TRANSACTION语句中用到了保存点，通常使用SAVE TRANSACTION语句在事务内设置保存点。

1. 语法如下：
   SAVE { TRAN | TRANSACTION } { SAVEPOINT_NAME | @SAVEPOINT_VARIABLE }[ ; ]
2. 参数说明：
   （1）SAVEPOINT_NAME是保存点的名称，它必须符合标识符规则。当条件回滚应只影响事务的一部分时，可使用SAVEPOINT_NAME。
   （2）@SAVEPOINT_VARIABLE是用户定义的、包含有效保存点名称的变量的名称，它必须用CHAR、VARCHAR、NCHAR 或 NVARCHAR数据类型声明变量。

九.事务的工作机制

下面将通过一个示例讲解事务的工作机制。
例：使用事务修改Employee表中的数据，并将指定的员工记录删除，SQL语句及运行结果

此例子的功能是修改“Employee”表中的员工信息，并将指定的员工记录删除，其事务的工作机制可以分为以下几点：
（1）当在代码中出现BEGIN TRANSACTION语句时，SQL Server将会显示事务，并会给新事务分配一个事务ID。
（2）当事务开始后，SQL Server将会运行事务体语句，并将事务体语句记录到事务日志中。
（3）在内存中执行事务日志中所记录的事务体语句。
（4）当执行到COMMIT语句时会结束事务，同时事务日志也会被写到数据库的日志设备上，从而保证日志可以被恢复。

十.自动提交事务

自动提交事务是SQL Server默认的事务处理方式，当任何一条有效的SQL语句被执行后，它对数据库所做的修改都将会被自动提交，如果发生错误，则将会自动回滚并返回错误信息。
例：使用INSERT语句向数据库中添加3条记录，但由于添加了重复的主键，导致最后一条INSERT语句在编译时产生错误，从而使这条语句没有被执行，SQL语句及运行结果。

在此例中，SQL Server将前两条记录添加到了指定的数据表中，而将第三条记录回滚，这是因为第三条记录出现编译错误并且不符合条件（主键不允许重复），所以被事务回滚。

十一.事务的并发问题

事务的并发问题主要体现在丢失或覆盖更新、未确认的相关性（脏读）、不一致的分析（不可重复读）和幻象读4个方面，这些是影响事务完整性的主要因素。如果没有锁定且多个用户同时访问一个数据库，则当他们的事务同时使用相同的数据时可能会发生以上几种问题。下面将一一说明。

1. 丢失更新
   （1）当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，会发生丢失更新问题。每个事务都不知道其他事务的存在。最后的更新将重写由其他事务所做的更新，这样就会导致数据丢失。
   （2）例如，最初有一份原始的电子文档，文档人员A和B同时修改此文档，当修改完成之后保存时，最后修改完成的文档必将替换第一个修改完成的文档，那么就造成了数据丢失更新的后果。如果文档人员A修改并保存之后，文档人员B再进行修改则可以避免该问题。
2. 未确认的相关性（脏读）
   （1）如果一个事务读取了另外一个事务尚未提交的更新，则称为脏读。
   （2）例如，文档人员B复制了文档人员A正在修改的文档，并将文档人员A的文档发布，此后，文档人员A认为文档中存在着一些问题需要重新修改，此时文档人员B所发布的文档就将与重新修改的文档内容不一致。如果文档人员A将文档修改完成并确认无误的情况下，文档人员B再复制则可以避免该问题。
3. 不一致的分析（不可重复读）
   （1）当事务多次访问同一行数据，并且每次读取的数据不同时，将会发生不一致分析问题。不一致的分析与未确认的相关性类似，因为其他事务也正在更改该数据。然而，在不一致的分析中，事务所读取的数据是由进行了更改的事务提交的。而且，不一致的分析涉及多次读取同一行，并且每次信息都由其他事务更改，因而该行被不可重复读取。
   （2）例如，文档人员B两次读取文档人员A的文档，但在文档人员B读取时，文档人员A又重新修改了该文档中的内容，在文档人员B第二次读取文档人员A的文档时，文档中的内容已被修改，此时则发生了不可重复读的情况。如果文档人员B在文档人员A全部修改后读取文档，则可以避免该问题。
4. 幻象读
   （1）幻象读和不一致的分析有些相似，当一个事务的更新结果影响到另一个事务时，将会发生幻象读问题。事务第一次读的行范围显示出其中一行已不复存在于第二次读或后续读中，因为该行已被其他事务删除。同样，由于其他事务的插入操作，事务的第二次或后续读显示有一行已不存在于原始读中。
   （2）例如，文档人员B更改了文档人员A所提交的文档，但当文档人员B将更改后的文档合并到主副本时，却发现文档人员A已将新数据添加到该文档中。如果文档人员B在修改文档之前，没有任何人将新数据添加到该文档中，则可以避免该问题。

十二.隔离级别

1. 当事务接受不一致的数据级别时被称为事务的隔离级别。如果事务的隔离级别比较低，会增加事务的并发问题，有效地设置事务的隔离级别可以降低并发问题的发生。
2. 设置隔离数据可以使一个进程使用，同时还可以防止其他进程的干扰。设置隔离级别定义了SQL SERVER会话中所有SELECT语句的默认锁定行为，当锁定用作并发控制机制时，它可以解决并发问题。这使所有事务得以在彼此完全隔离的环境中运行，但是任何时候都可以有多个正在运行的事务。
3. 在SQL SERVER中，可以使用SET TRANSACTION ISOLATION LEVEL语句来设置事务的隔离级别。
4. SET TRANSACTION ISOLATION LEVEL：控制由连接发出的所有SELECT语句的默认事务锁定行为。
5. 语法如下：
   SET TRANSACTION ISOLATION LEVEL{ READ COMMITTED | READ UNCOMMITTED | REPEATABLE READ | SERIALIZABLE}
6. 参数说明：
   （1）READ COMMITTED指定在读取数据时控制共享锁以避免脏读，但数据可在事务结束前更改，从而产生不可重复读取或幻象读取数据，该选项是SQL SERVER的默认值。
   （2）READ UNCOMMITTED执行脏读或0级隔离锁定，这表示不发出共享锁，也不接受排它锁，该选项的作用与在事务内所有语句中的所有表上设置NOLOCK相同，这是4个隔离级别中限制最小的级别。
   （3）REPEATABLE READ锁定查询中使用的所有数据以防止其他用户更新数据，但是其他用户可以将新的幻象行插入数据集，且幻象行包括在当前事务的后续读取中，因为并发低于默认隔离级别，所以应只在必要时才使用该选项。
   （4）SERIALIZABLE表示在数据集上放置一个范围锁，以防止其他用户在事务完成之前更新数据集或将行插入数据集内。
7. SQL Server提供了4种事务的隔离级别
   
8. SQL Server的默认隔离级别为Read Committed，可以使用锁来实现隔离性级别。
   （1）Read Uncommitted（未提交读）
   此隔离级别为隔离级别中最低的级别，如果将SQL Server的隔离级别设置为Read Uncommitted，则可以对数据执行未提交读或脏读，并且等同于将锁设置为NOLOCK。
   例：设置未提交读隔离级别，SQL语句及运行结果。
   BEGIN TRANSACTION
   UPDATE Employee SET Name = ‘章子婷’
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED --设置未提交读隔离级别
   COMMIT TRANSACTION
   SELECT * FROM Employee
   
   （2）Read Committed（提交读）
   此项隔离级别为SQL中默认的隔离级别，将事务设置为此级别，可以在读取数据时控制共享锁以避免脏读，从而产生不可重复读取或幻象读取数据。
   例：设置提交读隔离级别，SQL语句及运行结果。
   SET TRANSACTION ISOLATION LEVEL Read Committed
   BEGIN TRANSACTION
   SELECT * FROM Employee
   ROLLBACK TRANSACTION
   SET TRANSACTION ISOLATION LEVEL Read Committed --设置提交读隔离级别
   UPDATE Employee SET Name = '高丽‘
   
   （3）Repeatable Read（可重复读）
   此项隔离级别增加了事务的隔离级别，将事务设置为此级别可以防止脏读、不可重复读和幻象读。
   例：设置可重复读隔离级别，SQL语句及运行结果。
   SET TRANSACTION ISOLATION LEVEL Repeatable Read
   BEGIN TRANSACTION
   SELECT * FROM Employee
   ROLLBACK TRANSACTION
   SET TRANSACTION ISOLATION LEVEL Repeatable Read --设置可重复读隔离级别
   INSERT INTO Employee values (18,‘张雨’,‘男’,‘22’,‘明日科技有限公司’)
   
   （4）Serializable（可串行读）
   此项隔离级别是所有隔离级别中限制最大的级别，它防止了所有的事务并发问题，此级别可以适用于绝对的事务完整性的要求。
   例：设置可串行读隔离级别，SQL语句及运行结果。
   SET TRANSACTION ISOLATION LEVEL Serializable
   BEGIN TRANSACTION
   SELECT * FROM Employee
   ROLLBACK TRANSACTION
   SET TRANSACTION ISOLATION LEVEL Serializable --设置可串行读
   DELETE FROM Employee WHERE ID = 1