From: <pa...@bo...> - 2001-09-04 15:53:22
|
Jack Moffitt <ja...@xi...> wrote: > >You have to commit on select? So I should commit() before I do any >calls? That doesn't seem to make much sense. Shouldn't a commit() >update _all_ other connections? From previous experience, I'm fairly sure that you shouldn't *need* to commit anything if all you're doing is selecting. However, when I've been selecting data, I can't say that I have kept connections open for very long and, thinking about it a bit more, I do recall that closing a connection should rollback and thus close any transaction that was open and uncommitted. I don't remember whether "oracledb", for example, opened transactions for select operations, though, but for my purposes if it had done so then I would have been satisfactory isolated. (That module did have a bug which caused transactions to be committed by default, but I don't remember whether a transaction was always opened when a connection was created.) You're not necessarily going to get an entirely consistent view of the database across queries if you don't isolate yourself in a transaction - perhaps this is what Federico means... Federico Di Gregorio <fo...@mi...> wrote: > >postgresql allow for full isolation and we decided to use it. when you >create a connection, you take a snapshot of the db. that snapshot is only >updated (yes, even when doing a select) on commit() or rollback(). after >1.0 gets released we'll start working on user-selectable serialization >levels but that's another story. "Clark C . Evans" <cc...@cl...> wrote: > >Really... so there is *always* an active transaction >for each open connection? IMHO, this is bad. Especially >for connection pools. It forces the database to keep >data pages in memory to provide a consistent snapshot of >data that could be hours or days old... not pretty. Arguably, a database module shouldn't open transactions unless they are either necessary for the operations in question, or they are requested explicitly. Unfortunately, the DB-API (version 2) doesn't say which operations require such behaviour by default, nor does it mandate methods for explicitly beginning and ending transactions, nor does it offer a means of control over transactions, although mxODBC does provide an autocommit parameter to the connect function which could help a little in certain cases. >This also means that my hack won't work afterall... > >Why can't the library just let me do the "BEGIN" >when I want it? This "auto-begin-work" thing doesn't >work well... if I want a transaction, I'll start one. >Or is this a problem? I've always considered myself >a fairly experienced database person... I started >to use Oracle in '89. Why not try and get explicit transaction handling into the next release of the DB-API? With the current undefined nature of transactions in the current DB-API specification, it must be a potential nightmare to be sure that any application is portable across database systems. Federico Di Gregorio <fo...@mi...> wrote: > >the problem is the DBAPI-2.0 (as developed on the python db-sig mailing >list). it specifies that subsequent .execute() on a cursor should be, if >the database allows for transactions, inside a transaction. you don't >need (well you _should_not_ in the DBAPI view) fiddle with transactions >yourself. I don't see this in the specification, but mxODBC does implement this behaviour. This surprises me to an extent, because I never really thought much about wrapping selects within transactions, but then I never really needed to invoke many such queries in succession. I've learnt something from you today, I think. ;-) Thinking a bit more about it now, in your situation, I would consider the most logical place to have automatic opening of transactions to be at the cursor level. It's quite natural to perform a number of selects with the same cursor, and it's realistic to expect consistency between those queries. Moreover, it's typical to close cursors more or less immediately after use. Therefore, each cursor would have its own view of the database, and each connection wouldn't in itself have any particular idea of the state of the database. However, as you can see with the DB-API, commit and rollback are only available on connection objects. Transaction handling with the DB-API was always one of the hot topics, as I seem to remember. There are differences between database systems when it comes to the level certain operations are implemented - stored procedures might be logically implemented at the cursor level, for example, but I think some database systems implement them at the connection level instead. This does affect where transaction handling is implemented too. How about a proposal to the DB-SIG, along with some concrete information about how transactions are managed in some database systems? The DB-SIG mailing list is too quiet, but a real discussion could be had on this topic. Paul -- Get your firstname@lastname email for FREE at http://Nameplanet.com/?su |