Providing an isolation level to "DbConnection.BeginTransaction(isolationLevel)" has no effect on the SQL sent to the database

60 Views Asked by At

I've been doing some testing in the System.Data namespace using SQL Server Profiler, and noticed that whatever IsolationLevel you provide to DbConnection.BeginTransaction(isolationLevel) doesn't change the SQL that is actually sent to the database. For example, this code:

using DbConnection connection = new SqlConnection(EFContext.GetConnectionString());
connection.Open();
using var transaction = connection.BeginTransaction(IsolationLevel.Serializable);
//           changing this has no effect on the SQL ^^^^^^^^^^^^^^^^^^^^^^^^^^^
using var command = connection.CreateCommand();
command.Transaction = transaction;
command.CommandType = CommandType.Text;
command.CommandText = "DROP TABLE IF EXISTS __TEST";
command.ExecuteNonQuery();
transaction.Commit();

will always show these same SQL statements in SQL Server Profiler:

EventClass ApplicationName TextData
TM: Begin Tran completed Core Microsoft SqlClient Data Provider BEGIN TRANSACTION
SQL: BatchCompleted Core Microsoft SqlClient Data Provider DROP TABLE IF EXISTS __TEST
TM: Commit Tran completed Core Microsoft SqlClient Data Provider COMMIT TRANSACTION

So my question is, what is the use of providing an isolation level to DbConnection.BeginTransaction(isolationLevel) if it does not affect the SQL?

I know that one can manually send a statement like SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, so why doesn't BeginTransaction do this?

0

There are 0 best solutions below