wip3296

3 files changed, 162 insertions, 8 deletions

diff --git a/doc/build/orm/session_transaction.rst b/doc/build/orm/session_transaction.rst
index ce5757dd0..5ad02d217 100644
--- a/doc/build/orm/session_transaction.rst
+++ b/doc/build/orm/session_transaction.rst
@@ -271,6 +271,133 @@ transactions set the flag ``twophase=True`` on the session::
     # before committing both transactions
     session.commit()
 
+
+.. _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.
+
+SQLAlchemy's dialects support settable isolation modes on a per-:class:`.Engine`
+or per-:class:`.Connection` basis, using flags at both the
+:func:`.create_engine` level as well as at the :meth:`.Connection.execution_options`
+level.
+
+When using the ORM :class:`.Session`, it acts as a *facade* for engines and
+connections, but does not expose transaction isolation directly.  So in
+order to affect transaction isolation level, we need to act upon the
+:class:`.Engine` or :class:`.Connection` as appropriate.
+
+.. seealso::
+
+    :ref:`Postgresql Isolation Level <postgresql_isolation_level>_`
+
+    :ref:`MySQL Isolation Level <mysql_isolation_level>_`
+
+Setting Isolation Engine-Wide
+~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+
+To set up a :class:`.Session` or :class:`.sessionmaker` with a specific
+isolation level globally, use the :paramref:`.create_engine.isolation_level`
+parameter::
+
+    from sqlalchemy import create_engine
+    from sqlalchemy.orm import sessionmaker
+
+    eng = create_engine(
+        "postgresql://scott:tiger@localhost/test",
+        isolation_level='REPEATABLE_READ')
+
+    maker = sessionmaker(bind=eng)
+
+    session = maker()
+
+
+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.execution_options` method
+in order to produce a copy of the original :class:`.Engine` that will
+add this option::
+
+    session = maker(
+        bind=engine.execution_options(isolation_level='SERIALIZABLE'))
+
+For the case where the :class:`.Session` or :class:`.sessionmaker` is
+configured with multiple "binds", we can either re-specify the ``binds``
+argument fully, or if we want to only replace specific binds, we
+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'))
+
+We can also use the individual transaction method that follows.
+
+Setting Isolation for Individual Transactions
+~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+
+A key caveat regarding isolation level is that the setting cannot be
+safely modified on a :class:`.Connection` where a transaction has already
+started.  Databases cannot change the isolation level of a transaction
+in progress, and to accommodate this, drivers and some SQLAlchemy dialects
+have inconsitent approaches to this; the psycopg2 DBAPI will implicitly
+roll back any existing transaction, while the MySQLdb dialect in SQLAlchemy
+emits an implicit 'COMMIT'.  Therefore SQLAlchemy emits a warning if this
+option is set when a transaction is already in play, and the :class:`.Session`
+does not provide for us a :class:`.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`
+using the :meth:`.Session.connection` method::
+
+    from sqlalchemy.orm import Session
+
+    sess = Session(bind=engine)
+    sess.connection(execution_options={'isolation_level': 'SERIALIZABLE'})
+
+    # work with session
+
+    # commit 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_sesssionmaker()
+
+    # 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()
+
+.. _versionadded:: 0.9.9 Added the
+    :paramref:`.Session.connection.execution_options`
+    parameter to :meth:`.Session.connection`.
+
 .. _session_external_transaction:
 
 Joining a Session into an External Transaction (such as for test suites)
diff --git a/lib/sqlalchemy/engine/default.py b/lib/sqlalchemy/engine/default.py
index f6c2263b3..6e49fc783 100644
--- a/lib/sqlalchemy/engine/default.py
+++ b/lib/sqlalchemy/engine/default.py
@@ -395,6 +395,12 @@ class DefaultDialect(interfaces.Dialect):
             self._set_connection_isolation(connection, opts['isolation_level'])
 
     def _set_connection_isolation(self, connection, level):
+        if connection.in_transaction:
+            util.warn(
+                "Connection is already established with a Transaction; "
+                "setting isolation_level may implicitly rollback or commit "
+                "the existing transaction, or have no effect until "
+                "next transaction")
         self.set_isolation_level(connection.connection, level)
         connection.connection._connection_record.\
             finalize_callback.append(self.reset_isolation_level)
diff --git a/lib/sqlalchemy/orm/session.py b/lib/sqlalchemy/orm/session.py
index 0e272dc95..25a0b2711 100644
--- a/lib/sqlalchemy/orm/session.py
+++ b/lib/sqlalchemy/orm/session.py
@@ -226,10 +226,10 @@ class SessionTransaction(object):
     def _is_transaction_boundary(self):
         return self.nested or not self._parent
 
-    def connection(self, bindkey, **kwargs):
+    def connection(self, bindkey, execution_options=None, **kwargs):
         self._assert_active()
         bind = self.session.get_bind(bindkey, **kwargs)
-        return self._connection_for_bind(bind)
+        return self._connection_for_bind(bind, execution_options)
 
     def _begin(self, nested=False):
         self._assert_active()
@@ -301,14 +301,18 @@ class SessionTransaction(object):
             self._parent._deleted.update(self._deleted)
             self._parent._key_switches.update(self._key_switches)
 
-    def _connection_for_bind(self, bind):
+    def _connection_for_bind(self, bind, execution_options):
         self._assert_active()
 
         if bind in self._connections:
+            if execution_options:
+                util.warn(
+                    "Connection is already established for the "
+                    "given bind; execution_options ignored")
             return self._connections[bind][0]
 
         if self._parent:
-            conn = self._parent._connection_for_bind(bind)
+            conn = self._parent._connection_for_bind(bind, execution_options)
             if not self.nested:
                 return conn
         else:
@@ -321,6 +325,9 @@ class SessionTransaction(object):
             else:
                 conn = bind.contextual_connect()
 
+        if execution_options:
+            conn = conn.execution_options(**execution_options)
+
         if self.session.twophase and self._parent is None:
             transaction = conn.begin_twophase()
         elif self.nested:
@@ -793,6 +800,7 @@ class Session(_SessionClassMethods):
     def connection(self, mapper=None, clause=None,
                    bind=None,
                    close_with_result=False,
+                   execution_options=None,
                    **kw):
         """Return a :class:`.Connection` object corresponding to this
         :class:`.Session` object's transactional state.
@@ -837,6 +845,14 @@ class Session(_SessionClassMethods):
           configured with ``autocommit=True`` and does not already have a
           transaction in progress.
 
+        :param execution_options: a dictionary of execution options that will
+         be passed to :meth:`.Connection.execution_options`, **when the
+         connection is first procured**.   If the connection is already
+         present within the :class:`.Session`, a warning is emitted and
+         the arguments are ignored.
+
+         .. versionadded:: 0.9.9
+
         :param \**kw:
           Additional keyword arguments are sent to :meth:`get_bind()`,
           allowing additional arguments to be passed to custom
@@ -847,13 +863,18 @@ class Session(_SessionClassMethods):
             bind = self.get_bind(mapper, clause=clause, **kw)
 
         return self._connection_for_bind(bind,
-                                         close_with_result=close_with_result)
+                                         close_with_result=close_with_result,
+                                         execution_options=execution_options)
 
-    def _connection_for_bind(self, engine, **kwargs):
+    def _connection_for_bind(self, engine, execution_options=None, **kw):
         if self.transaction is not None:
-            return self.transaction._connection_for_bind(engine)
+            return self.transaction._connection_for_bind(
+                engine, execution_options)
         else:
-            return engine.contextual_connect(**kwargs)
+            conn = engine.contextual_connect(**kw)
+            if execution_options:
+                conn = conn.execution_options(**execution_options)
+            return conn
 
     def execute(self, clause, params=None, mapper=None, bind=None, **kw):
         """Execute a SQL expression construct or string statement within