Transactions

When working with databases, it is common to need to group operations in such a way that if one fails, then we can go back to the latest safe state.
This solution is described in the transaction paradigm, and is implemented by most database engines as it is necessary to meet ACID properties (Atomicity, Consistency, Isolation, Durability) [^ACID]

With this in mind, we present the following example:

We have two accounts (each represented by a name and an amount of money).

  1. db = get_bank_db
  3. create_account db, "John", amount: 100
  4. create_account db, "Sarah", amount: 100

In one moment a transfer is made from one account to the other. For example, John transfers $50 to Sarah

We have two accounts (each represented by a name and an amount of money).

  1. deposit db, "Sarah", 50
  2. withdraw db, "John", 50

It is important to have in mind that if one of the operations fails then the final state would be inconsistent. So we need to execute the two operations (deposit and withdraw) as one operation. And if an error occurs then we would like to go back in time as if that one operation was never executed.

  1. db = get_bank_db
  3. create_account db, "John", amount: 100
  4. create_account db, "Sarah", amount: 100
  6. db.transaction do |tx|
  7.   cnn = tx.connection
  9.   transfer_amount = 1000
  10.   deposit cnn, "Sarah", transfer_amount
  11.   withdraw cnn, "John", transfer_amount
  12. end

In the above example, we start a transaction simply by calling the method Database#transaction (how we get the database object is encapsulated in the method get_bank_db and is out of the scope of this document).
The block is the body of the transaction. When the block gets executed (without any error) then an implicit commit is finally executed to persist the changes in the database.
If an exception is raised by one of the operations, then an implicit rollback is executed, bringing the database to the state before the transaction started.

Exception handling and rolling back

As we mentioned early, an implicit rollback gets executed when an exception is raised, and it’s worth mentioning that the exception may be rescued by us.

  1. db = get_bank_db
  3. create_account db, "John", amount: 100
  4. create_account db, "Sarah", amount: 100
  6. begin
  7.   db.transaction do |tx|
  8.     cnn = tx.connection
  10.     transfer_amount = 1000
  11.     deposit(cnn, "Sarah", transfer_amount)
  12.     # John does not have enough money in his account!
  13.     withdraw(cnn, "John", transfer_amount)
  14.   end
  15. rescue ex
  16.   puts "Transfer has been rolled back due to: #{ex}"
  17. end

We may also raise an exception in the body of the transaction:

  1. db = get_bank_db
  3. create_account db, "John", amount: 100
  4. create_account db, "Sarah", amount: 100
  6. begin
  7.   db.transaction do |tx|
  8.     cnn = tx.connection
  10.     transfer_amount = 50
  11.     deposit(cnn, "Sarah", transfer_amount)
  12.     withdraw(cnn, "John", transfer_amount)
  13.     raise Exception.new "Because ..."
  14.   end
  15. rescue ex
  16.   puts "Transfer has been rolled back due to: #{ex}"
  17. end

As the previous example, the exception cause the transaction to rollback and then is rescued by us.

There is one exception with a different behaviour. If a DB::Rollback is raised within the block, the implicit rollback will happen, but the exception will not be raised outside the block.

  1. db = get_bank_db
  3. create_account db, "John", amount: 100
  4. create_account db, "Sarah", amount: 100
  6. begin
  7.   db.transaction do |tx|
  8.     cnn = tx.connection
  10.     transfer_amount = 50
  11.     deposit(cnn, "Sarah", transfer_amount)
  12.     withdraw(cnn, "John", transfer_amount)
  14.     # rollback exception
  15.     raise DB::Rollback.new
  16.   end
  17. rescue ex
  18.   # ex is never a DB::Rollback
  19. end

Explicit commit and rollback

In all the previous examples, the rolling back is implicit, but we can also tell the transaction to rollback:

  1. db = get_bank_db
  3. create_account db, "John", amount: 100
  4. create_account db, "Sarah", amount: 100
  6. begin
  7.   db.transaction do |tx|
  8.     cnn = tx.connection
  10.     transfer_amount = 50
  11.     deposit(cnn, "Sarah", transfer_amount)
  12.     withdraw(cnn, "John", transfer_amount)
  14.     tx.rollback
  16.     puts "Rolling Back the changes!"
  17.   end
  18. rescue ex
  19.   # Notice that no exception is used in this case.
  20. end

And we can also use the commit method:

  1. db = get_bank_db
  3. db.transaction do |tx|
  4.   cnn = tx.connection
  6.   transfer_amount = 50
  7.   deposit(cnn, "Sarah", transfer_amount)
  8.   withdraw(cnn, "John", transfer_amount)
  10.   tx.commit
  11. end

NOTE: After commit or rollback are used, the transaction is no longer usable. The connection is still open but any statement will be performed outside the context of the terminated transaction.

Nested transactions

As the name suggests, a nested transaction is a transaction created inside the scope of another transaction. Here is an example:

  1. db = get_bank_db
  3. create_account db, "John", amount: 100
  4. create_account db, "Sarah", amount: 100
  5. create_account db, "Jack", amount: 0
  7. begin
  8.   db.transaction do |outer_tx|
  9.     outer_cnn = outer_tx.connection
  11.     transfer_amount = 50
  12.     deposit(outer_cnn, "Sarah", transfer_amount)
  13.     withdraw(outer_cnn, "John", transfer_amount)
  15.     outer_tx.transaction do |inner_tx|
  16.       inner_cnn = inner_tx.connection
  18.       # John => 50 (pending commit)
  19.       # Sarah => 150 (pending commit)
  20.       # Jack => 0
  22.       another_transfer_amount = 150
  23.       deposit(inner_cnn, "Jack", another_transfer_amount)
  24.       withdraw(inner_cnn, "Sarah", another_transfer_amount)
  25.     end
  26.   end
  27. rescue ex
  28.   puts "Exception raised due to: #{ex}"
  29. end

