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

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

  1. db = get_bank_db
  2. create_account db, "John", amount: 100
  3. create_account db, "Sarah", amount: 100
  4. begin
  5. db.transaction do |tx|
  6. cnn = tx.connection
  7. transfer_amount = 50
  8. deposit(cnn, "Sarah", transfer_amount)
  9. withdraw(cnn, "John", transfer_amount)
  10. raise Exception.new "Because ..."
  11. end
  12. rescue ex
  13. puts "Transfer has been rolled back due to: #{ex}"
  14. 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
  2. create_account db, "John", amount: 100
  3. create_account db, "Sarah", amount: 100
  4. begin
  5. db.transaction do |tx|
  6. cnn = tx.connection
  7. transfer_amount = 50
  8. deposit(cnn, "Sarah", transfer_amount)
  9. withdraw(cnn, "John", transfer_amount)
  10. # rollback exception
  11. raise DB::Rollback.new
  12. end
  13. rescue ex
  14. # ex is never a DB::Rollback
  15. 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
  2. create_account db, "John", amount: 100
  3. create_account db, "Sarah", amount: 100
  4. begin
  5. db.transaction do |tx|
  6. cnn = tx.connection
  7. transfer_amount = 50
  8. deposit(cnn, "Sarah", transfer_amount)
  9. withdraw(cnn, "John", transfer_amount)
  10. tx.rollback
  11. puts "Rolling Back the changes!"
  12. end
  13. rescue ex
  14. # Notice that no exception is used in this case.
  15. end

And we can also use the commit method:

  1. db = get_bank_db
  2. db.transaction do |tx|
  3. cnn = tx.connection
  4. transfer_amount = 50
  5. deposit(cnn, "Sarah", transfer_amount)
  6. withdraw(cnn, "John", transfer_amount)
  7. tx.commit
  8. 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
  2. create_account db, "John", amount: 100
  3. create_account db, "Sarah", amount: 100
  4. create_account db, "Jack", amount: 0
  5. begin
  6. db.transaction do |outer_tx|
  7. outer_cnn = outer_tx.connection
  8. transfer_amount = 50
  9. deposit(outer_cnn, "Sarah", transfer_amount)
  10. withdraw(outer_cnn, "John", transfer_amount)
  11. outer_tx.transaction do |inner_tx|
  12. inner_cnn = inner_tx.connection
  13. # John => 50 (pending commit)
  14. # Sarah => 150 (pending commit)
  15. # Jack => 0
  16. another_transfer_amount = 150
  17. deposit(inner_cnn, "Jack", another_transfer_amount)
  18. withdraw(inner_cnn, "Sarah", another_transfer_amount)
  19. end
  20. end
  21. rescue ex
  22. puts "Exception raised due to: #{ex}"
  23. 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
  2. create_account db, "John", amount: 100
  3. create_account db, "Sarah", amount: 100
  4. create_account db, "Jack", amount: 0
  5. begin
  6. db.transaction do |outer_tx|
  7. outer_cnn = outer_tx.connection
  8. transfer_amount = 50
  9. deposit(outer_cnn, "Sarah", transfer_amount)
  10. withdraw(outer_cnn, "John", transfer_amount)
  11. outer_tx.transaction do |inner_tx|
  12. inner_cnn = inner_tx.connection
  13. # John => 50 (pending commit)
  14. # Sarah => 150 (pending commit)
  15. # Jack => 0
  16. another_transfer_amount = 150
  17. deposit(inner_cnn, "Jack", another_transfer_amount)
  18. withdraw(inner_cnn, "Sarah", another_transfer_amount)
  19. end
  20. raise Exception.new("Rollback all the things!")
  21. end
  22. rescue ex
  23. puts "Exception raised due to: #{ex}"
  24. 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
  2. create_account db, "John", amount: 100
  3. create_account db, "Sarah", amount: 100
  4. create_account db, "Jack", amount: 0
  5. begin
  6. db.transaction do |outer_tx|
  7. outer_cnn = outer_tx.connection
  8. transfer_amount = 50
  9. deposit(outer_cnn, "Sarah", transfer_amount)
  10. withdraw(outer_cnn, "John", transfer_amount)
  11. outer_tx.transaction do |inner_tx|
  12. inner_cnn = inner_tx.connection
  13. # John => 50 (pending commit)
  14. # Sarah => 150 (pending commit)
  15. # Jack => 0
  16. another_transfer_amount = 150
  17. deposit(inner_cnn, "Jack", another_transfer_amount)
  18. withdraw(inner_cnn, "Sarah", another_transfer_amount)
  19. raise Exception.new("Rollback all the things!")
  20. end
  21. end
  22. rescue ex
  23. puts "Exception raised due to: #{ex}"
  24. 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 the inner-transaction. Here is the example:

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

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

  1. db = get_bank_db
  2. create_account db, "John", amount: 100
  3. create_account db, "Sarah", amount: 100
  4. create_account db, "Jack", amount: 0
  5. begin
  6. db.transaction do |outer_tx|
  7. outer_cnn = outer_tx.connection
  8. transfer_amount = 50
  9. deposit(outer_cnn, "Sarah", transfer_amount)
  10. withdraw(outer_cnn, "John", transfer_amount)
  11. outer_tx.transaction do |inner_tx|
  12. inner_cnn = inner_tx.connection
  13. # John => 50 (pending commit)
  14. # Sarah => 150 (pending commit)
  15. # Jack => 0
  16. another_transfer_amount = 150
  17. deposit(inner_cnn, "Jack", another_transfer_amount)
  18. withdraw(inner_cnn, "Sarah", another_transfer_amount)
  19. # Rollback exception
  20. raise DB::Rollback.new
  21. end
  22. end
  23. rescue ex
  24. puts "Exception raised due to: #{ex}"
  25. 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