Q:

Deadlock occurs in broker database while publishing

I am getting deadlock in my broker database(Oracle based) while publishing lot of components/pages in my production environment. The deadlocks occurs in the following statement:
update TAXFACETS set FACET_ISABSTRACT=:1 , FACET_DEPTH=:2 , FACET_HASCHILDREN=:3 , FACET_ID=:4 , FACET_ITEMTYPE=:5 , FACET_KEY=:6 , FACET_LEFT=:7 , FACET_ISNAVIGABLE=:8 , FACET_PARENT=:9 , PUBLICATION_ID=:10 , FACET_RIGHT=:11 , TAXONOMY_ID=:12 , TOTAL_RELATEDITEMS=:13 , FACET_DESCRIPTION=:14 , FACET_NAME=:15 , FACET_ISUSEDFORIDENTIFICATION=:16  where NODE_ID=:17

I can see below message in my deployer cd_core log:
2015-05-21 11:21:33,491 DEBUG SearchIndexerDeployModule - Processing F:\tridion\deployer\incoming_staging\Zip\tcm_0-125870-66560.Content\components.xml file
2015-05-21 11:21:33,491 DEBUG JPAComponentPresentationDAO - Removing component presentation from storage.
2015-05-21 11:21:33,491 DEBUG PreCommitPhase - Executing worker: com.tridion.storage.deploy.workers.ComponentPresentationWorker@1262fa8 on transaction: tcm:0-125868-66560 took: 0
2015-05-21 11:21:33,491 DEBUG PreCommitPhase - Executing worker com.tridion.storage.deploy.workers.ComponentPresentationMetaWorker@7362351f on transaction: tcm:0-125868-66560 this is worker 20 of: 22
2015-05-21 11:21:33,491 DEBUG StorageManagerFactory - Loading a cached DAO for publicationId/typeMapping/itemExtension: 34 / ItemMeta / null
2015-05-21 11:21:33,491 DEBUG StorageManagerFactory - Wrapping DAO's, currently 0 wrappers installed
2015-05-21 11:21:33,491 DEBUG StorageManagerFactory - Loading a cached DAO for publicationId/typeMapping/itemExtension: 34 / ComponentPresentationMeta / null
2015-05-21 11:21:33,491 DEBUG StorageManagerFactory - Wrapping DAO's, currently 0 wrappers installed
2015-05-21 11:21:33,491 DEBUG StorageManagerFactory - Loading a cached DAO for publicationId/typeMapping/itemExtension: 34 / DynamicLinkInfo / null
2015-05-21 11:21:33,491 INFO  StorageManagerFactory - While loading caching DAO, no configuration found, returning uncached.
2015-05-21 11:21:33,491 DEBUG StorageManagerFactory - Storing non cached DAO into caching map as there is no configuration present for the cached version of the DAO
2015-05-21 11:21:33,491 DEBUG StorageManagerFactory - Wrapping DAO's, currently 0 wrappers installed
2015-05-21 11:21:33,491 DEBUG JPADynamicLinkDAO - Removing dynamic links with source TCMURI tcm:34-36190-48
2015-05-21 11:21:33,491 DEBUG CacheChannel - Received event from another VM [CacheEvent eventType=Invalidate regionPath=/com.tridion.storage.ReferenceEntry key=34:tcd:pub[34]/componentmeta[36156]:tcd:pub[34]/pagemeta[36157]]
2015-05-21 11:21:33,491 ERROR DeployPipelineExecutor - Unable to start processing deployment package with transactionId: tcm:0-125866-66560
com.tridion.deployer.ProcessingException: Phase: Deployment Commit Phase failed. 
Commit failed for transaction: tcm:0-125866-66560
    at com.tridion.deployer.phases.DeployPipelineExecutor.runMainExecutePhase(DeployPipelineExecutor.java:209) [cd_deployer-2013SP1.jar:na]
    at com.tridion.deployer.phases.DeployPipelineExecutor.doExecute(DeployPipelineExecutor.java:100) [cd_deployer-2013SP1.jar:na]
    at com.tridion.deployer.phases.DeployPipelineExecutor.execute(DeployPipelineExecutor.java:64) [cd_deployer-2013SP1.jar:na]
    at com.tridion.deployer.TransactionManager.handleDeployPackage(TransactionManager.java:82) [cd_deployer-2013SP1.jar:na]
    at com.tridion.deployer.queue.QueueLocationHandler$1.run(QueueLocationHandler.java:180) [cd_deployer-2013SP1.jar:na]
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471) [na:1.7.0_55]
    at java.util.concurrent.FutureTask.run(FutureTask.java:262) [na:1.7.0_55]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [na:1.7.0_55]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [na:1.7.0_55]
    at java.lang.Thread.run(Thread.java:745) [na:1.7.0_55]
