New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
sqlite3 COMMIT nested in SELECT returns unexpected results #67318
Comments
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)') 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:
Also tested on Windows with identical results
|
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() The members of the sqlite mailing list confirmed they had no problem executing I've been digging around the pysqlite C source but can't quite figure out Jim
|
(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 db = sq.connect(':memory:', isolation_level=None) I'm hoping to put some time in scrutinizing the c-api code later this week (as |
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: 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: 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 bpo-10513. I've pushed a patch to fix this for 2.7 and 3.5+ in 030e100f048a and 81f614dd8136. |
Note: these values reflect the state of the issue at the time it was migrated and might not reflect the current state.
Show more details
GitHub fields:
bugs.python.org fields:
The text was updated successfully, but these errors were encountered: