事务相关问题
不久前，看了一道关于数据库的题。一个事务有2个语句，第一个语句执行成功，第二个语句因违反约束执行失败，请问提交之后会发生什么，大家在回答问题时，先别急于回帖，最好做个实验后，回帖顺便把实验结果贴出来。有可能结果和想象的不太一样哦。做完试验后，我在提出我的问题。
34同行回答
回复 23# zhenda
但是有一个地方解释不通还想跟您探讨一下
比如
b2inst1@testing1:~> db2 +c "insert into t values(1)"
DB20000I The SQL command completed successfully.
db2inst1@testing1:~> db2 +c "insert into t values('d')"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0420N Invalid character found in a character string argument of the
function "INTEGER". SQLSTATE=22018
db2inst1@testing1:~> db2 +c "insert into t values('1')"
DB20000I The SQL command completed successfully.
db2inst1@testing1:~> db2 +c "select * from t"
I
-----------
1
1
2 record(s) selected.
db2inst1@testing1:~> db2 commit
DB20000I The SQL command completed successfully.
db2inst1@testing1:~> db2 "select * from t"
I
-----------
1
1
2 record(s) selected.
db2inst1@testing1:~>
但是如果用procedure写的话
CREATE or replace PROCEDURE test
LANGUAGE SQL
snap:BEGIN not ATOMIC
insert into t values(1);
commit;
insert into t values('d');
commit;
insert into t values(1);
commit;
END snap @
--------------------------------------------
db2inst1@testing1:~> db2 "call test()"
SQL0420N Invalid character found in a character string argument of the
function "INTEGER". SQLSTATE=22018
db2inst1@testing1:~> db2 "select * from t"
I
-----------
1
1 record(s) selected.
db2inst1@testing1:~>
procedure里面出错之后的语句都没有再执行，这个是正常的。
但是db2 +c 等于是不管是出错前的还是出错后的语句，只要是正常的都执行了。并且提交的时候都提交上去了。
我觉得这样应该解释得通
就是你在trancation中提交的语句如果有一句出错应该会整体回滚。
但是你用CLP提交的时候如果碰到了出错的语句应该是不会加入到事物中的，
比如你执行
db2 +c “insert into t values(1)”;
db2 +c “insert into t values(d)“;
db2 +c “insert into t values(1)”;
实际上db2的这个事物里面只执行了
db2 +c “insert into t values(1)”;
db2 +c “insert into t values(1)”;
中间那条已经报错返回了根本就没有给你加到事物中。
而你单独写一个trancation就不一样了
他会把三句都算到这个事物中，所以才会出现有一句错误就会整体回滚，或者对于not ATOMIC会把出错之前的都执行。
所以关于clp对于事物的定义是绝对符合的，只不过用clp执行的时候会把所有不成功的语句都过滤掉了而已。
通过写JAVA也能模拟出这种情况。收起
但是有一个地方解释不通还想跟您探讨一下
比如
b2inst1@testing1:~> db2 +c "insert into t values(1)"
DB20000I The SQL command completed successfully.
db2inst1@testing1:~> db2 +c "insert into t values('d')"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0420N Invalid character found in a character string argument of the
function "INTEGER". SQLSTATE=22018
db2inst1@testing1:~> db2 +c "insert into t values('1')"
DB20000I The SQL command completed successfully.
db2inst1@testing1:~> db2 +c "select * from t"
I
-----------
1
1
2 record(s) selected.
db2inst1@testing1:~> db2 commit
DB20000I The SQL command completed successfully.
db2inst1@testing1:~> db2 "select * from t"
I
-----------
1
1
2 record(s) selected.
db2inst1@testing1:~>
但是如果用procedure写的话
CREATE or replace PROCEDURE test
LANGUAGE SQL
snap:BEGIN not ATOMIC
insert into t values(1);
commit;
insert into t values('d');
commit;
insert into t values(1);
commit;
END snap @
--------------------------------------------
db2inst1@testing1:~> db2 "call test()"
SQL0420N Invalid character found in a character string argument of the
function "INTEGER". SQLSTATE=22018
db2inst1@testing1:~> db2 "select * from t"
I
-----------
1
1 record(s) selected.
db2inst1@testing1:~>
procedure里面出错之后的语句都没有再执行，这个是正常的。
但是db2 +c 等于是不管是出错前的还是出错后的语句，只要是正常的都执行了。并且提交的时候都提交上去了。
我觉得这样应该解释得通
就是你在trancation中提交的语句如果有一句出错应该会整体回滚。
但是你用CLP提交的时候如果碰到了出错的语句应该是不会加入到事物中的，
比如你执行
db2 +c “insert into t values(1)”;
db2 +c “insert into t values(d)“;
db2 +c “insert into t values(1)”;
实际上db2的这个事物里面只执行了
db2 +c “insert into t values(1)”;
db2 +c “insert into t values(1)”;
中间那条已经报错返回了根本就没有给你加到事物中。
而你单独写一个trancation就不一样了
他会把三句都算到这个事物中，所以才会出现有一句错误就会整体回滚，或者对于not ATOMIC会把出错之前的都执行。
所以关于clp对于事物的定义是绝对符合的，只不过用clp执行的时候会把所有不成功的语句都过滤掉了而已。
通过写JAVA也能模拟出这种情况。收起
浏览1133
回复 22# masaly
感谢兄弟积极回帖讨论，DB2相对oralce信息来源少，一些东东都没有具体解释。都是在一次次纠结与探讨中，了解DB2。根据信息中心的说明（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），从数据库工程角度验证了在DB2 CLP中 +C 命令行中，下一句SQL成功与失败对前面+C 命令中SQL的影响。
回到开篇问的问题，用DB2 +c 方法实验 事务问题时，似乎还没有合理解释。前面已经实验了，同一个窗口执行多个+c 的sql属于同一个事务，在显式提交下为什么还会有部分成功（关系型数据库事务ACID贯彻始终）。在我们身边也经常有很多朋友，在使用+c 命令去验证一些问题，往往结果与之前所想有较大差别。
在+c 语句中有两部分，一部分是下一句SQL成功与失败对前面+C 命令中SQL的影响，另一部分是显式commit。之前实验已经说明第一部分，关于第二部分显式commit中，个人理解，在每一个+C 命令行具有not atomic 。根据信息中心解释（If set OFF (+c or -c-), COMMIT or ROLLBACK must be issued explicitly），我们把+c 命令转换成存储过程，似乎这样就能解释开篇提到的理论问题。收起
感谢兄弟积极回帖讨论，DB2相对oralce信息来源少，一些东东都没有具体解释。都是在一次次纠结与探讨中，了解DB2。根据信息中心的说明（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），从数据库工程角度验证了在DB2 CLP中 +C 命令行中，下一句SQL成功与失败对前面+C 命令中SQL的影响。
回到开篇问的问题，用DB2 +c 方法实验 事务问题时，似乎还没有合理解释。前面已经实验了，同一个窗口执行多个+c 的sql属于同一个事务，在显式提交下为什么还会有部分成功（关系型数据库事务ACID贯彻始终）。在我们身边也经常有很多朋友，在使用+c 命令去验证一些问题，往往结果与之前所想有较大差别。
在+c 语句中有两部分，一部分是下一句SQL成功与失败对前面+C 命令中SQL的影响，另一部分是显式commit。之前实验已经说明第一部分，关于第二部分显式commit中，个人理解，在每一个+C 命令行具有not atomic 。根据信息中心解释（If set OFF (+c or -c-), COMMIT or ROLLBACK must be issued explicitly），我们把+c 命令转换成存储过程，似乎这样就能解释开篇提到的理论问题。
[db2inst1@TSTHDHCDB01 ~]$ db2 -td@ -vf 1.sql
CREATE PROCEDURE test
LANGUAGE SQL
snap:BEGIN not ATOMIC
insert into t1 values('A','A');
commit;
insert into t1 values('A','d');
commit;
END snap
DB20000I The SQL command completed successfully.
[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 +c "select * from t1"
CO11 C012
---------- ----------
A A
1 record(s) selected.
[db2inst1@TSTHDHCDB01 ~]$ db2 "select * from t1"
CO11 C012
---------- ----------
A A
1 record(s) selected.
浏览581
浏览581
savepoint s1
insert1
savepoint s2
insert2 -- 发生问题也仅会rollback到s2
commit; --commit s1
savepoint s1
insert1 -- 发生问题会rollback到s1
savepoint s2
insert2
commit; --commit s2
都没问题的话：
savepoint s1
insert1
savepoint s2
insert2
commit; --commit s1收起
insert1
savepoint s2
insert2 -- 发生问题也仅会rollback到s2
commit; --commit s1
savepoint s1
insert1 -- 发生问题会rollback到s1
savepoint s2
insert2
commit; --commit s2
都没问题的话：
savepoint s1
insert1
savepoint s2
insert2
commit; --commit s1收起
浏览686
回复 17# hp_leetaedong14
今早看到我的帖子挺热闹，讨论认为2个+c的语句是2个事务，把这问题上升到更高的层次，说国内DB2 水平不如oracle。 关于此结论不敢恭维。作为2家巨头公司，其背后商业模式和营销模式不同，可能存在历史问题或客观原因，IBM相对oracle对信息的公开度不够高，对DB2的学习者来说，知识来源渠道少，培训也没有oracle培训多，培训资料也不及oralce。市场份额也决定使用者和学习者的参与度。 千万不要把知识的问题，上升到政治高度哦。
言归正传，您下定这个结论不能说明您技能不高，只能说明对DB2还不够了解，或者说对ORACLE更熟练一些。贴一个oracle的例子，来说明DB2的问题（2个+c语句是2个事务）有点牵强吧。
哎，费点时间做个试验，用事实说话吧。
打开2个窗口，第一个窗口先插入数据，从第二个窗口观察实验结果。
第一个窗口，先插入第一条数据：
第二个窗口抓取信息：
insert into t2 values(1,'test','fly') 对应 AnchID 为 530 继续查看对应AppHandl（此环境只有我在使用， AnchID为唯一 530，所以以下略去了StmtUID）
对应AppHandl 12957，继续抓取查看对应Transactions
AppHandl12957 对应的TranHdl 为3 锁为3个。回到第一个窗口继续插入第二条SQL。
第二个窗口利用同上的方法继续观察。
查看380对应AppHandl
查找AppHandl 12957 对应 TranHdl 如下：
我们看到AppHandl 12957对应TranHdl 为3 locks由之前的3变为4，多了一个行锁，关于锁细节如下：
综上所述结论应该很明显了。
建议可以提出疑问或解决思路，咱们一起分享、探讨。每个人的思维都有局限性，学无止境，不可一概而论。可不要把技术问题上升到政治高度哦。其他DB2专家们躺着中枪了，伤不起啊，伤不起，:)。收起
今早看到我的帖子挺热闹，讨论认为2个+c的语句是2个事务，把这问题上升到更高的层次，说国内DB2 水平不如oracle。 关于此结论不敢恭维。作为2家巨头公司，其背后商业模式和营销模式不同，可能存在历史问题或客观原因，IBM相对oracle对信息的公开度不够高，对DB2的学习者来说，知识来源渠道少，培训也没有oracle培训多，培训资料也不及oralce。市场份额也决定使用者和学习者的参与度。 千万不要把知识的问题，上升到政治高度哦。
言归正传，您下定这个结论不能说明您技能不高，只能说明对DB2还不够了解，或者说对ORACLE更熟练一些。贴一个oracle的例子，来说明DB2的问题（2个+c语句是2个事务）有点牵强吧。
哎，费点时间做个试验，用事实说话吧。
打开2个窗口，第一个窗口先插入数据，从第二个窗口观察实验结果。
第一个窗口，先插入第一条数据：
[db2inst1@TSTHDHCDB01 ~]$ db2 "create table t2 (id int,c char(8) not null,d char(8) not null )"
DB20000I The SQL command completed successfully.
[db2inst1@TSTHDHCDB01 ~]$ db2 +c "insert into t2 values (1,'test','fly') "
DB20000I The SQL command completed successfully.
第二个窗口抓取信息：
Dynamic SQL Statements:
Address AnchID StmtUID NumEnv NumVar NumRef NumExe Text
0x00007F087E5DBFE0 530 1 1 1 1 1 insert into t2 values
(1,'test','fly')
0x00007F087E5D8440 568 1 1 1 2 1 create table t2 (id int,c
char(8) not null,d char(8) not null )
Dynamic SQL Environments:
Address AnchID StmtUID EnvID Iso QOpt Blk
0x00007F087E5DC140 530 1 1 CS 5 B
0x00007F087E5D85C0 568 1 1 CS 5 B
Dynamic SQL Variations:
Address AnchID StmtUID EnvID VarID NumRef Typ Lockname Val Insert Time Sect Size Num Copies
0x00007F087E5DC4C0 530 1 1 1 1 4 010000000100000001004042D6 Y 2015-03-04-11.52.19.776833 5448 1
0x00007F087E5D8940 568 1 1 1 1 1 010000000100000001000047D6 Y 2015-03-04-11.52.15.293841 1936 0
[db2inst1@TSTHDHCDB01 ~]$ db2pd -d image1 -app
insert into t2 values(1,'test','fly') 对应 AnchID 为 530 继续查看对应AppHandl（此环境只有我在使用， AnchID为唯一 530，所以以下略去了StmtUID）
Applications:
Address AppHandl [nod-index] NumAgents CoorEDUID Status C-AnchID C-StmtUID L-AnchID L-StmtUID Appid WorkloadID WorkloadOccID CollectActData CollectActPartition CollectSectionActuals
0x00007F08B8BE9080 12960 [000-12960] 1 1309 ConnectCompleted 0 0 0 0 *LOCAL.DB2.150304035208 0 0 N C N
0x00007F08B8E09080 12959 [000-12959] 1 1286 ConnectCompleted 0 0 0 0 *LOCAL.DB2.150304035207 0 0 N C N
0x00007F08B8929080 12965 [000-12965] 1 1314 ConnectCompleted 0 0 0 0 *LOCAL.DB2.150304035213 0 0 N C N
0x00007F08B8D79080 12958 [000-12958] 1 1285 ConnectCompleted 0 0 0 0 *LOCAL.DB2.150304035206 0 0 N C N
0x00007F08B89C9080 12964 [000-12964] 1 1313 ConnectCompleted 0 0 0 0 *LOCAL.DB2.150304035212 0 0 N C N
0x00007F08B8EB9080 12957 [000-12957] 1 1164 UOW-Waiting 0 0 530 1 *LOCAL.db2inst1.150304035205
对应AppHandl 12957，继续抓取查看对应Transactions
Transactions:
Address AppHandl [nod-index] TranHdl Locks State Tflag Tflag2 Firstlsn Lastlsn Firstlso Lastlso SpaceReserved LogSpace TID AxRegCnt GXID ClientUserID ClientWrkstnName ClientApplName ClientAccntng
0x00007F08624B0C00 12957 [000-12957] 3 3 WRITE 0x00000000 0x00000000 0x000000003C03E967 0x000000003C03E967 1118200848 1118200848 180 269 0x000000A3897D 1 0 n/a n/a n/a n/a
0x00007F08624B3A80 12958 [000-12958] 4 0 READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0 0 0 0 0x000000A38972 1 0 n/a n/a n/a n/a
AppHandl12957 对应的TranHdl 为3 锁为3个。回到第一个窗口继续插入第二条SQL。
[db2inst1@TSTHDHCDB01 ~]$ db2 +c "insert into t2 values (2,'bbc','') "
DB20000I The SQL command completed successfully.
第二个窗口利用同上的方法继续观察。
Dynamic SQL Statements:
Address AnchID StmtUID NumEnv NumVar NumRef NumExe Text
0x00007F087E5E1AA0 380 1 1 1 1 1 insert into t2 values
(2,'bbc','')
0x00007F087E5DBFE0 530 1 1 1 1 1 insert into t2 values
(1,'test','fly')
0x00007F087E5D8440 568 1 1 1 2 1 create table t2 (id int,c
char(8) not null,d char(8) not null )
查看380对应AppHandl
Applications:
Address AppHandl [nod-index] NumAgents CoorEDUID Status C-AnchID C-StmtUID L-AnchID L-StmtUID Appid WorkloadID WorkloadOccID CollectActData CollectActPartition CollectSectionActuals
0x00007F08B8BE9080 12960 [000-12960] 1 1309 ConnectCompleted 0 0 0 0 *LOCAL.DB2.150304035208 0 0 N C N
0x00007F08B8E09080 12959 [000-12959] 1 1286 ConnectCompleted 0 0 0 0 *LOCAL.DB2.150304035207 0 0 N C N
0x00007F08B8929080 12965 [000-12965] 1 1314 ConnectCompleted 0 0 0 0 *LOCAL.DB2.150304035213 0 0 N C N
0x00007F08B8D79080 12958 [000-12958] 1 1285 ConnectCompleted 0 0 0 0 *LOCAL.DB2.150304035206 0 0 N C N
0x00007F08B89C9080 12964 [000-12964] 1 1313 ConnectCompleted 0 0 0 0 *LOCAL.DB2.150304035212 0 0 N C N
0x00007F08B8EB9080 12957 [000-12957] 1 1164 UOW-Waiting 0 0 380 1 *LOCAL.db2inst1.150304035205 1 1 N C N
查找AppHandl 12957 对应 TranHdl 如下：
Transactions:
Address AppHandl [nod-index] TranHdl Locks State Tflag Tflag2 Firstlsn Lastlsn Firstlso Lastlso SpaceReserved LogSpace TID AxRegCnt GXID ClientUserID ClientWrkstnName ClientApplName ClientAccntng
0x00007F08624B0C00 12957 [000-12957] 3 4 WRITE 0x00000000 0x00000000 0x000000003C03E967 0x000000003C03E968 1118200848 1118200937 254 432 0x000000A3897D 1 0 n/a n/a n/a n/a
0x00007F08624B3A80 12958 [000-12958] 4 0 READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0 0 0 0 0x000000A38972 1 0 n/a n/a n/a n/a
0x00007F08624B6900 12959 [000-12959] 5 0 READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0 0 0 0 0x000000A38974 1 0 n/a n/a n/a n/a
我们看到AppHandl 12957对应TranHdl 为3 locks由之前的3变为4，多了一个行锁，关于锁细节如下：
Database Member 0 -- Database IMAGE1 -- Active -- Up 0 days 00:36:01 -- Date 2015-03-04-12.28.06.562387
Locks:
Address TranHdl Lockname Type Mode Sts Owner Dur HoldCount Att ReleaseFlg rrIID
0x00007F08628BBB80 3 03001300040020080000000052 RowLock ..X G 3 1 0 0x00200008 0x40000000 0
0x00007F08628BB380 3 4141414141664164FE8BC714C1 PlanLock ..S G 3 1 0 0x00000000 0x40000000 0
0x00007F08628BBA80 3 03001300050020080000000052 RowLock ..X G 3 1 0 0x00200008 0x40000000 0
0x00007F08628BBF80 3 03001300000000000000000054 TableLock .IX G 3 1 0 0x00202000 0x40000000 0
综上所述结论应该很明显了。
建议可以提出疑问或解决思路，咱们一起分享、探讨。每个人的思维都有局限性，学无止境，不可一概而论。可不要把技术问题上升到政治高度哦。其他DB2专家们躺着中枪了，伤不起啊，伤不起，:)。收起
浏览568
几个 大拿 还在讨论这种问题？ :L
难怪国内db2水平跟oracle比差一大截。
没有贬低的意思，只是 说出我看到后的第一感受。
你两个db2 +c，那肯定是两个事务啊。
oracle下：
SQL> begin
2 savepoint s1;
3 insert into t values(1);
4 insert into t values('a');
5 exception
6 when others then
7 rollback to s1;
8 raise;
9 end;
10 commit;
11 /
begin
*
ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at line 8
SQL> select * from t;
no rows selected收起
难怪国内db2水平跟oracle比差一大截。
没有贬低的意思，只是 说出我看到后的第一感受。
你两个db2 +c，那肯定是两个事务啊。
oracle下：
SQL> begin
2 savepoint s1;
3 insert into t values(1);
4 insert into t values('a');
5 exception
6 when others then
7 rollback to s1;
8 raise;
9 end;
10 commit;
11 /
begin
*
ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at line 8
SQL> select * from t;
no rows selected收起
浏览695
你在clp内，执行2个db2 +c应该算2个事物了吧niejiangshui 发表于 2015-3-3 11:49
顶你在clp内，执行2个db2 +c应该算2个事物了吧收起
浏览571