I have a strange thing happening here.
Using sessions from multiple ejb beans, with jboss and postgres as the backend, I can see that both EJBs run fine up to a point, but the first to reach insert/update, seems to block the other (becomes UPDATE waiting postgres process ... or INSERT waiting) As soon as I call session.close(), the other ejb starts up. Each has its own connection, and transaction... so they must be locking up somewhere :(
Any ideas? Or is this a postgres /jdbc problem?
Cheers
Ati.

I don't use postgres, but this sounds like a row or table locking problem. Are you explicitly setting up isolation levels on the connection used by Hibernate? If so, what are you setting it to? If not, what is the default for postgres?

Where can I set/view the isolation levels?

Depends on how the connection/pool/datasource was set up. You can always call java.sql.Connection.getTransactionIsolation() to ask for the current isolation level.

Then you need to interpret what that means for your given database. Locks are always held until the end of the current transaction. Sounds like postgres may just not be able to heandle fine-grained locking with the current isolation level.

transaction level=2...
which is.. java.sql.Connection.TRANSACTION_REPEATABLE_READ??
ok.. I'm lost now.. :(

You need to look at the postgres documentation to see what that implies. Does it issue a row lock? A region lock? A table lock?

Is your app trying to update the same row? If not then most likely postgres is not capable of locking just a single row.

Ok.. this is a bit odd.. but I had an idea.. and it turned out to work.
checking the logs, it appeared that tables were locked only when hibernate started inserting. I removed the foreign-key constraints from the tables it is inserting into and the locks went away.....

Apparently postgres locks the tables referenced by a table during an insert to ensure that the values referenced are valid...

whopee...

Postgres SQL doe's not lock row if you do not lock it yourself, transaction isolation is implemented without locking. I am no sure about all versions, but it must be true for 7.x

http://www.postgresql.org/docs/7.3/static/mvcc.html

Thats why I assumed that hibernate/boss were at fault.
I'm using a vanilla 7.3.2 postgreSQL server...

with NO explicit locking defined.. .. at least not by us :)
unless either jBoss, or hibernate, or .. more likely, the JDBC driver decides to enforce this behaviour...and I can figure out how to disable it...
until then.. down with foreign indexes
thnx
A.

Well its up to you about the FKs, but I'd highly recommend having FKs; its too easy to get into integrity trouble otherwise.

Actually Oracle does the same exact thing for FKs if there are no indexes defined for the FK. My guess is that postgres has some similiar requirement. If the only way for it to enforce FKs is to table-lock all FK referenced tables, then thats just crappy implementation. I'd check their docs for a way to use FK and avoid the table-locks.

I'm keeping all my FKs in a select so I can replace them.
We pretty much have programatically remove the possiblility of invalid FKs being inserted... deletion is a bit of a pain tho :(

FK is just a triggers on postgres.

BTW postgres is not more cappy than oracle :)

we dumped oracle in favour of postgres for most application...
my only problem now is that deletion doesn't throw an error w/o the FK's. ..... *sigh* :(

Just different paths to crappiness :)