On 31/01/2010 9:06 PM, Alex Besogonov wrote:
> On Sun, Jan 31, 2010 at 7:25 AM, Craig Ringer
> <craig@postnewspapers.com.au> wrote:
>>> 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.
> Actually, I the program I'm writing behaves exactly like parallel
> pg_dump from PostgreSQL's point of view.
>
> I've found this discussion in -HACKERS:
> http://osdir.com/ml/pgsql-hackers/2009-11/msg00265.html It seems, it's
> exactly what I need to do. I might try to contribute a patch.
Well, if you're able to that'd be absolutely brilliant :-)
>> 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.
> It should be possible to do this for read/write transactions as well.
>
>>> 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.
> I thought about it, but it's too deadlock-prone. I need to lock the
> whole database, and if I do this table-by-table then I'll almost
> certainly generate a deadlock.
Not if you specify, and stick to, a strict lock acquisition order and
never try to upgrade a lock you already hold.
--
Craig Ringer
