Thread: Postgres JDBC, WS and commit

Hi there,
I'm using postgres-JDBC ina GlassFish application server to develop a web
service application. I made several tries with either XA and standard
driver. I use JDBC connection pool of GlassFish.
I found that transaction started into a web service operation, are closed at
the end of operation, not when in the code the connection are closed.

I am searching a solution to close transaction that permit me to close
trasaction when I want it, before end of web service operation.

Is there a way to achieve this goal?

Thanks in advance.

Mephysto

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Postgres-JDBC-WS-and-commit-tp5697657.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

Hi Kevin,
I will try to explain my scenario: with my application, many users exchange
message within them. User1 send message to user2 by a web service. This web
service send message and wait the response from user 2 before end and send
notice to user1. I can't know how many time is needed for response, so, I
would to save te state in a table on database to prevent accidental
disconnection of client from web service.

But I found that, if transaction are not closed until web service operation
end, my table are not update, and I can't know the wait state of user1 and I
can't take back he to his request.
Moreover, if transaction lock my table, and other user, user3, send a
message to user4, I can't save his state until user1 operation end.

Is it clear enough?

Is there any wrong in my reasoning? Is there anything I ignore in pool
functionality?

Thanks in advance.

Mephysto

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Postgres-JDBC-WS-and-commit-tp5697657p5698263.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

Even in a pool if you issue a commit then the data should be
committed. Is the connection open in transaction mode ? Are you
executing a commit ?

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca


On Wed, May 9, 2012 at 3:11 PM, mephysto <mephystoonhell@gmail.com> wrote:
> Hi Kevin,
> I will try to explain my scenario: with my application, many users exchange
> message within them. User1 send message to user2 by a web service. This web
> service send message and wait the response from user 2 before end and send
> notice to user1. I can't know how many time is needed for response, so, I
> would to save te state in a table on database to prevent accidental
> disconnection of client from web service.
>
> But I found that, if transaction are not closed until web service operation
> end, my table are not update, and I can't know the wait state of user1 and I
> can't take back he to his request.
> Moreover, if transaction lock my table, and other user, user3, send a
> message to user4, I can't save his state until user1 operation end.
>
> Is it clear enough?
>
> Is there any wrong in my reasoning? Is there anything I ignore in pool
> functionality?
>
> Thanks in advance.
>
> Mephysto
>
> --
> View this message in context:
http://postgresql.1045698.n5.nabble.com/Postgres-JDBC-WS-and-commit-tp5697657p5698263.html
> Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc

On Wed, 9 May 2012 12:11:19 -0700 (PDT), mephysto wrote:
> Hi Kevin,
> I will try to explain my scenario: with my application, many users
> exchange
> message within them. User1 send message to user2 by a web service.
> This web
> service send message and wait the response from user 2 before end and
> send
> notice to user1. I can't know how many time is needed for response,
> so, I
> would to save te state in a table on database to prevent accidental
> disconnection of client from web service.
>
> But I found that, if transaction are not closed until web service
> operation
> end, my table are not update, and I can't know the wait state of
> user1 and I
> can't take back he to his request.
> Moreover, if transaction lock my table, and other user, user3, send a
> message to user4, I can't save his state until user1 operation end.
>
> Is it clear enough?
>
> Is there any wrong in my reasoning? Is there anything I ignore in
> pool
> functionality?
>
> Thanks in advance.
>
> Mephysto
>
> --
> View this message in context:
>
> http://postgresql.1045698.n5.nabble.com/Postgres-JDBC-WS-and-commit-tp5697657p5698263.html
> Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

Hello,

In Java there are two main ways for Webservices EJB+WS or HTTP+WS
(JAX-WS, etc). I assume you use EJB+WS, if not give more details. You
should not manipulate connections pools and transactions manually
because you will break something.

According to your scenario I guess you use container managed
transactions in EJB+WS. In this scenario transaction will not be
committed until method ends, so locks etc, will be preserved, as well
others will not see Users 1 changes till commit. You should rewrite (a
little bit) your code, use @TransactionManagement(BEAN), and
EJBContext.getUserTransactions() or manipulate with
@TransactionManagement(CONTAINER) (this is default) and
@TransactionAttribute(REQUIRES_NEW) /your state saving method should be
in REQUIRES_NEW/ - here I'm not quite sure if in EJB3 to call bean
method form same bean you need self-inject same bean, or just normal
Java call is enough.

Please bear in mind, that long running connections (as well JDBC, HTTP,
IIOP) and transactions may be treated as damaged and closed by server.

Regards,
Radosław Smogura

At the moment I don't know if.connections are opened in transaction mode. Must I use this mode?
With XA driver I can't use connection.commit.
I use only stored function in SQL or pgsql language.

Thanks.

Mephysto

