Transaction

Table of Contents

1 事务简介

数据库事务(简称:事务)是数据库管理系统执行过程中的一个逻辑单位,由一个有限的数据库操作序列构成。

参考:
Nuts and Bolts of Transaction Processing
事务处理——概念与技术, by Jim Gray

1.1 事务的ACID

ACID,是指数据库管理系统(DBMS)在修改数据的过程中,为保证事务(transaction)是正确可靠的,所必须具备的四个特性。

Atomicity 原子性
同一事务的所有操作要么都完成,要么都没有完成。
Consistency 一致性
数据库在事务的开始和结束时将处于一致的状态(不管在任何给定的时间并发事务有多少)
Isolation 隔离性
一个事务不应该知道由任何其他的事务对数据所做的修改,除非那些修改已提交到数据库。可以设定不同的隔离级以修改默认行为。
Durability 持久性
已成功提交数据库的事务结果要能够维持(只要事务提交,系统将保证数据不会丢失,如系统突然crash)。

参考:SQL宝典,原书第二版,7.2.1 什么是事务

2 事务的Isolation Level(隔离级别)

The SQL92 standard defines four levels of transaction isolation with differing degrees of impact on transaction processing.
These isolation levels are defined in terms of the following three read types.

参考:http://juliandontcheff.wordpress.com/2011/02/23/isolation-levels-in-database-transactions-in-oracle-and-db2/

2.1 Three read types

Dirty reads
A transaction reads data that has been written by another transaction that has not been committed yet.
Fuzzy reads
A transaction rereads data it has previously read and finds that another committed transaction has modified or deleted the data.
Phantom reads
A transaction re-runs a query returning a set of rows that satisfies a search condition and finds that another committed transaction has inserted additional rows that satisfy the condition.

参考:http://www.ibm.com/developerworks/data/zones/informix/library/techarticle/db_isolevels.html

2.1.1 Dirty Read

This occurs if one transaction can see the results of the actions of another transaction before it commits. Consider the following example:

Table 1: Dirty read example
Transaction Action 1 Action 2 Action 3
Transaction 1 Write(a) - Rollback
Transaction 2 - Read(a) -

If Transaction 2 reads the value written by Transaction 1, then a DIRTY READ has occurred.

2.1.2 Non-Repeatable Read (also called Fuzzy Read)

This occurs if the results of one transactions can be modified or deleted by another transaction before it commits. This is illustrated below:

Table 2: Non-repeatable read example
Transaction Action 1 Action 2 Action 3 Action 4
Transaction 1 Read(a) - - Read(a)
Transaction 2 - Write/Delete(a) Commit -

If Transaction 1 gets a different result from the each Read, then a NON-REPEATABLE READ has occurred.
对于“Transaction 1”来说,两次读的结果不一样,所以这称为“不可重复读(Non-Repeatable Read)”。

2.1.3 Phantom Read (幻读)

This occurs if the results of a query in one transaction can be changed by another transaction before it commits. This is illustrated below:

Table 3: Phantom read example
Transaction Action 1 Action 2 Action 3 Action 4
Transaction 1 Select(criteria) - - Select(criteria)
Transaction 2 - Update/Create(match to criteria) Commit -

If Transaction 1 gets a different result from each Select, then a PHANTOM READ has occurred.

2.1.4 “脏读、不可重复读、幻读”的通俗例子

脏读(dirty read):A改后还未提交,B读,A又改并提交。例:
1.Mary的原工资为1000,财务人员将Mary的工资改为了8000(但未提交事务)
2.Mary读取自己的工资,发现自己的工资变为了8000,欢天喜地!
3.而财务发现操作有误,回滚了事务,Mary的工资又变为了1000,像这样Mary记取的工资数8000是一个脏数据。

不可重复读(nonrepeatable read):A先读,B再改,A再读。例:
1.在事务A中,Mary读取了自己的工资为1000,操作并没有完成
2.在事务B中,这时财务人员修改了Mary的工资为2000,并提交了事务
3.在事务A中,Mary再次读取自己的工资时,工资变为了2000
注:如果只有在修改事务完全提交之后才可以读取数据,则可以避免该问题。

