事务相关问题

masaly其它易诚

这个问题这么纠结么? 自己看吧.

实验一:

[db2a /home/db2users/e97q7a]$ db2 +c "insert into t1 values(1)"
DB20000I  The SQL command completed successfully.

[db2a /home/db2users/e97q7a]$ db2 +c "insert into t1 values(1001)"  ------> 虽然因为check约束失败了, 但是因为是+C, 数据库不会做回滚
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0545N  The requested operation is not allowed because a row does not
satisfy the check constraint "E97Q7A.T1.ID_CHECK".  SQLSTATE=23513

[db2a /home/db2users/e97q7a]$ db2 +c "select * from t1" ----> 还能查到第一个insert的数据

ID
-----------
      1

  1 record(s) selected.

[db2a /home/db2users/e97q7a]$ db2 "select * from t1"  ----> 提交了

ID
-----------
      1

  1 record(s) selected.

实验二:

[db2a /home/db2users/e97q7a]$ db2 "delete from t1"
DB20000I  The SQL command completed successfully.

[db2a /home/db2users/e97q7a]$ db2 +c "insert into t1 values(1)"
DB20000I  The SQL command completed successfully.
[db2a /home/db2users/e97q7a]$ db2 "insert into t1 values(1001)"
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0545N  The requested operation is not allowed because a row does not
satisfy the check constraint "E97Q7A.T1.ID_CHECK".  SQLSTATE=23513

[db2a /home/db2users/e97q7a]$ db2 +c "select * from t1"

ID
-----------

  0 record(s) selected.

[db2a /home/db2users/e97q7a]$ db2 "select * from t1"

ID
-----------

  0 record(s) selected.

Information Center:

Auto-commit Option (-c):
This option specifies whether each command or statement is to be treated independently. If set ON (-c), each command or statement is automatically committed or rolled back. If the command or statement is successful, it and all successful commands and statements that were issued before it with autocommit OFF (+c or -c-) are committed. If, however, the command or statement fails, it and all successful commands and statements that were issued before it with autocommit OFF are rolled back. If set OFF (+c or -c-), COMMIT or ROLLBACK must be issued explicitly, or one of these actions will occur when the next command with autocommit ON (-c) is issued.

The default setting for this command option is ON.

The auto-commit option does not affect any other command line processor option.
Example: Consider the following scenario:

      db2 create database test
      db2 connect to test
      db2 +c "create table a (c1 int)"
      db2 select c2 from a

The SQL statement in step 4 fails because there is no column named C2 in table A. Since that statement was issued with auto-commit ON (default), it rolls back not only the statement in step 4, but also the one in step 3, because the latter was issued with auto-commit OFF.收起

软件开发 · 2015-03-06

查看赞同的人

zhenda数据库管理员昆仑银行

回复 31# zsj2002

感谢精彩回答，选为最佳答案。也感谢所有积极参与回答的朋友们，谢谢啦！！！收起

软件开发 · 2015-03-23

zhenda数据库管理员昆仑银行

回复 30# masaly

恩，咨询过800，9.5之后的版本与之前版本不太一样。谢谢参与讨论哦。收起

软件开发 · 2015-03-10

zhenda数据库管理员昆仑银行

回复 28# zsj2002

恩，这个图比较形象，clp中+c语句通过procedure的另一种形式说明。图中也指出了log的写入方式。这也是我提出这个数据库理论题目，在DB2工程上如何解释的答案吧。个人认为+c语句是同一个事务，并且该事物具有not atomic性质，通过2种方式完成对成功sql的提交。并且在数据库各个版本中也不太一样。收起

软件开发 · 2015-03-10

zhenda数据库管理员昆仑银行

回复 29# 张东焕

感谢老张也来参与讨论啦，欢迎欢迎，:)。这两个实验也论证了之前的讨论的。对于+c的命令，有2种方式决定之前所有成功的SQL提交与回滚操作（此实验为第一个种，显式提交或回滚）。If set OFF (+c or -c-), COMMIT or ROLLBACK must be issued explicitly, or one of these actions will occur when the next command with autocommit ON (-c) is issued.收起

软件开发 · 2015-03-10

masaly其它易诚

再来点料, V91和V95/V97在CLP上call procedure的不同的行为模式.

DB21085I  Instance "e91q3f" uses "64" bits and DB2 code release "SQL09014" with
level identifier "01050107".
Informational tokens are "DB2 v9.1.0.4", "s071028", "U811792", and Fix Pack
"4".
Product is installed at "/opt/IBM/db2/v9.1.3".

[db2a /home/e91q3f]$ db2 "delete from t1"
DB20000I  The SQL command completed successfully.
[db2a /home/e91q3f]$ db2 "select * from t1"

ID
-----------

  0 record(s) selected.

[db2a /home/e91q3f]$ db2 "alter table t1 add constraint id_check check(id<=1000)"
DB20000I  The SQL command completed successfully.

[db2a /home/e91q3f]$ db2 "create procedure proc_test begin insert into t1 values(1);insert into t1 values(1001);end"
DB20000I  The SQL command completed successfully.

[db2a /home/e91q3f]$ db2 "call proc_test"
SQL0545N  The requested operation is not allowed because a row does not
satisfy the check constraint "E91Q3F.T1.ID_CHECK".  SQLSTATE=23513

