SQLServer事务和并发控制
合集下载
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
/* After having updated the royaltyper entries for the two authors, the user inserts the savepoint percentchanged, and then determines how a 10-percent increase in the book's price would affect the authors' royalty earnings. */
8.1.2 事务对并发控制和保障 数据完整的重要性
• 2．事务对保障数据一致和完整性的作用 • 故障轻则造成运行事务非正常中断，影响数据库中 数据的正确性，重则破坏数据库，使数据库中全部 或部分数据丢失 。 • 影响事务正常运行的故障有：
• （1）事务内部的故障
• （2）系统故障 • （3）介质故障 • （4）计算机病毒
SELECT * FROM t2 GO
DROP TABLE t2 DROP TABLE t1 GO
2、事务和批的区别
•
•
问题：如何知道XACT_ABORT为ON还是OFF呢？
XACT_ABORT的默认值是什么？
2、事务和批的区别
• 问题：如何知道XACT_ABORT为ON还是OFF呢？
DBCC Uet if it's ON. If it's OFF then it will not show
8.2 事务的分类和控制
8.2.1 事务的分类 8.2.2 事务控制
• SQL Server 的事务模式可分为显式事务、隐式事 务和自动事务三种。 • 1) 显式事务 • 显式事务是指由用户执行T-sql事务语句而定义的事 务，这类事务又称做用户定义事务。定义事务的语 句包括： • BEGIN TRANSACTION:标识一个事务的开始，即 启动事务。 • COMMIT TRANSACTION、COMMIT WORK： 标识一个事务的结束，事务内所修改的数据被永久 保存到数据库中。 • ROLLBACK TRANSACTION、ROLLBACK WORK： 标识一个事务的结束，说明事务执行过程中遇到错 误，事务内所修改的数据被回滚到事务执行前的状 态。
2、事务和批的区别
•
GO
SET XACT_ABORT ON
BEGIN TRAN INSERT INTO t2 VALUES (4) INSERT INTO t2 VALUES (5) /* Foreign key error */ INSERT INTO t2 VALUES (6) COMMIT TRAN GO /* Select shows only keys 1 and 3 added. Key 2 insert failed and was rolled back, but XACT_ABORT was OFF and rest of transaction succeeded. Key 5 insert error with XACT_ABORT ON caused all of the second transaction to roll back. */
用于在事务中设置一个保存点， SAVE 目的是在撤消事务时可以只 TRANSACTION 撤消部分事务，以提高系统 的效率
SAVE TRANsaction <保存点>
说明要撤消事务，即撤消在该事 务中对数据库所做的更新操 ROLLBACK TRANsaction [<事务名>| < ROLLBACK 作，使数据库回退到 BEGIN TRANSACTION 保存点>] TRANSACTION或保存点之 前的状态
• •
DBCC USEROPTIONS
返回当前连接的活动（设置）的 SET 选项。
看看VB的例子：运行DBCC（顺便看一下联机帮助文 档）并查看结果
2、事务和批的区别
•
•
SET XACT_ABORT的默认值是什么？
对每个连接来说， SET XACT_ABORT的默 认值是OFF
•
看看VB的例子和查询分析器
2、事务和批的区别
•
•
指定当 Transact-SQL 语句产生运行时错误时，Microsoft® SQL Server™ 是 否自动回滚当前事务。
SET XACT_ABORT
语法
•
当 SET XACT_ABORT 为 ON 时，如果 Transact-SQL 语句产生运 行时错误，整个事务将终止并回滚。为 OFF 时，只回滚产生错误的 Transact-SQL 语句，而事务将继续进行处理。编译错误（如语法错 误）不受 SET XACT_ABORT 的影响。 对于大多数 OLE DB 提供程序（包括 SQL Server），隐性或显式事 务中的数据修改语句必须将 XACT_ABORT 设置为 ON。唯一不需要 该选项的情况是提供程序支持嵌套事务时。
要么全部执行，要么全部不执行。遇到 错误时，可以回滚事务，取消事务内所 做的所有改变，从而保证数据库中数据 的一致性和可恢复性。
1、事务的特性(ACID)
原子性(Atomicity)：事务是数据库的逻辑工作单位， 事务中的操作要么都做，要么都不做。
一致性(Consistency)：事务执行的结果必须是使数据 库从一个一致性状态变到另一个一致性状态。 隔离性(Isolation)：一事务的执行不能被其它事务干 扰。
8.2.1 事务的分类
• 2) 隐式事务
• 在隐式事务模式下，在当前事务提交或回滚后，SQL Server自动开始下一 个事务。所以，隐式事务不需要使用BEGIN TRANSACTION语句启动事 务，而只需要用户使用ROLLBACK TRANSACTION、ROLLBACK WORK、 COMMIT TRANSACTION、COMMIT WORK等语句提交或回滚事务。 在提交或回滚后，SQL Server自动开始下一个事务。 执行SET IMPLICIT_TRANSACTIONS ON语句可使SQL Server进入隐式事务模式。 在隐式事务模式下，当执行下面任意一个语句时，可使SQL Server重新启 动一个事务： 所有CREATE语句 ALTER TABLE 所有DROP语句 TRUNCATE TABLE GRANT REVOKE INSERT UPDATE DELETE SELECT OPEN FETCH 需要关闭隐式事务模式时，调用SET语句关闭 IMPLICIT_TRANSACTIONS OFF连接选项即可。
Save transaction示例（略）1：
下例更改分给 The Gourmet Microwave 的两位作者的版税。数据库将会在两个更新间不一致，因 此必须将它们分组为用户定义的事务。
BEGIN TRANSACTION royaltychange UPDATE titleauthor SET royaltyper = 65 FROM titleauthor, titles WHERE royaltyper = 75 AND titleauthor.title_id = titles.title_id AND title = 'The Gourmet Microwave' UPDATE titleauthor SET royaltyper = 35 FROM titleauthor, titles WHERE royaltyper = 25 AND titleauthor.title_id = titles.title_id AND title = 'The Gourmet Microwave' SAVE TRANSACTION percentchanged
持续性（永久性） (Durability) ：指事务一旦提交， 则其对数据库中数据的改变就应该是永久的
2、事务和批的区别 • 编程时，一定要区分事务和批的差别： 1. 批是一组整体编译的SQL语句，事务是一组作为 单个逻辑工作单元执行的SQL语句。 2. 批语句的组合发生在编译时刻，事务中语句的组 合发生在执行时刻。 3. 当在编译时，批中某个语句存在语法错误，系统 将取消整个批中所有语句执行，而在运行时刻， 如果事务中某个数据修改违反约束、规则等，系 统默认只回退到产生该错误的语句。 4. 如果批中产生一个运行时错误，系统默认只回退 到产生该错误的语句。但当打开XACT_ABORT 选项为ON时，可以系统自动回滚产生该错误的 当前事务。 • 一个事务中也可以拥有多个批，一个批里可以有 多个SQL语句组成的事务，事务内批的多少不影 响事务的提交或回滚操作。
CREATE TABLE t1 (a int PRIMARY KEY) CREATE TABLE t2 (a int REFERENCES t1(a)) GO INSERT INTO t1 VALUES (1) INSERT INTO t1 VALUES (3) INSERT INTO t1 VALUES (4) INSERT INTO t1 VALUES (6) GO SET XACT_ABORT OFF GO BEGIN TRAN INSERT INTO t2 VALUES (1) INSERT INTO t2 VALUES (2) /* Foreign key error */ INSERT INTO t2 VALUES (3) COMMIT TRAN GO
示例：
• BEGIN TRAN demo • SELECT * FROM Student • INSERT INTO Student • VALUES(‘9711112’,’张三’, … ) • SELECT * FROM Student • ROLLBACK -- 回滚整个事务 • 或： COMMIT -- 提交事务
• • • • • • •
• 3) 自动事务模式
• 在自动事务模式下，当一个语句被成功执行后，它 被自动提交，而当它执行过程中产生错误时，被自 动回滚。自动事务模式是SQL Server的默认事务 管理模式，当与SQL Server建立连接后，直接进入 自动事务模式，直到使用BEGIN TRANSACTION语句 开始一个显式事务，或者打开 IMPLICIT_TRANSACTIONS 连接选项进入隐式事务模 式为止。 • 而当显式事务被提交或IMPLICIT_TRANSACTIONS 被关闭后，SQL Server又进入自动事务管理模式。 • 看看VB的例子
8.1.2 事务对并发控制和保障 数据完整的重要性
• 1．事务与并发控制的关系 • 如果在用户并发访问期间没有保证多个事务正确 的交叉运行，用户操作相同的数据时可能会产生 一些意想不到的问题。包括： • （1）丢失修改或被覆盖 （火车票） • （2）读脏数据 （银行利息） • （3）不能重复读 （学生成绩） • （4）幻影读
8.2.2 事务控制
• SQL Server中有关事务的处理语句有：
命令名 作用 格式 BEGIN TRANsaction [<事务名>] COMMIT TRANsaction [<事务名>]
BEGIN 说明一个事务开始 TRANSACTION COMMIT 说明一个事务结束，它的作用是 TRANSACTION 提交或确认事务已经完成
SET XACT_ABORT { ON | OFF }
•
2、事务和批的区别
• 下例导致在含有其它 Transact-SQL 语句的事务中发生违反外键错误。 在第一个语句集中产生错误，但其它语句均成功执行且事务成功提交。 在第二个语句集中，SET XACT_ABORT 设置为 ON。这导致语句错 误使批处理终止，并使事务回滚。
第8章
SQL Server 事务和并发控制
8.1 事务 8.2 事务的分类和控制 8.3 并发控制
8.4 事务处理实例分析
8.5 分布式事务
8.6 并发控制
8.1
事务
8.1.1 事务的概念 8.1.2 事务对并发控制和保障数据完整的重 要性
8.1.1 事务的概念 • 事务是一个用户定义的完整的工作单元， 一个事务内的所有语句被作为整体执行，
8.1.2 事务对并发控制和保障 数据完整的重要性
• 2．事务对保障数据一致和完整性的作用 • 故障轻则造成运行事务非正常中断，影响数据库中 数据的正确性，重则破坏数据库，使数据库中全部 或部分数据丢失 。 • 影响事务正常运行的故障有：
• （1）事务内部的故障
• （2）系统故障 • （3）介质故障 • （4）计算机病毒
SELECT * FROM t2 GO
DROP TABLE t2 DROP TABLE t1 GO
2、事务和批的区别
•
•
问题：如何知道XACT_ABORT为ON还是OFF呢？
XACT_ABORT的默认值是什么？
2、事务和批的区别
• 问题：如何知道XACT_ABORT为ON还是OFF呢？
DBCC Uet if it's ON. If it's OFF then it will not show
8.2 事务的分类和控制
8.2.1 事务的分类 8.2.2 事务控制
• SQL Server 的事务模式可分为显式事务、隐式事 务和自动事务三种。 • 1) 显式事务 • 显式事务是指由用户执行T-sql事务语句而定义的事 务，这类事务又称做用户定义事务。定义事务的语 句包括： • BEGIN TRANSACTION:标识一个事务的开始，即 启动事务。 • COMMIT TRANSACTION、COMMIT WORK： 标识一个事务的结束，事务内所修改的数据被永久 保存到数据库中。 • ROLLBACK TRANSACTION、ROLLBACK WORK： 标识一个事务的结束，说明事务执行过程中遇到错 误，事务内所修改的数据被回滚到事务执行前的状 态。
2、事务和批的区别
•
GO
SET XACT_ABORT ON
BEGIN TRAN INSERT INTO t2 VALUES (4) INSERT INTO t2 VALUES (5) /* Foreign key error */ INSERT INTO t2 VALUES (6) COMMIT TRAN GO /* Select shows only keys 1 and 3 added. Key 2 insert failed and was rolled back, but XACT_ABORT was OFF and rest of transaction succeeded. Key 5 insert error with XACT_ABORT ON caused all of the second transaction to roll back. */
用于在事务中设置一个保存点， SAVE 目的是在撤消事务时可以只 TRANSACTION 撤消部分事务，以提高系统 的效率
SAVE TRANsaction <保存点>
说明要撤消事务，即撤消在该事 务中对数据库所做的更新操 ROLLBACK TRANsaction [<事务名>| < ROLLBACK 作，使数据库回退到 BEGIN TRANSACTION 保存点>] TRANSACTION或保存点之 前的状态
• •
DBCC USEROPTIONS
返回当前连接的活动（设置）的 SET 选项。
看看VB的例子：运行DBCC（顺便看一下联机帮助文 档）并查看结果
2、事务和批的区别
•
•
SET XACT_ABORT的默认值是什么？
对每个连接来说， SET XACT_ABORT的默 认值是OFF
•
看看VB的例子和查询分析器
2、事务和批的区别
•
•
指定当 Transact-SQL 语句产生运行时错误时，Microsoft® SQL Server™ 是 否自动回滚当前事务。
SET XACT_ABORT
语法
•
当 SET XACT_ABORT 为 ON 时，如果 Transact-SQL 语句产生运 行时错误，整个事务将终止并回滚。为 OFF 时，只回滚产生错误的 Transact-SQL 语句，而事务将继续进行处理。编译错误（如语法错 误）不受 SET XACT_ABORT 的影响。 对于大多数 OLE DB 提供程序（包括 SQL Server），隐性或显式事 务中的数据修改语句必须将 XACT_ABORT 设置为 ON。唯一不需要 该选项的情况是提供程序支持嵌套事务时。
要么全部执行，要么全部不执行。遇到 错误时，可以回滚事务，取消事务内所 做的所有改变，从而保证数据库中数据 的一致性和可恢复性。
1、事务的特性(ACID)
原子性(Atomicity)：事务是数据库的逻辑工作单位， 事务中的操作要么都做，要么都不做。
一致性(Consistency)：事务执行的结果必须是使数据 库从一个一致性状态变到另一个一致性状态。 隔离性(Isolation)：一事务的执行不能被其它事务干 扰。
8.2.1 事务的分类
• 2) 隐式事务
• 在隐式事务模式下，在当前事务提交或回滚后，SQL Server自动开始下一 个事务。所以，隐式事务不需要使用BEGIN TRANSACTION语句启动事 务，而只需要用户使用ROLLBACK TRANSACTION、ROLLBACK WORK、 COMMIT TRANSACTION、COMMIT WORK等语句提交或回滚事务。 在提交或回滚后，SQL Server自动开始下一个事务。 执行SET IMPLICIT_TRANSACTIONS ON语句可使SQL Server进入隐式事务模式。 在隐式事务模式下，当执行下面任意一个语句时，可使SQL Server重新启 动一个事务： 所有CREATE语句 ALTER TABLE 所有DROP语句 TRUNCATE TABLE GRANT REVOKE INSERT UPDATE DELETE SELECT OPEN FETCH 需要关闭隐式事务模式时，调用SET语句关闭 IMPLICIT_TRANSACTIONS OFF连接选项即可。
Save transaction示例（略）1：
下例更改分给 The Gourmet Microwave 的两位作者的版税。数据库将会在两个更新间不一致，因 此必须将它们分组为用户定义的事务。
BEGIN TRANSACTION royaltychange UPDATE titleauthor SET royaltyper = 65 FROM titleauthor, titles WHERE royaltyper = 75 AND titleauthor.title_id = titles.title_id AND title = 'The Gourmet Microwave' UPDATE titleauthor SET royaltyper = 35 FROM titleauthor, titles WHERE royaltyper = 25 AND titleauthor.title_id = titles.title_id AND title = 'The Gourmet Microwave' SAVE TRANSACTION percentchanged
持续性（永久性） (Durability) ：指事务一旦提交， 则其对数据库中数据的改变就应该是永久的
2、事务和批的区别 • 编程时，一定要区分事务和批的差别： 1. 批是一组整体编译的SQL语句，事务是一组作为 单个逻辑工作单元执行的SQL语句。 2. 批语句的组合发生在编译时刻，事务中语句的组 合发生在执行时刻。 3. 当在编译时，批中某个语句存在语法错误，系统 将取消整个批中所有语句执行，而在运行时刻， 如果事务中某个数据修改违反约束、规则等，系 统默认只回退到产生该错误的语句。 4. 如果批中产生一个运行时错误，系统默认只回退 到产生该错误的语句。但当打开XACT_ABORT 选项为ON时，可以系统自动回滚产生该错误的 当前事务。 • 一个事务中也可以拥有多个批，一个批里可以有 多个SQL语句组成的事务，事务内批的多少不影 响事务的提交或回滚操作。
CREATE TABLE t1 (a int PRIMARY KEY) CREATE TABLE t2 (a int REFERENCES t1(a)) GO INSERT INTO t1 VALUES (1) INSERT INTO t1 VALUES (3) INSERT INTO t1 VALUES (4) INSERT INTO t1 VALUES (6) GO SET XACT_ABORT OFF GO BEGIN TRAN INSERT INTO t2 VALUES (1) INSERT INTO t2 VALUES (2) /* Foreign key error */ INSERT INTO t2 VALUES (3) COMMIT TRAN GO
示例：
• BEGIN TRAN demo • SELECT * FROM Student • INSERT INTO Student • VALUES(‘9711112’,’张三’, … ) • SELECT * FROM Student • ROLLBACK -- 回滚整个事务 • 或： COMMIT -- 提交事务
• • • • • • •
• 3) 自动事务模式
• 在自动事务模式下，当一个语句被成功执行后，它 被自动提交，而当它执行过程中产生错误时，被自 动回滚。自动事务模式是SQL Server的默认事务 管理模式，当与SQL Server建立连接后，直接进入 自动事务模式，直到使用BEGIN TRANSACTION语句 开始一个显式事务，或者打开 IMPLICIT_TRANSACTIONS 连接选项进入隐式事务模 式为止。 • 而当显式事务被提交或IMPLICIT_TRANSACTIONS 被关闭后，SQL Server又进入自动事务管理模式。 • 看看VB的例子
8.1.2 事务对并发控制和保障 数据完整的重要性
• 1．事务与并发控制的关系 • 如果在用户并发访问期间没有保证多个事务正确 的交叉运行，用户操作相同的数据时可能会产生 一些意想不到的问题。包括： • （1）丢失修改或被覆盖 （火车票） • （2）读脏数据 （银行利息） • （3）不能重复读 （学生成绩） • （4）幻影读
8.2.2 事务控制
• SQL Server中有关事务的处理语句有：
命令名 作用 格式 BEGIN TRANsaction [<事务名>] COMMIT TRANsaction [<事务名>]
BEGIN 说明一个事务开始 TRANSACTION COMMIT 说明一个事务结束，它的作用是 TRANSACTION 提交或确认事务已经完成
SET XACT_ABORT { ON | OFF }
•
2、事务和批的区别
• 下例导致在含有其它 Transact-SQL 语句的事务中发生违反外键错误。 在第一个语句集中产生错误，但其它语句均成功执行且事务成功提交。 在第二个语句集中，SET XACT_ABORT 设置为 ON。这导致语句错 误使批处理终止，并使事务回滚。
第8章
SQL Server 事务和并发控制
8.1 事务 8.2 事务的分类和控制 8.3 并发控制
8.4 事务处理实例分析
8.5 分布式事务
8.6 并发控制
8.1
事务
8.1.1 事务的概念 8.1.2 事务对并发控制和保障数据完整的重 要性
8.1.1 事务的概念 • 事务是一个用户定义的完整的工作单元， 一个事务内的所有语句被作为整体执行，