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 df69815.
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 <df69815> . 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 <#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 072af84 please check