幻读(phantom read):A改后还未提交,B改其他的,A再查。例:
1.A把所有的“黑色”改为“白色”
2.B把所有的“红色”改为“黑色”
3.A再查询“黑色”,却发现还有“黑色”,这对A来说就好象发生了幻觉一样。

参考:
http://hi.baidu.com/zhushidan100/item/8937ad28a56960fc51fd8724
http://www.cnblogs.com/hollen/archive/2012/05/13/2498309.html

2.2 Four levels of isolation in SQL92

SQL92 defines four levels of isolation based on the read permissions above:

Table 4: Four levels of isolation in SQL92
Isolation Level Dirty Read Nonrepeatable Read Phantom Read
Read Uncommitted Possible Possible Possible
Read Committed Not Possible Possible Possible
Repeatable Read Not Possible Not Possible Possible
Serializable Not Possible Not Possible Not Possible

2.3 DB2 isolation levels

Table 5: DB2 equivalent to the ANSI SQL Isolation Level
ANSI SQL Isolation Level DB2 equivalent Note
Read Uncommitted UR (Uncommitted read)  
Read Committed (default) CS (Cursor stability)  
Repeatable Read RS (Read stability) 对所读记录要加锁
Serializable RR (Repeatable read) 整个表可能要加锁

UR是“最危险”的用法(性能相对好)。
RR是“最安全”的用法(性能相对差)。

参考:http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.perf.doc/doc/c0004121.html

2.4 Oracle isolation levels

Oracle中隔离级别最低对应DB2中的CS级别,若要把DB2代码迁移到Oracle中,其中的“WITH UR”和“WITH CS”语句可以安全地删除。但如果DB2代码中有“WITH RS”和“WITH RR”怎么办呢?可以在Oracle中通过下面语句来设置,但这些语句必须是当前事务的第一条语句:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION READ ONLY;

参考:http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10005.htm#SQLRF01705

Oracle does not allow reading uncommitted rows and WITH UR clause should be removed from the SELECT statement. Often it is not a problem as WITH UR is mostly used to prevent any read locks in DB2, while in Oracle readers are never blocked anyway.

WITH CS means to read committed rows, and this is the default in Oracle, so this clause can be also removed.

摘自:http://www.sqlines.com/db2-to-oracle/select_isolation

3 Distributed Transaction(分布式事务)

涉及一个以上数据库的事务称为分布式事务(又称全局事务)。
分布式事务必须在网络失败时最小化丢失数据的风险。分布式事务处理的困难源于两个方面:concurrency和failures。

分布式事务中使用两阶段提交协议(Two-Phase Commit protocol):
第1阶段:Commit request phase(准备阶段)
事务管理器接收到一个COMMIT请求后将这个请求传达到该事务涉及的所有资源管理器(即数据库),然后这些资源管理器准备执行一个COMMIT。
第2阶段:Commit phase(提交阶段)
在此阶段,这些资源管理器真正发出COMMIT并报告给处理协调器;当所有的COMMIT都成功后,处理协调器发通知给客户端应用程序。如果有任何资源管理器没有通知处理协调器,则处理协调器发送ROLLBACK命令给所有资源管理器。这个过程中可能需要在COMMIT执行后再执行ROLLBACK,通常需要使用日志文件。

参考:
Concurrency Control and Recovery in Database Systems, by Philip A. Bernstein, Section 7.4 The Two Phase Commit Protocol
SQL宝典,原书第二版,7.2.2 事务的COMMIT和ROLLBACK

3.1 分布式事务典型应用场景

Moving data between databases – An application moving data from one database to another requires a distributed transaction. Otherwise, the data may be duplicated (if the insert completes and the delete fails) or lost (if the delete completes and the insert fails).

