Repository navigation
Documentation about sqlite3 isolation_level #52392
Description
Activity
nagylzs commented
on Mar 15, 2010 nagylzsmannequinMannequinAuthorClarify what isolation_level does, and how to use it, and why connections do not commit/rollback in some cases.
Details here:
http://mail.python.org/pipermail/python-list/2010-March/1239374.html
I'll paste code for ctx_manager_2.py here. This is a new file, I could not include it as a diff:
import sqlite3 class MyConn(sqlite3.Connection): def __enter__(self): self.execute("BEGIN") return self def __exit__(self,exc_type,exc_info,traceback): if exc_type is None: self.execute("COMMIT") else: self.execute("ROLLBACK") conn = sqlite3.connect(':memory:',factory=MyConn) conn.isolation_level = None with conn: conn.execute("create table a ( i integer ) ") conn.execute("insert into a values (1)") try: with conn: conn.execute("insert into a values (2)") conn.execute("savepoint sp1") conn.execute("insert into a values (3)") conn.execute("rollback to sp1") conn.execute("insert into a values (4)") print "Before rollback: 1,2,4" for row in conn.execute("select * from a"): print row[0] # prints 1,2,4 raise Exception except: pass print "After rollback: 1" for row in conn.execute("select * from a"): print row[0] # prints 1
If the content of the patch is correct, it looks ready to apply, with only a touch of editing. Do we have a resident expert of sqlite3? Or Laszlo, do you have a reference for the statements made?
nagylzs commented
on Jul 11, 2010 nagylzsmannequinMannequinAuthor2010/7/11 Terry J. Reedy <report@bugs.python.org>
Terry J. Reedy <tjreedy@udel.edu> added the comment:
If the content of the patch is correct, it looks ready to apply, with only
a touch of editing. Do we have a resident expert of sqlite3? Or Laszlo, do
you have a reference for the statements made?Sorry, the only reference I have is that mailing list thread (
http://mail.python.org/pipermail/python-list/2010-March/1239374.html ).There you can find an example with SQL statements, showing the problem.
Ryan Kelly wrote the following:
<quote>
I have a theory, based on a quick perusal of the sqlite3 bindings
source.The bindings think that "SAVEPOINT sp1" is a "non-DML, non-query"
statement. So when isolation_level is something other than None, this
statement implicitly commits the current transaction and throws away
your savepoints!
</quote>So the problem is with the sqlite binding code, not sqlite itself. I'm not
an expert in C, so I'm not sure I can help more.Thanks,
Laszlo
Can someone review this patch?
(There is a typo: s/The/This/)I believe this patch is correct in essence, but I think it would be helpful to clarify the explanation of what autocommit mode is. I'll work on an updated patch.
It could also be considered a bug that the standard context manager does not support autocommit mode, but I'm going to treat that as a separate bug. Perhaps a fix will come out of fixing bpo-10740.
Here is a revised patch. I am leaving out the changes relating to the transaction manager. It turns out that the transaction manager doesn't do anything useful even if isolation_level is not None. I'm going to open a new issue to discuss the best way to fix it, and any documentation changes relating to any leftover brokenness will be part of that issue.
I misspoke, the transaction manager does do something useful in non-None isolation level. I'm still going to open a bug about isolation_level None.
sqlite_transaction_doc.patch looks good to me and applies cleanly to current default branch. One minor note: It would be nice to document that the default mode is "BEGIN DEFERRED" in Connection.isolation_level documentation.
The patch needs to be updated to address changes in bpo-10740 so I'm moving the stage field back to 'patch review'.
fschulze commented
on Nov 25, 2016 fschulzemannequinMannequinThe documentation patch is a definite improvement.
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: