Executing an SQLCommand without specifying a Trans

Question

We have some lists of data being fetched in our application via a SqlCommand performing a SELECT query on a SQL Server database. We do not explicitly setup a transaction on the SqlCommand, instead just passing it a SqlConnection and running it. Is it the case that when no transaction is specified that SQL Server will initiate and use a default transaction with the default IsolationLevel of ReadCommitted?

Answer 1

SQL Server can work happily without an explicit transaction. But yes, I believe it is essentially read-committed (unless, of course, you add extra hints to your query objects,such as UPDLOCK / NOLOCK). You can investigate this with:

DBCC USEROPTIONS

which shows (among others):

isolation level read committed

Answer 2

SQL creates a transaction implicitly for your statements, and this transaction is committed when the statement completes. The isolation level of this transaction will be the current isolation level, which defaults to READ COMMITTED. Certain statements overwrite the current isolation level and enforce READ COMMITTED (eg. RECEIVE).

If your SqlCommand executes a batch (more statements) then each statement that access tables will create its own transaction.

The default autocommit of transactions is controlled by changing the SET IMPLICIT_TRANSACTION ON.

See Controlling Transactions for more details.