TransactionScope are database updates available on reads within the same transaction

Question

I have a situation where I am using a transaction scope in .NET.

Within it are multiple method calls, the first perform database updates, and then the last reads the database.

My question is will the database reads pick up the changes in the first method calls that update the databases (note there are commits in these methods, but they are not truly committed until the transaction scope completes).

E.g Using TransactionScope.

{

Method 1 (Insert new comment into database).

Method 2 (Retrieve all comments from database).

complete.

}

Will method 2 results include the method 1 insert?

Thing that is confusing me is that I have ran loads of tests, and sometimes the update is there, sometimes its not!?!

I am aware there are isolation levels (at a high level), is there one that would allow reads to uncommitted data ONLY within the transactionscope?

Any and all help greatly appreciated......

Answer 1

You can do any operations on databases that you want (ms-sql), and until you don't make

transaction.commit()

any changes will appear.

Even if you insert NEW record in one transaction you can get its value in this same transaction. (ofc if you wont rollback()) it.

Answer 2

Yes, this is the purpose of transactions. Think about the situation where you have 2 tables, and 1 foreign keys the other. In your transaction, you insert into one and then the other one with a foreign key of your first insert, and it works. If the data was not available to you, the transaction would be pointless: it would be one operation at a time, which would be atomic, and thus negate the need for transactions.