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