4

I would like to test some variants of transaction concurrency in PostgreSQL and for that I need a script which would force two transaction to start at exactly the same time. Something that does not requires manual intervention ;) Any ideas?

Borys
  • 2,676
  • 2
  • 24
  • 37
  • 1
    Take a look at this http://jmeter.apache.org/. I've only breifly used it and am not sure it allows you to sync up two calls to run at the same time without you creating custom logic for it, but it may. – Kuberchaun Jun 11 '13 at 19:36
  • thx JustBob if I don't find something easier to maintain will try with jmeter. – Borys Jun 11 '13 at 19:43
  • 2
    Are you sure you want "same time"? That's a tricky thing to define. Are you not after (the much easier): test when A starts before B, repeat test with B before A? – Richard Huxton Jun 11 '13 at 21:01
  • You might want to define "exactly the same time" a little better. What's the tolerance? – Jakub Kania Jun 11 '13 at 21:21
  • @RichardHuxton - I know it's tricky thats why I'm asking you ;) Only requirement I've got is to run both transactions concurrently. Maybe adding pg_sleep at the beginning of the first transaction would be good solution? then I've got some time to run the second one and both would see the same initial state of data? – Borys Jun 11 '13 at 22:03
  • @JakubKania unfortunately I cannot provide any precise values. This is something that I would like to check as well. – Borys Jun 11 '13 at 22:06
  • ok, this one with pg_sleep works for me. but now other issues came up ;p – Borys Jun 11 '13 at 22:23

1 Answers1

2

You can homebrew this by taking a LOCK on a table, setting up your transactions, then releasing the lock by rolling back the transaction that got the lock. See this prior answer and its links for details on this approach. While I demonstrated it using three psql sessions it's equally viable to do it with bash co-processes, a Python script using psycopg2 and the multiprocessing or threading modules, etc. Fairly simple to do. Update: In fact here's an example I just wrote in python3.

For more sophisticated tests, grab the PostgreSQL source code and use the "isolationtester" tool in src/test/isolation which lets you write recipes that do complex orderings of commands. It doesn't support being built with PGXS (though such support would probably be pretty trivial to add) so you have to compile the whole PostgreSQL source tree, but that's quick enough. It'll run against your existing PostgreSQL so there's no need to install the one you compiled.

See src/test/isolation/README for more about the isolationtester tool. The docs are a little thin on the ground since it's an internal testing tool, but the existing tests cases should help you get started. Feel free to improve it to meet your needs and submit patches :)

Community
  • 1
  • 1
Craig Ringer
  • 307,061
  • 76
  • 688
  • 778