- rewrite all the sqlite/pysqlite transaction isolation docs

4 files changed, 172 insertions, 50 deletions

diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index b29e4a87c..374960765 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -101,6 +101,8 @@ all lower case both within SQLAlchemy as well as on the MySQL
 database itself, especially if database reflection features are
 to be used.
 
+.. _mysql_isolation_level:
+
 Transaction Isolation Level
 ---------------------------
 
diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py
index 306f45023..3c8b2d4f7 100644
--- a/lib/sqlalchemy/dialects/sqlite/base.py
+++ b/lib/sqlalchemy/dialects/sqlite/base.py
@@ -47,50 +47,116 @@ construct::
             Column('id', Integer, primary_key=True),
             sqlite_autoincrement=True)
 
-Transaction Isolation Level
----------------------------
 
-:func:`.create_engine` accepts an ``isolation_level`` parameter which results
-in the command ``PRAGMA read_uncommitted <level>`` being invoked for every new
-connection. Valid values for this parameter are ``SERIALIZABLE`` and ``READ
-UNCOMMITTED`` corresponding to a value of 0 and 1, respectively. See the
-section :ref:`pysqlite_serializable` for an important workaround when using
-serializable isolation with Pysqlite.
+.. _sqlite_concurrency:
 
 Database Locking Behavior / Concurrency
 ---------------------------------------
 
-Note that SQLite is not designed for a high level of concurrency. The database
-itself, being a file, is locked completely during write operations and within
-transactions, meaning exactly one connection has exclusive access to the
-database during this period - all other connections will be blocked during
-this time.
+SQLite is not designed for a high level of write concurrency. The database
+itself, being a file, is locked completely during write operations within
+transactions, meaning exactly one "connection" (in reality a file handle)
+has exclusive access to the database during this period - all other
+"connections" will be blocked during this time.
 
 The Python DBAPI specification also calls for a connection model that is
-always in a transaction; there is no BEGIN method, only commit and rollback.
-This implies that a SQLite DBAPI driver would technically allow only
-serialized access to a particular database file at all times. The pysqlite
-driver attempts to ameliorate this by deferring the actual BEGIN statement
-until the first DML (INSERT, UPDATE, or DELETE) is received within a
-transaction. While this breaks serializable isolation, it at least delays the
-exclusive locking inherent in SQLite's design.
-
-SQLAlchemy's default mode of usage with the ORM is known as
-"autocommit=False", which means the moment the :class:`.Session` begins to be
-used, a transaction is begun. As the :class:`.Session` is used, the autoflush
-feature, also on by default, will flush out pending changes to the database
-before each query. The effect of this is that a :class:`.Session` used in its
-default mode will often emit DML early on, long before the transaction is
-actually committed. This again will have the effect of serializing access to
-the SQLite database. If highly concurrent reads are desired against the SQLite
-database, it is advised that the autoflush feature be disabled, and
-potentially even that autocommit be re-enabled, which has the effect of each
-SQL statement and flush committing changes immediately.
-
-For more information on SQLite's lack of concurrency by design, please see
+always in a transaction; there is no ``connection.begin()`` method,
+only ``connection.commit()`` and ``connection.rollback()``, upon which a
+new transaction is to be begun immediately.  This may seem to imply
+that the SQLite driver would in theory allow only a single filehandle on a
+particular database file at any time; however, there are several
+factors both within SQlite itself as well as within the pysqlite driver
+which loosen this restriction significantly.
+
+However, no matter what locking modes are used, SQLite will still always
+lock the database file once a transaction is started and DML (e.g. INSERT,
+UPDATE, DELETE) has at least been emitted, and this will block
+other transactions at least at the point that they also attempt to emit DML.
+By default, the length of time on this block is very short before it times out
+with an error.
+
+This behavior becomes more critical when used in conjunction with the
+SQLAlchemy ORM.  SQLAlchemy's :class:`.Session` object by default runs
+within a transaction, and with its autoflush model, may emit DML preceding
+any SELECT statement.   This may lead to a SQLite database that locks
+more quickly than is expected.   The locking mode of SQLite and the pysqlite
+driver can be manipulated to some degree, however it should be noted that
+achieving a high degree of write-concurrency with SQLite is a losing battle.
+
+For more information on SQLite's lack of write concurrency by design, please
+see
 `Situations Where Another RDBMS May Work Better - High Concurrency
 <http://www.sqlite.org/whentouse.html>`_ near the bottom of the page.
 
