CODEWITHSUNDEEP
postgresql
user2309875
user2309875

Reputation: 21

How to run multiple transactions concurrently in PostgreSQL

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?

Upvotes: 2

Views: 7917

Answers (3)

xacinay
xacinay

Reputation: 909

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.

Upvotes: 1

Craig Ringer
Craig Ringer

Reputation: 324455

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.

Upvotes: 10

francs
francs

Reputation: 9189

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.

Upvotes: 0

Related Questions