From d27bdbb4e360b30ec0960634c13a6ba21c7a618d Mon Sep 17 00:00:00 2001 From: Marina Polyakova Date: Fri, 9 Jun 2017 16:34:08 +0300 Subject: [PATCH v1 2/4] Pgbench Set default transaction isolation level You can set the default transaction isolation level by the appropriate benchmarking option (-I). --- src/bin/pgbench/pgbench.c | 66 ++++++++++++++++++- .../004_set_default_transaction_isolation_level.pl | 76 ++++++++++++++++++++++ 2 files changed, 141 insertions(+), 1 deletion(-) create mode 100644 src/bin/pgbench/t/004_set_default_transaction_isolation_level.pl diff --git a/src/bin/pgbench/pgbench.c b/src/bin/pgbench/pgbench.c index bbf444b..8c48793 100644 --- a/src/bin/pgbench/pgbench.c +++ b/src/bin/pgbench/pgbench.c @@ -456,6 +456,24 @@ static const BuiltinScript builtin_script[] = } }; +/* Default transaction isolation level */ +typedef enum DefaultIsolationLevel +{ + READ_COMMITTED, + REPEATABLE_READ, + SERIALIZABLE, + NUM_DEFAULT_ISOLATION_LEVEL +} DefaultIsolationLevel; + +DefaultIsolationLevel default_isolation_level = READ_COMMITTED; + +static const char *DEFAULT_ISOLATION_LEVEL_ABBREVIATION[] = {"RC", "RR", "S"}; +static const char *DEFAULT_ISOLATION_LEVEL_SQL[] = { + "read committed", + "repeatable read", + "serializable" +}; + /* Function prototypes */ static void setIntValue(PgBenchValue *pv, int64 ival); @@ -508,6 +526,8 @@ usage(void) " -C, --connect establish new connection for each transaction\n" " -D, --define=VARNAME=VALUE\n" " define variable for use by custom script\n" + " -I, --default-isolation-level=RC|RR|S\n" + " default transaction isolation level (default: RC)\n" " -j, --jobs=NUM number of threads (default: 1)\n" " -l, --log write transaction times to log file\n" " -L, --latency-limit=NUM count transactions lasting more than NUM ms as late\n" @@ -2108,6 +2128,7 @@ doCustom(TState *thread, CState *st, StatsData *agg) if (st->con == NULL) { instr_time start; + char buffer[256]; if (INSTR_TIME_IS_ZERO(now)) INSTR_TIME_SET_CURRENT(now); @@ -2124,6 +2145,16 @@ doCustom(TState *thread, CState *st, StatsData *agg) /* Reset session-local state */ memset(st->prepared, 0, sizeof(st->prepared)); + + /* set default isolation level */ + snprintf(buffer, sizeof(buffer), + "set session characteristics as transaction isolation level %s", + DEFAULT_ISOLATION_LEVEL_SQL[ + default_isolation_level]); + executeStatement(st->con, buffer); + if (debug) + fprintf(stderr, "client %d execute command: %s\n", + st->id, buffer); } /* @@ -3583,6 +3614,8 @@ printResults(TState *threads, StatsData *total, instr_time total_time, /* Report test parameters. */ printf("transaction type: %s\n", num_scripts == 1 ? sql_script[0].desc : "multiple scripts"); + printf("default transaction isolation level: %s\n", + DEFAULT_ISOLATION_LEVEL_SQL[default_isolation_level]); printf("scaling factor: %d\n", scale); printf("query mode: %s\n", QUERYMODE[querymode]); printf("number of clients: %d\n", nclients); @@ -3719,6 +3752,7 @@ main(int argc, char **argv) {"fillfactor", required_argument, NULL, 'F'}, {"host", required_argument, NULL, 'h'}, {"initialize", no_argument, NULL, 'i'}, + {"default-isolation-level", required_argument, NULL, 'I'}, {"jobs", required_argument, NULL, 'j'}, {"log", no_argument, NULL, 'l'}, {"latency-limit", required_argument, NULL, 'L'}, @@ -3811,7 +3845,7 @@ main(int argc, char **argv) state = (CState *) pg_malloc(sizeof(CState)); memset(state, 0, sizeof(CState)); - while ((c = getopt_long(argc, argv, "ih:nvp:dqb:SNc:j:Crs:t:T:U:lf:D:F:M:P:R:L:", long_options, &optindex)) != -1) + while ((c = getopt_long(argc, argv, "ih:nvp:dqb:SNc:j:Crs:t:T:U:lf:D:F:M:P:R:L:I:", long_options, &optindex)) != -1) { char *script; @@ -4051,6 +4085,25 @@ main(int argc, char **argv) latency_limit = (int64) (limit_ms * 1000); } break; + case 'I': + { + benchmarking_option_set = true; + + for (default_isolation_level = 0; + default_isolation_level < NUM_DEFAULT_ISOLATION_LEVEL; + default_isolation_level++) + if (strcmp(optarg, + DEFAULT_ISOLATION_LEVEL_ABBREVIATION[ + default_isolation_level]) == 0) + break; + if (default_isolation_level >= NUM_DEFAULT_ISOLATION_LEVEL) + { + fprintf(stderr, "invalid default isolation level (-I): \"%s\"\n", + optarg); + exit(1); + } + } + break; case 0: /* This covers long options which take no argument. */ if (foreign_keys || unlogged_tables) @@ -4522,8 +4575,19 @@ threadRun(void *arg) /* make connections to the database */ for (i = 0; i < nstate; i++) { + char buffer[256]; + if ((state[i].con = doConnect()) == NULL) goto done; + + /* set default isolation level */ + snprintf(buffer, sizeof(buffer), + "set session characteristics as transaction isolation level %s", + DEFAULT_ISOLATION_LEVEL_SQL[default_isolation_level]); + executeStatement(state[i].con, buffer); + if (debug) + fprintf(stderr, "client %d execute command: %s\n", + state[i].id, buffer); } } diff --git a/src/bin/pgbench/t/004_set_default_transaction_isolation_level.pl b/src/bin/pgbench/t/004_set_default_transaction_isolation_level.pl new file mode 100644 index 0000000..cb9f03b --- /dev/null +++ b/src/bin/pgbench/t/004_set_default_transaction_isolation_level.pl @@ -0,0 +1,76 @@ +use strict; +use warnings; + +use PostgresNode; +use TestLib; +use Test::More tests => 27; + +# Test concurrent update in table row with different default transaction +# isolation levels. +my $node = get_new_node('main'); +$node->init; +$node->start; +$node->safe_psql('postgres', + 'CREATE UNLOGGED TABLE xy (x integer, y integer); ' + . 'INSERT INTO xy VALUES (1, 2);'); + +my $script = $node->basedir . '/pgbench_script'; +append_to_file($script, "\\set delta random(-5000, 5000)\n"); +append_to_file($script, "UPDATE xy SET y = y + :delta WHERE x = 1;"); + +# Test Read committed default transaction isolation level +$node->command_like( + [ qw(pgbench --no-vacuum --client=5 --transactions=10 + --default-isolation-level=RC --file), $script ], + qr{default transaction isolation level: read committed}, + 'concurrent update: Read Committed: check default isolation level'); + +$node->command_like( + [ qw(pgbench --no-vacuum --client=5 --transactions=10 + --default-isolation-level=RC --file), $script ], + qr{processed: 50/50}, + 'concurrent update: Read Committed: check processed transactions'); + +$node->command_like( + [ qw(pgbench --no-vacuum --client=5 --transactions=10 + --default-isolation-level=RC --file), $script ], + qr{serialization failures: 0 \(0\.000 %\)}, + 'concurrent update: Read Committed: check serialization failures'); + +# Test Repeatable read default transaction isolation level +$node->command_like( + [ qw(pgbench --no-vacuum --client=5 --transactions=10 + --default-isolation-level=RR --file), $script ], + qr{default transaction isolation level: repeatable read}, + 'concurrent update: Repeatable Read: check default isolation level'); + +$node->command_like( + [ qw(pgbench --no-vacuum --client=5 --transactions=10 + --default-isolation-level=RR --file), $script ], + qr{processed: 50/50}, + 'concurrent update: Repeatable Read: check processed transactions'); + +$node->command_like( + [ qw(pgbench --no-vacuum --client=5 --transactions=10 + --default-isolation-level=RR --file), $script ], + qr{serialization failures: [1-9]\d* \([1-9]\d*\.\d* %\)}, + 'concurrent update: Repeatable Read: check serialization failures'); + +# Test Serializable default transaction isolation level +$node->command_like( + [ qw(pgbench --no-vacuum --client=5 --transactions=10 + --default-isolation-level=S --file), $script ], + qr{default transaction isolation level: serializable}, + 'concurrent update: Serializable: check default isolation level'); + +$node->command_like( + [ qw(pgbench --no-vacuum --client=5 --transactions=10 + --default-isolation-level=S --file), $script ], + qr{processed: 50/50}, + 'concurrent update: Serializable: check processed transactions'); + +$node->command_like( + [ qw(pgbench --no-vacuum --client=5 --transactions=10 + --default-isolation-level=S --file), $script ], + qr{serialization failures: [1-9]\d* \([1-9]\d*\.\d* %\)}, + 'concurrent update: Serializable: check serialization failures'); -- 1.9.1