is there a difference between a select statement inside a transaction and one that is outside of it

Question

I was just wondering if that default READ COMMITTED isolation level somehow makes the select statement act different inside of a transaction than one that is not in a transaction, anybody knows ?
I am using MSSQL

Answer 1

yes, the one inside the transaction can see changes made by other previous Insert/Update/delete statements in that transaction, A Select statement outside the transaction cannot....

If all you are asking about is what the Isolation Level does, then understand - that all Select Statements (hey, all statements of any kind), - are in a transaction. The only difference between one that is exlicitly in a transaction and one that is standing on it's own is that the one that is standing alone starts it's transaction immediately before it executes it, and commits or roll back immediately after it executes,

whereas the one that is explicitly in a a transaction can, (because it has a Begin Transaction statement) can have other statements (inserts/updates/deletes, whatever) occcurring within that same transaction, either before or after that Select statement.

So whatever the isolation level is set to, both selects (inside or outside an explicit transaction) will nevertheless be in a transaction which is operating at that isolation level.

Answer 2

The READ COMMITTED isolation level is about the records that have been written. It has nothing to do with whether or not this select statement is in a transaction (except for those things written during that same transaction).

Answer 3

Yes, there is a bit of a difference. For MySQL, the database doesn't actually start with a snapshot until your first query. Therefore, it's not begin that matters, but the first statement within the transaction. If I do the following:

#Session 1
begin; select * from table;

#Session 2
delete * from table; #implicit autocommit

#Session 1
select * from table;

Then I'll get the same thing in session one both times (the information that was in the table before I deleted it). When I end session one's transaction (commit, begin, or rollback) and check again from that session, the table will show as empty.

Answer 4

If your database (or in mysql, the underlying storage engine of all tables used in your select statement) is transactional, then there simply no way to execute it "outside of a transaction".

Perhaps you meant "run it in autocommit mode", but that is not the same as "not transactional". In the latter case, it still runs in a transaction, it's just that the transaction ends immediately after your statement is finshed.

So, in both cases, during the run, a single select statement will be isolated at the READ COMMITTED level from the other transactions.

Now what this means for your READ COMMITTED transaction isolation level: perhaps surprisingly, not that much.

READ COMMITTED means that you may encounter non-repeatable reads: when running multiple select statements in the same transaction, it is possible that rows that you selected at a certain point in time are modified and comitted by another transaction. You will be able to see those changes when you re-execute the select statement later on in the same pending transaction. In autocommit mode, those 2 select statements would be executed in their own transaction. If another transaction would have modified and committed the rows you selected the first time, you would be able to see those changes just as well when you executed the statement the second time.