summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--doc/build/changelog/migration_20.rst4
-rw-r--r--doc/build/core/connections.rst118
-rw-r--r--doc/build/orm/session_transaction.rst141
-rw-r--r--lib/sqlalchemy/dialects/mssql/base.py7
-rw-r--r--lib/sqlalchemy/dialects/mysql/base.py4
-rw-r--r--lib/sqlalchemy/dialects/oracle/base.py4
-rw-r--r--lib/sqlalchemy/dialects/postgresql/base.py2
-rw-r--r--lib/sqlalchemy/dialects/sqlite/base.py4
-rw-r--r--lib/sqlalchemy/engine/base.py18
-rw-r--r--lib/sqlalchemy/testing/profiling.py3
-rw-r--r--lib/sqlalchemy/util/__init__.py1
-rw-r--r--lib/sqlalchemy/util/compat.py1
-rw-r--r--test/engine/test_transaction.py11
13 files changed, 237 insertions, 81 deletions
diff --git a/doc/build/changelog/migration_20.rst b/doc/build/changelog/migration_20.rst
index 9eb145cd0..ce26e7891 100644
--- a/doc/build/changelog/migration_20.rst
+++ b/doc/build/changelog/migration_20.rst
@@ -312,6 +312,10 @@ The above code is already available in current SQLAlchemy releases. Driver
support is available for PostgreSQL, MySQL, SQL Server, and as of SQLAlchemy
1.3.16 Oracle and SQLite as well.
+.. seealso::
+
+ :ref:`dbapi_autocommit`
+
.. _migration_20_implicit_execution:
"Implicit" and "Connectionless" execution, "bound metadata" removed
diff --git a/doc/build/core/connections.rst b/doc/build/core/connections.rst
index 83ad86e25..aa41a868a 100644
--- a/doc/build/core/connections.rst
+++ b/doc/build/core/connections.rst
@@ -173,12 +173,18 @@ one exists.
.. _autocommit:
-Understanding Autocommit
-========================
+Library Level (e.g. emulated) Autocommit
+==========================================
+
+.. deprecated:: 1.4 The "autocommit" feature of SQLAlchemy Core is deprecated
+ and will not be present in version 2.0 of SQLAlchemy. DBAPI-level
+ AUTOCOMMIT is now widely available which offers superior performance
+ and occurs transparently. See :ref:`migration_20_autocommit` for background.
-.. deprecated:: 2.0 The "autocommit" feature of SQLAlchemy Core is deprecated
- and will not be present in version 2.0 of SQLAlchemy.
- See :ref:`migration_20_autocommit` for background.
+.. note:: This section discusses the feature within SQLAlchemy that automatically
+ invokes the ``.commit()`` method on a DBAPI connection, however this is against
+ a DBAPI connection that **is itself transactional**. For true AUTOCOMMIT,
+ see the next section :ref:`dbapi_autocommit`.
The previous transaction example illustrates how to use :class:`.Transaction`
so that several executions can take part in the same transaction. What happens
@@ -218,6 +224,108 @@ it so that a SELECT statement will issue a COMMIT::
with engine.connect().execution_options(autocommit=True) as conn:
conn.execute(text("SELECT my_mutating_procedure()"))
+.. _dbapi_autocommit:
+
+Setting Transaction Isolation Levels including DBAPI Autocommit
+=================================================================
+
+Most DBAPIs support the concept of configurable transaction :term:`isolation` levels.
+These are traditionally the four levels "READ UNCOMMITTED", "READ COMMITTED",
+"REPEATABLE READ" and "SERIALIZABLE". These are usually applied to a
+DBAPI connection before it begins a new transaction, noting that most
+DBAPIs will begin this transaction implicitly when SQL statements are first
+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-tranasctional applications that need to read
+individual tables or rows in isolation of a true ACID transaction.
+
+SQLAlchemy dialects can support these isolation levels as well as autocommit to
+as great a degree as possible. The levels are set via family of
+"execution_options" parameters and methods that are throughout the Core, such
+as the :meth:`_engine.Connection.execution_options` method. The parameter is
+known as :paramref:`_engine.Connection.execution_options.isolation_level` and
+the values are strings which are typically a subset of the following names::
+
+ # possible values for Connection.execution_options(isolation_level="<value>")
+
+ "AUTOCOMMIT"
+ "READ COMMITTED"
+ "READ UNCOMMITTED"
+ "REPEATABLE READ"
+ "SERIALIZABLE"
+
+Not every DBAPI supports every value; if an unsupported value is used for a
+certain backend, an error is raised.
+
+For example, to force REPEATABLE READ on a specific connection::
+
+ with engine.connect().execution_options(isolation_level="REPEATBLE READ") as connection:
+ connection.execute(<statement>)
+
+The :paramref:`_engine.Connection.execution_options.isolation_level` option
+may also be set engine wide, as is often preferable. It can be set either
+within :func:`_sa.create_engine` directly via the :paramref:`_sa.create_engine.execution_options`
+parameter::
+
+
+ from sqlalchemy import create_engine
+
+ eng = create_engine(
+ "postgresql://scott:tiger@localhost/test",
+ isolation_level='REPEATABLE READ'
+ )
+
+Or for an application that chooses between multiple levels, as may be the case
+for the use of "AUTOCOMMIT" to switch between "transactional" and "read-only"
+engines, the :meth:`_engine.Engine.execution_options` method will provide a shallow
+copy of the :class:`_engine.Engine` that will apply the given isolation
+level to all connections::
+
+
+ from sqlalchemy import create_engine
+
+ eng = create_engine("postgresql://scott:tiger@localhost/test")
+
+ autocommit_engine = eng.execution_options(isolation_level="AUTOCOMMIT")
+
+
+Above, both ``eng`` and ``autocommit_engine`` share the same dialect
+and connection pool. However the "AUTOCOMMIT" mode will be set upon connections
+when they are acquired from the ``autocommit_engine``.
+
+The isolation level setting, regardless of which one it is, is unconditionally
+reverted when a connection is returned to the connection pool.
+
+
+.. note:: The :paramref:`_engine.Connection.execution_options.isolation_level`
+ parameter necessarily does not apply to statement level options, such as
+ that of :meth:`_sql.Executable.execution_options`. This because the option
+ must be set on a DBAPI connection on a per-transaction basis.
+
+.. seealso::
+
+ :ref:`SQLite Transaction Isolation <sqlite_isolation_level>`
+
+ :ref:`PostgreSQL Transaction Isolation <postgresql_isolation_level>`
+
+ :ref:`MySQL Transaction Isolation <mysql_isolation_level>`
+
+ :ref:`SQL Server Transaction Isolation <mssql_isolation_level>`
+
+ :ref:`session_transaction_isolation` - for the ORM
+
.. _dbengine_implicit:
diff --git a/doc/build/orm/session_transaction.rst b/doc/build/orm/session_transaction.rst
index b02a84ac5..c5f47697b 100644
--- a/doc/build/orm/session_transaction.rst
+++ b/doc/build/orm/session_transaction.rst
@@ -491,14 +491,23 @@ transactions set the flag ``twophase=True`` on the session::
.. _session_transaction_isolation:
-Setting Transaction Isolation Levels
-------------------------------------
-
-:term:`Isolation` refers to the behavior of the transaction at the database
-level in relation to other transactions occurring concurrently. There
-are four well-known modes of isolation, and typically the Python DBAPI
-allows these to be set on a per-connection basis, either through explicit
-APIs or via database-specific calls.
+Setting Transaction Isolation Levels / DBAPI AUTOCOMMIT
+-------------------------------------------------------
+
+Most DBAPIs support the concept of configurable transaction :term:`isolation` levels.
+These are traditionally the four levels "READ UNCOMMITTED", "READ COMMITTED",
+"REPEATABLE READ" and "SERIALIZABLE". These are usually applied to a
+DBAPI connection before it begins a new transaction, noting that most
+DBAPIs will begin this transaction implicitly when SQL statements are first
+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.
SQLAlchemy's dialects support settable isolation modes on a per-:class:`_engine.Engine`
or per-:class:`_engine.Connection` basis, using flags at both the
@@ -510,33 +519,65 @@ 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::
+Setting Isolation For A Sessionmaker / Engine Wide
+~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
- :paramref:`_sa.create_engine.isolation_level`
+To set up a :class:`.Session` or :class:`.sessionmaker` with a specific
+isolation level globally, the first technique is that an
+:class:`_engine.Engine` can be constructed against a specific isolation level
+in all cases, which is then used as the source of connectivity for a
+:class:`_orm.Session` and/or :class:`_orm.sessionmaker`::
- :ref:`SQLite Transaction Isolation <sqlite_isolation_level>`
+ from sqlalchemy import create_engine
+ from sqlalchemy.orm import sessionmaker
- :ref:`PostgreSQL Isolation Level <postgresql_isolation_level>`
+ eng = create_engine(
+ "postgresql://scott:tiger@localhost/test",
+ isolation_level='REPEATABLE READ'
+ )
- :ref:`MySQL Isolation Level <mysql_isolation_level>`
+ Session = sessionmaker(eng)
-Setting Isolation Engine-Wide
-~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-To set up a :class:`.Session` or :class:`.sessionmaker` with a specific
-isolation level globally, use the :paramref:`_sa.create_engine.isolation_level`
-parameter::
+Another option, useful if there are to be two engines with different isolation
+levels at once, is to use the :meth:`_engine.Engine.execution_options` method,
+which will produce a shallow copy of the original :class:`_engine.Engine` which
+shares the same connection pool as the parent engine. This is often preferable
+when operations will be separated into "transactional" and "autocommit"
+operations::
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
- eng = create_engine(
- "postgresql://scott:tiger@localhost/test",
- isolation_level='REPEATABLE_READ')
+ eng = create_engine("postgresql://scott:tiger@localhost/test")
- maker = sessionmaker(bind=eng)
+ autocommit_engine = eng.execution_options(isolation_level="AUTOCOMMIT")
- session = maker()
+ transactional_session = sessionmaker(eng)
+ autocommit_session = sessionmaker(autocommit_engine)
+
+
+Above, both "``eng``" and ``"autocommit_engine"`` share the same dialect and
+connection pool. However the "AUTOCOMMIT" mode will be set upon connections
+when they are acquired from the ``autocommit_engine``. The two
+:class:`_orm.sessionmaker` objects "``transactional_session``" and "``autocommit_session"``
+then inherit these characteristics when they work with database connections.
+
+
+The "``autocommit_session``" **continues to have transactional semantics**,
+including that
+:meth:`_orm.Session.commit` and :meth:`_orm.Session.rollback` still consider
+themselves to be "committing" and "rolling back" objects, however the
+transaction will be silently absent. For this reason, **it is typical,
+though not strictly required, that a Session with AUTOCOMMIT isolation be
+used in a read-only fashion**, that is::
+
+
+ with autocommit_session() as session:
+ some_objects = session.execute(<statement>)
+ some_other_objects = session.execute(<statement>)
+
+ # closes connection
Setting Isolation for Individual Sessions
@@ -545,12 +586,11 @@ Setting Isolation for Individual Sessions
When we make a new :class:`.Session`, either using the constructor directly
or when we call upon the callable produced by a :class:`.sessionmaker`,
we can pass the ``bind`` argument directly, overriding the pre-existing bind.
-We can combine this with the :meth:`_engine.Engine.execution_options` method
-in order to produce a copy of the original :class:`_engine.Engine` that will
-add this option::
+We can for example create our :class:`_orm.Session` from the
+"``transactional_session``" and pass the "``autocommit_engine``"::
- session = maker(
- bind=engine.execution_options(isolation_level='SERIALIZABLE'))
+ with transactional_session(bind=autocommit_engine) as session:
+ # work with session
For the case where the :class:`.Session` or :class:`.sessionmaker` is
configured with multiple "binds", we can either re-specify the ``binds``
@@ -559,8 +599,7 @@ can use the :meth:`.Session.bind_mapper` or :meth:`.Session.bind_table`
methods::
session = maker()
- session.bind_mapper(
- User, user_engine.execution_options(isolation_level='SERIALIZABLE'))
+ session.bind_mapper(User, autocommit_engine)
We can also use the individual transaction method that follows.
@@ -571,49 +610,27 @@ A key caveat regarding isolation level is that the setting cannot be
safely modified on a :class:`_engine.Connection` where a transaction has already
started. Databases cannot change the isolation level of a transaction
in progress, and some DBAPIs and SQLAlchemy dialects
-have inconsistent behaviors in this area. Some may implicitly emit a
-ROLLBACK and some may implicitly emit a COMMIT, others may ignore the setting
-until the next transaction. Therefore SQLAlchemy emits a warning if this
-option is set when a transaction is already in play. The :class:`.Session`
-object does not provide for us a :class:`_engine.Connection` for use in a transaction
-where the transaction is not already begun. So here, we need to pass
-execution options to the :class:`.Session` at the start of a transaction
-by passing :paramref:`.Session.connection.execution_options`
-provided by the :meth:`.Session.connection` method::
+have inconsistent behaviors in this area.
+
+Therefore it is preferable to use a :class:`_orm.Session` that is up front
+bound to an engine with the desired isolation level. However, the isolation
+level on a per-connection basis can be affected by using the
+:meth:`_orm.Session.connection` method at the start of a transaction::
from sqlalchemy.orm import Session
sess = Session(bind=engine)
- sess.connection(execution_options={'isolation_level': 'SERIALIZABLE'})
+ with sess.begin():
+ sess.connection(execution_options={'isolation_level': 'SERIALIZABLE'})
- # work with session
-
- # commit transaction. the connection is released
+ # commits transaction. the connection is released
# and reverted to its previous isolation level.
- sess.commit()
Above, we first produce a :class:`.Session` using either the constructor
or a :class:`.sessionmaker`. Then we explicitly set up the start of
a transaction by calling upon :meth:`.Session.connection`, which provides
for execution options that will be passed to the connection before the
-transaction is begun. If we are working with a :class:`.Session` that
-has multiple binds or some other custom scheme for :meth:`.Session.get_bind`,
-we can pass additional arguments to :meth:`.Session.connection` in order to
-affect how the bind is procured::
-
- sess = my_sessionmaker()
-
- # set up a transaction for the bind associated with
- # the User mapper
- sess.connection(
- mapper=User,
- execution_options={'isolation_level': 'SERIALIZABLE'})
-
- # work with session
-
- # commit transaction. the connection is released
- # and reverted to its previous isolation level.
- sess.commit()
+transaction is begun.
Tracking Transaction State with Events
diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py
index 35e6799c5..c3cc4e425 100644
--- a/lib/sqlalchemy/dialects/mssql/base.py
+++ b/lib/sqlalchemy/dialects/mssql/base.py
@@ -342,11 +342,12 @@ Valid values for ``isolation_level`` include:
* ``SERIALIZABLE``
* ``SNAPSHOT`` - specific to SQL Server
-.. versionadded:: 1.1 support for isolation level setting on Microsoft
- SQL Server.
-
.. versionadded:: 1.2 added AUTOCOMMIT isolation level setting
+.. seealso::
+
+ :ref:`dbapi_autocommit`
+
Nullability
-----------
MSSQL has support for three levels of column nullability. The default
diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index d3d7a8cce..d4ffcabd5 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -158,7 +158,9 @@ MySQLdb, MySQL-Client, MySQL-Connector Python, and PyMySQL. Using it,
the MySQL connection will return true for the value of
``SELECT @@autocommit;``.
-.. versionadded:: 1.1 - added support for the AUTOCOMMIT isolation level.
+.. seealso::
+
+ :ref:`dbapi_autocommit`
AUTO_INCREMENT Behavior
-----------------------
diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py
index 34c665fbe..6f4df8068 100644
--- a/lib/sqlalchemy/dialects/oracle/base.py
+++ b/lib/sqlalchemy/dialects/oracle/base.py
@@ -68,6 +68,10 @@ Valid values for ``isolation_level`` include:
as well as the notion of a default isolation level, currently harcoded
to "READ COMMITTED".
+.. seealso::
+
+ :ref:`dbapi_autocommit`
+
Identifier Casing
-----------------
diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index c2d9af4d2..5cef5d929 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -117,6 +117,8 @@ Valid values for ``isolation_level`` include:
.. seealso::
+ :ref:`dbapi_autocommit`
+
:ref:`psycopg2_isolation_level`
:ref:`pg8000_isolation_level`
diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py
index 2868eabba..3a8ffa23d 100644
--- a/lib/sqlalchemy/dialects/sqlite/base.py
+++ b/lib/sqlalchemy/dialects/sqlite/base.py
@@ -212,6 +212,10 @@ by *not even emitting BEGIN* until the first write operation.
degree than is often feasible. See the section :ref:`pysqlite_serializable`
for techniques to work around this behavior.
+.. seealso::
+
+ :ref:`dbapi_autocommit`
+
SAVEPOINT Support
----------------------------
diff --git a/lib/sqlalchemy/engine/base.py b/lib/sqlalchemy/engine/base.py
index 2d672099b..d60f14f31 100644
--- a/lib/sqlalchemy/engine/base.py
+++ b/lib/sqlalchemy/engine/base.py
@@ -212,16 +212,14 @@ class Connection(Connectable):
:param autocommit: Available on: Connection, statement.
When True, a COMMIT will be invoked after execution
when executed in 'autocommit' mode, i.e. when an explicit
- transaction is not begun on the connection. Note that DBAPI
- connections by default are always in a transaction - SQLAlchemy uses
- rules applied to different kinds of statements to determine if
- COMMIT will be invoked in order to provide its "autocommit" feature.
- Typically, all INSERT/UPDATE/DELETE statements as well as
- CREATE/DROP statements have autocommit behavior enabled; SELECT
- constructs do not. Use this option when invoking a SELECT or other
- specific SQL construct where COMMIT is desired (typically when
- calling stored procedures and such), and an explicit
- transaction is not in progress.
+ transaction is not begun on the connection. Note that this
+ is **library level, not DBAPI level autocommit**. The DBAPI
+ connection will remain in a real transaction unless the
+ "AUTOCOMMIT" isolation level is used.
+
+ .. deprecated:: 1.4 The library-level "autocommit" feature is being
+ removed in favor of database driver "autocommit" which is
+ now widely available. See the section :ref:`dbapi_autocommit`.
:param compiled_cache: Available on: Connection.
A dictionary where :class:`.Compiled` objects
diff --git a/lib/sqlalchemy/testing/profiling.py b/lib/sqlalchemy/testing/profiling.py
index c50ce1e15..c7ccf4688 100644
--- a/lib/sqlalchemy/testing/profiling.py
+++ b/lib/sqlalchemy/testing/profiling.py
@@ -23,6 +23,7 @@ import sys
from . import config
from .util import gc_collect
from ..util import cpython
+from ..util import osx
from ..util import win32
@@ -106,6 +107,8 @@ class ProfileStatsFile(object):
if win32:
platform_tokens.append("win")
+ if osx:
+ platform_tokens.append("osx")
platform_tokens.append(
"nativeunicode"
if config.db.dialect.convert_unicode
diff --git a/lib/sqlalchemy/util/__init__.py b/lib/sqlalchemy/util/__init__.py
index b2407ea18..cea9c4f66 100644
--- a/lib/sqlalchemy/util/__init__.py
+++ b/lib/sqlalchemy/util/__init__.py
@@ -60,6 +60,7 @@ from .compat import itertools_filter # noqa
from .compat import itertools_filterfalse # noqa
from .compat import namedtuple # noqa
from .compat import next # noqa
+from .compat import osx # noqa
from .compat import parse_qsl # noqa
from .compat import perf_counter # noqa
from .compat import pickle # noqa
diff --git a/lib/sqlalchemy/util/compat.py b/lib/sqlalchemy/util/compat.py
index 806ef8ae1..88e3c1640 100644
--- a/lib/sqlalchemy/util/compat.py
+++ b/lib/sqlalchemy/util/compat.py
@@ -23,6 +23,7 @@ py2k = sys.version_info < (3, 0)
cpython = platform.python_implementation() == "CPython"
win32 = sys.platform.startswith("win")
+osx = sys.platform.startswith("darwin")
has_refcount_gc = bool(cpython)
diff --git a/test/engine/test_transaction.py b/test/engine/test_transaction.py
index 51f440d88..8981028d2 100644
--- a/test/engine/test_transaction.py
+++ b/test/engine/test_transaction.py
@@ -1346,6 +1346,17 @@ class IsolationLevelTest(fixtures.TestBase):
self._non_default_isolation_level(),
)
+ def test_per_option_engine(self):
+ eng = create_engine(testing.db.url).execution_options(
+ isolation_level=self._non_default_isolation_level()
+ )
+
+ conn = eng.connect()
+ eq_(
+ eng.dialect.get_isolation_level(conn.connection),
+ self._non_default_isolation_level(),
+ )
+
def test_isolation_level_accessors_connection_default(self):
eng = create_engine(testing.db.url)
with eng.connect() as conn:
View Lorry Controller Status