[db2a /home/e91q3f]$ db2 "select * from t1"

ID
-----------
      1

  1 record(s) selected.

DB21085I  Instance "w95q7a" uses "64" bits and DB2 code release "SQL09057" with
level identifier "06080107".
Informational tokens are "DB2 v9.5.0.7", "s101129", "IP23136", and Fix Pack
"7".
Product is installed at "/opt/IBM/db2/V9.5.7".

[db2a /home/db2users/w95q7a]$ db2 "select * from t1"

ID
-----------

  0 record(s) selected.

[db2a /home/db2users/w95q7a]$ db2 "alter table t1 add constraint id_check check(id<=1000)"
DB20000I  The SQL command completed successfully.

[db2a /home/db2users/w95q7a]$ db2 "create procedure proc_test begin insert into t1 values(1);insert into t1 values(1001);end"
DB20000I  The SQL command completed successfully.

[db2a /home/db2users/w95q7a]$ db2 "call proc_test"
SQL0545N  The requested operation is not allowed because a row does not
satisfy the check constraint "W95Q7A.T1.IDCHECK".  SQLSTATE=23513

[db2a /home/db2users/w95q7a]$ db2 "select * from t1"

ID
-----------

  0 record(s) selected.

[db2a /home/db2users/w95q7a]$ db2 +c "call proc_test"
SQL0545N  The requested operation is not allowed because a row does not
satisfy the check constraint "W95Q7A.T1.IDCHECK".  SQLSTATE=23513

[db2a /home/db2users/w95q7a]$ db2 "select * from t1"

ID
-----------
      1

  1 record(s) selected.收起

软件开发 · 2015-03-10

张东焕技术总监IBM官方授权讲师

看大家讨论的很热烈，我也做了下实验，

我的实验结果如下：

C:\Program Files\IBM\SQLLIB\BIN>db2 delete from act where actdesc ='fly'
DB20000I  SQL 命令成功完成。

C:\Program Files\IBM\SQLLIB\BIN>db2 "select * from act"

ACTNO  ACTKWD ACTDESC
------ ------ --------------------
10 MANAGE MANAGE/ADVISE
20 ECOST  ESTIMATE COST
30 DEFINE DEFINE SPECS
40 LEADPR LEAD PROGRAM/DESIGN
50 SPECS  WRITE SPECS
60 LOGIC  DESCRIBE LOGIC
70 CODE CODE PROGRAMS
80 TEST TEST PROGRAMS
90 ADMQS  ADM QUERY SYSTEM
100 TEACH  TEACH CLASSES
110 COURSE DEVELOP COURSES
120 STAFF  PERS AND STAFFING
130 OPERAT OPER COMPUTER SYS
140 MAINT  MAINT SOFTWARE SYS
150 ADMSYS ADM OPERATING SYS
160 ADMDB  ADM DATA BASES
170 ADMDC  ADM DATA COMM
180 DOC DOCUMENT
   2 bbc

  19 条记录已选择。

C:\Program Files\IBM\SQLLIB\BIN>cat zenda.sql
insert into act values(5,'test','fly');
insert into act values(2,'bbc','');

C:\Program Files\IBM\SQLLIB\BIN>db2 +c -tvf zenda.sql
insert into act values(5,'test','fly')
DB20000I  SQL 命令成功完成。

insert into act values(2,'bbc','')
DB21034E  该命令被当作 SQL 语句来处理，因为它是无效的“命令行处理器”命令。在
SQL 处理期间，它返回：
SQL0803N  INSERT 语句、UPDATE 语句或由 DELETE
语句导致的外键更新中的一个或多个值无效，因为由 "2"
标识的主键、唯一约束或者唯一索引将表 "ZHANGDH.ACT"
的索引键限制为不能具有重复值。  SQLSTATE=23505

C:\Program Files\IBM\SQLLIB\BIN>db2 rollback
DB20000I  SQL 命令成功完成。

C:\Program Files\IBM\SQLLIB\BIN>db2 "select * from act"

ACTNO  ACTKWD ACTDESC
------ ------ --------------------
10 MANAGE MANAGE/ADVISE
20 ECOST  ESTIMATE COST
30 DEFINE DEFINE SPECS
40 LEADPR LEAD PROGRAM/DESIGN
50 SPECS  WRITE SPECS
60 LOGIC  DESCRIBE LOGIC
70 CODE CODE PROGRAMS
80 TEST TEST PROGRAMS
90 ADMQS  ADM QUERY SYSTEM
100 TEACH  TEACH CLASSES
110 COURSE DEVELOP COURSES
120 STAFF  PERS AND STAFFING
130 OPERAT OPER COMPUTER SYS
140 MAINT  MAINT SOFTWARE SYS
150 ADMSYS ADM OPERATING SYS
160 ADMDB  ADM DATA BASES
170 ADMDC  ADM DATA COMM
180 DOC DOCUMENT
   2 bbc

  19 条记录已选择。

C:\Program Files\IBM\SQLLIB\BIN>

C:\Program Files\IBM\SQLLIB\BIN>db2 delete from act where actdesc ='fly'
SQL0100W  找不到进行 FETCH、UPDATE 或 DELETE
操作的行；或者查询的结果是一个空表。  SQLSTATE=02000

C:\Program Files\IBM\SQLLIB\BIN>db2 +c -tvf zenda.sql
insert into act values(5,'test','fly')
DB20000I  SQL 命令成功完成。

insert into act values(2,'bbc','')
DB21034E  该命令被当作 SQL 语句来处理，因为它是无效的“命令行处理器”命令。在
SQL 处理期间，它返回：
SQL0803N  INSERT 语句、UPDATE 语句或由 DELETE
语句导致的外键更新中的一个或多个值无效，因为由 "2"
标识的主键、唯一约束或者唯一索引将表 "ZHANGDH.ACT"
的索引键限制为不能具有重复值。  SQLSTATE=23505

C:\Program Files\IBM\SQLLIB\BIN>db2 commit
DB20000I  SQL 命令成功完成。

C:\Program Files\IBM\SQLLIB\BIN>db2 "select * from act"

ACTNO  ACTKWD ACTDESC
------ ------ --------------------
10 MANAGE MANAGE/ADVISE
20 ECOST  ESTIMATE COST
30 DEFINE DEFINE SPECS
40 LEADPR LEAD PROGRAM/DESIGN
50 SPECS  WRITE SPECS
60 LOGIC  DESCRIBE LOGIC
70 CODE CODE PROGRAMS
80 TEST TEST PROGRAMS
90 ADMQS  ADM QUERY SYSTEM
100 TEACH  TEACH CLASSES
110 COURSE DEVELOP COURSES
120 STAFF  PERS AND STAFFING
130 OPERAT OPER COMPUTER SYS
140 MAINT  MAINT SOFTWARE SYS
150 ADMSYS ADM OPERATING SYS
160 ADMDB  ADM DATA BASES
170 ADMDC  ADM DATA COMM
180 DOC DOCUMENT
   2 bbc
   5 test fly

  20 条记录已选择。

C:\Program Files\IBM\SQLLIB\BIN>

所以还是需要程序加以干预。收起

IT咨询服务 · 2015-03-10

zsj2002数据库管理员澳門大豐銀行

回复 27# zhenda

嗯加了DECLARE CONTINUE HANDLER FOR SQLEXCEPTION的话就可以说的通了。
我也用JAVA模拟了我的想法写了一下。

public class main {

      private static Connection conn = null;

      public static void main(String[] args) {
            // TODO Auto-generated method stub
            Statement st = null;
            try {
                     Class.forName("com.ibm.db2.jcc.DB2Driver");
                     conn = DriverManager.getConnection(
                                    "jdbc:db2://192.168.105.60:50001/db2pe", "db2inst1",
                                    "password");
                     //关闭自动提交（模拟+c）
                     conn.setAutoCommit(false);
                     //创建一个statment object
                     st = conn.createStatement();
            } catch (SQLException e) {
                     // TODO Auto-generated catch block
                     e.printStackTrace();
            } catch (ClassNotFoundException e) {
                     // TODO Auto-generated catch block
                     e.printStackTrace();
            }
            //模拟正常插入
            try {
            st.executeUpdate("insert into t values(1)");
            } catch (SQLException e) {
                     System.out.println(e);
            }
            //模拟异常插入（输出错误）
            try {
            st.executeUpdate("insert into t values(\'d\')");
            } catch (SQLException e) {
                     System.out.println(e);
            }
            //模拟正常插入
            try {
            st.executeUpdate("insert into t values(1)");
            } catch (SQLException e) {
                     System.out.println(e);
            }
            //模拟提交
            try {
                     conn.commit();
            } catch (SQLException e) {
                     // TODO Auto-generated catch block
                     e.printStackTrace();
            }
      }

}

执行期间会报
com.ibm.db2.jcc.am.SqlDataException: DB2 SQL Error: SQLCODE=-420, SQLSTATE=22018, SQLERRMC=INTEGER, DRIVER=3.59.81
最后的结果也是两条正常的记录插入进去了（也是同一事物）。

虽然clp是用C写的，但是大概逻辑我想应该就是这样的吧。收起

银行 · 2015-03-10

zhenda数据库管理员昆仑银行

回复 25# zsj2002

前面的实验应该说明两个+c sql语句属于同一个事务(只是实验内容为两个成功sql)。我在重新做一次实验包含失败语句，会发现每一个+c的语句的TranHdl都是3。这次试验不在一一说明啦。第一个窗口每执行个+c语句，在另一个窗口打出dyn，tra，app对应的id。我们会发现AppHandl 都为15259，TranHdl都为3。第一个窗口如下：

[db2inst1@TSTHDHCDB01 ~]$ db2 delete from t1
DB20000I  The SQL command completed successfully.
[db2inst1@TSTHDHCDB01 ~]$ db2 +c "insert into t1 values('A','A')"
DB20000I  The SQL command completed successfully.
[db2inst1@TSTHDHCDB01 ~]$ db2 +c "insert into t1 values('A','a')"
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0545N  The requested operation is not allowed because a row does not
satisfy the check constraint "DB2INST1.T1.CHECKL".  SQLSTATE=23513
[db2inst1@TSTHDHCDB01 ~]$
[db2inst1@TSTHDHCDB01 ~]$
[db2inst1@TSTHDHCDB01 ~]$
[db2inst1@TSTHDHCDB01 ~]$ db2 +c "insert into t1 values('A','A')"
DB20000I  The SQL command completed successfully.
[db2inst1@TSTHDHCDB01 ~]$ db2 +c "insert into t1 values('A','B')"
DB20000I  The SQL command completed successfully.
[db2inst1@TSTHDHCDB01 ~]$
[db2inst1@TSTHDHCDB01 ~]$
[db2inst1@TSTHDHCDB01 ~]$
[db2inst1@TSTHDHCDB01 ~]$ db2 +c "select * from t1"

