postgresql strange behaviour

[rndgatewaynet]

Hi ,
i am experiencing a different behaviour in postgresql,
using
1) J2EE RI
2) JBoss 2.4

In 1) every function seems to run as an atomic operation,
(with the database queries either nested in begin-end blocks,
or by themeselfs if they are the only transaction in the function.

In 2) strangely for the first connection it begins, sets the transaction isolation level, and the *rollbacks*.
Afterwards, for every function, jboss starts with issuing a begin,
which unfortunately never ends, and postgresql locks the table.

I have read in the forums, that one should close() the connection
in every home method.

The point is that the code i am playing with is from SUN's
J2EE tutorial, and it would be great to run in JBoss without,
having to modify the code.

Theory says that a commit is done after the end of each method.
Any thoughts???

[jfifield]

Hmm... I wonder if this could be the same problem I am running into:

I have 3 BMP entity beans and a postgresql database. I have a simple servlet for creating and editing one of the entities. It seems the beans accept the new values and can be referenced ok, but the data never seems to get written to the database. I added some debug statements and the sql statements are in fact executing...

The part I don't really understand is why my command line test client that does the same thing works fine...

Could this be the same issue? If so, any thoughts?

Joe

[rndgatewaynet]

Check your transaction attributes, might be an issue.
>> and the sql statements are in fact executing...
followed by "end" or "commit"???
Do you do any explicit sql calls in your servlet?

My point, is that jboss tx manager seems to call commit
whenever he wants, so locks and uncommited changes
are expected.

Currently i am using BMP entity beans, with tx attribute= "Required",
and transaction Isolation level "Read Commited".

here is my samples....
(It is actually the SavingsAccount Example from SUN's J2EE tutorial)

test.jsp
======
...
SavingsAccount ach = home.create("1", "ach", "bach",0.00);
ach.credit(1000.0);
out.println(ach.getBalance());
....


pgsql.log
========
2001-09-11 16:20:54 [8962] DEBUG: connection: host=127.0.0.1 user=postgres database=dynatest
2001-09-11 16:20:54 [8962] DEBUG: query: set datestyle to 'ISO'; select getdatabaseencoding()
2001-09-11 16:20:54 [8962] DEBUG: ProcessUtility: set datestyle to 'ISO'; select getdatabaseencoding()
2001-09-11 16:20:54 [8962] DEBUG: query: begin
2001-09-11 16:20:54 [8962] DEBUG: ProcessUtility: begin
2001-09-11 16:20:54 [8962] DEBUG: query: SET TRANSACTION ISOLATION LEVEL READ COMMITTED
2001-09-11 16:20:54 [8962] DEBUG: ProcessUtility: SET TRANSACTION ISOLATION LEVEL READ COMMITTED
2001-09-11 16:20:54 [8962] DEBUG: query: rollback
2001-09-11 16:20:54 [8962] DEBUG: ProcessUtility: rollback
2001-09-11 16:20:54 [8962] DEBUG: query: begin
2001-09-11 16:20:54 [8962] DEBUG: ProcessUtility: begin
2001-09-11 16:20:54 [8962] DEBUG: query: SET TRANSACTION ISOLATION LEVEL READ COMMITTED
2001-09-11 16:20:54 [8962] DEBUG: ProcessUtility: SET TRANSACTION ISOLATION LEVEL READ COMMITTED

(the above is just after jboss startup - see the rollback at the begining!)..

(the folowing is after i call the test.jsp)
2001-09-11 16:22:52 [8962] DEBUG: query: insert into savingsaccount values (
'1' , 'ach' , 'bach' , 0.0 )
2001-09-11 16:22:52 [8962] DEBUG: query: commit
2001-09-11 16:22:52 [8962] DEBUG: ProcessUtility: commit
2001-09-11 16:22:52 [8962] DEBUG: query: begin
2001-09-11 16:22:52 [8962] DEBUG: ProcessUtility: begin
2001-09-11 16:22:52 [8962] DEBUG: query: SET TRANSACTION ISOLATION LEVEL READ COMMITTED
2001-09-11 16:22:52 [8962] DEBUG: ProcessUtility: SET TRANSACTION ISOLATION LEVEL READ COMMITTED
2001-09-11 16:22:52 [8962] DEBUG: query: update savingsaccount set firstname
= 'ach' ,lastname = 'bach' , balance = 1000.0 where id = '1'
2001-09-11 16:22:52 [8962] DEBUG: query: update savingsaccount set firstname
= 'ach' ,lastname = 'bach' , balance = 1000.0 where id = '1'

It seems that it can create the BMP bean correctly,
it actually calls ejbStore() twice (!!), but never ends the transaction.

[rndgatewaynet]

After reading about
a) Isolation levels
b) Transaction Attributes
c) Jboss Commit Options

i concluded that the last two should affect how and when
a transaction takes place, and how often db synchronization
and caching are managed.

BUT, once a transaction begins it must either rollback, or commit.
In CM transactions (entity beans case) i think this must be always
true.

I dont have a clue of why this is happening...
Maybe some help from a guru would be really good.

[jfifield]

I do not have any explicit transaction code in my beans. I messed around with the transaction attributes in the dd, but it didn't seem to help.

>> Do you do any explicit sql calls in your servlet?

No. I am only using the entity bean.

This is probably a bit off topic, but how do I get a pgsql.log? I don't seem to have one on my system. I assume I have to set an option somewhere?

I will spend some time reading up on transactions in JBoss and see what I can come up with.

As I said, the strange part is it works fine in a stand-alone, command-line client...this is only happening from a servlet (with the exact same bean interaction code).

[rndgatewaynet]

For the pgsql log thing...
start your pgsql like

#!/bin/sh
su postgres -c "pg_ctl -D /var/lib/pgsql/data/ start -l /var/log/pgsql.log"

make sure you rotate the logz :)

[jfifield]

Ahhh, thanks. I'll give that a try.

I seem to have solved my problem though. When I implemented the bean, I used the method in the Sun J2EE Developer's Guide of getting a connection in setEntityContext and closing it in unsetEntityContext. After reading about the connection pools more, I decided to try getting the connection immediately before and closing it immediately after I execute the sql. That seems to have fixed the problem with the data not being commited. I'm not sure if this new approach is suitable or not.

[rndgatewaynet]

Thats what i did myself and worked.
Are both your ejbCreate and ejbStore that dont do commit???
In my case i got a commit only in ejbCreate but NOT in ejbStore.
Note that i also tried the SavingsAccount example from SUN's
J2EE tutorial (the new one), which also suggests this method
of connection caching.

Whats your commit options??
Your transaction attributes??

[jfifield]

None of the writes to the database were being commited.

As far as transaction attributes, I tried setting different ones while trying to solve the problem. Currently I have none specified...so the whatever the defaults are.