Oh... yes. You need to use it. There is "Non Transactional Connection"
checkbox on Connection Pool, it should be disabled. When it's enabled,
connection will not be bound to transaction manager.

Regards,
Radek

On Wed, 9 May 2012 23:00:17 +0200, Mephysto wrote:
> At the moment I dont know if.connections are opened in transaction
> mode. Must I use this mode?
>  With XA driver I cant use connection.commit.
>  I use only stored function in SQL or pgsql language.
>
> Thanks.
>
> Mephysto
> Il giorno 09/mag/2012 21:21, "Dave Cramer"  ha scritto:
>
>> Even in a pool if you issue a commit then the data should be
>> committed. Is the connection open in transaction mode ? Are you
>> executing a commit ?
>>
>> Dave Cramer
>>
>> dave.cramer(at)credativ(dot)ca
>> http://www.credativ.ca [1]
>>
>> On Wed, May 9, 2012 at 3:11 PM, mephysto wrote:
>> > Hi Kevin,
>> > I will try to explain my scenario: with my application, many
>> users exchange
>> > message within them. User1 send message to user2 by a web
>> service. This web
>> > service send message and wait the response from user 2 before end
>> and send
>> > notice to user1. I cant know how many time is needed for
>> response, so, I
>> > would to save te state in a table on database to prevent
>> accidental
>> > disconnection of client from web service.
>> >
>> > But I found that, if transaction are not closed until web service
>> operation
>> > end, my table are not update, and I cant know the wait state of
>> user1 and I
>> > cant take back he to his request.
>> > Moreover, if transaction lock my table, and other user, user3,
>> send a
>> > message to user4, I cant save his state until user1 operation
>> end.
>> >
>> > Is it clear enough?
>> >
>> > Is there any wrong in my reasoning? Is there anything I ignore in
>> pool
>> > functionality?
>> >
>> > Thanks in advance.
>> >
>> > Mephysto
>> >
>> > --
>> > View this message in context:
>>
>
> http://postgresql.1045698.n5.nabble.com/Postgres-JDBC-WS-and-commit-tp5697657p5698263.html
>> [3]
>> > Sent from the PostgreSQL - jdbc mailing list archive at
>> Nabble.com.
>> >
>> > --
>> > Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org [4])
>> > To make changes to your subscription:
>> > http://www.postgresql.org/mailpref/pgsql-jdbc [5]
>
>
> Links:
> ------
> [1] http://www.credativ.ca
> [2] mailto:mephystoonhell@gmail.com
> [3]
>
> http://postgresql.1045698.n5.nabble.com/Postgres-JDBC-WS-and-commit-tp5697657p5698263.html
> [4] mailto:pgsql-jdbc@postgresql.org
> [5] http://www.postgresql.org/mailpref/pgsql-jdbc
> [6] mailto:pg@fastcrypt.com

Hello newly,
I tried to enable "Non Transacitonal Connection" and my function execute
commit as I want, but at the moment I have some little problems with threads
and I don't know the reason of these problems.

I would to know more about pgConnectionpoolDataSource and pgXADataSource to
set correct datasource in Glassfish connection pool. Where can I find
documentation about these two kind of DataSource?

If I will use a multinode postgres managed by pgpool, which DataSource suits
my needs?

Thanks in advance.

Meph




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Postgres-JDBC-WS-and-commit-tp5697657p5702984.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

Dnia piątek, 11 maja 2012 06:08:11 mephysto pisze:
> Hello newly,
> I tried to enable "Non Transacitonal Connection" and my function execute
> commit as I want, but at the moment I have some little problems with threads
> and I don't know the reason of these problems.
Actually non transactional connection makes connections unmanaged by
Glassfish's transaction manager, so EJB as well.

> I would to know more about pgConnectionpoolDataSource and pgXADataSource to
> set correct datasource in Glassfish connection pool. Where can I find
> documentation about these two kind of DataSource?
Both are almost same (do not mix it with Polling data source), in fact XA is
build on top of PGConnectionPool. Difference between those is transaction
managment, XA requires transaction manager as every XA resource and needs to
implement specific interface, PGConnectionPool doesn't require it. I can't
imagine XA resource without transactions, probably this may work like
autocommit.

> If I will use a multinode postgres managed by pgpool, which DataSource suits
> my needs?
There should be no difference, both connection will connect to pgpool.

In any case I suggest you to try to use @TransactionAttribute(REQUIRES_NEW) or
@TransactionManagement(BEAN). Such application that relies on Glassfish
specific feature like non transactional connection will not be portable.

> Thanks in advance.
>
> Meph
>
>
>
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/Postgres-JDBC-WS-and-commit-tp56976
> 57p5702984.html Sent from the PostgreSQL - jdbc mailing list archive at
> Nabble.com.

Sorry for replaying for self.

Dnia niedziela, 13 maja 2012 21:01:16 Radosław Smogura pisze:
> Dnia piątek, 11 maja 2012 06:08:11 mephysto pisze:
> > Hello newly,
> > I tried to enable "Non Transacitonal Connection" and my function execute
> > commit as I want, but at the moment I have some little problems with
> > threads and I don't know the reason of these problems.
>
> Actually non transactional connection makes connections unmanaged by
> Glassfish's transaction manager, so EJB as well.
Threads? What kind of problems do you have? You should not use threads in EJB
and Web.

> > I would to know more about pgConnectionpoolDataSource and pgXADataSource
> > to
> > set correct datasource in Glassfish connection pool. Where can I find
> > documentation about these two kind of DataSource?
>
> Both are almost same (do not mix it with Polling data source), in fact XA is
> build on top of PGConnectionPool. Difference between those is transaction
> managment, XA requires transaction manager as every XA resource and needs
> to implement specific interface, PGConnectionPool doesn't require it. I
> can't imagine XA resource without transactions, probably this may work like
> autocommit.
>
> > If I will use a multinode postgres managed by pgpool, which DataSource
> > suits my needs?
>
> There should be no difference, both connection will connect to pgpool.
>
> In any case I suggest you to try to use @TransactionAttribute(REQUIRES_NEW)
> or @TransactionManagement(BEAN). Such application that relies on Glassfish
> specific feature like non transactional connection will not be portable.
> > Thanks in advance.
> >
> > Meph
> >
> >
> >
> >
> > --
> > View this message in context:
> > http://postgresql.1045698.n5.nabble.com/Postgres-JDBC-WS-and-commit-tp5697
> > 6
> > 57p5702984.html Sent from the PostgreSQL - jdbc mailing list archive at
> > Nabble.com.

Hi,

This may means, but I'm not quite sure:
- you don't have commit, when you close connection rollback is fired if
connection is not in autocommit mode.
- I suspect you may lock row (this connection that have null), so check
if other request do not wait, you may do this with viusalvm, or any
debugger inspecting all threads stacks.

Or if you use multinode this may be root problem too, there was some
posts about syncing master-slave.

Regards,
Radek

On Mon, 14 May 2012 08:41:32 +0200, Mephysto wrote:
> Hi Radoslaw,
> I was referring to Glassfish threads. I dont run any other thread
> from
> my ejb.
>
> The problem is that sometimes, in this configuration, I retrieve null
> output from data from database, but indeed the real value is not
> null.
>
> Thank you very much.
>
> Meph
>
> On 13 May 2012 21:10, Radosław Smogura  wrote:
>
>> Sorry for replaying for self.
>>
>> Dnia niedziela, 13 maja 2012 21:01:16 Radosław Smogura pisze:
>>
>>> Dnia piÄ…tek, 11 maja 2012 06:08:11 mephysto pisze:
>> > > Hello newly,
>> > > I tried to enable "Non Transacitonal Connection" and my
>> function execute
>> > > commit as I want, but at the moment I have some little problems
>> with
>> > > threads and I dont know the reason of these problems.
>> >
>> > Actually non transactional connection makes connections unmanaged
>> by
>> > Glassfishs transaction manager, so EJB as well.
>> Threads? What kind of problems do you have? You should not use
>> threads in EJB
>> and Web.
>>
>> > > I would to know more about pgConnectionpoolDataSource and
>> pgXADataSource
>> > > to
>> > > set correct datasource in Glassfish connection pool. Where can
>> I find
>> > > documentation about these two kind of DataSource?
>> >
>> > Both are almost same (do not mix it with Polling data source), in
>> fact XA is
>> > build on top of PGConnectionPool. Difference between those is
>> transaction
>> > managment, XA requires transaction manager as every XA resource
>> and needs
>> > to implement specific interface, PGConnectionPool doesnt require
>> it. I
>> > cant imagine XA resource without transactions, probably this may
>> work like
>> > autocommit.
>> >
>> > > If I will use a multinode postgres managed by pgpool, which
>> DataSource
>> > > suits my needs?
>> >
>> > There should be no difference, both connection will connect to
>> pgpool.
>> >
>> > In any case I suggest you to try to use
>> @TransactionAttribute(REQUIRES_NEW)
>> > or @TransactionManagement(BEAN). Such application that relies on
>> Glassfish
>> > specific feature like non transactional connection will not be
>> portable.
>> > > Thanks in advance.
>> > >
>> > > Meph
>> > >
>> > >
>> > >
>> > >
>> > > --
>> > > View this message in context:
>> > >
>>
>
> http://postgresql.1045698.n5.nabble.com/Postgres-JDBC-WS-and-commit-tp5697
>> [1]
>> > > 6
>> > > 57p5702984.html Sent from the PostgreSQL - jdbc mailing list
>> archive at
>> > > Nabble.com.
>
>
>
> Links:
> ------
> [1]
>
> http://postgresql.1045698.n5.nabble.com/Postgres-JDBC-WS-and-commit-tp5697
> [2] mailto:rsmogura@softperience.eu

