[
https://issues.apache.org/jira/browse/HIVE-24589?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
mahesh kumar behera resolved HIVE-24589.
----------------------------------------
Resolution: Fixed
> 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
> Priority: Major
> Labels: pull-request-available
> Time Spent: 20m
> Remaining Estimate: 0h
>
> 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)
