lock wait 与 thread dump 有关，与 堆 dump无关，要分析为什么该语句执行如此长时间
分析表是否需要归档，是否需要加索引
Application Snapshot
Application handle = 18432
Application status = Lock-wait
Status change time = 03/01/2018 15:45:01.776361
Application code page = 1208
Application country/region code = 0
DUOW correlation token = 128.232.11.45.37537.191124004045
Application name = db2jcc_application
Application ID = 128.232.11.45.37537.191124004045 //试图加锁的applicationid
Sequence number = 09409
TP Monitor client user ID =
TP Monitor client workstation name = 128.232.11.45 //IP
TP Monitor client application name =
TP Monitor client accounting string =
Connection request start timestamp = 02/08/2018 22:07:03.930098
Connect request completion timestamp = 02/08/2018 22:07:03.930875
Application idle time =
CONNECT Authorization ID = ESUSR
Client login ID = ESUSR
Configuration NNAME of client = 128.232.11.45
Client database manager product ID = JCC04170
Process ID of client application = 0
Platform of client application = Unknown via DRDA
Communication protocol of client = TCP/IP
Inbound communication address = 128.232.11.45 37537
Database name = ESERVICE
Database path = /db2data/db2inst2/NODE0000/SQL00001/MEMBER0000/
Client database alias = ESERVICE
Input database alias =
Last reset timestamp =
Snapshot timestamp = 03/01/2018 16:45:01.361049
Authorization level granted =
User authority:
DBADM authority
CREATETAB authority
BINDADD authority
CONNECT authority
CREATE_NOT_FENC authority
LOAD authority
IMPLICIT_SCHEMA authority
CREATE_EXT_RT authority
QUIESCE_CONN authority
DATAACCESS authority
ACCESSCTRL authority
Group authority:
CREATETAB authority
BINDADD authority
CONNECT authority
IMPLICIT_SCHEMA authority
Coordinator member number = 0
Current member number = 0
Coordinator agent process or thread ID = 21001
Current Workload ID = 1
Agents stolen = 0
Agents waiting on locks = 1
Maximum associated agents = 1
Priority at which application agents work = 0
Priority type = Dynamic
Lock timeout (seconds) = -1
Locks held by application = 0
Lock waits since connect = 81
Time application waited on locks (ms) = 12015802
Deadlocks detected = 0
Lock escalations = 0
Exclusive lock escalations = 0
Number of Lock Timeouts since connected = 0
Total time UOW waited on locks (ms) = 3599584
Total sorts = 18772
Total sort time (ms) = 302175
Total sort overflows = 53
Buffer pool data logical reads = 1072290175
Buffer pool data physical reads = 307179068
Buffer pool temporary data logical reads = 3954333
Buffer pool temporary data physical reads = 9143
Buffer pool data writes = 25055
Buffer pool index logical reads = 497387710
Buffer pool index physical reads = 8012386
Buffer pool temporary index logical reads = 0
Buffer pool temporary index physical reads = 0
Buffer pool index writes = 53
Buffer pool xda logical reads = 0
Buffer pool xda physical reads = 0
Buffer pool temporary xda logical reads = 0
Buffer pool temporary xda physical reads = 0
Buffer pool xda writes = 0
Total buffer pool read time (milliseconds) = 5536179
Total buffer pool write time (milliseconds)= 25887
Time waited for prefetch (ms) = 52187946
Unread prefetch pages = 123336792
Direct reads = 485519
Direct writes = 43021
Direct read requests = 200544
Direct write requests = 8973
Direct reads elapsed time (ms) = 10276
Direct write elapsed time (ms) = 165452
Number of SQL requests since last commit = 5
Commit statements = 39382
Rollback statements = 23
Dynamic SQL statements attempted = 336036
Static SQL statements attempted = 39405
Failed statement operations = 0
Select SQL statements executed = 322607
Xquery statements executed = 0
Update/Insert/Delete statements executed = 33739
DDL statements executed = 0
Inactive stmt history memory usage (bytes) = 0
Internal automatic rebinds = 0
Internal rows deleted = 0
Internal rows inserted = 0
Internal rows updated = 0
Internal commits = 1
Internal rollbacks = 0
Internal rollbacks due to deadlock = 0
Binds/precompiles attempted = 0
Rows deleted = 27456 //受影响的范围
Rows inserted = 31405 //
Rows updated = 9710 //
Rows selected = 625102 //
Rows read = 10208822650 //
Rows written = 5663645 //
UOW log space used (Bytes) = 0
Previous UOW completion timestamp = 03/01/2018 15:44:59.986395
Elapsed time of last completed uow (sec.ms)= 0.000789
UOW start timestamp = 03/01/2018 15:44:59.987573
UOW stop timestamp =
UOW completion status =
Open remote cursors = 1
Open remote cursors with blocking = 1
Rejected Block Remote Cursor requests = 0
Accepted Block Remote Cursor requests = 322607
Open local cursors = 0
Open local cursors with blocking = 0
Total User CPU Time used by agent (s) = 6136.916155
Total System CPU Time used by agent (s) = 924.436024
Host execution elapsed time = 87472.497822
Package cache lookups = 331480
Package cache inserts = 4873
Application section lookups = 1000056
Application section inserts = 84778
Catalog cache lookups = 75456
Catalog cache inserts = 512
Catalog cache overflows = 0
Catalog cache high water mark = 0
Workspace Information
Most recent operation = Fetch
Cursor name = SQL_CURSH200C2
Most recent operation start timestamp = 03/01/2018 15:44:59.987670
Most recent operation stop timestamp =
Agents associated with the application = 1
Number of hash joins = 28538
Number of hash loops = 0
Number of hash join overflows = 2378
Number of small hash join overflows = 367
Number of OLAP functions = 0
Number of OLAP function overflows = 0
Statement type = Dynamic SQL Statement
Statement = Fetch
Section number = 2
Application creator = NULLID
Package name = SYSSH200
Consistency Token =
Package Version ID =
Cursor name = SQL_CURSH200C2
Statement member number = 0
Statement start timestamp = 03/01/2018 15:44:59.987670
Statement stop timestamp =
Elapsed time of last completed stmt(sec.ms)= 0.024236
Total Statement user CPU time = 0.136925
Total Statement system CPU time = 0.104202
SQL compiler cost estimate in timerons = 9586
SQL compiler cardinality estimate = 62
Degree of parallelism requested = 1
Number of agents working on statement = 1
Number of subagents created for statement = 1
Statement sorts = 0
Total sort time = 0
Sort overflows = 0
Rows read = 78888
Rows written = 0
Rows deleted = 0
Rows updated = 0
Rows inserted = 0
Rows fetched = 555
Buffer pool data logical reads = 2830
Buffer pool data physical reads = 1029
Buffer pool temporary data logical reads = 0
Buffer pool temporary data physical reads = 0
Buffer pool index logical reads = 0
Buffer pool index physical reads = 0
Buffer pool temporary index logical reads = 0
Buffer pool temporary index physical reads = 0
Buffer pool xda logical reads = 0
Buffer pool xda physical reads = 0
Buffer pool temporary xda logical reads = 0
Buffer pool temporary xda physical reads = 0
Blocking cursor = YES
Dynamic SQL statement text: //准备执行的语句
select this_."id" as id1_132_0_, this_."clm_id" as clm2_132_0_, this_."clm_no" as clm3_132_0_, this_."run_times" as run4_132_0_, this_."success" as success5_132_0_, this_."time" as time6_132_0_, this_."type" as type7_132_0_ from CLM_WARNING this_ where this_."success"=? and this_."type"=? and this_."time">?
Agent process/thread ID = 21001
Memory usage for application:
Memory Pool Type = Application Heap
Current size (bytes) = 131072
High water mark (bytes) = 131072
Configured size (bytes) = 1048576
Agent process/thread ID = 21001
Agent Lock timeout (seconds) = -1
Memory usage for agent:
Memory Pool Type = Other Memory
Current size (bytes) = 655360
High water mark (bytes) = 720896
Configured size (bytes) = 25769803776
//下面是冲突的信息
ID of agent holding lock = 12809
//当前表正在被这个IP的应用锁住了，经了解是insert
Application ID holding lock = 128.232.12.154.44475.191125032916
Lock name = 0x0002080D0000005631C2000A52
Lock attributes = 0x00000000
Release flags = 0x00000000
Lock object type = Row //行锁
Lock mode = Exclusive Lock (X) //排他锁
Lock mode requested = Next Key Share (NS)
Name of tablespace holding lock = USERSPACE1
Schema of table holding lock = ESERVICE
Name of table holding lock = CLM_WARNING
Data Partition Id of table holding lock = 0
Lock wait start timestamp = 03/01/2018 15:45:01.776362
经了解执行select语句时试图加锁，但是无法加锁，因为被insert语句的锁锁住了，首先我们要问这样正常吗？正常
insert是要加锁的，但select为什么要加锁呢？
1、带着这个问题，我们得首先将问题simulate出来：
select * from CLM_WARNING with rr 加锁，然后重新执行程序，发觉出现了生产一样的日志，证实确实是select语句无法加锁引起
2、经查 11.45的应用，她选用了hibernate，该应用是设置了事务隔离级别的
<prop key="hibernate.connection.isolation">1</prop>
都设置成了 READ_UNCOMMITTED，这个级别select 是不加锁的
3、所以问题来了，原来的select的加锁证明当前事务级别较高，但真实的配置，级别是很低的，证明配置不起作用
4、明白结构：应用程序 -〉spring -> hibernate -> DB2
应用程序及spring都没有事务级别的设置，hitbernate的不起作用，现在正是用了DB2默认的 readcommit 级别。
5、我们不明白为什hibernate的配置不起作用，但可转弯，尝试使用spring修改事务级别，经修改，生效，解决。
声明式：
@Transactional(isolation = Isolation.READ_UNCOMMITTED)
xml方式： isolation="DEFAULT"
<tx:advice id="advice" transaction-manager="txManager">
<tx:attributes>
<!-- tx:method的属性:
* name 是必须的,表示与事务属性关联的方法名(业务方法名),对切入点进行细化。通配符（*）可以用来指定一批关联到相同的事务属性的方法。
如：'get*'、'handle*'、'on*Event'等等.
* propagation 不是必须的 ，默认值是REQUIRED
表示事务传播行为, 包括REQUIRED,SUPPORTS,MANDATORY,REQUIRES_NEW,NOT_SUPPORTED,NEVER,NESTED
* isolation 不是必须的 默认值DEFAULT
表示事务隔离级别(数据库的隔离级别)
* timeout 不是必须的 默认值-1(永不超时)
表示事务超时的时间（以秒为单位）
* read-only 不是必须的 默认值false不是只读的
表示事务是否只读？
* rollback-for 不是必须的
表示将被触发进行回滚的 Exception(s)；以逗号分开。
如：'com.foo.MyBusinessException,ServletException'
* no-rollback-for 不是必须的
表示不被触发进行回滚的 Exception(s)；以逗号分开。
如：'com.foo.MyBusinessException,ServletException'
任何 RuntimeException 将触发事务回滚，但是任何 checked Exception 将不触发事务回滚
-->
<tx:method name="save*" propagation="REQUIRED" isolation="DEFAULT" read-only="false"/>
<tx:method name="update*" propagation="REQUIRED" isolation="DEFAULT" read-only="false"/>
<tx:method name="delete*" propagation="REQUIRED" isolation="DEFAULT" read-only="false"/>
<!-- 其他的方法之只读的 -->
<tx:method name="*" read-only="true"/>
</tx:attributes>
</tx:advice>
----------------------------------------------------------------------
spring 事务隔离级别isolation有5种：
ISOLATION_DEFAULT
使用底层数据库默认的隔离级别spring事务隔离级别的默认值
ISOLATION_READ_UNCOMMITED
充许另一个事务可以读到这个事务未提交的数据可能导致脏读、不可重复读和幻读。
ISOLATION_READ_COMMITED
保证一个事务修改的数据提交后才能被另一个事务读取可能导致不可重复读和幻读。
ISOLATION_REPEATABLE_READ
要求对相同字段的多次读取的结果必须相同，除非事务本身更新了数据可能导致幻读。
ISOLATION_SERIALIZABLE
事务被处理为顺序执行可以防止脏读、不可重复读、幻读。
217
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
