Eugene Koifman created HIVE-16933:
-------------------------------------
Summary: ORA-00060: deadlock detected while waiting on commit
Key: HIVE-16933
URL: https://issues.apache.org/jira/browse/HIVE-16933
Project: Hive
Issue Type: Improvement
Components: Transactions
Affects Versions: 1.2.0
Reporter: Eugene Koifman
When running transactional workload (esp streaming ingest api) with Oracle
backed Hive metastore it's possible to see Deadlock exceptions from the DB.
This due to lack of indexes on Foreign Key columns of Acid related metastore
tables.
For example, TXN_COMPONENTS references TXNS. It should have
CREATE INDEX TC_TXNID_INDEX ON TXN_COMPONENTS (TC_TXNID);
{noformat}
2017-06-20 13:42:00,687 ERROR [pool-3-thread-182]: txn.TxnHandler
(TxnHandler.java:checkRetryable(1952)) - Too many repeated deadlocks in
commitTxn(CommitTxnRequest(txnid:293)), giving up.
2017-06-20 13:42:00,696 ERROR [pool-3-thread-182]: metastore.RetryingHMSHandler
(RetryingHMSHandler.java:invokeInternal(195)) - MetaException(message:Unable to
update transaction database java.sql.SQLException: ORA-00060: deadlock detected
while waiting for resource
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:193)
at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:999)
at
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1315)
at
oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1822)
at
oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1787)
at
oracle.jdbc.driver.OracleStatementWrapper.executeUpdate(OracleStatementWrapper.java:280)
at
org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228)
at
org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228)
at
org.apache.hadoop.hive.metastore.txn.TxnHandler.commitTxn(TxnHandler.java:756)
{noformat}
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