+The following subsections introduce areas that are impacted by SQLite's
+file-based architecture and additionally will usually require workarounds to
+work when using the pysqlite driver.
+
+Transaction Isolation Level
+===========================
+
+SQLite supports "transaction isolation" in a non-standard way, along two
+axes.  One is that of the `PRAGMA read_uncommitted <http://www.sqlite.org/pragma.html#pragma_read_uncommitted>`_
+instruction.   This setting can essentially switch SQLite between its
+default mode of ``SERIALIZABLE`` isolation, and a "dirty read" isolation
+mode normally referred to as ``READ UNCOMMITTED``.
+
+SQLAlchemy ties into this PRAGMA statement using the
+:paramref:`.create_engine.isolation_level` parameter of :func:`.create_engine`.
+Valid values for this parameter when used with SQLite are ``"SERIALIZABLE"``
+and ``"READ UNCOMMITTED"`` corresponding to a value of 0 and 1, respectively.
+SQLite defaults to ``SERIALIZABLE``, however its behavior is impacted by
+the pysqlite driver's default behavior.
+
+The other axis along which SQLite's transactional locking is impacted is
+via the nature of the ``BEGIN`` statement used.   The three varieties
+are "deferred", "immediate", and "exclusive", as described at
+`BEGIN TRANSACTION <http://sqlite.org/lang_transaction.html>`_.   A straight
+``BEGIN`` statement uses the "deferred" mode, where the the database file is
+not locked until the first read or write operation, and read access remains
+open to other transactions until the first write operation.  But again,
+it is critical to note that the pysqlite driver interferes with this behavior
+by *not even emitting BEGIN* until the first write operation.
+
+.. warning::
+
+    SQLite's transactional scope is impacted by unresolved
+    issues in the pysqlite driver, which defers BEGIN statements to a greater
+    degree than is often feasible. See the section :ref:`pysqlite_serializable`
+    for techniques to work around this behavior.
+
+SAVEPOINT Support
+=================
+
+SQLite supports SAVEPOINTs, which only function once a transaction is
+begun.   SQLAlchemy's SAVEPOINT support is available using the
+:meth:`.Connection.begin_nested` method at the Core level, and
+:meth:`.Session.begin_nested` at the ORM level.   However, SAVEPOINTs
+won't work at all with pysqlite unless workarounds are taken.
+
+.. warning::
+
+    SQLite's SAVEPOINT feature is impacted by unresolved
+    issues in the pysqlite driver, which defers BEGIN statements to a greater
+    degree than is often feasible. See the section :ref:`pysqlite_serializable`
+    for techniques to work around this behavior.
+
+Transactional DDL
+=================
+
+The SQLite database supports transactional :term:`DDL` as well.
+In this case, the pysqlite driver is not only failing to start transactions,
+it also is ending any existing transction when DDL is detected, so again,
+workarounds are required.
+
+.. warning::
+
+    SQLite's transactional DDL is impacted by unresolved issues
+    in the pysqlite driver, which fails to emit BEGIN and additionally
+    forces a COMMIT to cancel any transaction when DDL is encountered.
+    See the section :ref:`pysqlite_serializable`
+    for techniques to work around this behavior.
+
 .. _sqlite_foreign_keys:
 
 Foreign Key Support
diff --git a/lib/sqlalchemy/dialects/sqlite/pysqlite.py b/lib/sqlalchemy/dialects/sqlite/pysqlite.py
index c67333283..62c19d145 100644
--- a/lib/sqlalchemy/dialects/sqlite/pysqlite.py
+++ b/lib/sqlalchemy/dialects/sqlite/pysqlite.py
@@ -200,30 +200,68 @@ is passed containing non-ASCII characters.
 
 .. _pysqlite_serializable:
 
