Forum OpenACS Q&A: nested db_foreach considered harmful

Posted by Andrew Piskorski on
Keep in mind that each database handle is using its own entirely independent transaction. If you use db_transaction, it will not work across multiple database handles from multiple pools (or even from same pool, I think), but it won't throw a warning or error either. It will just not enforce a transaction, because it can't.

I have personally never seen even one case where using two database handles and pools at once via db_foreach was actually a good idea. Don, do such cases exist at all in real life? Maybe OpenACS should ship a default AOLserver config file that allows use of only one database pool at a time?

After all, people can always configure a 2nd, 3rd, 4th, or Nth pool if they really want to, and know what they're doing. The automatic transparent grabbing of additional independent database handles via db_foreach (introduced in or shortly before ACS 4.0) always seemed like a dangerous feature to me, most especially because of the way it breaks transactional semantics, which no one new to OpenACS is going to realize. I suspect newbies would be better off just getting an error message...

Hm, I wonder if any other database drivers (e.g., Oratcl, SQLite) let you issue multiple concurrent SQL statements which are all part of the same transaction, or if that's even possible with the Oracle OCI or PostgreSQL C libraries. Anybody know?