Forum OpenACS Q&A: db_foreach in transactions

Request notifications

Collapse
Posted by Michael Hinds on
Hi folks, I've been having fun debugging a problem with a program I'm writing (it's an Oracle-only app on OpenACS 4.5b) which I've managed to simplify down to the following. Create a test table x with one varchar2 column called test. Put the following in to a tcl page and call it.
set output ""

db_transaction {

  db_dml i {insert into x values ('testing123')}

  db_foreach time {select sysdate from dual} {

    set test [db_string checkit "select test from x" -default "No 
rows found"]
    append output "Inside db_foreach: $sysdate $test
" } } set test [db_string checkit "select test from x" -default "No rows found"] append output "Outside db_foreach: $test" db_dml d {delete x} ns_return 200 text/html $output
(apologies for the wrap-around. don't know how to stop that)

This produces the output:

Inside db_foreach: 2002-09-24 No rows found
Outside db_foreach: testing123
As you can see, although the insert is done in the same transaction as the db_foreach, I can't see the inserted data inside the db_foreach.

I could workaround this by compiling a list first and using foreach instead of db_foreach, but I shouldn't have to!

This is probably related to this thread, but it doesn't really answer my question.

Thanks,

Michael

Collapse
Posted by defunct defunct on
Is it because its inside a transaction? i.e. the update hasn;t actually taken place yet hence the select query can't return the new value?
Collapse
Posted by Dan Wickstrom on
This is known problem with the db api.  The problem is that you have two db handles active, one for the db_foreach and one for the db_string call.  The db_string handle is not included in the transaction (even thought it appears that it should be), so your select insided the db_foreach loop doesn't return anything, because the transaction which includes the db_dml's hasn't completed yet.  In your example, you could replace the db_foreach with a db_string to get the date, and it would work okay.
Collapse
Posted by Michael Hinds on
Yes Dan, that would work for my simple example, but I only used sysdate in order to demonstrate the problem. In reality it's a select returning multiple rows. Do you know of a way I could do all this in the same db handle?
Collapse
Posted by Dan Wickstrom on
There really is no work around other than restructuring the code.  As you suggested, you could build a list and then use foreach.  It's just not possible to have multiple handles active within a transaction - at least within TCL.  The problem arises from how the db drivers are implemented (both oracle and postgresql have this limitation), and my understanding is that it's not something that is easy to fix.

Another possibility is to move the code to pl/sql, as there are no limitations on the number of active handles when using the db procedural languages.

Collapse
Posted by Michael Hinds on
OK. I'm calling Tcl procs within the loop, so I'll have to go with the foreach. Thanks for the response.
Collapse
Posted by Michael A. Cleverly on
The limitation is an artifact of the way the AOLserver ns_db command works, not of Tcl per-say. The Oratcl extension, for example, does not have the same limitation of one statement-handle per connection-handle.
Collapse
Posted by Andrew Grumet on
Just as a heads up to future bboard searchers, this sort of db handle confusion can also result in deadlocks:
db_transaction {

    db_dml ins { insert into foo ... }

    db_foreach cur { select from ... } {

        db_dml upd { update foo ... }

    }
}
The problem here is that the insert "ins" and update "upd" are running in separate transactions as a result of using different handles. The update will wait for the insert to complete, causing the db_foreach to hang. But the insert won't complete until the db_foreach completes, so you wait forever.

See also bug 41 on the dotlrn bug tracker.

Collapse
Posted by Roberto Mello on
Hmmm, perhaps the DB api handler could have a little bit of intelligence and catch the most obvious cases of misuse and warn the developer.
Collapse
Posted by Andrew Piskorski on
Warnings might be nice. But using two database handles when you could just as easily use only one is usually a bad idea anyway, no? IMO, doing DML inside a db_foreach is almost always something to be avoided, anyway.

Of course, if you really really wanted to prevent any possibility of this bug, just configure your DB API to only use one pool of database handles. Then when you do something that forces the DB API to use a second handle (from a second pool...), it won't. ;)