参考:
http://www.ibm.com/developerworks/websphere/library/techarticles/0407_woolf/0407_woolf.html

3.2 分布式事务最大缺点是性能不高

The greatest disadvantage of the two-phase commit protocol is that it is a blocking protocol. If the coordinator fails permanently, some cohorts will never resolve their transactions: After a cohort has sent an agreement message to the coordinator, it will block until a commit or rollback is received.

参考:
https://en.wikipedia.org/wiki/Two-phase_commit_protocol#Disadvantages
https://docs.oracle.com/database/121/ADFNS/adfns_xa.htm#ADFNS017

3.3 TPC-C benchmark

The TPC-C benchmark continues to be a popular yardstick for comparing OLTP performance on various hardware and software configurations.

4 事务处理标准

参考:事务处理——概念与技术, by Jim Gray, 2.7节

4.1 LU6.2事实标准

在IBM的 SNA (Systems Network Architecture) 事实标准中,有一部分内容定义了调用远程事务型服务器的协议,被称为 LU6.2 ,它定义了客户如何调用远程事务型服务器并和它建立一个会话。

4.2 OSI-TP标准

继IBM的SNA标准之后,ISO (国际标准化组织)定义了OSI (开放系统互连)的网络体系结构,其中包含有事务处理相关标准 (OSI-TP)。
OSI-TP和LU6.2非常相似,从某种程序上可以认为OSI-TP是LU6.2在OSI体系下的重定义。

参考:
http://www.iso.org/iso/home/store/catalogue_tc/catalogue_detail.htm?csnumber=27614

4.3 X/Open DTP标准

OSI-TP仅定义了消息传递和格式和所使用的协议,没有标准化具体的API。 这样,它们允许事务处理系统的互操作,但却没有提供编写可移植事务处理应用程序或服务器的方法。
另一个标准化组织X/Open意识到这个问题,它定义了分布事务处理标准(X/Open DTP)来解决这个问题。
在X/Open DTP模型中定义了资源管理器(RM),事务管理器(TM)等重要概念。在TM和TM之间的接口采用的还是OSI-TP标准。

参考:https://www2.opengroup.org/ogsys/catalog/G504

5 X/Open DTP

X/Open DTP模型的功能模块和接口如下:

transaction_XOpen_DTP.png

Figure 1: Functional Components and Interfaces

在简单场景下,模型可简化为:

transaction_XOpen_DTP_model_simplified.png

Figure 2: X/Open distributed transaction processing (DTP) model

The resource manager (RM) manages a defined part of the computer’s shared resources. These may be accessed using services that the RM provides. Examples for RMs are database management systems (DBMSs), a file access method such as X/Open ISAM, and a print server.

The transaction manager (TM) manages global transactions and coordinates the decision to start them, and commit them or roll them back. This ensures atomic transaction completion. The TM also coordinates recovery activities of the resource managers when necessary, such as after a component fails.

说明:在分布式事务处理环境中,RM必须依赖于TM来管理分布式事务。在分布式事务处理环境中,AP不应该直接使用RM提供的事务API,如EXEC SQL COMMIT WORK和EXEC SQL ROLLBACK WORK等。

参考:
Distributed Transaction Processing: Reference Model
Distributed Transaction Processing: The XA Specification
Distributed Transaction Processing: The TX (Transaction Demarcation) Specification

5.1 TX接口(应用程序和TM之间的接口)

TX接口是应用程序(AP)和事务管理器(TM)之间的接口。

Table 6: TX functions
Name Description
tx_begin Begin a global transaction.
tx_close Close a set of resource managers.
tx_commit Commit a global transaction.
tx_info Return global transaction information.
tx_open Open a set of resource managers.
tx_rollback Roll back a global transaction.
tx_set_transaction_control Set transaction_control characteristic.
tx_set_transaction_timeout Set transaction_timeout characteristic.

这些函数的原型定义在头文件tx.h中。

参考:Oracle Tuxedo ATMI (Application-to-Transaction Monitor Interface) C Function Reference

