dbms_alert test errors under high load

[kav23alex]

Under very high load, dbms_alert* tests may fail due to timeouts. We have applied a workaround in the form of increasing the timeouts for the dbms_alert tests sed -i 's/\(sleep(\)/\110*/; s/\(waitany(\)/\110*/; s/\(waitone([^,]\+,\)/\110*/' */dbms_alert*

one example test error:

2022-07-12 14:23:23.351 MSK [10079] LOG:  statement: SELECT dbms_alert.signal('b1','Transaction still running');
2022-07-12 14:23:23.359 MSK [10077] LOG:  statement: COMMIT;
2022-07-12 14:23:23.359 MSK [10077] LOG:  statement: /* The signal is received after transaction completed.
         * After this the tds signal is received in session B indicating that the
         * signal is received only after commit.
         */
        SELECT dbms_alert.waitone('b1',20);
2022-07-12 14:23:43.361 MSK [10077] PANIC:  timeout in waitone
2022-07-12 14:23:43.361 MSK [10077] STATEMENT:  /* The signal is received after transaction completed.
         * After this the tds signal is received in session B indicating that the
         * signal is received only after commit.
         */
        SELECT dbms_alert.waitone('b1',20);
2022-07-12 14:23:48.604 MSK [9428] LOG:  using stale statistics instead of current ones because stats collector is not responding
2022-07-12 14:23:50.212 MSK [10079] LOG:  statement: SELECT dbms_alert.signal('b1','Transaction committed');

There are 27 seconds between sending the first and second 'b1' signals to session_B, so waitone('b1',20) in session_A times out.

timeouts have been reduced on commit df69815fd5. What about using advisory locks to synchronize sessions or just converting those tests to isolation tests?

[okbob]

pá 9. 9. 2022 v 5:36 odesílatel kav23alex @.***> napsal:

Under very high load, dbms_alert* tests may fail due to timeouts. We have applied a workaround in the form of increasing the timeouts for the dbms_alert tests sed -i 's/(sleep()/\110*/; s/(waitany()/\110*/; s/(waitone([^,]+,)/\110*/' /dbms_alert

one example test error:

2022-07-12 14:23:23.351 MSK [10079] LOG: statement: SELECT dbms_alert.signal('b1','Transaction still running'); 2022-07-12 14:23:23.359 MSK [10077] LOG: statement: COMMIT; 2022-07-12 14:23:23.359 MSK [10077] LOG: statement: /* The signal is received after transaction completed. * After this the tds signal is received in session B indicating that the * signal is received only after commit. / SELECT dbms_alert.waitone('b1',20); 2022-07-12 14:23:43.361 MSK [10077] PANIC: timeout in waitone 2022-07-12 14:23:43.361 MSK [10077] STATEMENT: / The signal is received after transaction completed. * After this the tds signal is received in session B indicating that the * signal is received only after commit. */ SELECT dbms_alert.waitone('b1',20); 2022-07-12 14:23:48.604 MSK [9428] LOG: using stale statistics instead of current ones because stats collector is not responding 2022-07-12 14:23:50.212 MSK [10079] LOG: statement: SELECT dbms_alert.signal('b1','Transaction committed');

There are 27 seconds between sending the first and second 'b1' signals to session_B, so waitone('b1',20) in session_A times out.

timeouts have been reduced on commit df69815 https://github.com/orafce/orafce/commit/df69815fd53470d7689b155683c3e63f5b14d207 . What about using advisory locks to synchronize sessions or just converting those tests to isolation tests?

Please, send PL - probably using advisory locks should be ultimate fix

— Reply to this email directly, view it on GitHub https://github.com/orafce/orafce/issues/193, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAEFO4YIE7QRRSXEVV2WWL3V5KWFPANCNFSM6AAAAAAQIJXTRQ . You are receiving this because you are subscribed to this thread.Message ID: @.***>

[okbob]

should be fixed by 072af84a898ba07133992a0cee5e1211ef8348e4 please check