Executing a SQLCommand Without Specifiying a Transaction

Question

Hi,

I feel like I should be able to find the answer to this question but for the life of me I can't seem to get the information I'm looking for.

We have some lists of data being fetched in our application via a SqlCommand performing a SELECT query on a SqlServer 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 SqlServer will initiate and use a default transaction with the default IsolationLevel of ReadCommitted?

Just looking for confirmation on this one.

Thanks,

Graham

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.