Caused by: com.tridion.deployer.ProcessingException: Unable to commit transactions
    at com.tridion.deployer.phases.AbstractStorageStep.commitTransaction(AbstractStorageStep.java:34) ~[cd_deployer-2013SP1.jar:na]
    at com.tridion.deployer.phases.CommitPhase.execute(CommitPhase.java:77) ~[cd_deployer-2013SP1.jar:na]
    at com.tridion.deployer.phases.DeployPipelineExecutor.runMainExecutePhase(DeployPipelineExecutor.java:198) [cd_deployer-2013SP1.jar:na]
    ... 9 common frames omitted
Caused by: com.tridion.broker.StorageException: Commit failed for transaction tcm:0-125866-66560 because of Unexpected error ocurred while committing
    at com.tridion.storage.StorageManagerFactory.commitTransaction(StorageManagerFactory.java:358) ~[cd_datalayer-2013SP1.jar:na]
    at com.tridion.deployer.phases.AbstractStorageStep.commitTransaction(AbstractStorageStep.java:32) ~[cd_deployer-2013SP1.jar:na]
    ... 11 common frames omitted
Caused by: com.tridion.broker.StorageException: Unexpected error ocurred while committing
    at com.tridion.storage.persistence.JPADAOFactory.commitTransaction(JPADAOFactory.java:209) ~[cd_datalayer-2013SP1.jar:na]
    at com.tridion.storage.StorageManagerFactory.commitTransaction(StorageManagerFactory.java:354) ~[cd_datalayer-2013SP1.jar:na]
    ... 12 common frames omitted
Caused by: javax.persistence.RollbackException: Error while committing the transaction
    at org.hibernate.ejb.TransactionImpl.commit(TransactionImpl.java:92) ~[hibernate-entitymanager-4.2.10.Final.jar:4.2.10.Final]
    at com.tridion.storage.persistence.JPADAOFactory.commitTransaction(JPADAOFactory.java:206) ~[cd_datalayer-2013SP1.jar:na]
    ... 13 common frames omitted
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.LockAcquisitionException: could not execute statement
    at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1387) ~[hibernate-entitymanager-4.2.10.Final.jar:4.2.10.Final]
    at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1310) ~[hibernate-entitymanager-4.2.10.Final.jar:4.2.10.Final]
    at org.hibernate.ejb.TransactionImpl.commit(TransactionImpl.java:80) ~[hibernate-entitymanager-4.2.10.Final.jar:4.2.10.Final]
    ... 14 common frames omitted
Caused by: org.hibernate.exception.LockAcquisitionException: could not execute statement
    at org.hibernate.dialect.Oracle8iDialect$2.convert(Oracle8iDialect.java:450) ~[hibernate-core-4.2.10.Final.jar:4.2.10.Final]
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49) ~[hibernate-core-4.2.10.Final.jar:4.2.10.Final]
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:124) ~[hibernate-core-4.2.10.Final.jar:4.2.10.Final]
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109) ~[hibernate-core-4.2.10.Final.jar:4.2.10.Final]
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:189) ~[hibernate-core-4.2.10.Final.jar:4.2.10.Final]
    at org.hibernate.engine.jdbc.batch.internal.NonBatchingBatch.addToBatch(NonBatchingBatch.java:58) ~[hibernate-core-4.2.10.Final.jar:4.2.10.Final]
    at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:3236) ~[hibernate-core-4.2.10.Final.jar:4.2.10.Final]
    at org.hibernate.persister.entity.AbstractEntityPersister.updateOrInsert(AbstractEntityPersister.java:3138) ~[hibernate-core-4.2.10.Final.jar:4.2.10.Final]
    at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:3468) ~[hibernate-core-4.2.10.Final.jar:4.2.10.Final]
    at org.hibernate.action.internal.EntityUpdateAction.execute(EntityUpdateAction.java:140) ~[hibernate-core-4.2.10.Final.jar:4.2.10.Final]
    at org.hibernate.engine.spi.ActionQueue.execute(ActionQueue.java:393) ~[hibernate-core-4.2.10.Final.jar:4.2.10.Final]
    at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:385) ~[hibernate-core-4.2.10.Final.jar:4.2.10.Final]
    at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:302) ~[hibernate-core-4.2.10.Final.jar:4.2.10.Final]
    at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:349) ~[hibernate-core-4.2.10.Final.jar:4.2.10.Final]
    at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:56) ~[hibernate-core-4.2.10.Final.jar:4.2.10.Final]
    at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1159) ~[hibernate-core-4.2.10.Final.jar:4.2.10.Final]
    at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:404) ~[hibernate-core-4.2.10.Final.jar:4.2.10.Final]
    at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.beforeTransactionCommit(JdbcTransaction.java:101) ~[hibernate-core-4.2.10.Final.jar:4.2.10.Final]
    at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.commit(AbstractTransactionImpl.java:175) ~[hibernate-core-4.2.10.Final.jar:4.2.10.Final]
    at org.hibernate.ejb.TransactionImpl.commit(TransactionImpl.java:75) ~[hibernate-entitymanager-4.2.10.Final.jar:4.2.10.Final]
    ... 14 common frames omitted
Caused by: java.sql.SQLException: ORA-00060: deadlock detected while waiting for resource

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445) ~[ojdbc6.jar:11.2.0.3.0]
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396) ~[ojdbc6.jar:11.2.0.3.0]
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879) ~[ojdbc6.jar:11.2.0.3.0]
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450) ~[ojdbc6.jar:11.2.0.3.0]
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192) ~[ojdbc6.jar:11.2.0.3.0]
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531) ~[ojdbc6.jar:11.2.0.3.0]
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207) ~[ojdbc6.jar:11.2.0.3.0]
    at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1044) ~[ojdbc6.jar:11.2.0.3.0]
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1329) ~[ojdbc6.jar:11.2.0.3.0]
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3594) ~[ojdbc6.jar:11.2.0.3.0]
    at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3675) ~[ojdbc6.jar:11.2.0.3.0]
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1354) ~[ojdbc6.jar:11.2.0.3.0]
    at weblogic.jdbc.wrapper.PreparedStatement.executeUpdate(PreparedStatement.java:174) ~[weblogic.server.merged.jar:12.1.2.0.0]
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:186) ~[hibernate-core-4.2.10.Final.jar:4.2.10.Final]
    ... 29 common frames omitted
2015-05-21 11:21:33,491 DEBUG CacheChannel - Received event from another VM [CacheEvent eventType=Invalidate regionPath=/com.tridion.storage.ReferenceEntry key=34:tcd:pub[34]/componentmeta[36154]:tcd:pub[34]/pagemeta[36157]]
2015-05-21 11:21:33,491 DEBUG JPAComponentPresentationMetaDAO - ComponentPresentationMeta was found for removal

As this issue occurs in my production environment, I can't do anything to bottleneck the root cause. This issue is redundant(Even with same data) and I am not able to reproduce the same every time on my development environment with similar data. By looking in the above log I can assume that error occurs because of use of many taxonomies in the metadata schema. If anyone faced similar issue, please share the root cause and respective work around. Thanks in advance..

A:

If you are using Tridion 2013 SP1 HR1, there is a hotfix for a problem that looks very similar to this issue. Have you tried this hotfix ?

CD_2013.1.1.88660
Deadlock and javax.persistence.OptimisticLockException apparently on
metadata leading to a failed transaction

The description of the hotfix states:

Hotfix Description
(SRQ-3065) Deadlock and javax.persistence.OptimisticLockException apparently on metadata
leading to a failed transaction"
Deadlock and javax.persistence.OptimisticLockException apparently on
metadata leading to a failed transaction. There will be a deadlock as
such: 2015-03-02 15:02:14,840 DEBUG SqlExceptionHelper - ORA-00060:
deadlock detected while waiting for resource [n/a]
java.sql.SQLException: ORA-00060: deadlock detected while waiting for
resource

You can find the hotfix on the sdl tridion world website https://www.sdltridionworld.com/downloads/hotfixes/SDL_Tridion_2013_SP1_HR1/index.aspx