CO11    C012
---------- ----------
A       A
A       A
A       B

第二个窗口滚屏，如下：

[db2inst1@TSTHDHCDB01 ~]$ db2pd -d image1 -dyn

Dynamic SQL Statements:
Address          AnchID StmtUID NumEnv    NumVar    NumRef    NumExe    Text
0x00007F09F90752C0 172 1       1       1       1       1       insert into t1
values('A','A')
0x00007F09F9070440 410 1       1       1       1       1       delete from t1
0x00007F09F907A6C0 762 1       1       1       1       1       insert into t1
values('A','a')

Dynamic SQL Environments:
Address          AnchID StmtUID EnvID    Iso QOpt Blk
0x00007F09F9075420 172 1       1       CS  5 B
0x00007F09F90705A0 410 1       1       CS  5 B
0x00007F09F907A820 762 1       1       CS  5 B

Dynamic SQL Variations:
Address          AnchID StmtUID EnvID    VarID    NumRef    Typ Lockname                Val Insert Time             Sect Size  Num Copies
0x00007F09F90757A0 172 1       1       1       1       4 010000000100000001008015D6 Y 2015-03-10-11.36.09.418843 7712    1
0x00007F09F9070920 410 1       1       1       1       4 010000000100000001004033D6 Y 2015-03-10-11.35.40.386614 5824    0
0x00007F09F907ABA0 762 1       1       1       1       4 01000000010000000100405FD6 Y 2015-03-10-11.37.33.755670 7768    1
[db2inst1@TSTHDHCDB01 ~]$ db2pd -d image1 -app

Database Member 0 -- Database IMAGE1 -- Active -- Up 0 days 00:02:31 -- Date 2015-03-10-11.37.58.406798

Applications:
Address          AppHandl [nod-index] NumAgents  CoorEDUID  Status                C-AnchID C-StmtUID  L-AnchID L-StmtUID  Appid                                                          WorkloadID  WorkloadOccID CollectActData       CollectActPartition    CollectSectionActuals
0x00007F08B8A69080 15263 [000-15263] 1       1549    ConnectCompleted       0       0       0       0       *LOCAL.DB2.150310033531                                        0          0          N                      C                      N
0x00007F08B9069080 15262 [000-15262] 1       1548    ConnectCompleted       0       0       0       0       *LOCAL.DB2.150310033530                                        0          0          N                      C                      N
0x00007F08B92C9080 15261 [000-15261] 1       1524    ConnectCompleted       0       0       0       0       *LOCAL.DB2.150310033529                                        0          0          N                      C                      N
0x00007F08B8D99080 15267 [000-15267] 1       1553    ConnectCompleted       0       0       0       0       *LOCAL.DB2.150310033535                                        0          0          N                      C                      N
0x00007F08B9079080 15260 [000-15260] 1       1525    ConnectCompleted       0       0       0       0       *LOCAL.DB2.150310033528                                        0          0          N                      C                      N
0x00007F08B8EC9080 15266 [000-15266] 1       1552    ConnectCompleted       0       0       0       0       *LOCAL.DB2.150310033534                                        0          0          N                      C                      N
0x00007F08B91E9080 15259 [000-15259] 1       1494    UOW-Waiting          0       0       762    1       *LOCAL.db2inst1.150310033527                                  1          1          N                      C                      N
0x00007F08B8BE9080 15265 [000-15265] 1       1551    ConnectCompleted       0       0       0       0       *LOCAL.DB2.150310033533                                        0          0          N                      C                      N
0x00007F08B9239080 15264 [000-15264] 1       1550    ConnectCompleted       0       0       0       0       *LOCAL.DB2.150310033532                                        0          0          N                      C                      N

[db2inst1@TSTHDHCDB01 ~]$ db2pd -d image1 -tra

Database Member 0 -- Database IMAGE1 -- Active -- Up 0 days 00:02:50 -- Date 2015-03-10-11.38.17.356849

Transactions:
Address          AppHandl [nod-index] TranHdl Locks    State Tflag    Tflag2    Firstlsn          Lastlsn          Firstlso          Lastlso             SpaceReserved LogSpace       TID          AxRegCnt GXID    ClientUserID                ClientWrkstnName             ClientApplName                ClientAccntng
0x00007F09DCF48C00 15259 [000-15259] 3       4       WRITE 0x00000000 0x00000000 0x000000003C040754 0x000000003C040756 1151584357          1151584357          180          434          0x000000A3971A 1       0       n/a                         n/a                         n/a                         n/a
0x00007F09DCF4BA80 15260 [000-15260] 4       0       READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0                   0                   0             0             0x000000A3970F 1       0       n/a                         n/a                         n/a                         n/a
0x00007F09DCF4E900 15261 [000-15261] 5       0       READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0                   0                   0             0             0x000000A39711 1       0       n/a                         n/a                         n/a                         n/a
0x00007F09DCF51780 15262 [000-15262] 6       0       READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0                   0                   0             0             0x000000A39712 1       0       n/a                         n/a                         n/a                         n/a
0x00007F09DCF54600 15263 [000-15263] 7       0       READ 0x00000000 0x00000020 0x0000000000000000 0x0000000000000000 0                   0                   0             0             0x000000A39713 1       0       n/a                         n/a                         n/a                         n/a
0x00007F09DCF57480 15264 [000-15264] 8       0       READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0                   0                   0             0             0x000000A39714 1       0       n/a                         n/a                         n/a                         n/a
0x00007F09DCF5A300 15265 [000-15265] 9       0       READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0                   0                   0             0             0x000000A39715 1       0       n/a                         n/a                         n/a                         n/a
0x00007F09DCF5D180 15266 [000-15266] 10       0       READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0                   0                   0             0             0x000000A39716 1       0       n/a                         n/a                         n/a                         n/a
0x00007F09DCF60000 15267 [000-15267] 11       0       READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0                   0                   0             0             0x000000A39718 1       0       n/a                         n/a                         db2evmg_DB2DETAILDEADLOCK    n/a
Total application commits : 0
Total application rollbacks : 0

