在session1中执行:
db2 => select * from db2admin.t_da_jkda_rkxzl --会执行很长时间
在session2中执行:
db2 => drop table db2admin.t_da_jkda_rkxzl --此时无法执行完成,hang住
Auth Id Application Appl. Application Id DB # of
Name Handle Name Agents
-------- -------------- ---------- -------------------------------------------------------------- -------- -----
DB2INST1 db2bp 40 *LOCAL.db2inst1.171019012914 TEST 1
DB2INST1 db2bp 33 *LOCAL.db2inst1.171019012907 TEST 1
[db2inst1@db ~]$ db2pd -d test -apinfo 40
Database Partition 0 -- Database TEST -- Active -- Up 0 days 00:00:37
Application :
Address : 0x0000000200DC65E0
AppHandl [nod-index] : 40 [000-00040]
TranHdl : 8
Application PID : 4806
Application Node Name : db
IP Address: n/a
Connection Start Time : (1508376551)Thu Oct 19 09:29:11 2017
Client User ID : db2inst1
System Auth ID : DB2INST1
Coordinator EDU ID : 58
Coordinator Partition : 0
Number of Agents : 1
Locks timeout value : NotSet
Locks Escalation : No
Workload ID : 1
Workload Occurrence ID : 1
Trusted Context : n/a
Connection Trust Type : non trusted
Role Inherited : n/a
Application Status : UOW-Waiting
Application Name : db2bp
Application ID : *LOCAL.db2inst1.171019012914
ClientUserID : n/a
ClientWrkstnName : n/a
ClientApplName : n/a
ClientAccntng : n/a
List of active statements :
UOW-ID : 1
Activity ID : 1
Package Schema : NULLID
Package Name : SQLC2H20
Package Version :
Section Number : 201
SQL Type : Dynamic
Isolation : CS
Statement Type : DML, Select (blockable)
Statement : select * from
db2admin.T_DA_JKDA_JKZK
--------------------------------------------------------------------------------
[db2inst1@db ~]$ db2pd -d test -apinfo 33
Database Partition 0 -- Database TEST -- Active -- Up 0 days 00:00:41
Application :
Address : 0x0000000200DA0080
AppHandl [nod-index] : 33 [000-00033]
TranHdl : 2
Application PID : 4963
Application Node Name : db
IP Address: n/a
Connection Start Time : (1508376547)Thu Oct 19 09:29:07 2017
Client User ID : db2inst1
System Auth ID : DB2INST1
Coordinator EDU ID : 17
Coordinator Partition : 0
Number of Agents : 1
Locks timeout value : NotSet
Locks Escalation : No
Workload ID : 1
Workload Occurrence ID : 2
Trusted Context : n/a
Connection Trust Type : non trusted
Role Inherited : n/a
Application Status : Lock-wait
Application Name : db2bp
Application ID : *LOCAL.db2inst1.171019012907
ClientUserID : n/a
ClientWrkstnName : n/a
ClientApplName : n/a
ClientAccntng : n/a
List of active statements :
*UOW-ID : 1
Activity ID : 1
Package Schema : NULLID
Package Name : SQLC2H20
Package Version :
Section Number : 203
SQL Type : Dynamic
Isolation : CS
Statement Type : DDL, (not Set Constraints)
Statement : drop table
db2admin.T_DA_JKDA_JKZK
--------------------------------------------------------------------------------
[db2inst1@db ~]$ db2 "force application (40)" -->此时Kill掉select查询sessin1后,session2中的drop可以正常执行
DB20000I The FORCE APPLICATION command completed successfully.
DB21024I This command is asynchronous and may not be effective immediately.
[db2inst1@db ~]$ db2pd -d test -apinfo 33
Database Partition 0 -- Database TEST -- Active -- Up 0 days 00:01:01
Application :
Address : 0x0000000200DA0080
AppHandl [nod-index] : 33 [000-00033]
TranHdl : 2
Application PID : 4963
Application Node Name : db
IP Address: n/a
Connection Start Time : (1508376547)Thu Oct 19 09:29:07 2017
Client User ID : db2inst1
System Auth ID : DB2INST1
Coordinator EDU ID : 17
Coordinator Partition : 0
Number of Agents : 1
Locks timeout value : NotSet
Locks Escalation : No
Workload ID : 1
Workload Occurrence ID : 2
Trusted Context : n/a
Connection Trust Type : non trusted
Role Inherited : n/a
Application Status : UOW-Waiting
Application Name : db2bp
Application ID : *LOCAL.db2inst1.171019012907
ClientUserID : n/a
ClientWrkstnName : n/a
ClientApplName : n/a
ClientAccntng : n/a
db2 => select * from db2admin.t_da_jkda_rkxzl --会执行很长时间
在session2中执行:
db2 => drop table db2admin.t_da_jkda_rkxzl --此时无法执行完成,hang住
以下操作步骤均在session3中执行:
[db2inst1@db ~]$ db2 list applicationAuth Id Application Appl. Application Id DB # of
Name Handle Name Agents
-------- -------------- ---------- -------------------------------------------------------------- -------- -----
DB2INST1 db2bp 40 *LOCAL.db2inst1.171019012914 TEST 1
DB2INST1 db2bp 33 *LOCAL.db2inst1.171019012907 TEST 1
[db2inst1@db ~]$ db2pd -d test -apinfo 40
Database Partition 0 -- Database TEST -- Active -- Up 0 days 00:00:37
Application :
Address : 0x0000000200DC65E0
AppHandl [nod-index] : 40 [000-00040]
TranHdl : 8
Application PID : 4806
Application Node Name : db
IP Address: n/a
Connection Start Time : (1508376551)Thu Oct 19 09:29:11 2017
Client User ID : db2inst1
System Auth ID : DB2INST1
Coordinator EDU ID : 58
Coordinator Partition : 0
Number of Agents : 1
Locks timeout value : NotSet
Locks Escalation : No
Workload ID : 1
Workload Occurrence ID : 1
Trusted Context : n/a
Connection Trust Type : non trusted
Role Inherited : n/a
Application Status : UOW-Waiting
Application Name : db2bp
Application ID : *LOCAL.db2inst1.171019012914
ClientUserID : n/a
ClientWrkstnName : n/a
ClientApplName : n/a
ClientAccntng : n/a
List of active statements :
UOW-ID : 1
Activity ID : 1
Package Schema : NULLID
Package Name : SQLC2H20
Package Version :
Section Number : 201
SQL Type : Dynamic
Isolation : CS
Statement Type : DML, Select (blockable)
Statement : select * from
db2admin.T_DA_JKDA_JKZK
--------------------------------------------------------------------------------
[db2inst1@db ~]$ db2pd -d test -apinfo 33
Database Partition 0 -- Database TEST -- Active -- Up 0 days 00:00:41
Application :
Address : 0x0000000200DA0080
AppHandl [nod-index] : 33 [000-00033]
TranHdl : 2
Application PID : 4963
Application Node Name : db
IP Address: n/a
Connection Start Time : (1508376547)Thu Oct 19 09:29:07 2017
Client User ID : db2inst1
System Auth ID : DB2INST1
Coordinator EDU ID : 17
Coordinator Partition : 0
Number of Agents : 1
Locks timeout value : NotSet
Locks Escalation : No
Workload ID : 1
Workload Occurrence ID : 2
Trusted Context : n/a
Connection Trust Type : non trusted
Role Inherited : n/a
Application Status : Lock-wait
Application Name : db2bp
Application ID : *LOCAL.db2inst1.171019012907
ClientUserID : n/a
ClientWrkstnName : n/a
ClientApplName : n/a
ClientAccntng : n/a
List of active statements :
*UOW-ID : 1
Activity ID : 1
Package Schema : NULLID
Package Name : SQLC2H20
Package Version :
Section Number : 203
SQL Type : Dynamic
Isolation : CS
Statement Type : DDL, (not Set Constraints)
Statement : drop table
db2admin.T_DA_JKDA_JKZK
--------------------------------------------------------------------------------
[db2inst1@db ~]$ db2 "force application (40)" -->此时Kill掉select查询sessin1后,session2中的drop可以正常执行
DB20000I The FORCE APPLICATION command completed successfully.
DB21024I This command is asynchronous and may not be effective immediately.
[db2inst1@db ~]$ db2pd -d test -apinfo 33
Database Partition 0 -- Database TEST -- Active -- Up 0 days 00:01:01
Application :
Address : 0x0000000200DA0080
AppHandl [nod-index] : 33 [000-00033]
TranHdl : 2
Application PID : 4963
Application Node Name : db
IP Address: n/a
Connection Start Time : (1508376547)Thu Oct 19 09:29:07 2017
Client User ID : db2inst1
System Auth ID : DB2INST1
Coordinator EDU ID : 17
Coordinator Partition : 0
Number of Agents : 1
Locks timeout value : NotSet
Locks Escalation : No
Workload ID : 1
Workload Occurrence ID : 2
Trusted Context : n/a
Connection Trust Type : non trusted
Role Inherited : n/a
Application Status : UOW-Waiting
Application Name : db2bp
Application ID : *LOCAL.db2inst1.171019012907
ClientUserID : n/a
ClientWrkstnName : n/a
ClientApplName : n/a
ClientAccntng : n/a
--------------------------------------------------------------------------------
Oracle中实验如下:
session1中:
SQL> create table test as select * from dba_objects;
SQL> select * from dba_objects; --此处会执行很长时间
session2中
SQL> drop table test; -->在session1中的select执行期间,在session2中执行drop操作,此时可以正常删除test表
Table dropped.
SQL> select * from test;
select * from test
*
ERROR at line 1:
ORA-00942: table or view does not exist