Re: [pysqlite] database locked

Dennis Lee Bieber wrote:
> [...]
> 
> 	Based upon a box in "The Definitive Guide...", pysqlite2 sort of
> wraps modification SQL in transactions, and commits when the SQL
> operation changes type... So the implicit BEGIN happens when the cursor
> executes an INSERT/UPDATE/DELETE/REPLACE, and the transaction continues
> through subsequent similar statements... But issuing a SELECT /after/
> issuing an INSERT (for example) results in pysqlite2 doing a implicit
> COMMIT before the SELECT. 

Wrong! There are only two possibilities where pysqlite commits.

1) You call .commit() on the connection object
2) You execute a DDL statement (CREATE TABLE/CREATE VIEW/...). pysqlite 
then does an implicit COMMIT before these

Transactions are implicitly opened on the first encountered DML 
statement (INSERT/UPDATE/DELETE/REPLACE).

> [...] There is a warning not to use "... ON CONFLICT
> ROLLBACK" because it confuses pysqlite2.

I removed the warning a while ago, because I now call an SQLite API 
function to detect if *SQLite* implicitly did a ROLLBACK.

> [...]
> However, note the other thing in my tests... A SELECT does not
> finish until all the data has been read from it! If you are looping over
> a large number of records, and each record takes, say 0.5 seconds to
> process (slow machine?), that cursor will have a shared read lock during
> the entire looping; whereas doing a .fetchall() and then looping over
> the results frees the cursor practically immediately (but will take a
> bit more memory to store the list of all results rather than one row at
> a time). [...]

Yes, that's correct and I should probably add a note about this to the 
documentation.

FWIW this is a good place to use cursor_factory and provide your own 
cursor class that transparently does the fetchall() after the execute() 
if you encounter this problem.

-- Gerhard