5.2 XA接口(TM和RM之间的接口)

XA接口是事务管理器(TM)和资源管理器(RM)之间接口。
XA uses a two-phase commit to ensure that all resources either commit or rollback any particular transaction consistently (all do the same).

Table 7: XA functions
Name Description
ax_reg Register an RM with a TM.
ax_unreg Unregister an RM with a TM.
xa_close Terminate the AP's use of an RM.
xa_commit Tell the RM to commit a transaction branch.
xa_complete Test an asynchronous xa_ operation for completion.
xa_end Dissociate the thread from a transaction branch.
xa_forget Permit the RM to discard its knowledge of a heuristically-completed transaction branch.
xa_open Initialise an RM for use by an AP.
xa_prepare Ask the RM to prepare to commit a transaction branch.
xa_recover Get a list of XIDs the RM has prepared or heuristically completed.
xa_rollback Tell the RM to roll back a transaction branch.
xa_start Start or resume a transaction branch - associate an XID with future work that the thread requests of the RM.

ax_开头的API由TM提供,被RM调用。xa_开头的API由RM提供,被TM调用。
这些函数的原型定义在头文件xa.h中。

说明1:RM可以自由定义xa_开头API的名字,TM通过结构体xa_switch_t找到它们。
The XA interface describes a structure called the switch table, which lists the names of the xa_ routines implemented in the resource manager. This structure is called xa_switch_t, and is defined in the xa.h header file. The switch table helps build servers that are truly independent of the database vendor being used.

struct xa_switch_t {
  char  name[RMNAMESZ];       /* name of resource manager */
  long  flags;                /* resource manager specific options */
  long  version;              /* must be 0 */
  int (*xa_open_entry)(char *, int, long);    /* xa_open function pointer */
  int (*xa_close_entry)(char *, int, long);   /* xa_close function pointer*/
  int (*xa_start_entry)(XID *, int, long);    /* xa_start function pointer */
  int (*xa_end_entry)(XID *, int, long);      /* xa_end function pointer */
  int (*xa_rollback_entry)(XID *, int, long); /* xa_rollback function pointer */
  int (*xa_prepare_entry)(XID *, int, long);  /* xa_prepare function pointer */
  int (*xa_commit_entry)(XID *, int, long);   /* xa_commit function pointer */
  int (*xa_recover_entry)(XID *, long, int, long);
                                              /* xa_recover function pointer*/
  int (*xa_forget_entry)(XID *, int, long);   /* xa_forget function pointer */
  int (*xa_complete_entry)(int *, int *, int, long);
};                                            /* xa_complete function pointer */

说明2:Some database vendors, such as Oracle, typically have the xa.h header file in their default include directory. Other database vendors, such as Sybase and DB2, generally do not have the xa.h file in the include directory. If the database vendor does not supply xa.h, this file should be located in the include directory of the TM vendor.

下面给出一个新进程的简单控制流程。该进程执行的事务仅涉及一个资源管理器,它静态地注册和参加所有的事务。

transaction_processing_flow.jpg

Figure 3: DTP中,新进程的简单控制流程

注:tm开头的函数就是TX接口中那些tx开关的函数。

参考:事务处理——概念与技术,16.6节

5.3 常见RMs

主流的数据库都可以做为RM。

5.3.2 Oracle Database

5.3.3 MySQL

5.4 常见TMs

IBM TXSeries(CICS): https://en.wikipedia.org/wiki/IBM_TXSeries
Oracle Tuxedo: https://en.wikipedia.org/wiki/Tuxedo_(software)
Microsoft Transaction Server(MTS): https://en.wikipedia.org/wiki/Microsoft_Transaction_Server
LIXA, LIbre XA, is a free and open source XA transaction manager: http://sourceforge.net/p/lixa/wiki/Home/


Author: cig01

Created: <2013-07-14 Sun 00:00>

Last updated: <2018-03-24 Sat 19:05>

Creator: Emacs 25.3.1 (Org mode 9.1.4)