asked
Is there any benefit to wrapping multiple selects in a READ COMMITTED transaction?
I've been puzzling a bit over the effects of the different transaction isolation levels on select queries. Assuming the default isolation level of the database is read-committed, I am questioning whether this SELECT * FROM Foo SELECT * FROM Bar SELECT * FROM Foo behaves any differently than this BEGIN TRANSACTION SELECT * FROM Foo SELECT * FROM Bar SELECT * FROM Foo COMMIT/ROLLBACK TRANSACTION --only reading rows, so no difference between commit or rollback The read committed transaction isolation level prevents "dirty reads", allows "non-repeatable reads", and allows "phantom reads". From what I can tell, this means that for a series of select statements, there is no difference between what results you get by simply issuing a series of individual select statements and wrapping a series of select statements in a single transaction boundary, when using `READ COMMITTED` isolation level. At other isolation levels, I can see there is a difference. Is there any benefit to wrapping multiple select statements in an explicit transaction under `READ COMMITTED` isolation level?
I do not see any benefits if only the select statement will be executed in the READ COMMITTED isolation level. There can be only a problem if those queries will be a long running queries, than the single transaction can block transaction log truncation as it will be active transaction.
15 People are following this question.
