It occurred to me yesterday that transactions can help database performance in certain cases. Specifically when multiple UPDATE’s, INSERT’s, and/or DELETE’s are used sequentially.
The reasoning goes like this: the most expensive part of any database update is flushing the changes to disk. If you have several successive updates(/inserts/deletes), then the default behavior in most databases is that they will be flushed to disk one at a time, sequentially, forcing you to wait a full round trip for every change you’re making.
Contrast that with a transaction which is guaranteed to be atomic: if the whole group of changes is written to disk altogether or not at all, that means the database is free to hold everything in memory until it’s committed, and perform the commit with a single flush to disk.
Just to make sure I wasn’t crazy, I pulled out ActiveRecord and ran a couple tests (source code here). The results: I wasn’t crazy. For two successive inserts, the transaction version ran in half the time. For twenty successive inserts, the transaction version ran in just over a tenth of the time.
Comments