WITH CONCURRENT TRANSACTION 选项

WITH CONCURRENT TRANSACTION 子句使您能够在当前连接中存在活动事务时，切换到另一个连接。如果当前连接不是使用 WITH CONCURRENT TRANSACTION 子句建立的，则您不能在事务是活动的情况下切换到另一个连接；CONNECT 或 SET CONNECTION 语句失败，返回一条错误，并且当前连接中的事务继续保持活动。

这种情况中，应用程序必须在当前连接切换到另一个连接之前提交或者回滚当前连接中活动的事务。

WITH CONCURRENT TRANSACTION 子句支持多个并发事务的概念，其中每个连接可有其自己的事务并且 COMMIT WORK 和 ROLLBACK WORK 语句仅影响当前的连接。WITH CONCURRENT TRANSACTION 子句不支持单个事务横跨多个连接上的数据库的全局事务。COMMIT WORK 和 ROLLBACK WORK 语句对跨越多个连接的数据库不起作用。

以下示例说明如何使用 WITH CONCURRENT TRANSACTION 子句：

main()
{
EXEC SQL connect to 'a@srv1' as 'A';
EXEC SQL connect to 'b@srv2' as 'B' with concurrent transaction;
EXEC SQL connect to 'c@srv3' as 'C' with concurrent transaction;

/*
   Execute SQL statements in connection 'C' , starting a transaction
*/
EXEC SQL set connection 'B'; -- switch to connection 'B'
 
/* 
   Execute SQL statements starting a transaction in 'B'.
   Now there are two active transactions, one each in 'B' and 'C'.
*/

EXEC SQL set connection 'A'; -- switch to connection 'A'

/* 
   Execute SQL statements starting a transaction in 'A'.
   Now there are three active transactions, one each in 'A', 'B' and 'C'.
*/

EXEC SQL set connection 'C'; -- ERROR, transaction active in 'A'

/*
   SET CONNECTION 'C' fails (current connection is still 'A')
   The transaction in 'A' must be committed or rolled back because 
   connection 'A' was started without the CONCURRENT TRANSACTION 
   clause.
*/

EXEC SQL commit work;   -- commit tx in current connection ('A')

/*
   Now, there are two active transactions, in 'B' and in 'C',
   which must be committed or rolled back separately
*/

EXEC SQL set connection 'B'; -- switch to connection 'B'
EXEC SQL commit work;        -- commit tx in current connection ('B')

EXEC SQL set connection 'C'; -- go back to connection 'C'
EXEC SQL commit work;         -- commit tx in current connection ('C')

EXEC SQL disconnect all;
}

警告：当应用程序使用 WITH CONCURRENT TRANSACTION 子句建立到同一个数据库环境的多个连接时，可能发生死锁现象。