Re: Ability to 'fork' a running transaction?

On 31/01/2010 8:19 AM, Alex Besogonov wrote:
> I'm writing a program which needs to do a lot of (read only)
> processing on a database. The program works on a SMP system with 16
> CPUs, so it's natural to try to make it use all of them.
>
> However, here lies the problem: I need to use SERIALIZABLE transaction
> isolation level, and AFAIK it's not possible to make several database
> connections to share the same exact view of the database.

I've noticed some talk on -HACKERS of finding ways to make this
possible. It's needed for parallel pg_dump, among other things.

It's not clear if it'd work for non-read-only transactions; I didn't
notice that being discussed, and don't know enough about it to have an
opinion of my own. Still, it's worth looking into for the future.

> So, is there a way to somehow stop all mutating operations?

Take explicit locks on the resources of interest that are permissive
enough to be shared with other read transactions, but not to permit writes.

You might have to do this on the table level, rather than just using
SELECT ... FOR SHARE. The reason for that is that the locks taken by
SELECT ... FOR SHARE won't prevent the insertion of new rows that match
the where clause used in the select, so:

SELECT id FROM sometable WHERE customer = 1337 FOR SHARE;

won't block someone else inserting a record with customer=1337 in
`sometable', and if another transaction acquires a snapshot after that
INSERT commits it'll see the inserted row.

--
Craig Ringer