SQL Server transaction and SELECT statement

Question

I often saw many people use SELECT statement within a transaction. I often use insert/update/delete only in transaction. I just do not understand that what is the utility of putting a SELECT statement inside transaction.

I got one answer that....SELECT inside the transaction can see changes made by other previous Insert/Update/Delete statements in that transaction, a SELECT statement outside the transaction cannot.

Above statement is it true or not ?

Is this is the only reason that people put SELECT statement inside transaction? Please discuss all the reason in detail if possible. thanks

Answer 1

try doing this and you will understand:

Open a two new queries on SSMS (lets call it A and B from now one) and on A, create a simple table like this:

create table transTest(id int)
insert into transTest values(1)

now, do the following:

do select * from transTest in both of them. You will see the value 1

On A run:

set transaction isolation level read committed

On B run:

begin transaction
insert into transTest values(2)

On A run:

select * from transTest

you will see that the query wont finish because it is locked by the transaction on A

On B run:

commit transaction

Go back to A and you will see that the query finished

Repeat the test with set transaction isolation level read uncommitted on A you will see that the query wont be locked by the transaction

Answer 2

One of the main reasons I can think of (the only reason, in fact) is if you want to set a different isolation level, eg:

USE AdventureWorks2008R2;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;

SELECT * FROM HumanResources.EmployeePayHistory;

SELECT * FROM HumanResources.Department;

COMMIT TRANSACTION;

For single SELECT statements though, I'm not so sure, unless you had a reason to go the other way and set READ UNCOMMITTED in cases where response time/maximising concurrency is more important than accurate or valid data.

<speculation certainty="75%"> If the single SELECT statement is inside an explicit transaction without altering the isolation levels, I'm pretty sure that will have no effect at all. Individual statements are, by themselves, transactions that are auto-committed or rolled back on error.</speculation>