[db2inst1@TSTHDHCDB01 ~]$
[db2inst1@TSTHDHCDB01 ~]$
[db2inst1@TSTHDHCDB01 ~]$
[db2inst1@TSTHDHCDB01 ~]$
[db2inst1@TSTHDHCDB01 ~]$
[db2inst1@TSTHDHCDB01 ~]$
[db2inst1@TSTHDHCDB01 ~]$
[db2inst1@TSTHDHCDB01 ~]$ db2pd -d image1 -dyn

Database Member 0 -- Database IMAGE1 -- Active -- Up 0 days 00:04:04 -- Date 2015-03-10-11.39.31.679398

Dynamic SQL Statements:
Address          AnchID StmtUID NumEnv    NumVar    NumRef    NumExe    Text
0x00007F09F90752C0 172 1       1       1       2       2       insert into t1
values('A','A')
0x00007F09F9070440 410 1       1       1       1       1       delete from t1
0x00007F09F907A6C0 762 1       1       1       1       1       insert into t1
values('A','a')

Dynamic SQL Environments:
Address          AnchID StmtUID EnvID    Iso QOpt Blk
0x00007F09F9075420 172 1       1       CS  5 B
0x00007F09F90705A0 410 1       1       CS  5 B
0x00007F09F907A820 762 1       1       CS  5 B

Dynamic SQL Variations:
Address          AnchID StmtUID EnvID    VarID    NumRef    Typ Lockname                Val Insert Time             Sect Size  Num Copies
0x00007F09F90757A0 172 1       1       1       2       4 010000000100000001008015D6 Y 2015-03-10-11.36.09.418843 7712    1
0x00007F09F9070920 410 1       1       1       1       4 010000000100000001004033D6 Y 2015-03-10-11.35.40.386614 5824    0
0x00007F09F907ABA0 762 1       1       1       1       4 01000000010000000100405FD6 Y 2015-03-10-11.37.33.755670 7768    1
[db2inst1@TSTHDHCDB01 ~]$ db2pd -d image1 -dyn

Database Member 0 -- Database IMAGE1 -- Active -- Up 0 days 00:04:33 -- Date 2015-03-10-11.40.00.882042

Dynamic SQL Statements:
Address          AnchID StmtUID NumEnv    NumVar    NumRef    NumExe    Text
0x00007F09F90752C0 172 1       1       1       2       2       insert into t1
values('A','A')
0x00007F09F9070440 410 1       1       1       1       1       delete from t1
0x00007F09F907A6C0 762 1       1       1       1       1       insert into t1
values('A','a')
0x00007F09F907FE00 847 1       1       1       1       1       insert into t1
values('A','B')

Dynamic SQL Environments:
Address          AnchID StmtUID EnvID    Iso QOpt Blk
0x00007F09F9075420 172 1       1       CS  5 B
0x00007F09F90705A0 410 1       1       CS  5 B
0x00007F09F907A820 762 1       1       CS  5 B
0x00007F09F90C0820 847 1       1       CS  5 B

Dynamic SQL Variations:
Address          AnchID StmtUID EnvID    VarID    NumRef    Typ Lockname                Val Insert Time             Sect Size  Num Copies
0x00007F09F90757A0 172 1       1       1       2       4 010000000100000001008015D6 Y 2015-03-10-11.36.09.418843 7712    1
0x00007F09F9070920 410 1       1       1       1       4 010000000100000001004033D6 Y 2015-03-10-11.35.40.386614 5824    0
0x00007F09F907ABA0 762 1       1       1       1       4 01000000010000000100405FD6 Y 2015-03-10-11.37.33.755670 7768    1
0x00007F09F90C0BA0 847 1       1       1       1       4 01000000010000000100E069D6 Y 2015-03-10-11.39.56.292977 7768    1
[db2inst1@TSTHDHCDB01 ~]$ db2pd -d image1 -app

Database Member 0 -- Database IMAGE1 -- Active -- Up 0 days 00:04:45 -- Date 2015-03-10-11.40.12.688529

