From 5479799e8b3930de706e4ce6a114bad374db245e Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Tue, 5 Oct 2021 11:46:54 -0400 Subject: [PATCH] Add new section clarifying DBAPI autocommit more fully Make sure it's documented that autocommit isolation level is not a connection-level setting, and does not interact with the connection's notion of a transaction. Fixes: #7141 Change-Id: Iefc580798551d74022ee2cfe102c22440b7d2f98 --- doc/build/core/connections.rst | 174 ++++++++++++++++++++++++-- doc/build/orm/session_transaction.rst | 5 + 2 files changed, 167 insertions(+), 12 deletions(-) diff --git a/doc/build/core/connections.rst b/doc/build/core/connections.rst index 979969c08c..556f6c4750 100644 --- a/doc/build/core/connections.rst +++ b/doc/build/core/connections.rst @@ -376,18 +376,22 @@ emitted. DBAPIs that support isolation levels also usually support the concept of true "autocommit", which means that the DBAPI connection itself will be placed into -a non-transactional autocommit mode. This usually means that the typical -DBAPI behavior of emitting "BEGIN" to the database automatically no longer -occurs, but it may also include other directives. When using this mode, -**the DBAPI does not use a transaction under any circumstances**. SQLAlchemy -methods like ``.begin()``, ``.commit()`` and ``.rollback()`` pass silently -and have no effect. - -Instead, each statement invoked upon the connection will commit any changes -automatically; it sometimes also means that the connection itself will use -fewer server-side database resources. For this reason and others, "autocommit" -mode is often desirable for non-transactional applications that need to read -individual tables or rows outside the scope of a true ACID transaction. +a non-transactional autocommit mode. This usually means that the typical DBAPI +behavior of emitting "BEGIN" to the database automatically no longer occurs, +but it may also include other directives. SQLAlchemy treats the concept of +"autocommit" like any other isolation level; in that it is an isolation level +that loses not only "read committed" but also loses atomicity. + +.. tip:: + + It is important to note, as will be discussed further in the section below at + :ref:`dbapi_autocommit_understanding`, that "autocommit" isolation level like + any other isolation level does **not** affect the "transactional" behavior of + the :class:`_engine.Connection` object, which continues to call upon DBAPI + ``.commit()`` and ``.rollback()`` methods (they just have no effect under + autocommit), and for which the ``.begin()`` method assumes the DBAPI will + start a transaction implicitly (which means that SQLAlchemy's "begin" **does + not change autocommit mode**). SQLAlchemy dialects should support these isolation levels as well as autocommit to as great a degree as possible. The levels are set via family of @@ -414,6 +418,19 @@ begin a transaction:: with connection.begin(): connection.execute() +.. note:: The return value of + the :meth:`_engine.Connection.execution_options` method is a so-called + "branched" connection under the SQLAlchemy 1.x series when not using + :paramref:`_sa.create_engine.future` mode, which is a shallow + copy of the original :class:`_engine.Connection` object. Despite this, + the ``isolation_level`` execution option applies to the + original :class:`_engine.Connection` object and all "branches" overall. + + When using :paramref:`_sa.create_engine.future` mode (i.e. :term:`2.0 style` + usage), the concept of these so-called "branched" connections is removed, + and :meth:`_engine.Connection.execution_options` returns the **same** + :class:`_engine.Connection` object without creating any copies. + The :paramref:`_engine.Connection.execution_options.isolation_level` option may also be set engine wide, as is often preferable. This is achieved by passing it within the :paramref:`_sa.create_engine.execution_options` @@ -478,6 +495,139 @@ reverted when a connection is returned to the connection pool. :ref:`faq_execute_retry_autocommit` - a recipe that uses DBAPI autocommit to transparently reconnect to the database for read-only operations +.. _dbapi_autocommit_understanding: + +Understanding the DBAPI-Level Autocommit Isolation Level +--------------------------------------------------------- + +In the parent section, we introduced the concept of the :paramref:`_engine.Connection.execution_options.isolation_level` +parameter and how it can be used to set database isolation levels, including +DBAPI-level "autocommit" which is treated by SQLAlchemy as another transaction +isolation level. In this section we will attempt to clarify the implications +of this approach. + +If we wanted to check out a :class:`_engine.Connection` object and use it +"autocommit" mode, we would proceed as follows:: + + with engine.connect() as connection: + connection.execution_options(isolation_level="AUTOCOMMIT") + connection.execute() + connection.execute() + +Above illustrates normal usage of "DBAPI autocommit" mode. There is no +need to make use of methods such as :meth:`_engine.Connection.begin` +or :meth:`_future.Connection.commit` (noting the latter applies to :term:`2.0 style` usage). + +What's important to note however is that the above autocommit mode is +**persistent on that particular Connection until we change it directly using +isolation_level again**. The isolation level is also reset on the DBAPI +connection when we :term:`release` the connection +back to the connection pool. However, calling upon :meth:`_engine.Connection.begin` +**will not** change the isolation level, meaning we stay in autocommit. The +example below illustrates this:: + + with engine.connect() as connection: + connection = connection.execution_options(isolation_level="AUTOCOMMIT") + + # this begin() does nothing, isolation stays at AUTOCOMMIT + with connection.begin() as trans: + connection.execute() + connection.execute() + +When we run a block like the above with logging turned on, the logging +will attempt to indicate that while a DBAPI level ``.commit()`` is called, +it probably will have no effect due to autocommit mode:: + + INFO sqlalchemy.engine.Engine BEGIN (implicit) + ... + INFO sqlalchemy.engine.Engine COMMIT using DBAPI connection.commit(), DBAPI should ignore due to autocommit mode + +Similarly, when using :term:`2.0 style` :paramref:`_sa.create_engine.future` +mode, the :class:`_engine.Connection` will use :ref:`autobegin ` +behavior, meaning that the pattern below will raise an error:: + + engine = create_engine(..., future=True) + + with engine.connect() as connection: + connection = connection.execution_options(isolation_level="AUTOCOMMIT") + + # "transaction" is autobegin (but has no effect due to autocommit) + connection.execute() + + # this will raise; "transaction" is already begun + with connection.begin() as trans: + connection.execute() + +This is all to demonstrate that the autocommit isolation level setting is +**completely independent from the begin/commit behavior of the SQLAlchemy +Connection object**. The "autocommit" mode will not interact with :meth:`_engine.Connection.begin` +in any way and the :class:`_engine.Connection` does not consult this status +when performing its own state changes with regards to the transaction (with +the exception of suggesting within engine logging that these blocks are not +actually committing). The rationale for this design is to maintain a +completely consistent usage pattern with the :class:`_engine.Connection` where +DBAPI-autocommit mode can be changed independently without indicating any code +changes elsewhere. + +Isolation level settings, including autocommit mode, are reset automatically +when the connection is released back to the connection pool. Therefore it is +preferable to avoid trying to switch isolation levels on a single +:class:`_engine.Connection` object as this leads to excess verbosity. + +To illustrate how to use "autocommit" in an ad-hoc mode within the scope of a +single :class:`_engine.Connection` checkout, the +:paramref:`_engine.Connection.isolation_level` setting must be used explicitly, +meaning we would need to retrieve the. We can write our above block "correctly" +as (noting 2.0 style usage below):: + + # if we wanted to flip autocommit on and off on a single connection/ + # which... we usually don't. + + engine = create_engine(..., future=True) + + with engine.connect() as connection: + + connection.execution_options(isolation_level="AUTOCOMMIT") + + # run statement in autocommit mode + connection.execute() + + # "commit" the autobegun "transaction" (2.0/future mode only) + connection.commit() + + # switch to default isolation level + connection.execution_options(isolation_level=connection.default_isolation_level) + + # use a begin block + with connection.begin() as trans: + connection.execute() + +Above, to manually revert the isolation level we made use of +:attr:`_engine.Connection.default_isolation_level` to revert to the normal +default for our engine (assuming that's what we want here). However, it's +probably a much better idea to work with the architecture of of the +:class:`_engine.Connection` which already handles resetting of isolation level +automatically upon checkin. The **preferred** way to write the above is to +use two blocks :: + + engine = create_engine(..., future=True) + + # use an autocommit block + with engine.connect().execution_options(isolation_level="AUTOCOMMIT") as connection: + + # run statement in autocommit mode + connection.execute() + + # use a regular block + with engine.begin() as connection: + connection.execute() + +To sum up: + +1. "DBAPI level autocommit" isolation level is entirely independent of the + :class:`_engine.Connection` object's notion of "begin" and "commit" +2. use individual :class:`_engine.Connection` checkouts per isolation level, + let the engine do the work of restoring default isolation levels .. _engine_stream_results: diff --git a/doc/build/orm/session_transaction.rst b/doc/build/orm/session_transaction.rst index e7401f618f..6d7c4dd18e 100644 --- a/doc/build/orm/session_transaction.rst +++ b/doc/build/orm/session_transaction.rst @@ -525,6 +525,11 @@ connections, but does not expose transaction isolation directly. So in order to affect transaction isolation level, we need to act upon the :class:`_engine.Engine` or :class:`_engine.Connection` as appropriate. +.. seealso:: + + :ref:`dbapi_autocommit` - be sure to review how isolation levels work at + the level of the SQLAlchemy :class:`_engine.Connection` object as well. + Setting Isolation For A Sessionmaker / Engine Wide ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ -- 2.47.3