一次lock_wait的解决

我会带着你远行 2022-01-16 13:49 210阅读 0赞

[2019独角兽企业重金招聘Python工程师标准>>> ][2019_Python_] ![hot3.png][]

#### 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="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  
事务被处理为顺序执行可以防止脏读、不可重复读、幻读。

转载于:https://my.oschina.net/u/2277088/blog/3002537

[2019_Python_]: https://my.oschina.net/u/2663968/blog/3061697
[hot3.png]: /images/20220114/68801dd07049421e86939f4e1e2f4e60.png