Applications:
Address          AppHandl [nod-index] NumAgents  CoorEDUID  Status                C-AnchID C-StmtUID  L-AnchID L-StmtUID  Appid                                                          WorkloadID  WorkloadOccID CollectActData       CollectActPartition    CollectSectionActuals
0x00007F08B8A69080 15263 [000-15263] 1       1549    ConnectCompleted       0       0       0       0       *LOCAL.DB2.150310033531                                        0          0          N                      C                      N
0x00007F08B9069080 15262 [000-15262] 1       1548    ConnectCompleted       0       0       0       0       *LOCAL.DB2.150310033530                                        0          0          N                      C                      N
0x00007F08B92C9080 15261 [000-15261] 1       1524    ConnectCompleted       0       0       0       0       *LOCAL.DB2.150310033529                                        0          0          N                      C                      N
0x00007F08B8D99080 15267 [000-15267] 1       1553    ConnectCompleted       0       0       0       0       *LOCAL.DB2.150310033535                                        0          0          N                      C                      N
0x00007F08B9079080 15260 [000-15260] 1       1525    ConnectCompleted       0       0       0       0       *LOCAL.DB2.150310033528                                        0          0          N                      C                      N
0x00007F08B8EC9080 15266 [000-15266] 1       1552    ConnectCompleted       0       0       0       0       *LOCAL.DB2.150310033534                                        0          0          N                      C                      N
0x00007F08B91E9080 15259 [000-15259] 1       1494    UOW-Waiting          0       0       847    1       *LOCAL.db2inst1.150310033527                                  1          1          N                      C                      N
0x00007F08B8BE9080 15265 [000-15265] 1       1551    ConnectCompleted       0       0       0       0       *LOCAL.DB2.150310033533                                        0          0          N                      C                      N
0x00007F08B9239080 15264 [000-15264] 1       1550    ConnectCompleted       0       0       0       0       *LOCAL.DB2.150310033532                                        0          0          N                      C                      N
                                                                                                                     non trusted                n/a

Autonomous Routine Connections
Address          AppHandl [nod-index] Status       Autonomous Routine Handl [nod-index] Status

Anonymous Block Connections
Address          AppHandl [nod-index] Status       Anonymous Block Handl [nod-index] Status
[db2inst1@TSTHDHCDB01 ~]$ db2pd -d image1 -tra

Database Member 0 -- Database IMAGE1 -- Active -- Up 0 days 00:04:58 -- Date 2015-03-10-11.40.25.238100

Transactions:
Address          AppHandl [nod-index] TranHdl Locks    State Tflag    Tflag2    Firstlsn          Lastlsn          Firstlso          Lastlso             SpaceReserved LogSpace       TID          AxRegCnt GXID    ClientUserID                ClientWrkstnName             ClientApplName                ClientAccntng
0x00007F09DCF48C00 15259 [000-15259] 3       6       WRITE 0x00000000 0x00000000 0x000000003C040754 0x000000003C040758 1151584357          1151584701          328          762          0x000000A3971A 1       0       n/a                         n/a                         n/a                         n/a
0x00007F09DCF4BA80 15260 [000-15260] 4       0       READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0                   0                   0             0             0x000000A3970F 1       0       n/a                         n/a                         n/a                         n/a
0x00007F09DCF4E900 15261 [000-15261] 5       0       READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0                   0                   0             0             0x000000A39711 1       0       n/a                         n/a                         n/a                         n/a
0x00007F09DCF51780 15262 [000-15262] 6       0       READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0                   0                   0             0             0x000000A39712 1       0       n/a                         n/a                         n/a                         n/a
0x00007F09DCF54600 15263 [000-15263] 7       0       READ 0x00000000 0x00000020 0x0000000000000000 0x0000000000000000 0                   0                   0             0             0x000000A39713 1       0       n/a                         n/a                         n/a                         n/a
0x00007F09DCF57480 15264 [000-15264] 8       0       READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0                   0                   0             0             0x000000A39714 1       0       n/a                         n/a                         n/a                         n/a
0x00007F09DCF5A300 15265 [000-15265] 9       0       READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0                   0                   0             0             0x000000A39715 1       0       n/a                         n/a                         n/a                         n/a
0x00007F09DCF5D180 15266 [000-15266] 10       0       READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0                   0                   0             0             0x000000A39716 1       0       n/a                         n/a                         n/a                         n/a
0x00007F09DCF60000 15267 [000-15267] 11       0       READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0                   0                   0             0             0x000000A39718 1       0       n/a                         n/a                         db2evmg_DB2DETAILDEADLOCK    n/a
Total application commits : 0
Total application rollbacks : 0

[db2inst1@TSTHDHCDB01 ~]$ db2pd -d image1 -dyn

Database Member 0 -- Database IMAGE1 -- Active -- Up 0 days 00:05:45 -- Date 2015-03-10-11.41.12.044672

Dynamic SQL Statements:
Address          AnchID StmtUID NumEnv    NumVar    NumRef    NumExe    Text
0x00007F09F90752C0 172 1       1       1       2       2       insert into t1
values('A','A')
0x00007F09F9070440 410 1       1       1       1       1       delete from t1
0x00007F09F907A6C0 762 1       1       1       1       1       insert into t1
values('A','a')
0x00007F09F907FE00 847 1       1       1       1       1       insert into t1
values('A','B')
0x00007F09F90C5AC0 944 1       1       1       1       1       select * from t1

Dynamic SQL Environments:
Address          AnchID StmtUID EnvID    Iso QOpt Blk
0x00007F09F9075420 172 1       1       CS  5 B
0x00007F09F90705A0 410 1       1       CS  5 B
0x00007F09F907A820 762 1       1       CS  5 B
0x00007F09F90C0820 847 1       1       CS  5 B
0x00007F09F90C5BE0 944 1       1       CS  5 B

Dynamic SQL Variations:
Address          AnchID StmtUID EnvID    VarID    NumRef    Typ Lockname                Val Insert Time             Sect Size  Num Copies
0x00007F09F90757A0 172 1       1       1       2       4 010000000100000001008015D6 Y 2015-03-10-11.36.09.418843 7712    1
0x00007F09F9070920 410 1       1       1       1       4 010000000100000001004033D6 Y 2015-03-10-11.35.40.386614 5824    0
0x00007F09F907ABA0 762 1       1       1       1       4 01000000010000000100405FD6 Y 2015-03-10-11.37.33.755670 7768    1
0x00007F09F90C0BA0 847 1       1       1       1       4 01000000010000000100E069D6 Y 2015-03-10-11.39.56.292977 7768    1
0x00007F09F90C5F60 944 1       1       1       1       6 010000000100000001000076D6 Y 2015-03-10-11.41.01.368206 6192    1
[db2inst1@TSTHDHCDB01 ~]$ db2pd -d image1 -app

Database Member 0 -- Database IMAGE1 -- Active -- Up 0 days 00:06:04 -- Date 2015-03-10-11.41.31.128365

Applications:
Address          AppHandl [nod-index] NumAgents  CoorEDUID  Status                C-AnchID C-StmtUID  L-AnchID L-StmtUID  Appid                                                          WorkloadID  WorkloadOccID CollectActData       CollectActPartition    CollectSectionActuals
0x00007F08B8A69080 15263 [000-15263] 1       1549    ConnectCompleted       0       0       0       0       *LOCAL.DB2.150310033531                                        0          0          N                      C                      N
0x00007F08B9069080 15262 [000-15262] 1       1548    ConnectCompleted       0       0       0       0       *LOCAL.DB2.150310033530                                        0          0          N                      C                      N
0x00007F08B92C9080 15261 [000-15261] 1       1524    ConnectCompleted       0       0       0       0       *LOCAL.DB2.150310033529                                        0          0          N                      C                      N
0x00007F08B8D99080 15267 [000-15267] 1       1553    ConnectCompleted       0       0       0       0       *LOCAL.DB2.150310033535                                        0          0          N                      C                      N
0x00007F08B9079080 15260 [000-15260] 1       1525    ConnectCompleted       0       0       0       0       *LOCAL.DB2.150310033528                                        0          0          N                      C                      N
0x00007F08B8EC9080 15266 [000-15266] 1       1552    ConnectCompleted       0       0       0       0       *LOCAL.DB2.150310033534                                        0          0          N                      C                      N
0x00007F08B91E9080 15259 [000-15259] 1       1494    UOW-Waiting          0       0       944    1       *LOCAL.db2inst1.150310033527                                  1          1          N                      C                      N
0x00007F08B8BE9080 15265 [000-15265] 1       1551    ConnectCompleted       0       0       0       0       *LOCAL.DB2.150310033533                                        0          0          N                      C                      N
0x00007F08B9239080 15264 [000-15264] 1       1550    ConnectCompleted       0       0       0       0       *LOCAL.DB2.150310033532                                        0          0          N                      C                      N

Autonomous Routine Connections
Address          AppHandl [nod-index] Status       Autonomous Routine Handl [nod-index] Status

Anonymous Block Connections
Address          AppHandl [nod-index] Status       Anonymous Block Handl [nod-index] Status
[db2inst1@TSTHDHCDB01 ~]$ db2pd -d image1 -tra

Database Member 0 -- Database IMAGE1 -- Active -- Up 0 days 00:06:49 -- Date 2015-03-10-11.42.16.124736

Transactions:
Address          AppHandl [nod-index] TranHdl Locks    State Tflag    Tflag2    Firstlsn          Lastlsn          Firstlso          Lastlso             SpaceReserved LogSpace       TID          AxRegCnt GXID    ClientUserID                ClientWrkstnName             ClientApplName                ClientAccntng
0x00007F09DCF48C00 15259 [000-15259] 3       6       WRITE 0x00000000 0x00000000 0x000000003C040754 0x000000003C040758 1151584357          1151584701          328          762          0x000000A3971A 1       0       n/a                         n/a                         n/a                         n/a
0x00007F09DCF4BA80 15260 [000-15260] 4       0       READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0                   0                   0             0             0x000000A3970F 1       0       n/a                         n/a                         n/a                         n/a
0x00007F09DCF4E900 15261 [000-15261] 5       0       READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0                   0                   0             0             0x000000A3971F 1       0       n/a                         n/a                         n/a                         n/a
0x00007F09DCF51780 15262 [000-15262] 6       0       READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0                   0                   0             0             0x000000A39712 1       0       n/a                         n/a                         n/a                         n/a
0x00007F09DCF54600 15263 [000-15263] 7       0       READ 0x00000000 0x00000020 0x0000000000000000 0x0000000000000000 0                   0                   0             0             0x000000A39713 1       0       n/a                         n/a                         n/a                         n/a
0x00007F09DCF57480 15264 [000-15264] 8       0       READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0                   0                   0             0             0x000000A39714 1       0       n/a                         n/a                         n/a                         n/a
0x00007F09DCF5A300 15265 [000-15265] 9       0       READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0                   0                   0             0             0x000000A39715 1       0       n/a                         n/a                         n/a                         n/a
0x00007F09DCF5D180 15266 [000-15266] 10       0       READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0                   0                   0             0             0x000000A39716 1       0       n/a                         n/a                         n/a                         n/a
0x00007F09DCF60000 15267 [000-15267] 11       0       READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0                   0                   0             0             0x000000A39718 1       0       n/a                         n/a                         db2evmg_DB2DETAILDEADLOCK    n/a
Total application commits : 0
Total application rollbacks : 0

