DB2中，INSERT 语句等待行级别的U锁的两种情景

DB2中，INSERT 语句等待行级别的U锁的两种情景

一般情况下，DB2中的INSERT语句是不会等行级别的U锁或者X锁的，之前的文章中已经有讲述

DB2 锁问题分析与解释

但在某些特殊情况下，INSERT语句也可能会等待它插入对象表的某一行上的U锁，这里列举两个场景：第一个场景是触发器，第二个场景是往有unique index的对象上插入了相同的值。

场景1，触发器导致：

利用到了触发器，这个触发器的作用是每当表有insert语句时，触发执行一条本表上update语句，update语句是需要U锁的，insert就是等的这个U锁：

Session 1(agent id 7):
======================
$ db2 "create table t1(id int)"

$ db2 "insert into t1 values(1),(200),(300),(700),(900)"

//以下触发器的目地是，每往表t1插入一条记录，就在t1 最小的ID字段加+1
$ db2 "create trigger trig1 after insert on t1  for each row mode db2sql update t1 set id = id + 1 where id = (select min(id) from t1)"


Session 2(agent id 41):
=======================
$ db2 +c "delete from t1"
DB20000I  The SQL command completed successfully.


Session 1(agent id 7):
======================
$ db2 "insert into t1 values(1000)"
DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL0911N  The current transaction has been rolled back because of a deadlock 
or timeout.  Reason code "68".  SQLSTATE=40001

如果在SQL0911N发生之前抓取session1的snapshot:

            Application Snapshot

Application handle                         = 7
Application status                         = Lock-wait
Status change time                         = 07/14/2017 00:23:24.856071
Application code page                      = 1208
Application country/region code            = 1

..

Dynamic SQL statement text:
insert into t1 values(1000)

..

  ID of agent holding lock                 = 41
  Application ID holding lock              = *LOCAL.inst105.170714070817
  Lock name                                = 0x03001800040000000000000052
  Lock attributes                          = 0x00000000
  Release flags                            = 0x00000000
  Lock object type                         = Row
  Lock mode                                = Exclusive Lock (X)
  Lock mode requested                      = Update Lock (U)
  Name of tablespace holding lock          = IBMDB2SAMPLEREL
  Schema of table holding lock             = INST105
  Name of table holding lock               = T1
  Data Partition Id of table holding lock  = 0
  Lock wait start timestamp                = 07/14/2017 00:30:10.869618

使用db2look查看表t1的定义，可以看到上面定义的触发器：

SET SYSIBM.NLS_STRING_UNITS = 'SYSTEM';
SET CURRENT SCHEMA = "INST105 ";
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","INST105";
create trigger trig1 after insert on t1  for each row mode db2sql update
t1 set id = id + 1 where id = (select min(id) from t1);

场景2，在有unique index的对象上，插入相同的值：

session 1: 

=======
$ db2 "create table t2 (c1 int not null primary key,c2 int)"


session 2: 

========
$ db2 +c "insert into t2 values (3,2)" 


Session 1:

========
$ db2 +c "insert into t2 values (3,2)" 
DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL0911N  The current transaction has been rolled back because of a deadlock 
or timeout.  Reason code "68".  SQLSTATE=40001

锁超时之前，查看session1的 snapshot:


  ID of agent holding lock                 = 17
  Application ID holding lock              = *LOCAL.inst105.170717080306
  Lock name                                = 0x03000600050000000000000052
  Lock attributes                          = 0x00000000
  Release flags                            = 0x00000000
  Lock object type                         = Row
  Lock mode                                = Exclusive Lock (X)
  Lock mode requested                      = Update Lock (U)
  Name of tablespace holding lock          = IBMDB2SAMPLEREL
  Schema of table holding lock             = INST105
  Name of table holding lock               = T2
  Data Partition Id of table holding lock  = 0
  Lock wait start timestamp                = 07/17/2017 01:06:05.452205