C# SqlTransaction блочит таблицу, как обойти блокировку или что делаю не так?

Question

[Pantuchi]

Есть "мелкие" методы которые подключаются к базе получают значение и закрывают подключение.

Есть две SQLCommand последовательные.

1. Создаю подключение.
2. Бидню транзакцию к подключению.
2.1. Биндю команду к подключению и транзакции.
3. Исполняю первую команду command_1.ExecuteNonQuery();
4. Хочу получить значение из тб. вызывая "мелкие" метод. И он вываливается по таймауту.
5. До исполнения второй команды разумеется дело не дошло.

Окей меняю мелкий метод передавая ему транзакцию и коннект, чтобы он стал частью транзакции.
Снова выполняю пункты 1., 2., 2.1, 3.
4. Теперь мелкий метод став частью транзакции получает значение из тб.
5. Выполняю command_2.ExecuteNonQuery();
6. transaction.Commit();

После исполнения первой транзакции и до выполнения второй заметил что блочится вся таблица и все коннекты и запросы вываливаются по таймауту. Я даже на момент транзакции запустил sql management studio и не смог выполнить запрос, пока транзакция на C# не закончилась. Ну я тут подумал хм ... транзакция блочит тб да как то странно ...

Открыл sql management studio вставил первую команду и до commit сделал задержку в 1 минуту.
begin transaction
...
WAITFOR DELAY '00:01';
commit transaction
Затем выполнил параллельно другую команду и она спокойно выполнилась до того как закоммитилась первая команда.

Вот и я не понимаю, почему с# транзакция блочит таблицу. Когда те же самые операции делаю через sql management studio без всяких блокировок.

Игрался с уровнями блокировок connection.BeginTransaction(IsolationLevel.....) ни к чему это не привело. Как блочилась вся тб так и блочится и ни одна учетка не может выполнить запрос без завершения транзакции.

Краткий листинг:

SqlConnection connection = new SqlConnection(ark.stringConnect);
connection.Open();
SqlTransaction transaction = connection.BeginTransaction(IsolationLevel.......);
SqlCommand command1 = _Delete(userID, periodID);
command1.CommandTimeout = 60;
command1.Connection = connection;
command1.Transaction = transaction;
command1.ExecuteNonQuery();

string S = "мелкий_метод(получить какое то значение)" он делает новое подключение и получает значение

SqlCommand command2 = Command_UpdateCascadePeriod(userID, uPeriod);
command2.CommandTimeout = 60;
 command2.Connection = connection;
command2.Transaction = transaction;
command2.ExecuteNonQuery();

transaction.Commit();

Comments

[Kano]

Для полного понимания того что у вас блокирутся нужно написать выбранный вами уровень изоляции транзакции, итоговый текст запроса на удаление (первая команда) и текст запроса который зависает

[Pantuchi]

Листинг мелкого метода:

public DateTime getDatetimeValue(int ID, int propertyID)
        {
            string sql = "select dbo.getDatetimeValue(@ID, @propertyID)";
            DateTime result = new DateTime();

            using (SqlConnection con = new SqlConnection(stringConnect))
            {
                SqlCommand com = new SqlCommand(sql, con);
                com.Parameters.Add(new SqlParameter("@ID", ID));
                com.Parameters.Add(new SqlParameter("@propertyID", propertyID));

                try
                {
                    con.Open();
                    object val = com.ExecuteScalar();
                    DateTime.TryParse(val.ToString(), out result);
                }
                catch (Exception e)
                {
                    string stack = e.StackTrace;
                    string msg = e.Message;
                    string method = "getDatetimeValue";
                    string param = "ID=" + ID + "; propertyID=" + propertyID + ";";

                    addlogErr(WebConfigurationManager.AppSettings["AppName"], method, param, msg, stack);
                    Debug.WriteLine(formaExceptionMsg(method, param, msg, stack));

                    if (interceptException)
                        throw new Exception(method, e);
                }
                finally
                {
                    con.Close();
                }
            }

            return result;
        }

Краткий листинг крупного метода инициирующего транзакцию: (черновой не критиковать что нет using-ов)))

