Есть ли жизнь на 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, само собой, тоже доступен. Просто этот пост давно писался.