TRANSACTIONS 237

	This outputs
	In memory size = 0
	Refreshed size = 1
	The correct approach is to add the child to the parent.
File 5	product = Product.create(:title => "Programming Ruby",
	:date_available => Time.now)
	product.line_items.create
	puts "In memory size = #{product.line_items.size}"
	puts "Refreshed size = #{product.line_items(:refresh).size}"
	This outputs the correct numbers. (It’s also shorter code, so that tells you
	you’re doing it right.)
	In memory size = 1
	Refreshed size = 1

14.7 Transactions

A database transaction groups a series of changes together in such a way that either all the changes are applied or none of the changes are applied. The classic example of the need for transactions (and one used in Active Record’s own documentation) is transferring money between two bank accounts. The basic logic is simple.

account1.deposit(100)

account2.withdraw(100)

However, we have to be careful. What happens if the deposit succeeds but for some reason the withdrawal fails (perhaps the customer is overdrawn)? We’ll have added $100 to the balance in account1 without a corresponding deduction from account2. In effect we’ll have created $100 out of thin air.

Transactions to the rescue. A transaction is something like the Three Musketeers with their motto “All for one and one for all.” Within the scope of a transaction, either every SQL statement succeeds or they all have no effect. Putting that another way, if any statement fails, the entire transaction has no effect on the database.10

In Active Record we use the transaction( ) method to execute a block in the context of a particular database transaction. At the end of the block, the

10Transactions are actually more subtle than that. They exhibit the so-called ACID properties: they’re Atomic, they ensure Consistency, they work in Isolation, and their effects are Durable (they are made permanent when the transaction is committed). It’s worth finding a good database book and reading up on transactions if you plan to take a database application live.

Prepared exclusively for Rida Al Barazi

Report erratum

TRANSACTIONS 238

transaction is committed, updating the database, unless an exception is raised within the block, in which case all changes are rolled back and the database is left untouched. Because transactions exist in the context of a database connection, we have to invoke them with an Active Record class as a receiver. Thus we could write

Account.transaction do account1.deposit(100) account2.withdraw(100)

end

Let’s experiment with transactions. We’ll start by creating a new database table. Because we’re using the MySQL database, we have to ask for a table stored using the InnoDB storage engine, as it supports transactions.

File 6	create table accounts (
	id	int	not null auto_increment,
	number	varchar(10)	not null,
	balance	decimal(10,2) default 0.0,
	primary key (id)
	) type=InnoDB;
	Next, we’ll define a simple bank account class. This class defines instance
	methods to deposit money to and withdraw money from the account (which
	delegate the work to a shared helper method). It also provides some basic
	validation—for this particular type of account, the balance can never be
	negative.
File 16	class Account < ActiveRecord::Base

def withdraw(amount) adjust_balance_and_save(-amount)

end

def deposit(amount) adjust_balance_and_save(amount)

end

private

def adjust_balance_and_save(amount)

self.balance += amount save!

end

def validate

errors.add(:balance, "is negative") if balance < 0 end

end

Let’s look at the helper method, adjust_balance_and_save( ). The first line simply updates the balance field. The method then attempts to save the model using save!. (Remember that save! raises an exception if the object cannot be saved—we use the exception to signal to the transaction that something has gone wrong.)

Prepared exclusively for Rida Al Barazi

Report erratum

TRANSACTIONS 239

So now let’s write the code to transfer money between two accounts. It’s pretty straightforward.

File 16	peter = Account.create(:balance => 100, :number => "12345")
	paul = Account.create(:balance => 200, :number => "54321")
File 16	Account.transaction do
	paul.deposit(10)
	peter.withdraw(10)
	end

We check the database, and, sure enough, the money got transfered.

mysql>			select *		from accounts;
+	----	+	--------	+	---------	+
| id |			number |		balance |
+----		+--------		+---------		+
|	5	|	12345	|	90.00	|
|	6	|	54321	|	210.00	|
+----		+--------		+---------		+

	Now let’s get radical. If we start again but this time try to transfer $350,
	we’ll run Peter into the red, which isn’t allowed by the validation rule. Let’s
	try it.
File 16	peter = Account.create(:balance => 100, :number => "12345")
	paul = Account.create(:balance => 200, :number => "54321")
File 16	Account.transaction do
	paul.deposit(350)
	peter.withdraw(350)
	end
	When we run this, we get an exception reported on the console.
	validations.rb:652:in ‘save!': ActiveRecord::RecordInvalid
	from transactions.rb:36:in ‘adjust_balance_and_save'

from transactions.rb:25:in ‘withdraw'

::

from transactions.rb:71

Looking in the database, we can see that the data remains unchanged.

