Uploaded image for project: 'Opencast'
  1. MH-13332

Bulk Delete Deadlock exception from "AbstractADeleteQuery.deleteOrphanedProperties"

    Details

    • Type: Task
    • Status: Resolved
    • Priority: Major
    • Resolution: Duplicate
    • Affects versions: 5.2
    • Fix versions: 5.5
    • Components: Backend Software

      Description

      Bulk Delete activity occasionally causes a Deadlock exception from "AbstractADeleteQuery.deleteOrphanedProperties"

      NOTE: not sure the exception is in the out-of-the-box Opencast v5.2, but the deleteOrphanedProperties() query is the same in 5x and develop branches.

      Example exception log:

      2019-01-28 09:53:18,522 | WARN  | (JavaLog:300) - 
      Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.6.4.v20160829-44060b6): org.eclipse.persistence.exceptions.DatabaseException
      Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
      Error Code: 1213
      Call: DELETE p FROM
      oc_assets_properties p LEFT JOIN oc_assets_snapshot e ON p.mediapackage_id = e.mediapackage_id
      WHERE e.id IS NULL;
      Query: DataModifyQuery(sql="DELETE p FROM
      oc_assets_properties p LEFT JOIN oc_assets_snapshot e ON p.mediapackage_id = e.mediapackage_id
      WHERE e.id IS NULL;")
              at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:331)
              at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:902)[315:org.eclipse.persistence.core:2.6.4.v20160829-44060b6]
              at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeNoSelect(DatabaseAccessor.java:964)[315:org.eclipse.persistence.core:2.6.4.v20160829-44060b6]
              at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:633)[315:org.eclipse.persistence.core:2.6.4.v20160829-44060b6]
              at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:560)[315:org.eclipse.persistence.core:2.6.4.v20160829-44060b6]
              at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:2056)[315:org.eclipse.persistence.core:2.6.4.v20160829-44060b6]
              at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:306)[315:org.eclipse.persistence.core:2.6.4.v20160829-44060b6]
              at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:242)[315:org.eclipse.persistence.core:2.6.4.v20160829-44060b6]
              at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:228)[315:org.eclipse.persistence.core:2.6.4.v20160829-44060b6]
              at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeNoSelectCall(DatasourceCallQueryMechanism.java:271)[315:org.eclipse.persistence.core:2.6.4.v20160829-44060b6]
              at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeNoSelect(DatasourceCallQueryMechanism.java:251)[315:org.eclipse.persistence.core:2.6.4.v20160829-44060b6]
              at org.eclipse.persistence.queries.DataModifyQuery.executeDatabaseQuery(DataModifyQuery.java:85)[315:org.eclipse.persistence.core:2.6.4.v20160829-44060b6]
              at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:904)[315:org.eclipse.persistence.core:2.6.4.v20160829-44060b6]
              at org.eclipse.persistence.queries.DatabaseQuery.executeInUnitOfWork(DatabaseQuery.java:803)[315:org.eclipse.persistence.core:2.6.4.v20160829-44060b6]
              at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2896)[315:org.eclipse.persistence.core:2.6.4.v20160829-44060b6]
              at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1857)[315:org.eclipse.persistence.core:2.6.4.v20160829-44060b6]
              at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1839)[315:org.eclipse.persistence.core:2.6.4.v20160829-44060b6]
              at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1804)[315:org.eclipse.persistence.core:2.6.4.v20160829-44060b6]
              at org.eclipse.persistence.internal.jpa.QueryImpl.executeUpdate(QueryImpl.java:298)[316:org.eclipse.persistence.jpa:2.6.4.v20160829-44060b6]
              at org.opencastproject.util.persistencefn.Queries$QueriesBase.update(Queries.java:311)[71:opencast-common:5.0.0.SNAPSHOT]
              at org.opencastproject.assetmanager.impl.query.AbstractADeleteQuery$5.mysql(AbstractADeleteQuery.java:284)[58:opencast-asset-manager-impl:5.0.0.SNAPSHOT]
              at org.opencastproject.assetmanager.impl.query.AbstractADeleteQuery$5.mysql(AbstractADeleteQuery.java:273)[58:opencast-asset-manager-impl:5.0.0.SNAPSHOT]
              at org.opencastproject.assetmanager.impl.persistence.Database$2.apply(Database.java:102)[58:opencast-asset-manager-impl:5.0.0.SNAPSHOT]
              at org.opencastproject.assetmanager.impl.persistence.Database$2.apply(Database.java:94)[58:opencast-asset-manager-impl:5.0.0.SNAPSHOT]
              at org.opencastproject.util.persistencefn.PersistenceEnvs$2$1.tx(PersistenceEnvs.java:78)[71:opencast-common:5.0.0.SNAPSHOT]
              at org.opencastproject.util.persistencefn.PersistenceEnvs$3.tx(PersistenceEnvs.java:105)[71:opencast-common:5.0.0.SNAPSHOT]
              at org.opencastproject.assetmanager.impl.persistence.Database.runSql(Database.java:94)[58:opencast-asset-manager-impl:5.0.0.SNAPSHOT]
              at org.opencastproject.assetmanager.impl.query.AbstractADeleteQuery.deleteOrphanedProperties(AbstractADeleteQuery.java:273)[58:opencast-asset-manager-impl:5.0.0.SNAPSHOT]
              at org.opencastproject.assetmanager.impl.query.AbstractADeleteQuery.runQueries(AbstractADeleteQuery.java:195)[58:opencast-asset-manager-impl:5.0.0.SNAPSHOT]
              at org.opencastproject.assetmanager.impl.query.AbstractADeleteQuery.access$000(AbstractADeleteQuery.java:59)[58:opencast-asset-manager-impl:5.0.0.SNAPSHOT]
              at org.opencastproject.assetmanager.impl.query.AbstractADeleteQuery$3.apply(AbstractADeleteQuery.java:104)[58:opencast-asset-manager-impl:5.0.0.SNAPSHOT]
              at org.opencastproject.assetmanager.impl.query.AbstractADeleteQuery$3.apply(AbstractADeleteQuery.java:102)[58:opencast-asset-manager-impl:5.0.0.SNAPSHOT]
              at org.opencastproject.assetmanager.impl.persistence.Database$1.apply(Database.java:84)[58:opencast-asset-manager-impl:5.0.0.SNAPSHOT]
              at org.opencastproject.assetmanager.impl.persistence.Database$1.apply(Database.java:82)[58:opencast-asset-manager-impl:5.0.0.SNAPSHOT]
              at org.opencastproject.util.persistencefn.PersistenceEnvs$2.tx(PersistenceEnvs.java:81)[71:opencast-common:5.0.0.SNAPSHOT]
              at org.opencastproject.util.persistencefn.PersistenceEnvs$3.tx(PersistenceEnvs.java:105)[71:opencast-common:5.0.0.SNAPSHOT]
              at org.opencastproject.assetmanager.impl.persistence.Database.run(Database.java:82)[58:opencast-asset-manager-impl:5.0.0.SNAPSHOT]
              at org.opencastproject.assetmanager.impl.query.AbstractADeleteQuery.run(AbstractADeleteQuery.java:102)[58:opencast-asset-manager-impl:5.0.0.SNAPSHOT]
              at org.opencastproject.assetmanager.impl.AssetManagerWithMessaging$ADeleteQueryWithMessaging.run(AssetManagerWithMessaging.java:273)[58:opencast-asset-manager-impl:5.0.0.SNAPSHOT]
              at org.opencastproject.scheduler.impl.SchedulerServiceImpl.removeEvent(SchedulerServiceImpl.java:1290)[134:opencast-scheduler-impl:5.0.0.SNAPSHOT]
              at org.opencastproject.index.service.impl.IndexServiceImpl.removeEvent(IndexServiceImpl.java:1482)[98:opencast-index-service:5.0.0.SNAPSHOT]
      

      Possible solution: make a preliminary query of all properties currently associated to the pending deletion mediapackage and pending deleted snapshots of the MP, and save the ids of the asset_property results to a set. Then delete the mp snapshots in the assets table. Afterwards, delete the specific set of properties that were gathered previously that are now newly orphaned.

      Change the current deleteOrphanedProperties FROM:

      DELETE properties from asset_properties LEFT JOIN on asset_snapshots ON asset_properties.mediapackage_id = asset_snapshots.mediapackage_id WHERE asset_snapshots.id IS NULL // This seems like a really weird query, normally an "id" column isn't allowed to be null.
      

      TO something like :

      DELETE properties from asset_properties  WHERE  asset_properties.id IN (set of ids gathered from previous query) // This seems a lot more sane
      

      Ref:
      https://github.com/opencast/opencast/blob/9e9e6f20ddb80addd4bcff5274c384b2c35490ff/modules/asset-manager-impl/src/main/java/org/opencastproject/assetmanager/impl/query/AbstractADeleteQuery.java#L193-L218

      Ref:
      https://github.com/opencast/opencast/blob/9e9e6f20ddb80addd4bcff5274c384b2c35490ff/modules/asset-manager-impl/src/main/java/org/opencastproject/assetmanager/impl/query/AbstractADeleteQuery.java#L276-L286

        TestRail: Results

          Attachments

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                karen_dolan Karen Dolan
              • Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  TestRail: Cases