I know this is a question that depends on the details of a specific situation, and a rather open-ended question at that. But, nevertheless, are there any good guidelines out there about when database transactions ought to be used in the course of everyday module development? I've begun to worry that I should be doing transactions a lot more than I currently am (which is to say almost not at all), but it would obviously be wrong to go off on a frenzy of wrapping transactions around everything. Any thoughts out there?
When you're running multiple queries in the same function/context that change data in the db, and the failure of any one of those queries would leave your data in an inconsistent state...that's when you should be using a transaction.
To the best of my knowledge there's no point in wrapping single queries in a transaction (it seems redundant. The query will either fail or succeed and you can catch those on an individual basis). Even if you're running multiple queries that won't leave data in an inconsistent state should one of them fail, I would say the same thing goes. You can catch errors for each query individually, and fail/continue gracefully.
Transactions are also useful to speed up inserts. When doing multiple inserts the difference could be dramatic. Anyhow you need to measure it to be sure if it works for your code.