I reported this to the sqlite mailing list, and the comments I received suggested the problem may by the python sqlite connector issue, so I'm opening this as a bug report.

I understand that performing a SELECT and nested COMMIT on the same table is not supported in sqlite, but I would have expected a COMMIT on a separate table would not be a problem.  Some test code in python however reveals that performing the COMMIT disrupts the SELECT statement, and causes duplicate data to be returned.

If this is not a supported operation, would you mind pointing me to the docs so I can understand it better?

Example

#!/usr/bin/env python

import sqlite3 as sq

db = sq.connect(':memory:')

db.execute('CREATE TABLE tbl (col INTEGER)')
db.execute('CREATE TABLE tbl2 (col INTEGER)')
db.executemany('INSERT INTO tbl (col) VALUES (?)', [(0,), (1,), (2,)])
db.commit()

print('count=' + str(db.execute('SELECT count(*) FROM tbl').fetchone()[0]))

# Read and print the values just inserted into tbl
for col in db.execute('SELECT col FROM tbl'):
    print(col)
    db.execute('INSERT INTO tbl2 VALUES (?)', col)
    db.commit()

print('count=' + str(db.execute('SELECT count(*) FROM tbl').fetchone()[0]))


The output we see is:

count=3
(0,)
(1,)
(0,)
(1,)
(2,)
count=3


The expected output was:

count=3
(0,)
(1,)
(2,)
count=3

Tested on Linux:

    sqlite version 3.7.13

    sqlite3 connector version 2.6.0
     
    # uname -a
    Linux hostname 3.16-0.bpo.3-amd64 #1 SMP Debian 
    3.16.5-1~bpo70+1 (2014-11-02) x86_64 GNU/Linux

Also tested on Windows with identical results

     Sqlite version 3.6.21
     Windows 7 Professional, 64-bit

I'd say you have a bug here of some sort, but I'm not sure if it is a doc bug or a code bug.  Commit specifically does *not* reset the cursors, according to the code, but I'm not even sure what resetting a cursor means :)  I've poked around the sqlite3 module's code a bit, but not enough to have an answer to this.  I do note that a commit does a call to sqlite3_reset on all statements associated with the connection, and I suspect that's where the problem originates.  Which probably makes it a doc bug.

Hi David,

One more data point. Although I demonstrated the bug using the .execute() 
method associated with a connection object -- you can also create the exact 
problem using the .execute() method associated with cursors. This leaves no 
means to COMMIT inside a nested SELECT.

The members of the sqlite mailing list confirmed they had no problem executing 
the SQL statements using C and PHP. I think this is a bug, rather than just a 
problem with the docs.

I've been digging around the pysqlite C source but can't quite figure out 
what's going on yet.

Jim

> -----Original Message-----
> From: R. David Murray [mailto:report@bugs.python.org]
> Sent: Monday, December 29, 2014 1:08 PM
> To: jim@carroll.com
> Subject: [issue23129] sqlite3 COMMIT nested in SELECT returns
> unexpected results
>
>
> R. David Murray added the comment:
>
> I'd say you have a bug here of some sort, but I'm not sure if it is a
> doc bug or a code bug.  Commit specifically does *not* reset the
> cursors, according to the code, but I'm not even sure what resetting a
> cursor means :)  I've poked around the sqlite3 module's code a bit, but
> not enough to have an answer to this.  I do note that a commit does a
> call to sqlite3_reset on all statements associated with the connection,
> and I suspect that's where the problem originates.  Which probably
> makes it a doc bug.
>
> ----------
> nosy: +r.david.murray
>
> _______________________________________
> Python tracker <report@bugs.python.org>
> <http://bugs.python.org/issue23129>
> _______________________________________

(Please trim the original message when replying to a tracker issue.)

When I said, "that probably means it's a doc issue", I meant that, even if it is a bug, for backward compatibility reasons we'd have to just document the behavior.  Fixing the bug would then be a new feature (new keyword to change the behavior or some such).  (ie: the fix to a design bug is a feature :)

Now, if it turns out that the bug lies elsewhere, or that changing the current statement-reset behavior wouldn't actually change the meaning of anyone's program if we changed it, then we could fix it as a bug.  But we'll need to understand the bug in detail before we can make that determination.

Completely understood.

I recently found a workaround. Setting isolation_level to None seems to 
mitigate the issue, ie:

    db = sq.connect(':memory:', isolation_level=None)

I'm hoping to put some time in scrutinizing the c-api code later this week (as 
SQLite bugs directly affect projects we work on) to see if we can get to the 
bottom of the issue.

Ah, then I suspect you are getting hit by the 'automatic transaction' feature of the DB2 API.  So it is probably not the commit, but the subsequent implicit 'begin transaction' that is causing the problem.  The trick then is to figure out why that affects the existing select cursor.

I've ran into this as well, when a program that was running correctly with PostgreSQL turned out to produce garbage with SQLite. Code to reproduce roughly looks like this:

    sel_cursor = conn.cursor()
    sel_cursor.execute("SELECT prim_key_id FROM "
                       "somedb ORDER BY start_time ASC")
    add_cursor = conn.cursor()
    prim_keys = set()
    row = sel_cursor.fetchone()
    while row:
        seq = row[0]
        if seq in prim_keys:
            raise RuntimeError
        prim_keys.add(seq)
        add_cursor.execute("INSERT INTO someotherdb "
                           "VALUES (?)",
                           seq)
        conn.commit()
        row = sel_cursor.fetchone()
    conn.commit()

This will raise a RuntimeError because the SELECT will return the same primary key value twice - something that's obviously impossible.

This exact bug has been filed once more already, and it seems to be an actual regression in Python 2.7:
http://bugs.python.org/issue10513

Looking at the code there, I agree with the analysis in this message: http://bugs.python.org/issue10513#msg150162

Either the pysqlite_do_all_statements should set reset_cursors=1 to warn the user that the cursor has been reset, or it shouldn't reset them to begin with (as Python <2.7 used to do).

I don't think there's any argument this isn't a (bad) bug: Python <2.7 works correctly and later versions silently corrupt data.

I agree on the nastiness of this bug. It's been plaguing my production code for months and I had been at a loss to explain why I've been getting duplicate rows until I found this SO post:

http://stackoverflow.com/questions/27624049/python-sqlite3-cursor-returns-duplicates-when-a-commit-intervenes

By the way, the test case in Jim Carroll's report appears to come from this StackOverflow question.

Thanks for the report! This is a duplicate of issue 10513. I've pushed a patch to fix this for 2.7 and 3.5+ in 030e100f048a and 81f614dd8136.