Returning connection back to pool and committing changes
843859Oct 19 2005 — edited Oct 25 2005Hello everyone,
I've a question related to closing connections and committing transactions. This is the scenario:
Say, there are 2 APIs - insertRecord() and updateRecord().
1. User1 connects to the database, is authenticated and a session is set for this user.
2. User1 calls insertRecord(). I open a connection, execute the insert and close the connection. Autocommit is set to false.
3. User1 calls updateRecord(). I open a new connection, execute the update, and close the connection.
Now, user2 does the same set of things.
At this point, user1 wants to commit all the changes related to this session. So, does user2.
However, the connection objects were closed and the connections were returned to the pool. So, I cannot perform a commit. Invoking commit on a new connection doesn't mean anything as there is no transaction associated with this connection.
The only option I see is to NOT return the connection back to the pool after executing every method (insertRecord, deleteRecord and so on) and tie the connection to the user session. This way, when the user issues a commit, the changes can be committed and the connection can then be returned to the pool at that point. This works except that it is not a scalable approach. What if I have 1000 users? I don't want all of them to hang on their connections till they decide to either do a commit or a rollback.
Given this, what are my options?
Thanks.