Thread: 2 connections 1 transaction

2 connections 1 transaction

From
"Daniel Schuchardt"
Date:
Hi @ all,
 
Our software consists of a Client-Side App and a Application Server. Every client owns a direct connection to the PSql-Server and for every Client the Application-Server also creates a connection to the PSql-Server. The problem is that it is nescesary that the Client and the Application-Server are in the same transaction. But how say connection x to be in the same transaction like connection y?
 
Thanks for help,

Daniel

 

Re: 2 connections 1 transaction

From
Jason Godden
Date:
Hi Daniel,

Maybe make procedural wrappers around all events undertaken and simulate it?

ie:

1. Client connects to MS SQL Server (Application Server) and PG Server
2. SQL Server connects to PG Server aswell
3. Client begins new record process
4. New record process starts by putting PG into serializable transactions (so
App Server can respect the changes).
5. If one server transaction fails roll back the other and vice versa and
start again.
6. If all good, commit both at the client as the last item in the new record
process.

Meanwhile the Application Server may make it's own calls against Pg through
strored procedures.  If any part of this PG transaction fails, error comes
back to App Server and App Server transaction roll backs, which tells the
client and the client rolls back it's own call to PG.

I don't know if this is a viable method (v. messy) and given the concurrent
update issue this may play havoc with the client logic if both the client and
the app server attempt to modify the same data at the PG Server.  Can all
connections simply go through the App Server leaving it to manage the PG
stuff in it's own transactions (still, concurrent update problem can occur if
dealing with same records)?

Rgds,

Jason

On Thu, 17 Jul 2003 09:45 pm, Daniel Schuchardt wrote:
> Hi @ all,
>
> Our software consists of a Client-Side App and a Application Server.
> Every client owns a direct connection to the PSql-Server and for every
> Client the Application-Server also creates a connection to the
> PSql-Server. The problem is that it is nescesary that the Client and the
> Application-Server are in the same transaction. But how say connection x
> to be in the same transaction like connection y?
>
> Thanks for help,
>
> Daniel


Re: 2 connections 1 transaction

From
"Daniel Schuchardt"
Date:
Hi Jason,

Thats not what I mean. Both, the Client and the App-Server connects to
the same Postgres-Database. The problem a procedure like this:

1.Client starts Transaction and does some changes.
2.Now the client notices that very huge operations are nescesarry and
starts a procedure @ App-Server
3.App-Server reads the database and makes changes. Problem : the changes
the client does are not commited -> the server can't see the changes or
the case more bad the server waits for the client connection.
(transaction isolation and table / record locking)
4.If everything works well both changes (done by the client and the
server) should commit or rollback now

So both Postgres-Connections has to be @ the same TransAction-OID in
Postgres.

greets

Daniel

-----Ursprüngliche Nachricht-----
Von: pgsql-general-owner+M45575@postgresql.org
[mailto:pgsql-general-owner+M45575@postgresql.org] Im Auftrag von Jason
Godden
Gesendet: Donnerstag, 17. Juli 2003 14:23
An: Daniel Schuchardt; pgsql-general@postgresql.org
Betreff: Re: [GENERAL] 2 connections 1 transaction


Hi Daniel,

Maybe make procedural wrappers around all events undertaken and simulate
it?

ie:

1. Client connects to MS SQL Server (Application Server) and PG Server
2. SQL Server connects to PG Server aswell 3. Client begins new record
process 4. New record process starts by putting PG into serializable
transactions (so
App Server can respect the changes).
5. If one server transaction fails roll back the other and vice versa
and
start again.
6. If all good, commit both at the client as the last item in the new
record
process.

Meanwhile the Application Server may make it's own calls against Pg
through
strored procedures.  If any part of this PG transaction fails, error
comes
back to App Server and App Server transaction roll backs, which tells
the
client and the client rolls back it's own call to PG.

I don't know if this is a viable method (v. messy) and given the
concurrent
update issue this may play havoc with the client logic if both the
client and
the app server attempt to modify the same data at the PG Server.  Can
all
connections simply go through the App Server leaving it to manage the PG

stuff in it's own transactions (still, concurrent update problem can
occur if
dealing with same records)?

Rgds,

Jason

On Thu, 17 Jul 2003 09:45 pm, Daniel Schuchardt wrote:
> Hi @ all,
>
> Our software consists of a Client-Side App and a Application Server.
> Every client owns a direct connection to the PSql-Server and for every

> Client the Application-Server also creates a connection to the
> PSql-Server. The problem is that it is nescesary that the Client and
> the Application-Server are in the same transaction. But how say
> connection x to be in the same transaction like connection y?
>
> Thanks for help,
>
> Daniel


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster



Re: 2 connections 1 transaction

From
"Shridhar Daithankar"
Date:
On 17 Jul 2003 at 16:47, Daniel Schuchardt wrote:
> 1.Client starts Transaction and does some changes.
> 2.Now the client notices that very huge operations are nescesarry and
> starts a procedure @ App-Server
> 3.App-Server reads the database and makes changes. Problem : the changes
> the client does are not commited -> the server can't see the changes or
> the case more bad the server waits for the client connection.
> (transaction isolation and table / record locking)
> 4.If everything works well both changes (done by the client and the
> server) should commit or rollback now

