Re: [pysqlite] repeatable reads with pysqlite?

Phil Budne wrote:
> I started a project using apsw, since I hadn't known about lastrowid
> in DB-API (or it's support in pysqlite).  Since "sqlite3" is now part
> of the base Python distribution (and installation of apsw under both
> FreeBSD "ports" and Mac "MacPorts" failed, requiring me to to a hand
> install), I was looking at porting my code to it, with an eye to
> making it database neutral.
> 
> When working with apsw, I wrapped series of queries that need to
> return consistent results in BEGIN/END pairs (to ensure "repeatable
> reads" / avoid "phantom reads"), 

How does issuing SELECTs in a transaction behave any differently than 
outside? AFAIK SQLite's different transaction types only affect how 
locking is handled. Or is there anything like "READ COMMITTED", "READ 
UNCOMMITTED" like in other systems that I'm not aware of?

 > as well as when deleting and
> inserting groups of rows.  However, I've found that sqlite3 has it's
> own ideas about issuing BEGIN statements, which can cause execute()
> calls to fail.

pysqlite transparently opens transactions for you to be DB-API 
compliant. SQLite itself never does. What did "fail" for you? Can you 
provide an example?

> It doesn't seem like DB-API implementations are consistent on this
> subject.  From what I can see MySQL-python-1.2.2 doesn't EVER start
> transactions on it's own, 

MySQLdb (or MySQL itself, I can't say at the moment) sure does open 
transactions for you, if you're using InnoDB tables.

> and psycopg2 does (depending on isolation
> level) on EVERY execute.
> 
> Here is the logic in pysqlite-2.5.0/src/cursor.c's
>  _pysqlite_query_execute() function:
> [snip]
> 
> It seems to me that if you want to execute a series of SELECTs inside
> a transaction under pysqlite, you need to issue an UPDATE, DELETE,
> INSERT or UPDATE first!

Yes, that's true. If there's any compelling reason to also open 
transactions for SELECT statements, I'll implement a switch for pysqlite.

-- Gerhard