Hi,

I've mention it in http://forum.hibernate.org/viewtopic.php?p=2323169#2323169

when I use c3p0 connection pool with Oracle 10g my stored procedures changes are not commited on database, but when I disable it they are commited,

how can I solve this problem?

I must mention that I use Callable Statments....

So, I'm only guessing here. This might not be right at all. But do you perform other hibernate operations on the Session from which you grab your Connection before close()ing it?

Here's my guess: You create a Session, and get underneath of it via direct JDBC to run your stored procedure. But you don't do anything that hibernate understands to require commit()ment, and you don't directly call commit() yourself (since you generallu delegate transaction management to hibernate). When you end the Session, hibernate doesn't commit(), so c3p0 rolls back uncommitted transactional work while accepting the Connection back into the pool, and the effect of your stored procedure is lost.

When you run without a pool, you execute the CallableStatement in one session, close() it, then do some hibernate work in a second session, that commits and ends up committing your stored procedure as well, since all the work is done on one Connection. With c3p0, the stored procedure and hibernate work, which occur in different Sessions, are performed on two different Connections, and work not commited prior to Connection close() is explicitly rolled back.

Again, I'm only guessing here, and others here can comment in better detail about when and whether Sessions commit() if you don't do that directly. But if I'm right, two workarounds: 1) call commit() yourself via direct JDBC when you punch through the hibernate layer for your stored procedure (and do this work in a distinct Session so you don't otherwise interfere with hibernate's commit/rollback decision); or 2) do some other hibernate work that would provoke a commit() in the same session with your stored procedure. I think that 1) is probably the cleaner solution, and would require minimal modification of your code.

Good luck!

Steve

Well, I believe this is not the problem (but I will check it one more time), because there is only a single entry point to a session in my application which is "HibernateUtils" which gets the session from a thread-local; and the connection is also get from that only session.

has it anything related to release mode strategy?

pasha,

i know c3p0 real good, but hibernate, not so good. so take my rantings with several grains of salt.

looking here http://www.hibernate.org/hib_docs/v3/reference/en/html/transactions.html, i think release mode could indeed have something to do with your problem. at root, the issue is to understand 1) the lifecycle of your Sessions, 2) the relationship between your Sessions and underlying database Connections, and 3) when your Sessions commit. my comments above were implicitly based on what i now see is an outdated model of a Session, i.e. one Connection-per-Session. it's clear from the discussion of release modes that one Session can be associated with multiple Connections, and since it sounds like you might be using a pretty long-lived Session, a "release mode" that associates a Connection with the Session only when needed would be sensible.

the take home point here is this: when you're not using a Connection pool, there's only one Connection in your application, and your Callable[/url]

[Umm, there was an accidental premature post event there. As I was saying...]

...the take home point here is this: when you're not using a Connection pool, there's only one Connection in your application, and your CallableStatement eventually gets commit()ed if any work gets commit()ed (unless something explicitly fails and there's a rollback()). but this is fragile -- you are not doing anything to ensure that your CallableStatement gets committed, you are just getting lucky.

when using a Connection pool, hibernate is working with many different Connections, checking them in and out as-needed. so you no longer get lucky. you ask your Session for a Connection at one moment in your application, do some work that requires a commit(), but you don't commit() explicitly, and you don't do anything that forces hibernate to commit() for you before it releases the Connection.

again, hibernate jockeys might be able to give you better advice, but here are some ideas 1) call commit() directly on the Connection after your CallableStatement (but going underneath hibernate like this might clash with hibernate's transaction management); or 2) begin an explicit transaction on your Session object prior to calling connection() and doing your jdbc work, and use hibernate's transaction interface to commit the transaction after your callable statement work. Stealing and modifying from http://www.hibernate.org/hib_docs/v3/reference/en/html/transactions.html, it'd be something like...



good luck! hope this helps!

Steve