(Database) Transactions should be used modestly

It is good practice to leave your database in a consistent state. There are different ways to do this. Foreign key constraints, indexes, typing of columns, are all strategies to keep your database in a consistent state. Transactions are another way providing you a tool to keep the database consistent: if one of the inserts or updates fail, your database will rollback to the state before the first in the series of inserts and updates within that transaction.

Some languages make it really simple to create a transaction. In Ruby on Rails it is simply opening a block:

User.transaction do
  # ... all db operations are now in a transaction
end

But be cautious; transactions don’t come for free: they lock the table or row, which is bad for performance. It can, by design, stop other processes from updating the same rows. And all this gets worse when transactions take longer, when for example they contain request to remote resources.

Hence, my approach to transactions is don’t, unless you can’t do without. And if you can’t do without, make it execute quickly and remove everything from the critical path that doesn’t belong there.