Re: [pysqlite] Concurrency - first steps

> > However, http://www.sqlite.org/lockingv3.html lists UNLOCKED, 
> > SHARED,  RESERVED, PENDING and EXCLUSIVE. What is the mapping to 
> > PySQLite's modes?
> >
> 
> 	These are the internal locking levels ("The Definitive Guide to
> SQLite" is a tad costly, but has a whole chapter on the subject). 
> The isolation levels correspond to: BEGIN <TRANSACTION>, BEGIN 
> IMMEDIATE, and BEGIN EXCLUSIVE.

That got me to http://www.sqlite.org/lang_transaction.html which explains
about those. I was trying to compare apples and oranges: the type of
BEGIN statement vs the locking levels.

Thanks for your analysis. It seems to me that I would do best to have
BEGIN IMMEDIATE as the default transaction. That would immediately move
to a RESERVED lock. My update statements are simply executemany INSERT
statements.

That would allow other SELECT statements to execute simultaneously while
the update changes are being prepared by SQLite. However it would prevent
this circumstance as you described:

> I've not done multi-user database, so haven't encountered the
> situation... But attempts to recommit may not be sufficient -- say 
> you have a reader thread trying to move to RESERVED (that is, it has 
> issued maybe "insert into x values ..."), but the other thread is
> trying to do a connection.commit(). The commit will time-out because
> it is in PENDING waiting for the last SHARED to exit; and that SHARED
> will time-out because it is trying to get into RESERVED.

It seems to me that sacrificing a little bit of possible parallel
operation is preferable to multiple retries which presumably will require
complete reprocessing of the statement.
 
> {I'm not sure if the commit() are needed -- possibly the 
> transactions will "end" once the last record has been retrieved}

http://oss.itsystementwicklung.de/download/pysqlite/doc/sqlite3.html#contr
olling-transactions
makes it clear that an implicit transaction won't be opened in the case
of a simple SELECT.

Hugh