COMMIT TRANSACTION statements are mostly superfluous in this specific case.
As for running all queries with
READ UNCOMMITTED isolation level, I'd strongly advise against it, unless you have a specific need, and aren't worried about the implications of dirty reads:
- You may end up querying data from a half-finished transaction (e.g. only one half of a double-entry accounting ledger entry, only some of an order's line items, etc.)
- You may end up querying data from a transaction that is eventually rolled back.
If you're concerned about write queries blocking read queries, I would recommend either using
SNAPSHOT isolation, or setting the
READ_COMMITTED_SNAPSHOT database option, which effectively uses snapshot isolation for all queries. The net result is that instead of being blocked, read queries will see data as it existed prior to any currently running write transactions.
Be advised that this option increases tempdb usage, as that's where the snapshot data is stored (and it has to be stored for all write transactions in databases where it's enabled). If this overhead is unacceptable, then I would only use
READ UNCOMMITTED after evaluating queries that need it on a case-by-case basis.
I know you aren't the one making the decision to use it, but you can use these points as ammo if you need to push back on it. :)