SQL & PL/SQL

Thanks for taking my questions!

I have a job that kicks off 20 threads of the below update statement. Each thread processes a unique range of EMPL_IDs. Every so often 1 or more of the threads fail with a deadlock error. I know this is a program problem but don't know how to fix it.

Why would I get a deadlock error when each thread processes a different range of EMPL_ID's?

Would creating a unique key help?


Thanks!
Kathie

11g/windows

UPDATE PS_EAUT_TRM001 SET TRM1_STATUS = ' ',TRM1_PROCESSED = 0 WHERE TRM1_EMPL_ID >= :1 AND TRM1_EMPL_ID < :2


CREATE TABLE PS_EAUT_TRM001
(
TRM1_I_DATE DATE,
TRM1_STATUS VARCHAR2(1 CHAR),
TRM1_PROCESSED NUMBER(15),
TRM1_EMPL_ID VARCHAR2(8 CHAR) NOT NULL,
TRM1_CAREER VARCHAR2(4 CHAR) NOT NULL,
TRM1_CAR_NBR NUMBER(3) NOT NULL,
TRM1_EFFDT DATE NOT NULL,
TRM1_STRM_PR_ADMIT VARCHAR2(4 CHAR),
TRM1_PLAN VARCHAR2(10 CHAR),
TRM1_PLAN2 VARCHAR2(10 CHAR),
TRM1_PLAN3 VARCHAR2(10 CHAR),
TRM1_PROG_ACTION VARCHAR2(4 CHAR),
TRM1_PROG_REASON VARCHAR2(4 CHAR),
TRM1_STAND_ACTION VARCHAR2(4 CHAR),
TRM1_WDRAW_ACTION VARCHAR2(4 CHAR),
TRM1_LEAVE_ACTION VARCHAR2(4 CHAR),
TRM1_STRM_PROG_REQ VARCHAR2(4 CHAR),
TRM1_STRM_CAR_REQ VARCHAR2(4 CHAR),
TRM1_STRM_GRAD VARCHAR2(4 CHAR),
TRM1_GRAD_EFFDT DATE,
TRM1_2ND_CAR_PLAN VARCHAR2(10 CHAR),
TRM1_DEG_GRANTED NUMBER(1),
TRM1_TERM NUMBER(6),
TRM1_PUBLIC_ID NUMBER(9),
TRM1_EFF_STRM VARCHAR2(4 CHAR),
PREPROC_NOTES VARCHAR2(128 CHAR)
)
TABLESPACE PSDEFAULT
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;


CREATE INDEX IDX01_TRM001 ON PS_EAUT_TRM001
(TRM1_EMPL_ID, TRM1_CAREER, TRM1_CAR_NBR, TRM1_EFFDT)
NOLOGGING
TABLESPACE PSINDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 40K
NEXT 104K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;


CREATE INDEX PS0EAUT_TRM001 ON PS_EAUT_TRM001
(TRM1_STATUS, TRM1_I_DATE, TRM1_EMPL_ID, TRM1_CAREER, TRM1_CAR_NBR,
TRM1_EFFDT)
LOGGING
TABLESPACE PSINDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 40K
NEXT 104K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;


CREATE INDEX PS1EAUT_TRM001 ON PS_EAUT_TRM001
(TRM1_PROCESSED, TRM1_I_DATE, TRM1_EMPL_ID, TRM1_CAREER, TRM1_CAR_NBR,
TRM1_EFFDT)
LOGGING
TABLESPACE PSINDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 40K
NEXT 104K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;


CREATE INDEX PS2EAUT_TRM001 ON PS_EAUT_TRM001
(TRM1_PLAN, TRM1_I_DATE, TRM1_EMPL_ID, TRM1_CAREER, TRM1_CAR_NBR,
TRM1_EFFDT)
LOGGING
TABLESPACE PSINDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 40K
NEXT 104K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;


CREATE INDEX PS3EAUT_TRM001 ON PS_EAUT_TRM001
(TRM1_PLAN2, TRM1_I_DATE, TRM1_EMPL_ID, TRM1_CAREER, TRM1_CAR_NBR,
TRM1_EFFDT)
LOGGING
TABLESPACE PSINDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 40K
NEXT 104K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;


CREATE INDEX PS4EAUT_TRM001 ON PS_EAUT_TRM001
(TRM1_PLAN3, TRM1_I_DATE, TRM1_EMPL_ID, TRM1_CAREER, TRM1_CAR_NBR,
TRM1_EFFDT)
LOGGING
TABLESPACE PSINDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 40K
NEXT 104K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;


CREATE INDEX PS5EAUT_TRM001 ON PS_EAUT_TRM001
(TRM1_PROG_ACTION, TRM1_I_DATE, TRM1_EMPL_ID, TRM1_CAREER, TRM1_CAR_NBR,
TRM1_EFFDT)
LOGGING
TABLESPACE PSINDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 40K
NEXT 104K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;


CREATE INDEX PS6EAUT_TRM001 ON PS_EAUT_TRM001
(TRM1_PUBLIC_ID, TRM1_I_DATE, TRM1_EMPL_ID, TRM1_CAREER, TRM1_CAR_NBR,
TRM1_EFFDT)
LOGGING
TABLESPACE PSINDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 40K
NEXT 104K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;


CREATE UNIQUE INDEX PS_EAUT_TRM001 ON PS_EAUT_TRM001
(TRM1_I_DATE, TRM1_EMPL_ID, TRM1_CAREER, TRM1_CAR_NBR, TRM1_EFFDT)
LOGGING
TABLESPACE PSINDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 40K
NEXT 104K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;