mysql>			select *		from accounts;
+	----	+	--------	+	---------	+
| id |			number |		balance |
+----		+--------		+---------		+
|	7	|	12345	|	100.00	|
|	8	|	54321	|	200.00	|
+----		+--------		+---------		+

However, there’s a trap waiting for you here. The transaction protected the database from becoming inconsistent, but what about our model objects? To see what happened to them, we have to arrange to intercept the exception to allow the program to continue running.

File 16	peter =	Account.create(:balance	=>	100,	:number	=>	"12345")
	paul =	Account.create(:balance	=>	200,	:number	=>	"54321")

Prepared exclusively for Rida Al Barazi

Report erratum

	TRANSACTIONS	240
File 16	begin
	Account.transaction do
	paul.deposit(350)
	peter.withdraw(350)
	end
	rescue
	puts "Transfer aborted"
	end
	puts "Paul has #{paul.balance}"
	puts "Peter has #{peter.balance}"
	What we see is a little surprising.
	Transfer aborted
	Paul has 550.0
	Peter has -250.0
	Although the database was left unscathed, our model objects were updated
	anyway. This is because Active Record wasn’t keeping track of the before
	and after states of the various objects—in fact it couldn’t, because it had
	no easy way of knowing just which models were involved in the transac-
	tions. We can rectify this by listing them explicitly as parameters to the
	transaction( ) method.
File 16	peter = Account.create(:balance => 100, :number => "12345")
	paul = Account.create(:balance => 200, :number => "54321")
File 16	begin
	Account.transaction(peter, paul) do
	paul.deposit(350)
	peter.withdraw(350)
	end
	rescue
	puts "Transfer aborted"
	end
	puts "Paul has #{paul.balance}"
	puts "Peter has #{peter.balance}"
	This time we see the models are unchanged at the end.
	Transfer aborted
	Paul has 200.0
	Peter has 100.0
	We can tidy this code a little by moving the transfer functionality into the
	Account class. Because a transfer involves two separate accounts, and
	isn’t driven by either of them, we’ll make it a class method that takes
	two account objects as parameters. Notice how we can simply call the
	transaction( ) method inside the class method.
File 16	class Account < ActiveRecord::Base
	def self.transfer(from, to, amount)
	transaction(from, to) do

from.withdraw(amount)

to.deposit(amount) end

end end

Prepared exclusively for Rida Al Barazi

Report erratum

TRANSACTIONS 241

	With this method defined, our transfers are a lot tidier.
File 16	peter = Account.create(:balance => 100, :number => "12345")
	paul = Account.create(:balance => 200, :number => "54321")
File 16	Account.transfer(peter, paul, 350) rescue puts "Transfer aborted"
	puts "Paul has #{paul.balance}"
	puts "Peter has #{peter.balance}"
	Transfer aborted
	Paul has 200.0
	Peter has 100.0
	There’s a downside to having the transaction code recover object state
	automatically—you can’t get to any error information added during valida-
	tion. Invalid objects won’t be saved, and the transaction will roll everything
	back, but there’s no easy way of knowing what went wrong.

Built-in Transactions

When we discussed parent and child tables, we said that Active Record takes care of saving all the dependent child rows when you save a parent row. This takes multiple SQL statement executions (one for the parent, and one each for any changed or new children). Clearly this change should be atomic, but until now we haven’t been using transactions when saving these interrelated objects. Have we been negligent?

Fortunately not. Active Record is smart enough to wrap all of the updates and inserts related to a particular save( ) (and also the deletes related to a destroy( )) in a transaction; they either all succeed or no data is written permanently to the database. You need explicit transactions only when you manage multiple SQL statements yourself.

Multidatabase Transactions

How do you go about synchronizing transactions across a number of different databases in Rails?

The current answer is that you can’t. Rails doesn’t support distributed two-phase commits (which is the jargon term for the protocol that lets databases synchronize with each other).

However, you can (almost) simulate the effect by nesting transactions. Remember that transactions are associated with database connections, and connections are associated with models. So, if the accounts table is in one database and users is in another, you could simulate a transaction spanning the two using something such as

Prepared exclusively for Rida Al Barazi

Report erratum

TRANSACTIONS 242

User.transaction(user) do Account.transaction(account) do

account.calculate_fees

user.date_fees_last_calculated = Time.now user.save

account.save end

end

This is only an approximation to a solution. It is possible that the commit in the users database might fail (perhaps the disk is full), but by then the commit in the accounts database has completed and the table has been updated. This would leave the overall transaction in an inconsistent state. It is possible (if not pleasant) to code around these issues for each individual set of circumstances, but for now, you probably shouldn’t be relying on Active Record if you are writing applications that update multiple databases concurrently.

Prepared exclusively for Rida Al Barazi

Report erratum