Skip to content

Documentation about sqlite3 isolation_level #52392

Closed
Closed
Documentation about sqlite3 isolation_level#52392
Labels
3.7 (EOL)end of life3.8 (EOL)end of lifedocsDocumentation in the Doc dirtype-featureA feature request or enhancement

Description

@nagylzs
nagylzs
mannequin
opened on Mar 15, 2010
BPO 8145
Nosy @birkenfeld, @ezio-melotti, @bitdancer, @berkerpeksag, @zooba, @palaviv, @Mariatta, @fschulze
PRs
  • bpo-8145: Improve isolation_level documentation #8499
  • [3.6] bpo-8145: Improve isolation_level documentation (GH-8499) #8539
  • [3.7] bpo-8145: Improve isolation_level documentation (GH-8499) #8543
    		•
    Files
  • sqlite3.rst.diff: unified diff to http://svn.python.org/projects/python/branches/release26-maint/Doc/library/sqlite3.rst
  • sqlite_transaction_doc.patch
    		•

    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:

    assignee = None
closed_at = <Date 2018-07-29.11:12:07.075>
created_at = <Date 2010-03-15.09:01:43.951>
labels = ['3.8', 'type-feature', '3.7', 'docs']
title = 'Documentation about sqlite3 isolation_level'
updated_at = <Date 2018-07-29.11:12:07.072>
user = 'https://bugs.python.org/nagylzs'

    bugs.python.org fields:

    activity = <Date 2018-07-29.11:12:07.072>
actor = 'berker.peksag'
assignee = 'docs@python'
closed = True
closed_date = <Date 2018-07-29.11:12:07.075>
closer = 'berker.peksag'
components = ['Documentation']
creation = <Date 2010-03-15.09:01:43.951>
creator = 'nagylzs'
dependencies = []
files = ['16554', '28719']
hgrepos = []
issue_num = 8145
keywords = ['patch']
message_count = 14.0
messages = ['101090', '109948', '109965', '175909', '179686', '179905', '179906', '179910', '273722', '281710', '281716', '322615', '322618', '322627']
nosy_count = 11.0
nosy_names = ['georg.brandl', 'ghaering', 'ezio.melotti', 'r.david.murray', 'nagylzs', 'docs@python', 'berker.peksag', 'steve.dower', 'palaviv', 'Mariatta', 'fschulze']
pr_nums = ['8499', '8539', '8543']
priority = 'normal'
resolution = 'fixed'
stage = 'resolved'
status = 'closed'
superseder = None
type = 'enhancement'
url = 'https://bugs.python.org/issue8145'
versions = ['Python 3.6', 'Python 3.7', 'Python 3.8']

    Activity

    1. nagylzs commented on Mar 15, 2010

      @nagylzs
      nagylzsmannequin
      on Mar 15, 2010
      MannequinAuthor

      Clarify 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
    2. nagylzs
      assigned
      birkenfeld
      on Mar 15, 2010
    3. nagylzs
      added
      docsDocumentation in the Doc dir
      type-featureA feature request or enhancement
      on Mar 15, 2010

    4. terryjreedy commented on Jul 11, 2010

      @terryjreedy
      terryjreedy
      on Jul 11, 2010
      Member

      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?

    5. terryjreedy
      assigned
      docspython
      and unassigned
      birkenfeld
      on Jul 11, 2010

    6. nagylzs commented on Jul 11, 2010

      @nagylzs
      nagylzsmannequin
      on Jul 11, 2010
      MannequinAuthor

      2010/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

    7. ezio-melotti commented on Nov 18, 2012

      @ezio-melotti
      ezio-melotti
      on Nov 18, 2012
      Member

      Can someone review this patch?
      (There is a typo: s/The/This/)

    8. ezio-melotti
      added
      easy
      on Nov 18, 2012

    9. bitdancer commented on Jan 11, 2013

      @bitdancer
      bitdancer
      on Jan 11, 2013
      Member

      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.

    10. bitdancer commented on Jan 14, 2013

      @bitdancer
      bitdancer
      on Jan 14, 2013
      Member

      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.

    11. bitdancer commented on Jan 14, 2013

      @bitdancer
      bitdancer
      on Jan 14, 2013
      Member

      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.

    12. bitdancer commented on Jan 14, 2013

      @bitdancer
      bitdancer
      on Jan 14, 2013
      Member

      Opened bpo-16958 for the transaction manager problem.

    13. berkerpeksag commented on Aug 26, 2016

      @berkerpeksag
      berkerpeksag
      on Aug 26, 2016
      Member

      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.

    14. berkerpeksag commented on Nov 25, 2016

      @berkerpeksag
      berkerpeksag
      on Nov 25, 2016
      Member

      The patch needs to be updated to address changes in bpo-10740 so I'm moving the stage field back to 'patch review'.

    15. berkerpeksag
      added
      3.7 (EOL)end of life
      on Nov 25, 2016

    16. fschulze commented on Nov 25, 2016

      @fschulze
      fschulzemannequin
      on Nov 25, 2016
      Mannequin

      The documentation patch is a definite improvement.

    17. zooba
      removed
      easy
      on Jul 28, 2018

    18. zooba commented on Jul 29, 2018

      @zooba
      zooba
      on Jul 29, 2018
      Member

      New changeset a71fed0 by Steve Dower (Berker Peksag) in branch 'master':
      bpo-8145: Improve isolation_level documentation (GH-8499)
      a71fed0

    19. zooba commented on Jul 29, 2018

      @zooba
      zooba
      on Jul 29, 2018
      Member

      New changeset 94972d5 by Steve Dower (Miss Islington (bot)) in branch '3.6':
      bpo-8145: Improve isolation_level documentation (GH-8499)
      94972d5

    20. zooba commented on Jul 29, 2018

      @zooba
      zooba
      on Jul 29, 2018
      Member

      New changeset 3dc8cdf by Steve Dower in branch '3.7':
      bpo-8145: Improve isolation_level documentation (GH-8499)
      3dc8cdf

    21. berkerpeksag
      added
      3.8 (EOL)end of life
      on Jul 29, 2018
    22. berkerpeksag
      closed this as completedon Jul 29, 2018
    23. ezio-melotti
      transferred this issue fromon Apr 10, 2022
    Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

    Metadata

    Metadata

    Assignees

    No one assigned

      Labels

      3.7 (EOL)end of life3.8 (EOL)end of lifedocsDocumentation in the Doc dirtype-featureA feature request or enhancement

      Projects

      No projects

        Milestone

        No milestone

        Relationships

        None yet

        Development

        No branches or pull requests

        Issue actions