Есть ли жизнь на MARSe?

 

Давнишний постинг с sqlclub.ru, перепостуемый сюдыть (https://blogs.msdn.com/alexejs/archive/2009/07/10/marse.aspx) по причине недоступности оного.

-----------------------------------------------------------------------------------------

Вообще-то тему Марса я уже как-то затрагивал: https://sqlclub.ru/forum/viewtopic.php?t=538 J, посему сегодня под MARSом будем понимать Multiple Active Result Sets. Скорее всего, многие слышали, что да, появилась подобная фишка в 2005-м, а некоторые даже ее заюзали в своих приблудах. Для остальных этот топег дает краткое введение, че это за и почему оно надо.

Сперва смотрим, как оно было. Открываем студию, делаем какой-нибудь консольный проект и подключаем в References старенькую ADOшную (не .NET) библиотеку.

В моем случае их нашлось целых 3: две Primary Interop Assemblies в VSTO к старому и новому офису и третья в стандартных .NET-обертках. После чего пишем следующий код. Я не буду его комментировать – здесь и так все понятно: две разные команды на одной коннекции, одна выполняется один раз, другая несколько последовательно.

static void Main(string[] args)

{

ADODB.Connection cnn = new ADODB.Connection();

cnn.Open("Provider=SQLOLEDB;Server=(local);

Database=AdventureWorksLT;Integrated Security=SSPI", "", "",

(int) ADODB.ConnectOptionEnum.adConnectUnspecified);

      object пустышка = Type.Missing;

      ADODB.Command cmd1 = new ADODB.Command();

      cmd1.CommandText = "select * from SalesLT.Product";

      cmd1.ActiveConnection = cnn;

cmd1.Execute(out пустышка, ref пустышка, (int) ADODB.ExecuteOptionEnum.adOptionUnspecified);

      ADODB.Command cmd2 = new ADODB.Command();

      cmd2.CommandText = "select * from SalesLT.Customer";

      cmd2.ActiveConnection = cnn;

      for (int i = 0; i < 3; i++)

cmd2.Execute(out пустышка, ref пустышка, (int) ADODB.ExecuteOptionEnum.adOptionUnspecified);

    cnn.Close();

 }

Вот, говорят отдельные неустойчивые элементы, тыча приблизительно этим кодом, все работает. Все же ведь выполняется с одного соединения и еxceptionов не выкидывает. На самом деле, конечно, не с одного. Поставьте брейкпойнт на втором Execute:

И выполните из SSMS следующий запрос

select s.session_id, s.host_process_id, s.client_interface_name,

db_name(r.database_id) db_name, user_name(r.user_id) user_name, st.text sql,

r.open_resultset_count, r.open_transaction_count, s.status, r.wait_type

from sys.dm_exec_sessions s

join sys.dm_exec_requests r on s.session_id = r.session_id

outer apply sys.dm_exec_sql_text(r.sql_handle) st

where s.program_name = 'Microsoft (R) Visual Studio (R) 2008'

который позвольте мне сейчас не объяснять, чтобы не отвлекаться. Это приблизительный аналог sp_who / sp_who2, они же вирт.табл. sysprocesses. Вот, что там будет:

В VS прокрутите цикл один раз, снова встаньте на брейкпойнте. Повторите запрос в SSMS. Мы видим, что в действительности ADO втихаря открыла новое соединение под 2-й Execute:

Прокрутите цикл еще раз. Под очередное выполнение той же команды завелось еще одно соединение:

и т.д.

Так незаметно для себя можно было наплодить целую кучу соединений. Существовало два способа этого не допустить. Первый – запретить явно, поставив после открытия соединения строчку

cnn.Properties["Multiple Connections"].Value = false;

Тогда мы просто получим ошибку при попытке открытия нового рекордсета на соединении, где уже есть живой

Второй способ – следить самому и закрывать Recordset прежде, чем открывать новый на том же соединении. Т.е. примерно так:

static void Main(string[] args)

{

ADODB.Connection cnn = new ADODB.Connection();

            cnn.Open("Provider=SQLOLEDB;Server=(local);Database=AdventureWorksLT;Integrated Security=SSPI",

                "", "", (int)ADODB.ConnectOptionEnum.adConnectUnspecified);

      object пустышка = Type.Missing;

      ADODB.Command cmd1 = new ADODB.Command();

      cmd1.CommandText = "select * from SalesLT.Product";

      cmd1.ActiveConnection = cnn;

      ADODB.Recordset rst1 = cmd1.Execute(out пустышка, ref пустышка, (int)ADODB.ExecuteOptionEnum.adOptionUnspecified);

    ADODB.Command cmd2 = new ADODB.Command();

      cmd2.CommandText = "select * from SalesLT.Customer";

      cmd2.ActiveConnection = cnn;

      ADODB.Recordset rst2;

      for (int i = 0; i < 3; i++)

      {

      rst2 = cmd2.Execute(out пустышка, ref пустышка, (int)ADODB.ExecuteOptionEnum.adOptionUnspecified);

            rst2.Close();

      }

      cnn.Close();

}

Изменения по сравнению с предыдущим вариантом кода выделены. Если на соединении висит результат, провайдер SQLOLEDB (да и native тоже) порождает под новую команду новое соединение в дополнение к занятому. А что делать, если вам единомоментно нужно иметь два или более рекордсетов? Ну там вы их сравниваете или производите над ними еще какие-то совместные операции. Ответ очевиден – открывать для них новые соединения, либо ADO это сделает за вас (если ему явно не запретить) . Открытие соединения дорогостоящая операция, соединение отнимает много ресурсов – это общеизвестные истины. К тому же эти дополнительные соединения не пулируются, как вы видите. Если бы после первого прохода цикла соединение вернулось в пул, на второй итерации мы бы видели по-прежнему два соединения, а не три. Короче, все плохо. Пишем следующий код:

static void Main(string[] args)

{

SqlConnection cnn = new SqlConnection("Server=(local);Database=AdventureWorksLT;" +

                "Integrated Security=true;MultipleActiveResultSets=true");

      cnn.Open();

      SqlCommand cmd1 = new SqlCommand("select * from SalesLT.Product", cnn);

      cmd1.ExecuteReader();

      for (int i = 0; i < 3; i++)

      {

      SqlCommand cmd2 = new SqlCommand("select * from SalesLT.Customer", cnn);

            cmd2.ExecuteReader();

      }

      cnn.Close();

}

Принципиальный момент в строке соединения выделен болдом. Опция MultipleActiveResultSets=true включает возможность иметь несколько активных рекордсетов на одном соединении. Без нее поведение будет ровно таким же, как мы наблюдали в случае ADO: провайдер будет неявно плодить соединения под каждый новый рекордсет. Ставим брейкпойнт на cnn.Close(). Как мы помним, в этом месте у нас открывалось 4 соединения: одно от cmd1 и 3 от cmd2. Смотрим, что произойдет сейчас

Мы видим, что у нас открыто всего одно соединение с session_id = 52, на котором висят 4 exec_requesta соответственно тому, как мы поочередно отправляли на SQL Server команды. Обратите внимание, как прирастал open_resultset_count. Еще обратите внимание, если будете воспроизводить это у себя, что в запросе нужно поменять условие where

where s.program_name = '.Net SqlClient Data Provider'

Изменение провайдера на Native привело к тому, что наше приложение теперь стало по-другому представляться SQL Server. На что еще нужно обратить внимание? MARS позволяет иметь несколько активных результатов на одном соединении, но это не значит, что эти результаты могут относиться к одному и тому же объекту команды. Именно поэтому мы спрятали SqlCommand cmd2 внутрь цикла:

 

for (int i = 0; i < 3; i++)

{

  SqlCommand cmd2 = new SqlCommand("select * from SalesLT.Customer", cnn);

      cmd2.ExecuteReader();

}

Если бы было вот так:

SqlCommand cmd2 = new SqlCommand("select * from SalesLT.Customer", cnn);

for (int i = 0; i < 3; i++)

{

cmd2.ExecuteReader();

}

было бы вот что:

Имейте это в виду, потому что народ часто на этом попадается. Достаточно сделать поиск на «There is already an open DataReader associated», чтобы увидеть, сколько народу жалуется по форумам. Типа как же так, а как же MARS? Но еще веселее почитать ответы «спецов». Гы. Поэтому еще раз: MARS – это возможность иметь несколько активных результатов на одном соединении, но не на одной команде.

Как я уже говорил, с практической точки зрения MARS нужен, чтобы не плодить соединения, когда на клиенте или на middleware требуется провести совместную обработку нескольких рекордсетов. Например, вот такой извращенный вариант джойна ProductCategory c Products.

static void Main(string[] args)

{

SqlConnection cnn = new SqlConnection("Server=(local);Database=AdventureWorksLT;" +

                "Integrated Security=true;MultipleActiveResultSets=true");

            cnn.Open();

      SqlCommand cmd1 = new SqlCommand("select * from SalesLT.ProductCategory", cnn);

      SqlDataReader sdr1 = cmd1.ExecuteReader();

      while (sdr1.Read())

      {

      SqlCommand cmd2 = new SqlCommand("select * from SalesLT.Product " + "where ProductCategoryID = @ProductCategoryID", cnn);

            cmd2.Parameters.AddWithValue("@ProductCategoryID", sdr1["ProductCategoryID"]);

            SqlDataReader sdr2 = cmd2.ExecuteReader();

            while (sdr2.Read()) Console.WriteLine("{0} <- {1}", sdr1["Name"], sdr2["Name"]);

      }

      Console.WriteLine("\nPress any key to continue or any other key to stop...");

      Console.ReadKey(true);

      cnn.Close();

}

MARS доступен для нативного клиента SQL Server 2005 и поддерживается во всех редакциях, включая Express. Про его наличие в других провайдерах мне неизвестно.

В этой жизни ничего не дается даром, и применение MARS может повлечь свои негативные моменты. Информация к размышлению. В ту пору, когда SQL Server 2005 звался еще Юкон, MARS был включен по умолчанию, т.е. вот эта опция MultipleActiveResultSets=true подразумевалась автоматически. Где-то на промежутке между второй бетой и RC мужики подумали и решили ее по умолчанию загасить нафиг от греха. Как говорил известный системный аналитик Винни-Пух, «Это ж-ж-ж неспроста». Но об этом в следующий раз.

Для нативного клиента SQL Server 2008 MARS, само собой, тоже доступен. Просто этот пост давно писался.