[db2inst1@TSTHDHCDB01 ~]$ db2pd -d image1 -dyn

Dynamic SQL Statements:
Address          AnchID StmtUID NumEnv    NumVar    NumRef    NumExe    Text
0x00007F09F90752C0 172 1       1       1       2       2       insert into t1
values('A','A')
0x00007F09F9070440 410 1       1       1       1       1       delete from t1
0x00007F09F907A6C0 762 1       1       1       1       1       insert into t1
values('A','a')
0x00007F09F907FE00 847 1       1       1       1       1       insert into t1
values('A','B')
0x00007F09F90C5AC0 944 1       1       1       2       2       select * from t1

Dynamic SQL Environments:
Address          AnchID StmtUID EnvID    Iso QOpt Blk
0x00007F09F9075420 172 1       1       CS  5 B
0x00007F09F90705A0 410 1       1       CS  5 B
0x00007F09F907A820 762 1       1       CS  5 B
0x00007F09F90C0820 847 1       1       CS  5 B
0x00007F09F90C5BE0 944 1       1       CS  5 B

Dynamic SQL Variations:
Address          AnchID StmtUID EnvID    VarID    NumRef    Typ Lockname                Val Insert Time             Sect Size  Num Copies
0x00007F09F90757A0 172 1       1       1       2       4 010000000100000001008015D6 Y 2015-03-10-11.36.09.418843 7712    1
0x00007F09F9070920 410 1       1       1       1       4 010000000100000001004033D6 Y 2015-03-10-11.35.40.386614 5824    0
0x00007F09F907ABA0 762 1       1       1       1       4 01000000010000000100405FD6 Y 2015-03-10-11.37.33.755670 7768    1
0x00007F09F90C0BA0 847 1       1       1       1       4 01000000010000000100E069D6 Y 2015-03-10-11.39.56.292977 7768    1
0x00007F09F90C5F60 944 1       1       1       2       6 010000000100000001000076D6 Y 2015-03-10-11.41.01.368206 6192    1
[db2inst1@TSTHDHCDB01 ~]$

收起

软件开发 · 2015-03-10

zhenda数据库管理员昆仑银行

回复 25# zsj2002

感谢社区专家也来一起讨论，欢迎欢迎，热烈欢迎！！:) 相信咱们一起积极讨论，一个看似简单的问题也能发现无限得美！！
个人认为，通过前面的实验，在工程上用not atomic 解释了开篇的理论问题（也就是说在+c中，事务部分成功的问题）。在DB2中，社区专家提出了一个很好的问题，procedure里面出错之后的语句都没有再执行（默认方式），这个是正常的。procedure考虑更多的是ACID。db2 +c 等于是不管是出错前的还是出错后的语句，只要是正常的都执行了。并且提交的时候都提交上去了。

在procedure里，也可以完成+c的功能，在procedure加入DECLARE CONTINUE HANDLER FOR SQLEXCEPTION即可，有不足之处，再接着一起讨论。

CREATE PROCEDURE test
LANGUAGE SQL
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
snap:BEGIN not ATOMIC
insert into t1 values('A','A');
commit;
insert into t1 values('A','a');
commit;
insert into t1 values('A','A');
commit;
END snap

执行结果如下：

[db2inst1@TSTHDHCDB01 ~]$ db2 delete from t1
DB20000I  The SQL command completed successfully.
[db2inst1@TSTHDHCDB01 ~]$ db2 -td@ -vf 1.sql

DB21007E  End of file reached while reading the command.
[db2inst1@TSTHDHCDB01 ~]$ db2 "select * from t1"

CO11    C012
---------- ----------

  0 record(s) selected.

[db2inst1@TSTHDHCDB01 ~]$ db2 "call test()"
SQL0545N  The requested operation is not allowed because a row does not
satisfy the check constraint "DB2INST1.T1.CHECKL".  SQLSTATE=23513
[db2inst1@TSTHDHCDB01 ~]$ db2 "select * from t1"

CO11    C012
---------- ----------
A       A
A       A

  2 record(s) selected.

收起

软件开发 · 2015-03-10

34同行回答
全部行业
全部行业 银行 互联网服务 软件开发 零售/批发 IT咨询服务
|
按赞同排序
按时间排序

提问者

相关问题

相关资料

相关文章

问题状态

事务相关问题

34同行回答全部行业全部行业银行互联网服务软件开发零售/批发IT咨询服务|按赞同排序按时间排序

提问者

相关问题

相关资料

相关文章

问题状态

34同行回答
全部行业
全部行业银行互联网服务软件开发零售/批发 IT咨询服务
|
按赞同排序
按时间排序