mahesh kumar behera created HIVE-24589:
------------------------------------------
Summary: Drop catalog failing with deadlock error for Oracle
backend dbms.
Key: HIVE-24589
URL: https://issues.apache.org/jira/browse/HIVE-24589
Project: Hive
Issue Type: Bug
Reporter: mahesh kumar behera
Assignee: mahesh kumar behera
When we do a drop catalog we drop the catalog from the CTLGS table. The DBS
table has a foreign key reference on CTLGS for CTLG_NAME. This is causing the
DBS table to be locked exclusively and causing deadlocks. This can be avoided
by creating an index in the DBS table on CTLG_NAME.
{code:java}
CREATE INDEX CTLG_NAME_DBS ON DBS(CTLG_NAME); {code}
{code:java}
Oracle Database maximizes the concurrency control of parent keys in relation
to dependent foreign keys.Locking behaviour depends on whether foreign key
columns are indexed. If foreign keys are not indexed, then the child table will
probably be locked more frequently, deadlocks will occur, and concurrency will
be decreased. For this reason foreign keys should almost always be indexed. The
only exception is when the matching unique or primary key is never updated or
deleted.{code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
