Re: more questions from the reasonably na ïve regarding cursors, record locking, and committin g

sorry about the long delayed reply.  reasons too complicated to explain.

Dennis Lee Bieber wrote:
> On Tue, 27 Feb 2007 11:24:52 -0500, "Eric S. Johansson"
> <[email protected]> declaimed the following in
> gmane.comp.python.db.pysqlite.user:
> 
>> I think some of these questions fall in the category of "running in the
>> dark with scissors".  If it's a simple matter of I need to read
>> something, just point me at the appropriate documentation that I've 
>> obviously messed.
>>
> 	Based on the following (snipped) text, your questions are not DBMS
> specific... Pretty much any DBMS with cursors (or emulated cursors) have
> the same reasons.

I figured as much.  I am trying to climb the learning curve and it's... 
interesting...

> 	For simplicity, a cursor represents a /query/; it can be reused once
> all the results from a previous operation have been processed. It is not
> table specific (unless your queries only use single tables with no
> joins). Multiple cursors would be of use if you need to perform parallel
> queries (and perform updates of base tables when a join would get in the
> way of using a single query).

interesting.  That helps a lot.
> 
> 	Processes are totally separate executions, and don't share anything.
> Threads could share -- but for that you need to read the DB-API
> regarding the thread-safety parameter, and /then/ check for what value
> that parameter has for SQLite (as I recall, SQLite requires each thread
> to create its own cursor -- and maybe even create its own database
> connection).

I wasn't sure where the state was So it sounds like it's within the 
cursor object itself.  Again, makes sense now.
>  
>> Record locking.  I did read http://www.sqlite.org/lockingv3.html  I do a
>> fair number of read/modify/write cycles on individual rows in all of the
>> tables from multiple processes.  if I interpret the documentation
>> correctly, I need to create a transaction and when I am done changing
>> things, "commit" which is really just turning on auto commit?
>>
> 	No -- leave auto commit OFF if you want to properly handle potential
> conflicts.

k

> 	Until committed, a transaction should only be visible to the thread
> that started it. Failure to commit should result in the entire
> transaction being "forgotten" -- no changes to the database.
> 
> 	Closing an uncommitted transaction should result in automatic
> roll-back (forgetting) of the changes.
> 
> 	Commit when you know the transaction is completed -- and be ready to
> handle conflicts if you really do have multiple processes/threads
> working with the database.

I'm a bit confused.  The locking documentation implies that when you 
start a transaction, it locks the record or maybe even the file.  But 
your description makes it sound like a transaction does not lock the 
record but instead detects if the record has changed while the 
transaction was happening.  Is that correct?

thanks

-- 
Speech-recognition in use.  It makes mistakes, I correct some.