Does an insert made in a transaction is visible by SELECT before transaction is commited

Question

I need a confirmation.

Client 1 insert rows in a table inside a transaction.

Client 2 request this table with a SELECT. If on this client isolation level is set to READ COMMITTED, can you confirm that the SELECT won't returns the rows that aren't yet committed by Client 1.

Thanks

Answer 1

It depends on the Transaction Isolation Level. If the Isolation Level is indeed 'Read Committed', other connections cannot see the rows that have been inserted by another client in a transaction that hasn't been committed yet.

Some reading:

• MSDN

  Craig Freedman's blog

Answer 2

Yes, your statement is correct. READ COMMITTED specifies that statements cannot read data that has been modified but not committed by other transactions.

Answer 3

Correct.

The MSDN reference for this is here which (quote) says:

Specifies that statements cannot read data that has been modified but not committed by other transactions. This prevents dirty reads. Data can be changed by other transactions between individual statements within the current transaction, resulting in nonrepeatable reads or phantom data. This option is the SQL Server default.

The SELECT would need to be running under READ UNCOMMITTED in order to read the data that has been modified by the other process, but not yet had the transaction committed.