事务相关问题
不久前，看了一道关于数据库的题。一个事务有2个语句，第一个语句执行成功，第二个语句因违反约束执行失败，请问提交之后会发生什么，大家在回答问题时，先别急于回帖，最好做个实验后，回帖顺便把实验结果贴出来。有可能结果和想象的不太一样哦。做完试验后，我在提出我的问题。
34同行回答
这个问题这么纠结么? 自己看吧.
实验一:
[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.收起
实验一:
[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.收起
浏览1312
浏览1316
再来点料, 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.收起
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.收起
浏览1373
回复 25# zsj2002
前面的实验应该说明两个+c sql语句属于同一个事务(只是实验内容为两个成功sql)。我在重新做一次实验包含失败语句，会发现每一个+c的语句的TranHdl都是3。这次试验不在一一说明啦。第一个窗口每执行个+c语句，在另一个窗口打出dyn，tra，app对应的id。我们会发现AppHandl 都为15259，TranHdl都为3。第一个窗口如下：
第二个窗口滚屏，如下：收起
前面的实验应该说明两个+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 ~]$
浏览1343
回复 25# zsj2002
感谢社区专家也来一起讨论，欢迎欢迎，热烈欢迎！！:) 相信咱们一起积极讨论，一个看似简单的问题也能发现无限得美！！
个人认为，通过前面的实验，在工程上用not atomic 解释了开篇的理论问题（也就是说在+c中，事务部分成功的问题）。在DB2中，社区专家提出了一个很好的问题，procedure里面出错之后的语句都没有再执行（默认方式），这个是正常的。procedure考虑更多的是ACID。db2 +c 等于是不管是出错前的还是出错后的语句，只要是正常的都执行了。并且提交的时候都提交上去了。
在procedure里，也可以完成+c的功能，在procedure加入DECLARE CONTINUE HANDLER FOR SQLEXCEPTION即可，有不足之处，再接着一起讨论。
执行结果如下：收起
感谢社区专家也来一起讨论，欢迎欢迎，热烈欢迎！！:) 相信咱们一起积极讨论，一个看似简单的问题也能发现无限得美！！
个人认为，通过前面的实验，在工程上用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.
浏览1412
回复 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.
浏览580