Hello SSC Members,
We have a stored procedure template that embeds SELECTS in a transaction. I understand that there is nothing to commit or rollback and therefore it is an unnecessary overhead. But the question is will it cause any performance issues? - additional locking/blocking of the database?. Assume that the select was not in a transaction and the database is READ COMMITTED the select irrespective of whether it is in transaction or not will acquire READ/Shared lock and Insert/update/delete queries will have to wait for the shared lock to be released. But what is the downside from an overall database point of view to have every select in transactions?