Why not route everything thr. app-server? If you have an app-server, then it
does not make sense exposing database directly to client.

Bye
 Shridhar

--
> No manual is ever necessary.May I politely interject here: BULLSHIT.  That's
the biggest Apple lie of all!(Discussion in comp.os.linux.misc on the
intuitiveness of interfaces.)


Re: 2 connections 1 transaction

From
Dmitry Tkach
Date:
The short answer is - there is no way you can do it.
Different connections in postgres (and in every other DB engine I heard
of) can never share the same transaction.

As far as I can see, the only way to do what you want is to rethink your
architechture so that the clients never talk directly to the database,
and always go through the app server... or, the other way around - get
rid of the app server, and just have every client go to the database
directly. You can' thave it both ways.

Dima


Daniel Schuchardt wrote:

> Hi @ all,
>
> Our software consists of a Client-Side App and a Application Server.
> Every client owns a direct connection to the PSql-Server and for every
> Client the Application-Server also creates a connection to the
> PSql-Server. The problem is that it is nescesary that the Client and
> the Application-Server are in the same transaction. But how say
> connection x to be in the same transaction like connection y?
>
> Thanks for help,
>
> Daniel
>
>




Re: 2 connections 1 transaction

From
Jason Godden
Date:
Hi Daniel,

> 3.App-Server reads the database and makes changes. Problem : the changes
> the client does are not commited -> the server can't see the changes or
> the case more bad the server waits for the client connection.
> (transaction isolation and table / record locking)

The app server CAN see the changes if you set the transaction level to
serializable.  If using multiple servers is a requirement (and Dima's
comments are right - why not just use the one connection to pg and pass
everything through the app server?) just rethink your post-pg processing so
that the app server doesn't update rows modified by pg.  If you process all
the changed rows in a different table then everything should work.  If not
roll back pg and the app server.

Or get rid of the app server.  Tricky one..

Cheers,

Jason


On Fri, 18 Jul 2003 12:47 am, Daniel Schuchardt wrote:
> Hi Jason,
>
> Thats not what I mean. Both, the Client and the App-Server connects to
> the same Postgres-Database. The problem a procedure like this:
>
> 1.Client starts Transaction and does some changes.
> 2.Now the client notices that very huge operations are nescesarry and
> starts a procedure @ App-Server
> 3.App-Server reads the database and makes changes. Problem : the changes
> the client does are not commited -> the server can't see the changes or
> the case more bad the server waits for the client connection.
> (transaction isolation and table / record locking)
> 4.If everything works well both changes (done by the client and the
> server) should commit or rollback now
>
> So both Postgres-Connections has to be @ the same TransAction-OID in
> Postgres.
>
> greets
>
> Daniel
>
> -----Ursprüngliche Nachricht-----
> Von: pgsql-general-owner+M45575@postgresql.org
> [mailto:pgsql-general-owner+M45575@postgresql.org] Im Auftrag von Jason
> Godden
> Gesendet: Donnerstag, 17. Juli 2003 14:23
> An: Daniel Schuchardt; pgsql-general@postgresql.org
> Betreff: Re: [GENERAL] 2 connections 1 transaction
>
>
> Hi Daniel,
>
> Maybe make procedural wrappers around all events undertaken and simulate
> it?
>
> ie:
>
> 1. Client connects to MS SQL Server (Application Server) and PG Server
> 2. SQL Server connects to PG Server aswell 3. Client begins new record
> process 4. New record process starts by putting PG into serializable
> transactions (so
> App Server can respect the changes).
> 5. If one server transaction fails roll back the other and vice versa
> and
> start again.
> 6. If all good, commit both at the client as the last item in the new
> record
> process.
>
> Meanwhile the Application Server may make it's own calls against Pg
> through
> strored procedures.  If any part of this PG transaction fails, error
> comes
> back to App Server and App Server transaction roll backs, which tells
> the
> client and the client rolls back it's own call to PG.
>
> I don't know if this is a viable method (v. messy) and given the
> concurrent
> update issue this may play havoc with the client logic if both the
> client and
> the app server attempt to modify the same data at the PG Server.  Can
> all
> connections simply go through the App Server leaving it to manage the PG
>
> stuff in it's own transactions (still, concurrent update problem can
> occur if
> dealing with same records)?
>
> Rgds,
>
> Jason
>
> On Thu, 17 Jul 2003 09:45 pm, Daniel Schuchardt wrote:
> > Hi @ all,
> >
> > Our software consists of a Client-Side App and a Application Server.
> > Every client owns a direct connection to the PSql-Server and for every
> >
> > Client the Application-Server also creates a connection to the
> > PSql-Server. The problem is that it is nescesary that the Client and
> > the Application-Server are in the same transaction. But how say
> > connection x to be in the same transaction like connection y?
> >
> > Thanks for help,
> >
> > Daniel
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster