[jira] [Created] (HIVE-24589) Drop catalog failing with deadlock error for Oracle backend dbms.

["mahesh kumar behera (Jira)" <ji...@apache.org>]

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)