Some observations from the above example:
the inner_tx works with the values updated although the outer_tx is pending the commit.
The connection used by outer_tx and inner_tx is the same connection. This is because the inner_tx inherits the connection from the outer_tx when created.

Rollback nested transactions

As we’ve already seen, a rollback may be fired at any time (by an exception or by sending the message rollback explicitly)

So let’s present an example with a rollback fired by an exception placed at the outer-transaction:

  1. db = get_bank_db
  3. create_account db, "John", amount: 100
  4. create_account db, "Sarah", amount: 100
  5. create_account db, "Jack", amount: 0
  7. begin
  8.   db.transaction do |outer_tx|
  9.     outer_cnn = outer_tx.connection
  11.     transfer_amount = 50
  12.     deposit(outer_cnn, "Sarah", transfer_amount)
  13.     withdraw(outer_cnn, "John", transfer_amount)
  15.     outer_tx.transaction do |inner_tx|
  16.       inner_cnn = inner_tx.connection
  18.       # John => 50 (pending commit)
  19.       # Sarah => 150 (pending commit)
  20.       # Jack => 0
  22.       another_transfer_amount = 150
  23.       deposit(inner_cnn, "Jack", another_transfer_amount)
  24.       withdraw(inner_cnn, "Sarah", another_transfer_amount)
  25.     end
  27.     raise Exception.new("Rollback all the things!")
  28.   end
  29. rescue ex
  30.   puts "Exception raised due to: #{ex}"
  31. end

The rollback place in the outer_tx block, rolled back all the changes including the ones in the inner_tx block (the same happens if we use an explicit rollback).

If the rollback is fired by an exception at the inner_tx block all the changes including the ones in the outer_tx are rollbacked.

  1. db = get_bank_db
  3. create_account db, "John", amount: 100
  4. create_account db, "Sarah", amount: 100
  5. create_account db, "Jack", amount: 0
  7. begin
  8.   db.transaction do |outer_tx|
  9.     outer_cnn = outer_tx.connection
  11.     transfer_amount = 50
  12.     deposit(outer_cnn, "Sarah", transfer_amount)
  13.     withdraw(outer_cnn, "John", transfer_amount)
  15.     outer_tx.transaction do |inner_tx|
  16.       inner_cnn = inner_tx.connection
  18.       # John => 50 (pending commit)
  19.       # Sarah => 150 (pending commit)
  20.       # Jack => 0
  22.       another_transfer_amount = 150
  23.       deposit(inner_cnn, "Jack", another_transfer_amount)
  24.       withdraw(inner_cnn, "Sarah", another_transfer_amount)
  26.       raise Exception.new("Rollback all the things!")
  27.     end
  28.   end
  29. rescue ex
  30.   puts "Exception raised due to: #{ex}"
  31. end

There is a way to rollback the changes in the inner-transaction but keep the ones in the outer-transaction. Use rollback in the inner_tx object. This will rollback only then inner-transaction. Here is the example:

  1. db = get_bank_db
  3. create_account db, "John", amount: 100
  4. create_account db, "Sarah", amount: 100
  5. create_account db, "Jack", amount: 0
  7. begin
  8.   db.transaction do |outer_tx|
  9.     outer_cnn = outer_tx.connection
  11.     transfer_amount = 50
  12.     deposit(outer_cnn, "Sarah", transfer_amount)
  13.     withdraw(outer_cnn, "John", transfer_amount)
  15.     outer_tx.transaction do |inner_tx|
  16.       inner_cnn = inner_tx.connection
  18.       # John => 50 (pending commit)
  19.       # Sarah => 150 (pending commit)
  20.       # Jack => 0
  22.       another_transfer_amount = 150
  23.       deposit(inner_cnn, "Jack", another_transfer_amount)
  24.       withdraw(inner_cnn, "Sarah", another_transfer_amount)
  26.       inner_tx.rollback
  27.     end
  28.   end
  29. rescue ex
  30.   puts "Exception raised due to: #{ex}"
  31. end

The same happens if a DB::Rollback exception is raised in the inner-transaction block.

  1. db = get_bank_db
  3. create_account db, "John", amount: 100
  4. create_account db, "Sarah", amount: 100
  5. create_account db, "Jack", amount: 0
  7. begin
  8.   db.transaction do |outer_tx|
  9.     outer_cnn = outer_tx.connection
  11.     transfer_amount = 50
  12.     deposit(outer_cnn, "Sarah", transfer_amount)
  13.     withdraw(outer_cnn, "John", transfer_amount)
  15.     outer_tx.transaction do |inner_tx|
  16.       inner_cnn = inner_tx.connection
  18.       # John => 50 (pending commit)
  19.       # Sarah => 150 (pending commit)
  20.       # Jack => 0
  22.       another_transfer_amount = 150
  23.       deposit(inner_cnn, "Jack", another_transfer_amount)
  24.       withdraw(inner_cnn, "Sarah", another_transfer_amount)
  26.       # Rollback exception
  27.       raise DB::Rollback.new
  28.     end
  29.   end
  30. rescue ex
  31.   puts "Exception raised due to: #{ex}"
  32. end

[^ACID]: Theo Haerder and Andreas Reuter. 1983. Principles of transaction-oriented database recovery. ACM Comput. Surv. 15, 4 (December 1983), 287-317. DOI=http://dx.doi.org/10.1145/289.291