Excuse me but I'm really confused, I did many tests, but I'm not sure about
results. If I understand it, using glassfish JDBC connection pool, if a
thread requires a connection to database, glassfish assign one from pool to
thread and commit is executed at the end of thread, not on every stored
function launch.

I would, if it is possible, the commit is executed every write stored
function.

A this point, I would to know if this goal is achievable and if use of
commit that I wand is admissible. Finally, I would to know, why commit is
execute by deafult only one time at the end of thread.

Thanks in advance and excuse me newly.

Mephysto

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Postgres-JDBC-WS-and-commit-tp5697657p5708644.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

This has nothing to do with PostgreSQL specifically.
Please read up on Java (EE) application servers, connection pooling
and container transaction models.

Florent

On Mon, May 14, 2012 at 3:55 PM, mephysto <mephystoonhell@gmail.com> wrote:
> Excuse me but I'm really confused, I did many tests, but I'm not sure about
> results. If I understand it, using glassfish JDBC connection pool, if a
> thread requires a connection to database, glassfish assign one from pool to
> thread and commit is executed at the end of thread, not on every stored
> function launch.
>
> I would, if it is possible, the commit is executed every write stored
> function.
>
> A this point, I would to know if this goal is achievable and if use of
> commit that I wand is admissible. Finally, I would to know, why commit is
> execute by deafult only one time at the end of thread.
>
> Thanks in advance and excuse me newly.
>
> Mephysto
>
> --
> View this message in context:
http://postgresql.1045698.n5.nabble.com/Postgres-JDBC-WS-and-commit-tp5697657p5708644.html
> Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc



--
Florent Guillaume, Director of R&D, Nuxeo
Open Source, Java EE based, Enterprise Content Management (ECM)
http://www.nuxeo.com   http://www.nuxeo.org   +33 1 40 33 79 87

On Mon, 14 May 2012 06:55:25 -0700 (PDT), mephysto wrote:
> Excuse me but I'm really confused, I did many tests, but I'm not sure
> about
> results. If I understand it, using glassfish JDBC connection pool, if
> a
> thread requires a connection to database, glassfish assign one from
> pool to
> thread and commit is executed at the end of thread, not on every
> stored
> function launch.
>
> I would, if it is possible, the commit is executed every write stored
> function.
>
> A this point, I would to know if this goal is achievable and if use
> of
> commit that I wand is admissible. Finally, I would to know, why
> commit is
> execute by deafult only one time at the end of thread.
>
> Thanks in advance and excuse me newly.
>
> Mephysto
>
> --
> View this message in context:
>
> http://postgresql.1045698.n5.nabble.com/Postgres-JDBC-WS-and-commit-tp5697657p5708644.html
> Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

If you are using non transactional connection then commits and
rollbacks may not be performed by transaction manager (I haven't made
such tests, but I know connection is not bound to transaction manager),
when e.g. EJB request ends. Here is short description
http://docs.oracle.com/cd/E19798-01/821-1752/beamu/index.html - this is
not standard J2EE behavior. This is why I suggest you to operate with
REQUIRES_NEW, and slightly rebuilding if required your application. You
will achieve same result, and your application will be portable, as well
you have less risky points in application.

For each connection you take from pool you gets new connection.

If you want that commit will be called by stored procedure, call commit
after procedure call or do something like this

@EJB
EJB self

void doSoemthinf() {
self->callProcedure();
}

@(REQUIRES_NEW)
callPorcedure() {
jdbc chain;
}

In both cases new connection will be taken from pool.

If you have more questions, write prv message. Removing pgsql-jdbc from
CC.

Regards

On 05/09/2012 11:06 PM, mephysto wrote:
> Hi there,
> I'm using postgres-JDBC ina GlassFish application server to develop a web
> service application. I made several tries with either XA and standard
> driver. I use JDBC connection pool of GlassFish.
> I found that transaction started into a web service operation, are closed at
> the end of operation, not when in the code the connection are closed.
>
> I am searching a solution to close transaction that permit me to close
> trasaction when I want it, before end of web service operation.
>
> Is there a way to achieve this goal?

If you're using XA transactions in a Java EE context, what you want is
probably an EJB3 with

   @TransactionAttribute(TransactionAttributeType.REQUIRES_NEW)

which will force the EJB business method(s) so annotated to run in their
own independent transactions. Note that these transactions will *NOT* be
able to see any uncommitted changes from your main transaction, they're
completely independent and behind the scenes with PostgreSQL are
actually new connections to the server.

--
Craig Ringer