Uploaded image for project: 'AeroGear'
  1. AeroGear
  2. AEROGEAR-3227

UPS deadlock on DB - using more AG UPS in cluster

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Obsolete
    • Icon: Major Major
    • None
    • None
    • None
    • None

      Using more UPS in cluster with one DB.
      Midnight DB clean up cause deadlock and it is not possible to set up time for scheduler not to run at midnight.

      scheduler is singleton per node ( https://github.com/aerogear/aerogear-unifiedpush-server/blob/master/service/src/main/java/org/jboss/aerogear/unifiedpush/service/metrics/DeleteOldPushMessageInformationScheduler.java )

      Example: oracle DB ( FYI ... DB owner PUSHSRVAG ... DB application user APUSHSRVAG)
      -----------------------------------------------------------------------------------------

          • 2015-04-10 00:00:04.506
          • SESSION ID:(84.48937) 2015-04-10 00:00:04.506
          • CLIENT ID) 2015-04-10 00:00:04.506
          • SERVICE NAME:(SYS$USERS) 2015-04-10 00:00:04.506
          • MODULE NAME:(JDBC Thin Client) 2015-04-10 00:00:04.506
          • CLIENT DRIVER:(jdbcthin) 2015-04-10 00:00:04.506
          • ACTION NAME) 2015-04-10 00:00:04.506
          • 2015-04-10 00:00:04.506
            DEADLOCK DETECTED ( ORA-00060 )
            See Note 60.1 at My Oracle Support for Troubleshooting ORA-60 Errors
            [Transaction Deadlock]

      The following deadlock is not an ORACLE error. It is a
      deadlock due to user error in the design of an application
      or from issuing incorrect ad-hoc SQL. The following
      information may aid in determining the deadlock:

      Deadlock graph:
      --------Blocker(s)------- --------Waiter(s)--------
      Resource Name process session holds waits process session holds waits
      TM-00013891-00000000-00000000-00000000 142 84 SX SSX 144 162 SX SSX
      TM-00013891-00000000-00000000-00000000 144 162 SX SSX 142 84 SX SSX

      Rows waited on:
      Session 84: no row
      Session 162: no row

      ----- Information for the OTHER waiting sessions -----
      Session 162:
      sid: 162 ser: 54036 audsid: 10790094 user: 115/APUSHSRVAG
      flags: (0x41) USR/- flags_idl: (0x1) BSY/////-
      flags2: (0x40009) //INC
      pid: 144 O/S info: user: oracle, term: UNKNOWN, ospid: 11534420
      image: oracle@s16000ks
      client details:
      O/S info: user: jbotp, term: unknown, ospid: 1234
      machine: s1.sk.srv.acc.sys program: JDBC Thin Client
      application name: JDBC Thin Client, hash value=2546894660
      current SQL:
      delete from PUSHSRVAG.PushMessageInformation where submitDate<:1

      ----- End of information for the OTHER waiting sessions -----

      Information for THIS session:

      ----- Current SQL Statement for this session (sql_id=bsk109wpq55yg) -----
      delete from PUSHSRVAG.PushMessageInformation where submitDate<:1

              mwessend@redhat.com Matthias Wessendorf
              ivan.gurtler Ivan Gurtler (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

                Created:
                Updated:
                Resolved: