I have recently inherited a project that has a fair amount of scripting and RDBMS interface. The original author (now unavailable), seems to have not understood the use of transactions; either that or I’m about to learn something new.
In ALL of the scripting that makes DB calls, the original author is usning transactions around try/except blocks; even for a single SELECT statement. While I have no issue with try/except, I’m wondering exactly what might be rolled back after some exception on a SELECT; probably best to just log soemthing in this case.
Can there be a good reason for transactions around SELECT?
If this is indeed mis/over use of DB transactions, is it being done with some detrimental, performance limiting affect?
If you had multiple selects I could see using transactions so that you don’t see the effects of other inserts/updates/deletes within the middle of your select sequence.
I can’t think of a good reason for a transaction around a single select – a single SQL statement is always a transaction even if you don’t make that explicit. Maybe if some form of SQL decides during the query optimization that an inner query isn’t part of the same transaction?
Would different default isolation levels make a statement like
SELECT * FROM FOO;
behave differently if you wrapped it in an explicit transaction (without adding an isolation level specific to that transaction and without adding other statements to the transaction)?