invalid transaction termination with postgres 14.5 hammerdb 4.7

[siddhsql]

hello i am using hammerdb 4.7 with postgres 14.5. i have done a successful run with 1 vu and 1 async client per vu (so effectively no concurrency) but when i try multiple vus i get this error as example:

Error in Virtual User 10: ERROR:  invalid transaction termination
CONTEXT:  PL/pgSQL function neword(integer,integer,integer,integer,integer,integer) line 248 at ROLLBACK

i have set the server to use serializable isolation level. has anyone seen this and know how to fix it? thanks.

[sm-shaw]

How are you setting serializable isolation level?

[sm-shaw]

assuming you are doing this:

postgres=# alter database tpcc set DEFAULT_TRANSACTION_ISOLATION to serializable;

If you use stored procedures instead of functions, you get this error which is clearer:

Error in Virtual User 6: Error: ERROR:  could not serialize access due to read/write dependencies among transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during commit attempt.
HINT:  The transaction might succeed if retried.

This is described here: https://www.postgresql.org/docs/current/transaction-iso.html

HammerDB will not be introducing any changes to support serializable for PostgreSQL.

[siddhsql]

https://github.com/TPC-Council/HammerDB/blob/master/src/postgresql/pgoltp.tcl

EXCEPTION
            WHEN serialization_failure OR deadlock_detected OR no_data_found
            THEN ROLLBACK;

it seems the tx is rolled back only if there is a serialization_failure OR deadlock_detected OR no_data_found. shouldn't it be rolled back irrespective of the error?

[sm-shaw]

You can look at examples of official reports on the TPC site, for example: https://www.tpc.org/results/fdr/tpcc/oracle_sparc_t5-8_tpc-c_fdr_032613.pdf

#define DEADLOCK 60 /* ORA-00060: deadlock */
#define NO_DATA_FOUND 1403 /* ORA-01403: no data found */
#define NOT_SERIALIZABLE 8177 /* ORA-08177: transaction not serializable*/
#define SNAPSHOT_TOO_OLD 1555 /* ORA-01555: snapshot too old */

not_serializable EXCEPTION;
PRAGMA EXCEPTION_INIT(not_serializable,-8177);
deadlock EXCEPTION;
PRAGMA EXCEPTION_INIT(deadlock,-60);
snapshot_too_old EXCEPTION;
PRAGMA EXCEPTION_INIT(snapshot_too_old,-1555);

As it is all open source, you can of course modify to your own purposes. However, I do recommend reading the HammerDB documentation https://www.hammerdb.com/document.html and database documentation to understand more.

In general, though, serializable transaction isolation is not compatible with high throughput database benchmarks to test system performance and therefore not something by default we want to do.