oracle – Deadlock detected but can’t identify it

بدون دیدگاه


I’m getting ORA-00060 Deadlock detected in our Oracle Database and i’m trying to figure out what is firing this.

In the trace file i got this:

Deadlock graph:
                                      ---------Blocker(s)--------  ---------
Waiter(s)---------
Resource Name                             process session holds waits  
process session holds waits
TM-00018269-00000000-00000000-00000000         79     428    SX             81      73    SX   SSX
TM-000285FE-00000000-00000000-00000000         81      73    SX             79     428    SX   SSX

session 428: DID 0001-004F-000011C8     session 73: DID 0001-0051-00000293
session 73: DID 0001-0051-00000293      session 428: DID 0001-004F-000011C8

Rows waited on:
  Session 428: no row
  Session 73: no row

Searching about it, i’ve found this article from Arup Nanda:
http://arup.blogspot.com.es/2013/04/application-design-is-only-reason-for.html

and it seems to be a Foreign Key related deadlock.

But i’ve checked all my tables and foreign keys and i did not found anything.

The “Current SQL” field is:

UPDATE CL_PEDIDOLINEA SET IDPEDIDOESTADO = :B2 WHERE ID = :B1

That table has the following structure:

TABLE CL_PEDIDOLINEA
(
  ID                      NUMBER,
  IDPEDIDO                NUMBER                NOT NULL,
  IDPEDIDOESTADO          NUMBER                NOT NULL,
  IDPEDIDOLINEAORIGEN     NUMBER,
  IDPRODUCTOREFERENCIA    NUMBER                NOT NULL,
  IDDIRECCION             NUMBER                NOT NULL,
  FECHALIMITE             DATE,
  FECHACOMPLETADO         DATE,
  FECHAANULADO            DATE,
  EAN                     NUMBER                NOT NULL,
  REFERENCIA              VARCHAR2(32 BYTE)     NOT NULL,
  NOMBRE                  VARCHAR2(1024 BYTE)   NOT NULL,
  CANTIDAD                NUMBER                DEFAULT 0                     NOT NULL,
  PRECIO                  NUMBER(22,4)          DEFAULT 0                     NOT NULL,
  DTO                     NUMBER(22,2)          DEFAULT 0                     NOT NULL,
  PRECIODTO               NUMBER(22,4)          DEFAULT 0                     NOT NULL,
  IDIMPUESTO              NUMBER                NOT NULL,
  TASAIMPUESTO            NUMBER(22,2)          DEFAULT 0                     NOT NULL,
  CUOTAIMPUESTO           NUMBER(22,2)          DEFAULT 0                     NOT NULL,
  TASARECARGO             NUMBER(22,2)          DEFAULT 0                     NOT NULL,
  CUOTARECARGO            NUMBER(22,2)          DEFAULT 0                     NOT NULL,
  IDIMPUESTO_GASTOSENVIO  NUMBER,
  TASAGASTOSENVIO         NUMBER(22,2)          DEFAULT 0,
  CUOTAGASTOSENVIO        NUMBER(22,2)          DEFAULT 0,
  SUBTOTAL                NUMBER(22,2)          DEFAULT 0                     NOT NULL,
  OBS_PRODUCCION          VARCHAR2(4000 BYTE),
  OBS_PREPRODUCCION       VARCHAR2(4000 BYTE)
)

Indices:

CREATE INDEX IDDIRECCION ON CL_PEDIDOLINEA
(IDDIRECCION)
LOGGING
TABLESPACE CLOUDIA
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           );

CREATE INDEX IDPEDIDO ON CL_PEDIDOLINEA
(IDPEDIDO)
LOGGING
TABLESPACE CLOUDIA
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           );

CREATE INDEX IDPEDIDOESTADO ON CL_PEDIDOLINEA
(IDPEDIDOESTADO)
LOGGING
TABLESPACE CLOUDIA
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           );

CREATE INDEX IDPEDIDOLINEAORIGEN ON CL_PEDIDOLINEA
(IDPEDIDOLINEAORIGEN)
LOGGING
TABLESPACE CLOUDIA
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           );

CREATE INDEX IDPRODUCTOREFERENCIA ON CL_PEDIDOLINEA
(IDPRODUCTOREFERENCIA)
LOGGING
TABLESPACE CLOUDIA
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           );

Constraints:

ALTER TABLE CL_PEDIDOLINEA ADD (
  CONSTRAINT CL_PEDIDOLINEA_PK
  PRIMARY KEY
  (ID)
  USING INDEX CL_PEDIDOLINEA_PK
  ENABLE VALIDATE);

ALTER TABLE CL_PEDIDOLINEA ADD (
  CONSTRAINT CL_PEDIDOLINEA_R01 
  FOREIGN KEY (IDPEDIDO) 
  REFERENCES CL_PEDIDO (ID)
  ENABLE VALIDATE,
  CONSTRAINT CL_PEDIDOLINEA_R02 
  FOREIGN KEY (IDPEDIDOESTADO) 
  REFERENCES CL_PEDIDOLINEAESTADO (ID)
  ENABLE VALIDATE,
  CONSTRAINT CL_PEDIDOLINEA_R03 
  FOREIGN KEY (IDPRODUCTOREFERENCIA) 
  REFERENCES CL_PRODUCTOREFERENCIA (ID)
  ENABLE VALIDATE,
  CONSTRAINT CL_PEDIDOLINEA_R04 
  FOREIGN KEY (IDPEDIDOLINEAORIGEN) 
  REFERENCES CL_PEDIDOLINEA (ID)
  ENABLE VALIDATE,
  CONSTRAINT CL_PEDIDOLINEA_R05 
  FOREIGN KEY (IDDIRECCION) 
  REFERENCES CL_TERCERODIRECCION (ID)
  ENABLE VALIDATE,
  CONSTRAINT CL_PEDIDOLINEA_R06 
  FOREIGN KEY (IDIMPUESTO_GASTOSENVIO) 
  REFERENCES CL_IMPUESTO (ID)
  ENABLE VALIDATE);

And all related constrained tables’ fields have indexes.

I dont know how can I figure out what is firing the deadlocks.

Could you help me?

Thanks in advance.

Ps. Sorry by my poor English 🙂



لینک منبع

برچسب‌ها: ,,,
  • نویسنده
    A-brand
  • تعداد بازدید
    18 views
0دیدگاه فرستاده شده است.
شما هم دیدگاه خود را بنویسید