-Serializable Transaction Isolation
-----------------------------------
-
-The pysqlite DBAPI driver has a long-standing bug in which transactional
-state is not begun until the first DML statement, that is INSERT, UPDATE
-or DELETE, is emitted.  A SELECT statement will not cause transactional
-state to begin.   While this mode of usage is fine for typical situations
-and has the advantage that the SQLite database file is not prematurely
-locked, it breaks serializable transaction isolation, which requires
-that the database file be locked upon any SQL being emitted.
-
-To work around this issue, the ``BEGIN`` keyword can be emitted
-at the start of each transaction.   The following recipe establishes
-a :meth:`.ConnectionEvents.begin` handler to achieve this::
+Serializable isolation / Savepoints / Transactional DDL
+-------------------------------------------------------
+
+In the section :ref:`sqlite_concurrency`, we refer to the pysqlite
+driver's assortment of issues that prevent several features of SQLite
+from working correctly.  The pysqlite DBAPI driver has several
+long-standing bugs which impact the correctness of its transactional
+behavior.   In its default mode of operation, SQLite features such as
+SERIALIZABLE isolation, transactional DDL, and SAVEPOINT support are
+non-functional, and in order to use these features, workarounds must
+be taken.
+
+The issue is essentially that the driver attempts to second-guess the user's
+intent, failing to start transactions and sometimes ending them prematurely, in
+an effort to minimize the SQLite databases's file locking behavior, even
+though SQLite itself uses "shared" locks for read-only activities.
+
+SQLAlchemy chooses to not alter this behavior by default, as it is the
+long-expected behavior of the pysqlite driver; if and when the pysqlite
+driver attempts to repair these issues, that will be more of a driver towards
+defaults for SQLAlchemy.
+
+The good news is that with a few events, we can implement transactional
+support fully, by disabling pysqlite's feature entirely and emitting BEGIN
+ourselves. This is achieved using two event listeners::
 
     from sqlalchemy import create_engine, event
 
-    engine = create_engine("sqlite:///myfile.db",
-                           isolation_level='SERIALIZABLE')
+    engine = create_engine("sqlite:///myfile.db")
+
+    @event.listens_for(engine, "connect")
+    def do_connect(dbapi_connection, connection_record):
+        # disable pysqlite's emitting of the BEGIN statement entirely.
+        # also stops it from emitting COMMIT before any DDL.
+        dbapi_connection.isolation_level = None
 
     @event.listens_for(engine, "begin")
     def do_begin(conn):
+        # emit our own BEGIN
         conn.execute("BEGIN")
 
+Above, we intercept a new pysqlite connection and disable any transactional
+integration.   Then, at the point at which SQLAlchemy knows that transaction
+scope is to begin, we emit ``"BEGIN"`` ourselves.
+
+When we take control of ``"BEGIN"``, we can also control directly SQLite's
+locking modes, introduced at `BEGIN TRANSACTION <http://sqlite.org/lang_transaction.html>`_,
+by adding the desired locking mode to our ``"BEGIN"``::
+
+    @event.listens_for(engine, "begin")
+    def do_begin(conn):
+        conn.execute("BEGIN EXCLUSIVE")
+
+.. seealso::
+
+    `BEGIN TRANSACTION <http://sqlite.org/lang_transaction.html>`_ - on the SQLite site
+
+    `sqlite3 SELECT does not BEGIN a transaction <http://bugs.python.org/issue9924>`_ - on the Python bug tracker
+
+    `sqlite3 module breaks transactions and potentially corrupts data <http://bugs.python.org/issue10740>`_ - on the Python bug tracker
+
+
 """
 
 from sqlalchemy.dialects.sqlite.base import SQLiteDialect, DATETIME, DATE
diff --git a/lib/sqlalchemy/engine/__init__.py b/lib/sqlalchemy/engine/__init__.py
index 9c9e03821..68145f5cd 100644
--- a/lib/sqlalchemy/engine/__init__.py
+++ b/lib/sqlalchemy/engine/__init__.py
@@ -248,6 +248,22 @@ def create_engine(*args, **kwargs):
         Microsoft SQL Server.   Set this to ``False`` to disable
         the automatic usage of RETURNING.
 
+    :param isolation_level: this string parameter is interpreted by various
+        dialects in order to affect the transaction isolation level of the
+        database connection.   The parameter essentially accepts some subset of
+        these string arguments: ``"SERIALIZABLE"``, ``"REPEATABLE_READ"``,
+        ``"READ_COMMITTED"``, ``"READ_UNCOMMITTED"`` and ``"AUTOCOMMIT"``.
+        Behavior here varies per backend, and
+        individual dialects should be consulted directly.
+
+        .. seealso::
+
+            :ref:`SQLite Concurrency <sqlite_concurrency>`
+
+            :ref:`Postgresql Transaction Isolation <postgresql_isolation_level>`
+
+            :ref:`MySQL Transaction Isolation <mysql_isolation_level>`
+
     :param label_length=None: optional integer value which limits
         the size of dynamically generated column labels to that many
         characters. If less than 6, labels are generated as