public void RemoveAndGlue(int userID, int periodID)
        {
           //.....

            SqlConnection connection = new SqlConnection(ark.stringConnect);
            connection.Open();
            SqlTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadUncommitted);

            try
            {
                ModelPeriod model = GetPeriod(periodID);
                if (model == null) throw new Exception("Ошибка операции");

                List<int> items = GetFriends(periodID);

                SqlCommand command1 = _Delete(userID, periodID);
                command1.CommandTimeout = 60;
                command1.Connection = connection;
                command1.Transaction = transaction;
                command1.ExecuteNonQuery();

                ModelPeriod uPeriod = null;
                if (items[0] > 0 && items[2] > 0)
                {
                    DateTime dateEnd = ark.getDatetimeValue(items[2], pid_dateBegin); //<-- вываливается по таймауту
                    dateEnd = dateEnd.AddDays(-1);
                    uPeriod = new ModelPeriod();
                    uPeriod.PeriodID = items[0];
                    uPeriod.DateBegin = ark.getDatetimeValue(items[0], pid_dateBegin); //<-- вываливается по таймауту
                    uPeriod.DateEnd = dateEnd;
                }
                else if (items[0] > 0 && items[2] <= 0 && (status == pid_active || status == pid_expired))
                {
                    DateTime dateEnd = ark.getDatetimeValue(items[0], pid_dateEnd); //<-- вываливается по таймауту
                    ark.setDatetimeValue(contractId, pid_c_dateEnd, dateEnd); //<-- вываливается по таймауту
                }
                else if (items[0] <= 0 && items[2] > 0)
                {
                    DateTime dateBegin = ark.getDatetimeValue(contractId, pid_c_dateBegin); //<-- вываливается по таймауту
                    uPeriod = new ModelPeriod();
                    uPeriod.PeriodID = items[2];
                    uPeriod.DateBegin = dateBegin;
                    uPeriod.DateEnd = ark.getDatetimeValue(items[2], pid_dateEnd); //<-- вываливается по таймауту
                }

                if (uPeriod != null)
                {
                    SqlCommand command2 = Command_UpdateCascadePeriod(userID, uPeriod);
                    command2.CommandTimeout = 60;
                    command2.Connection = connection;
                    command2.Transaction = transaction;
                    command2.ExecuteNonQuery();
                }

                transaction.Commit();
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                throw new Exception(ex.Message);
            }
            finally
            {
                connection.Close();
            }
        }

[Pantuchi]

Дополнение: Стал переписывать мелкие методы для двух режимов, собственный коннект или работа как часть транзакции.

[Kano]

Pantuchi, вы так и не показали полный текст запросов. Всё что я вижу это dbo.getDatetimeValue и вызов метода _Delete. Что бы понять причину проблемы нужно знать на какие блокировки на какие строки накладываются в вашей БД (схема с индексами тоже помогает в определении причин взаимных блокиовок)

[Pantuchi]

Kano, я не могу скинуть сюда листинг запросов. Так как первый запрос из 145 строк, мелки вообще длиной порядка 1-3 строк, последний на 500 строк. Могу сказать одно, первый запрос удаляет объект из тб1 и тб2, затем мелки запросы обращаются к тб1 и тб2 получают данные и передают запросу последнему, который может произвести удаление из тб1 и тб2 или просто изменить данные в тб2. И все это выполняется в транзакции на C#. И проблема была в том что когда первый запрос выполнил удаление, мелкие запросы пошли устанавливать свой коннект к базе для того чтобы получить данные из тб1 и тб2. Новый коннект устанавливался, но запрос вываливался по таймауту, а все это время висит открытая транзакция.
Переписал так что мелкие методы могут создавать свое подключение или использовать уже установленное подключение и работать в составе транзакции. И проблема решена.

PS: Я просто не представляю как вам это все скидывать вы с ума сойдете так как у меня все не так прозрачно написано как это на пабликах показывают. То что вы запросы увидите это вам ничего не даст, я их выполнял через ms sql studio одновременно, с задержкой и блокировок никаких не было. Повторюсь ... блокируется именно запрос выполняемый (не в режиме транзакции) на новом подключении во время активной транзакции, которая уже внесла изменения в таблицу. И все это под одним именем входа. Вываливается такой запрос по таймауту.
Если я оберну мелкий запрос в свою транзакцию то это тоже решает проблему. Но я не хочу на мелкие запросы вешать собственную транзакцию, это не по назначению. Поэтому я включаю по необходимости мелкие запросы в состав транзакции и все. Уровни изоляции мне тут не нужны, мне нужна гарантия выполнения всех команд.

Answer 1

[MVV]

Игрался с уровнями блокировок connection.BeginTransaction(IsolationLevel.....) ни к чему это не привело.

Попробуйте обе транзации - и основную, и в мелком методе - делать с уровнем изоляции Snapshot.
По умолчанию изоляция транзакций в MS SQL реализуется через блокировки (изначально - обрабатываемых записей, но может переползти и на всю таблицу).
А вообще, при работе с незакрытй транзакциейоя бы передавал в мелкий метод то подключение, на котором открыта транзакция, чтобы он работал с ней. Можно сделать этот параметр необязательным (null по умолчанию) и в случае, если он не передан - открывать дополнительное подключение.

Comments

[Pantuchi]

Да я также думаю, передавать в мелкие методы транзакции благо они у меня как базовые методы взаимодействия с базой. Буду наследовать класс с мелкими методами для класса с крупными и передавая или задавая транзакцию для крупного метода мелкие методы будут выбирать альтернативное подключение как транзакция.
Протестил разные варианты:
Оказывается нет блокировки таблицы, блокировка идет как то на уровне библиотеки. Кака я это понял:
Стартанул проект и сделал два подключения к сайту через два разных браузера и учетки. Одна учетка дернула метод с транзакцией которая эмулировала долгую транзакцию, а вторая учетка сделала запрос к таблице и никакой блокировки. Даже если я в двух браузерах авторизуюсь под одной учеткой sql тоже нет блокировки тб.
Блокировка идет только из текущего запущенного потока подключения пользователя. Т.е. если запущена транзакция в работе и до коммита идет метод по подключению к базе, то этот метод коннектится к базе но запрос по таймауту вываливается, и чтобы это избежать нужно этот запрос включить в транзакцию. Заметил еще что это блокировка срабатывает когда были внесены изменения и эта блокировка распространяется только на текущий поток приложения. И такое ощущение что это не на уровне ядра блокировка ...
P.S. Повторюсь мелкие методы у меня подключаются не по ACID а обычным способом.

[Pantuchi]

Переписал базовые класс и его методы на работу в режиме своего коннекта либо в режиме как часть транзакции. Протестил все работает.
Более крупный класс унаследовал от базового класса с мелкими методами. В базовый класс объявил два свойства коннект и трансактион. Мелкие методы по этим свойствам определяют как им работать.

В крупном классе в методе под using инициирую транзакцию и задаю всему классу режим транзакции передав в его свойства, которые он унаследовал от базового класса, SQLConnection и SQLTransaction теперь весь класс работает в режиме одной транзакции включая мелкие методы. По завершении блока using SQLConnection и SQLTransaction диспозятся тем самым переводя весь класс в режим обычный не транзакции.

Answer 2

[Pantuchi]

Пока что путь решения такой. Мелкие методы переписал в два режима работы: режим своего подключения без транзакции и в режиме как часть транзакции. Все мелкие запросы лежат у меня в отдельном классе он у меня как базовый класс. Докинул в него два свойства SQLConnection и SQLTransaction. По ним мелкие методы определяют как им выполнить команду, в режиме транзакции или в обычном режиме своего подключения без транзакции (можно сказать грязное чтение и запись). Так же есть тематические расширенные классы которые наследуют этот базовый класс. Если методу в расширенном классе нужно отработать как транзакция внутри этого метода используя using задаю SQLConnection и SQLTransaction и внутри этого блока присваиваю свойствам SQLConnection И SQLTransaction которые унаследовал расширенный класс от базового класса, ссылку на коннект и на транзакцию и теперь мелкие методы работают как часть одной транзакции. Мне не нужны режимы Снепшота или другие изоляции главное назначение транзакции это гарантия выполнения частей команд в целом.
До всего этого у меня мелкие методы работали с базой независимо друг от друга и при крахе одной из них я не гарантировал целостности данных. Так как появилась необходимость в гарантии целостности так как команда 1 и 2 сами по себе делают много операции внутри себя, а вторая команда вообще зависит от успеха выполнения первой то и появилась необходимость в транзакции.

Я не стал все мелкие методы переписывать в режи транзакции так как это не целесообразно да и вообще мне кажется ресурсозатратно. Допустим у меня есть объект сlass People у него 10 свойств, и (~) на каждое свойство у меня метод на получение его из базы (да у нас база находится на одном сервере с проектами) это получается 10 транзакций ... да можно сделать так чтобы получить все свойства одним запросом, но не всегда это надо получать все свойства. Короче как то так. Таблиц у нас всего 5 описывающие объект. Таблица Классов, Таблица Свойств, Таблица Обектов (тела уникальных объектов) и Таблица Свойства объектов ну и Таблица Логи.

Если кому то покажется дичью прошу высказаться интересно мнение (только по существу)