You're so confused, you're confusing me. Let's start from the top.
A handle is a database session. One session, one handle. All transaction modifiers affect only their controlling session. So when you say 'begin transaction' in one handle, it doesn't put any other handles into transaction mode. Go ahead, open up several psql or sql*plus terminals and try it out.
1. Take a handle and use it to say "begin transaction" (from pool1)
2. Take a new handle and use it to do updates etc. (from pool2)
3. Take again a new handle and use it to delete etc. (from pool3)
4. Use handle from (1) and say "abort transaction"
5. Release unused handles...
In this case, handle #2 isn't running those updates within a transaction, nor is handle #3. And action #4 only aborts what you've done in the transaction on handle #1, ie, nothing. #5 doesn't even make sense, because if the handle is unused then why is it allocated to the thread?
The correct way to do this is:
- take a handle: set handle1 [ns_db gethandle main]
- open a transaction: ns_db dml $handle1 "begin transaction"
- use the handle to do some updates: ns_db dml $handle1 "update insults set mother = ' was a hamster' where user_region = 'French'"
- do some deletes: ns_db dml $handle1 "delete from knights where saying = 'Nee!'"
- abort transaction: ns_db dml $handle1 "abort transaction"
- have a cookie
Why can't I release the handles from (2) and (3)?
ns_db releasehandle $handle_2
ns_db releasehandle $handle_3
Problem?
There are only three pools defined. What if I need another dml statement in the transaction?
Same as above: by spreading your statements out over time with a single handle, rather than over multiple handles all at once. There are actually very few times when you need multiple statements active at once; such as when you have to create new statements on the fly and drive them with data taken from another statement. But that's relatively uncommon.
