I want to do some basic experiment on PostgreSQL, for example to generate deadlocks, to create non-repeatable reads, etc. But I could not figure out how to run multiple transactions at once to see such behavior. Can anyone give me some Idea?
1
-
That depends on which SQL client you use.user330315– user3303152013-04-23 06:37:57 +00:00Commented Apr 23, 2013 at 6:37
Add a comment
|
3 Answers
Open more than one psql session, one terminal per session.
If you're on Windows you can do that by launching psql via the Start menu multiple times. On other platforms open a couple of new terminals or terminal tabs and start psql in each.
I routinely do this when I'm examining locking and concurrency issues, used in answers like:
... probably more. A useful trick when you want to set up a race condition is to open a third psql session and BEGIN; LOCK TABLE the_table_to_race_on;. Then run statements in your other sessions; they'll block on the lock. ROLLBACK the transaction holding the table lock and the other sessions will race. It's not perfect, since it doesn't simulate offset-start-time concurrency, but it's still very helpful.
Other alternatives are outlined in this later answer on a similar topic.
Comments
pgbench is probably the best solution in yours case. It allows you to test different complex database resource contentions, deadlocks, multi-client, multi-threaded access.
To get dealocks you can simply right some script like this ('bench_script.sql):
DECLARE cnt integer DEFAULT 0;
BEGIN;
LOCK TABLE schm.tbl IN SHARE MODE;
select count(*) from schm.tabl into cnt;
insert into schm.tbl values (1 + 9999*random(), 'test descr' );
END;
and pass it to pgbench with -f parameter.
For more detailed pgbench usage I would recommend to read the official manual for postgreSQL pgBench
and get acquented with my pgbench question resolved recently here.
Comments
Craig Ringer provide a way that open mutiple transactions manualy， if you find that is not very convenient， You can use pgbench run multiple transactions at once.
2 Comments
Colin 't Hart
At first I thought this could be a neat idea, but on reading the documentation of pgbench it's not obvious to me how it responds to the conditions that the OP is trying to test for: "deadlocks, non-repeatble reads, etc." I'd go for Craig's approach.
Craig Ringer
Per this later answer you can also use isolationtester from
src/test/isolation: stackoverflow.com/a/17055880/398670