Support for specifying transaction isolation level

If your database supports setting the isolation level for a transaction,
you can set it like so:

  Post.transaction(isolation: :serializable) do
    # ...
  end

Valid isolation levels are:

* `:read_uncommitted`
* `:read_committed`
* `:repeatable_read`
* `:serializable`

You should consult the documentation for your database to understand the
semantics of these different levels:

* http://www.postgresql.org/docs/9.1/static/transaction-iso.html
* https://dev.mysql.com/doc/refman/5.0/en/set-transaction.html

An `ActiveRecord::TransactionIsolationError` will be raised if:

* The adapter does not support setting the isolation level
* You are joining an existing open transaction
* You are creating a nested (savepoint) transaction

The mysql, mysql2 and postgresql adapters support setting the
transaction isolation level. However, support is disabled for mysql
versions below 5, because they are affected by a bug
(http://bugs.mysql.com/bug.php?id=39170) which means the isolation level
gets persisted outside the transaction.

7 files changed, 107 insertions, 15 deletions

diff --git a/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb b/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb
index 32e3c7f5d8..793f58d4d3 100644
--- a/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb
+++ b/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb
@@ -155,10 +155,47 @@ module ActiveRecord
       #       # active_record_1 now automatically released
       #     end  # RELEASE SAVEPOINT active_record_1  <--- BOOM! database error!
       #   end
+      #
+      # == Transaction isolation
+      #
+      # If your database supports setting the isolation level for a transaction, you can set
+      # it like so:
+      #
+      #   Post.transaction(isolation: :serializable) do
+      #     # ...
+      #   end
+      #
+      # Valid isolation levels are:
+      #
+      # * <tt>:read_uncommitted</tt>
+      # * <tt>:read_committed</tt>
+      # * <tt>:repeatable_read</tt>
+      # * <tt>:serializable</tt>
+      #
+      # You should consult the documentation for your database to understand the
+      # semantics of these different levels:
+      #
+      # * http://www.postgresql.org/docs/9.1/static/transaction-iso.html
+      # * https://dev.mysql.com/doc/refman/5.0/en/set-transaction.html
+      #
+      # An <tt>ActiveRecord::TransactionIsolationError</tt> will be raised if:
+      #
+      # * The adapter does not support setting the isolation level
+      # * You are joining an existing open transaction
+      # * You are creating a nested (savepoint) transaction
+      #
+      # The mysql, mysql2 and postgresql adapters support setting the transaction
+      # isolation level. However, support is disabled for mysql versions below 5,
+      # because they are affected by a bug[http://bugs.mysql.com/bug.php?id=39170]
+      # which means the isolation level gets persisted outside the transaction.
       def transaction(options = {})
-        options.assert_valid_keys :requires_new, :joinable
+        options.assert_valid_keys :requires_new, :joinable, :isolation
 
         if !options[:requires_new] && current_transaction.joinable?
+          if options[:isolation]
+            raise ActiveRecord::TransactionIsolationError, "cannot set isolation when joining a transaction"
+          end
+
           yield
         else
           within_new_transaction(options) { yield }
@@ -168,10 +205,10 @@ module ActiveRecord
       end
 
       def within_new_transaction(options = {}) #:nodoc:
-        begin_transaction(options)
+        transaction = begin_transaction(options)
         yield
       rescue Exception => error
-        rollback_transaction
+        rollback_transaction if transaction
         raise
       ensure
         begin
@@ -191,9 +228,7 @@ module ActiveRecord
       end
 
       def begin_transaction(options = {}) #:nodoc:
-        @transaction = @transaction.begin
-        @transaction.joinable = options.fetch(:joinable, true)
-        @transaction
+        @transaction = @transaction.begin(options)
       end
 
       def commit_transaction #:nodoc:
@@ -217,6 +252,22 @@ module ActiveRecord
       # Begins the transaction (and turns off auto-committing).
       def begin_db_transaction()    end
 
+      def transaction_isolation_levels
+        {
+          read_uncommitted: "READ UNCOMMITTED",
+          read_committed:   "READ COMMITTED",
+          repeatable_read:  "REPEATABLE READ",
+          serializable:     "SERIALIZABLE"
+        }
+      end
+
+      # Begins the transaction with the isolation level set. Raises an error by
+      # default; adapters that support setting the isolation level should implement
+      # this method.
+      def begin_isolated_db_transaction(isolation)
+        raise ActiveRecord::TransactionIsolationError, "adapter does not support setting transaction isolation"
+      end
+
       # Commits the transaction (and turns on auto-committing).
       def commit_db_transaction()   end
 
diff --git a/activerecord/lib/active_record/connection_adapters/abstract/transaction.rb b/activerecord/lib/active_record/connection_adapters/abstract/transaction.rb
index 2117eae5cb..4cca94e40b 100644
--- a/activerecord/lib/active_record/connection_adapters/abstract/transaction.rb
+++ b/activerecord/lib/active_record/connection_adapters/abstract/transaction.rb
@@ -13,8 +13,8 @@ module ActiveRecord
         0
       end
 
-      def begin
-        RealTransaction.new(connection, self)
+      def begin(options = {})
+        RealTransaction.new(connection, self, options)
       end
 
       def closed?
@@ -38,13 +38,13 @@ module ActiveRecord
       attr_reader :parent, :records
       attr_writer :joinable
 
-      def initialize(connection, parent)
+      def initialize(connection, parent, options = {})
         super connection
 
         @parent    = parent
         @records   = []
         @finishing = false
-        @joinable  = true
+        @joinable  = options.fetch(:joinable, true)
       end
 
       # This state is necesarry so that we correctly handle stuff that might
@@ -66,11 +66,11 @@ module ActiveRecord
         end
       end
 
-      def begin
+      def begin(options = {})
         if finishing?
           parent.begin
         else
-          SavepointTransaction.new(connection, self)
+          SavepointTransaction.new(connection, self, options)
         end
       end
 
@@ -120,9 +120,14 @@ module ActiveRecord
     end
 
     class RealTransaction < OpenTransaction #:nodoc:
-      def initialize(connection, parent)
+      def initialize(connection, parent, options = {})
         super
-        connection.begin_db_transaction
+
+        if options[:isolation]
+          connection.begin_isolated_db_transaction(options[:isolation])
+        else
+          connection.begin_db_transaction
+        end
       end
 
       def perform_rollback
@@ -137,7 +142,11 @@ module ActiveRecord
     end
 
     class SavepointTransaction < OpenTransaction #:nodoc:
-      def initialize(connection, parent)
+      def initialize(connection, parent, options = {})
+        if options[:isolation]
+          raise ActiveRecord::TransactionIsolationError, "cannot set transaction isolation in a nested transaction"
+        end
+
         super
         connection.create_savepoint
       end
diff --git a/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb b/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb
index 3a8fbcf93f..0cb219767b 100644
--- a/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb
+++ b/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb
@@ -167,6 +167,11 @@ module ActiveRecord
         false
       end
 
+      # Does this adapter support setting the isolation level for a transaction?
+      def supports_transaction_isolation?
+        false
+      end
+
       # QUOTING ==================================================
 
       # Returns a bind substitution value given a +column+ and list of current
diff --git a/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb b/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb
index 1126fe7fce..1783b036a2 100644
--- a/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb
+++ b/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb
@@ -169,6 +169,14 @@ module ActiveRecord
         true
       end
 
+      # MySQL 4 technically support transaction isolation, but it is affected by a bug
+      # where the transaction level gets persisted for the whole session:
+      #
+      # http://bugs.mysql.com/bug.php?id=39170
+      def supports_transaction_isolation?
+        version[0] >= 5
+      end
+
       def native_database_types
         NATIVE_DATABASE_TYPES
       end
@@ -269,6 +277,13 @@ module ActiveRecord
         # Transactions aren't supported
       end
 
+      def begin_isolated_db_transaction(isolation)
+        execute "SET TRANSACTION ISOLATION LEVEL #{transaction_isolation_levels.fetch(isolation)}"
+        begin_db_transaction
+      rescue
+        # Transactions aren't supported
+      end
+
       def commit_db_transaction #:nodoc:
         execute "COMMIT"
       rescue
diff --git a/activerecord/lib/active_record/connection_adapters/postgresql/database_statements.rb b/activerecord/lib/active_record/connection_adapters/postgresql/database_statements.rb
index c8437c18cc..553985bd1e 100644
--- a/activerecord/lib/active_record/connection_adapters/postgresql/database_statements.rb
+++ b/activerecord/lib/active_record/connection_adapters/postgresql/database_statements.rb
@@ -205,6 +205,11 @@ module ActiveRecord
           execute "BEGIN"
         end
 
+        def begin_isolated_db_transaction(isolation)
+          begin_db_transaction
+          execute "SET TRANSACTION ISOLATION LEVEL #{transaction_isolation_levels.fetch(isolation)}"
+        end
+
         # Commits a transaction.
         def commit_db_transaction
           execute "COMMIT"
diff --git a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb
index 761052a788..5e35f472c7 100644
--- a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb
+++ b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb
@@ -370,6 +370,10 @@ module ActiveRecord
         true
       end
 
+      def supports_transaction_isolation?
+        true
+      end
+
       class StatementPool < ConnectionAdapters::StatementPool
         def initialize(connection, max)
           super
diff --git a/activerecord/lib/active_record/errors.rb b/activerecord/lib/active_record/errors.rb
index 5f157fde6d..0637dd58b6 100644
--- a/activerecord/lib/active_record/errors.rb
+++ b/activerecord/lib/active_record/errors.rb
@@ -195,4 +195,7 @@ module ActiveRecord
 
   class ImmutableRelation < ActiveRecordError
   end
+
+  class TransactionIsolationError < ActiveRecordError
+  end
 end