oracle死锁父母/孩子和孩子已经索引FK [英] oracle deadlock parent/child and child has indexed FK

查看:367
本文介绍了oracle死锁父母/孩子和孩子已经索引FK的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我需要有人帮我弄清以下僵局的原因。
涉及的表是父/子,子表已经索引FK。
parent:PK_FMS_FC_MAIN_FLD_INPUT_LIMIT
chile:FMS_FC_REL_FLD_INPUT_LIMIT
$ b

 死锁图:
----- ---- Blocker(s)-------- --------- Waiter(s)---------
资源名称进程会话持有等待进程会话持有等待
TX-0019000b-0000b486 22 2755 X 57 492 S
TX-00010019-00061e13 57 492 X 22 2755 S

会话2755:DID 0001-0016-00000FCE会话492:DID 0001-0039-00000719
会话492:DID 0001-0039-00000719会话2755:DID 0001-0016-00000FCE

等待的行:
会话2755:obj - rowid = 000DB256 - AADbJWABbAAAYJQAAA
(dictionary objn - 897622,file - 91,block - 98896,slot - 0)
Session 492:no row

-----其他等待会议的信息-----
Session 492:
sid:492 ser:2757 audsid:132281277 user:364 / GKS_IM_9990740
flags:(0x41)US R / - flags_idl:(0x1)BSY / - / - / - / - / -
flags2:(0x40009) - / - / INC
pid:57 O / S info:user:grid,term :UNKNOWN,ospid:16372
image:oracle @ hgksdb01
客户端信息:
O / S信息:user:imart,term:unknown,ospid:1234
machine:hgkswa05 program :JDBC瘦客户端
应用程序名称:JDBC瘦客户端,哈希值= 2546894660
当前SQL:
删除FMS_FC_MAIN_FLD_INPUT_LIMIT MAIN WHERE MAIN.ID =:B1

- ----其他等待会议的信息结束-----

本届会议信息:

-----本次会议的当前SQL语句(B4,:B3,:B2,SYSDATE,:B1,SYSDATE)$ b $ INSERT INTO FMS_FC_REL_FLD_INPUT_LIMIT(ID,RELATION_FIELD_MASTER_CD,CREATE_USER_CD,CREATE_DATE,RECORD_USER_CD,RECORD_DATE)
----- PL / SQL堆栈-----
----- PL / SQL调用堆栈-----
对象行对象
句柄编号名称
0x1f37f7a58 462包体GKS_IM_9990740.ZPKG_PRJ_FLDREL
0x1ed1a9d38 1匿名块
================================= ==================

CREATE TABLE GKS_IM_9990740.FMS_FC_MAIN_FLD_INPUT_LIMIT

ID NUMBER(18,0)NOT NULL,
COMPANY_CD VARCHAR2(100)NOT NULL,
MAIN_FIELD_TYPE VARCHAR2(100)NOT NULL,
RELATION_FIELD_TYPE VARCHAR2(100)NOT NULL,
START_DATE DATE NOT NULL,
MAIN_FIELD_MASTER_CD VARCHAR2(1000)NOT NULL,
MAIN_FIELD_SUB_CD VARCHAR2(1000),
IS_INPUT_ENABLE VARCHAR2(1)NOT NULL,
IS_INPUT_COMPULSORY VARCHAR2(1)NOT NULL,
DEFAULT_VALUE VARCHAR2(1000) ,
IS_INPUT_LIMIT VARCHAR2(1)NOT NULL,
CREATE_USER_CD VARCHAR2(100)NOT NULL,
CREATE_DATE TIMESTAMP(6)NOT NULL,
RECORD_USER_CD VARCHAR2(100),
RECORD_DATE TIMESTAMP(6),
CONSTRAINT PK_FMS_FC_MAIN_FLD_INPUT_LIMIT使用索引的主键(ID)
PCTFREE 10
INITRANS 2
MAXTRANS 255
TABLESPACE FMS_DATA
STORAGE(INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT)
LOGGING


CREATE TABLE GKS_IM_9990740.FMS_FC_REL_FLD_INPUT_LIMIT

$ NUMBER(18,0)NOT NULL,
RELATION_FIELD_MASTER_CD VARCHAR2(1000)NOT NULL,
CREATE_USER_CD VARCHAR2(100)NOT NULL,
CREATE_DATE TIMESTAMP(6)NOT NULL,
RECORD_USER_CD VARCHAR2(100),
RECORD_DATE TIMESTAMP(6),
CONSTRAINT PK_FMS_FC_REL_FLD_INPUT_LIMIT PRIMARY KEY(ID,RELATION_FIELD_MASTER_CD)使用索引
PCTFREE 10
INITRANS 2
MAXTRANS 255
TABLESPACE FMS_DATA
存储(初始64K下一个MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DE FAULT)
LOGGING,
CONSTRAINT FK_FMS_FC_REL_FLD_INPUT_LIMIT0 FOREIGN KEY(ID)REFERENCES GKS_IM_9990740.FMS_FC_MAIN_FLD_INPUT_LIMIT(ID)

PCTFREE 10
MAXTRANS 255
TABLESPACE FMS_DATA
STORAGE(初始64K NEXT 1M MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT)
NOCACHE
LOGGING

CREATE INDEXGKS_IM_9990740。FK_FMS_FC_REL_FLD_INPUT_LIMITONGKS_IM_9990740。FMS_FC_REL_FLD_INPUT_LIMIT

ID

PCTFREE 10 INITRANS 2 MAXTRANS 255计算统计存储

初始值65536下一个1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT

TABLESPACEFMS_DATA;


解决方案

涉及死锁的队列是TX,而不是TM,这与没有索引的外键问题没有任何关系。
2.)由于会话正在等待TX'S'(共享)模式,这不是行级锁定问题。
3.)由于其中一个陈述是一个INSERT,所以不会成为ITL插槽短缺的问题。

所以,上面,我最好的猜测是:
删除,位图索引或ITL插槽短缺。
为插入,重叠pk / uk。

希望有帮助....


I need somebody help me to figure out the cause of following deadlock. the involved tables are parent/child and child table has indexed FK. parent:PK_FMS_FC_MAIN_FLD_INPUT_LIMIT chile:FMS_FC_REL_FLD_INPUT_LIMIT

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-0019000b-0000b486        22    2755     X             57     492           S
TX-00010019-00061e13        57     492     X             22    2755           S

session 2755: DID 0001-0016-00000FCE    session 492: DID 0001-0039-00000719 
session 492: DID 0001-0039-00000719 session 2755: DID 0001-0016-00000FCE 

Rows waited on:
  Session 2755: obj - rowid = 000DB256 - AADbJWABbAAAYJQAAA
  (dictionary objn - 897622, file - 91, block - 98896, slot - 0)
  Session 492: no row

----- Information for the OTHER waiting sessions -----
Session 492:
  sid: 492 ser: 2757 audsid: 132281277 user: 364/GKS_IM_9990740
    flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
    flags2: (0x40009) -/-/INC
  pid: 57 O/S info: user: grid, term: UNKNOWN, ospid: 16372
    image: oracle@hgksdb01
  client details:
    O/S info: user: imart, term: unknown, ospid: 1234
    machine: hgkswa05 program: JDBC Thin Client
    application name: JDBC Thin Client, hash value=2546894660
  current SQL:
  DELETE FROM FMS_FC_MAIN_FLD_INPUT_LIMIT MAIN WHERE MAIN.ID = :B1 

----- End of information for the OTHER waiting sessions -----

Information for THIS session:

----- Current SQL Statement for this session (sql_id=56azrvwd48huf) -----
INSERT INTO FMS_FC_REL_FLD_INPUT_LIMIT( ID, RELATION_FIELD_MASTER_CD, CREATE_USER_CD, CREATE_DATE, RECORD_USER_CD, RECORD_DATE ) VALUES( :B4 , :B3 , :B2 , SYSDATE, :B1 , SYSDATE )
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x1f37f7a58       462  package body GKS_IM_9990740.ZPKG_PRJ_FLDREL
0x1ed1a9d38         1  anonymous block
===================================================

CREATE TABLE GKS_IM_9990740.FMS_FC_MAIN_FLD_INPUT_LIMIT
(
ID                             NUMBER(18,0) NOT NULL,
COMPANY_CD                     VARCHAR2(100) NOT NULL,
MAIN_FIELD_TYPE                VARCHAR2(100) NOT NULL,
RELATION_FIELD_TYPE            VARCHAR2(100) NOT NULL,
START_DATE                     DATE NOT NULL,
MAIN_FIELD_MASTER_CD           VARCHAR2(1000) NOT NULL,
MAIN_FIELD_SUB_CD              VARCHAR2(1000),
IS_INPUT_ENABLE                VARCHAR2(1) NOT NULL,
IS_INPUT_COMPULSORY            VARCHAR2(1) NOT NULL,
DEFAULT_VALUE                  VARCHAR2(1000),
IS_INPUT_LIMIT                 VARCHAR2(1) NOT NULL,
CREATE_USER_CD                 VARCHAR2(100) NOT NULL,
CREATE_DATE                    TIMESTAMP(6) NOT NULL,
RECORD_USER_CD                 VARCHAR2(100),
RECORD_DATE                    TIMESTAMP(6),
CONSTRAINT PK_FMS_FC_MAIN_FLD_INPUT_LIMIT PRIMARY KEY (ID) USING INDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
TABLESPACE FMS_DATA
STORAGE(INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT)
LOGGING
)

CREATE TABLE GKS_IM_9990740.FMS_FC_REL_FLD_INPUT_LIMIT
(
ID                             NUMBER(18,0) NOT NULL,
RELATION_FIELD_MASTER_CD       VARCHAR2(1000) NOT NULL,
CREATE_USER_CD                 VARCHAR2(100) NOT NULL,
CREATE_DATE                    TIMESTAMP(6) NOT NULL,
RECORD_USER_CD                 VARCHAR2(100),
RECORD_DATE                    TIMESTAMP(6),
CONSTRAINT PK_FMS_FC_REL_FLD_INPUT_LIMIT PRIMARY KEY (ID, RELATION_FIELD_MASTER_CD) USING INDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
TABLESPACE FMS_DATA
STORAGE(INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT)
LOGGING,
CONSTRAINT FK_FMS_FC_REL_FLD_INPUT_LIMIT0 FOREIGN KEY (ID) REFERENCES GKS_IM_9990740.FMS_FC_MAIN_FLD_INPUT_LIMIT (ID)
)
PCTFREE 10
MAXTRANS 255
TABLESPACE FMS_DATA
STORAGE(INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT)
NOCACHE
LOGGING

CREATE INDEX "GKS_IM_9990740"."FK_FMS_FC_REL_FLD_INPUT_LIMIT" ON "GKS_IM_9990740"."FMS_FC_REL_FLD_INPUT_LIMIT"
(
"ID"
)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE
(
INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
)
TABLESPACE "FMS_DATA" ;

解决方案

Some thoughts: 1.) Since the enqueues involved in the deadlock are TX, and not TM, this has nothing to do with the unindexed foreign key problem. 2.) Since the sessions are waiting on TX 'S' (shared) mode, this is not a row-level locking problem. 3.) Since one of the statements involved is an INSERT, that one is not going to be a problem with ITL slot shortage.

So, given the above, my best guesses are: For the delete, bitmap index or ITL slot shortage. for the insert, overlapping pk/uk.

Hope that helps....

这篇关于oracle死锁父母/孩子和孩子已经索引FK的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