[ 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)