中山大学数据库系统与技术（1）课件第9章1 事务.pptVIP

Chapter 9: Transactions Chapter 15: Transactions Transaction Concept Transaction State Concurrent Executions Serializability Recoverability Implementation of Isolation Transaction Concept Transaction Concept A transaction(事务) is a unit of program execution that accesses and possibly updates various data items. Multiple transactions can execute in parallel. A transaction must see a consistent database During transaction execution the database may be temporarily inconsistent. When the transaction completes successfully (is committed), the database must be consistent. After a transaction commits, the changes it has made to the database persist, even if there are system failures. Two main issues to deal with: Failures of various kinds, such as hardware failures and system crashes Concurrent execution of multiple transactions 如何定义事务 Data manipulation language must include a construct for specifying the set of actions that comprise a transaction. 在 MS SQL Server 中，可以按显式、自动提交或隐性模式启动事务。 显式事务 通过发出 BEGIN TRANSACTION 语句显式启动事务。 自动提交事务 这是 SQL Server 的默认模式。每个单独的 Transact-SQL 语句都在其完成后提交。不必指定任何语句控制事务。 隐性事务 通过 API 函数或 Transact-SQL SET IMPLICIT_TRANSACTIONS ON 语句，将隐性事务模式设置为打开。下一个语句自动启动一个新事务。当该事务完成时，再下一个 Transact-SQL 语句又将启动一个新事务。 连接模式在连接层进行管理。如果一个连接从一种事务模式改变到另一种，那么它对任何其它连接的事务模式没有影响。 A transaction in SQL ends by: Commit [work/transaction] commits current transaction and begins a new one. Rollback [work/transaction] causes current transaction to abort. 例子 如何定义事务 显式定义方式 例子 BEGIN TRANSACTION 读账户甲的余额至变量BALANCE BALANCE=BALANCE-AMOUNT(AMOUNT为转账金额） IF (BALANCE0) THEN {显示‘金额不足，不能转账’; ROLLBACK; (撤销该事务）} ELSE {将BALANCE的值写回到账户甲； 读账户乙的余额至变量BALANCE1 BALANCE1=BALANCE1+AMOUNT 将BALANCE1的值写回到账户乙； COMMIT;} ACID Properties Atomicity（原子性）. Either all operations of the transaction are properly reflected in the database or none are. Consistency（一致性）. Execution of a transaction in isolation preserves the consistency of the database. Isolation（隔离性）. Although mult