Обсуждение: One transaction and several processes

Поиск
Список
Период
Сортировка

One transaction and several processes

От
Valery Kuzmin
Дата:
Hi,

Can multiple processes participate in a single transaction's execution?
I need to do the following actions sequence:
1. Calling service begins transaction. At this step transaction
manager generate new XID.
2. Calling service updates some data.
3. Need to detach a transaction from a service context before other
service is called.
4. Called service resume transaction using XID.
5. Called service updates its data.
6. Calling service gets control, resume transaction using XID and does commit.
How can the third and fourth step be implemented?

Best regards,
Valery


Re: One transaction and several processes

От
Tomas Vondra
Дата:
On 07/18/2018 05:31 PM, Valery Kuzmin wrote:
> Hi,
> 
> Can multiple processes participate in a single transaction's execution?
> I need to do the following actions sequence:
> 1. Calling service begins transaction. At this step transaction
> manager generate new XID.
> 2. Calling service updates some data.
> 3. Need to detach a transaction from a service context before other
> service is called.
> 4. Called service resume transaction using XID.
> 5. Called service updates its data.
> 6. Calling service gets control, resume transaction using XID and does commit.
> How can the third and fourth step be implemented?
> 

It's not very clear what you mean by "detaching" (and more importantly 
why you want this at all). PostgreSQL certainly does not support handing 
over regular XID to some other connection, or even keeping it active 
after the connection closes.

There are two approaches how you might achieve something similar:

1) proxy - Instead of communicating directly with the database, the 
services may communicate with a proxy that handles the actual database 
connection and communicates with the connection.

2) 2PC - Both services may do their own "private" transaction, but 
confirm it using a two-phase commit. This is more expensive than plain 
transactions and may not have exactly the same behavior, but it does 
provide the durability guarantees etc.

FWIW this question is for pgsql-general rather than pgsql-hackers.

regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


Re: One transaction and several processes

От
"David G. Johnston"
Дата:
On Wed, Jul 18, 2018 at 8:31 AM, Valery Kuzmin <valerij.kuzmin@gmail.com> wrote:
Hi,

Can multiple processes participate in a single transaction's execution?
I need to do the following actions sequence:
1. Calling service begins transaction. At this step transaction
manager generate new XID.
2. Calling service updates some data.
3. Need to detach a transaction from a service context before other
service is called.
4. Called service resume transaction using XID.
5. Called service updates its data.
6. Calling service gets control, resume transaction using XID and does commit.
How can the third and fourth step be implemented?

​Your original pgsql-general post was the right place for this but I see it got no response​...

Basically no, if you need multiple service processes to share a session you would need to share the persistent database connection between them at a higher level (i.e., outside the database).  IOW, the physical connection needs to be shared, not an XID.

Depending on the specifics of this orchestration two-phase commit might provide some useful capabilities but its considerably harder to setup reliably.

David J.