#sql #база_данных #транзакции
Нужно ли отправлять изменения в БД одной транзакцией, или каждый INSERT, UPDATE,
DELETE лучше выполнять отдельной транзакцией? Что лучше при выполнении 10 несвязанных
запросов (не нужно откатывать предыдущий запрос при ошибке последующего), отправить
их одной транзакцией, или каждый по отдельности? Какой вариант лучше для производительности
приложения и субд?
Ответы
Ответ 1
В общем случае ни первый ни второй вариант не являются оптимальными с точки зрения производительности. Если нужно удалить(вставить/поменять) действительно большое количество строк, в первом случае мы получаем большие расходы на открытие, закрытие большого количества транзакций. Во втором случае имеем слишком огромную транзакцию, которая тоже под конец работы будет долго закрываться, да ещё и рискуем упасть, т.к. размеры транзакции ограничены(видел такую ошибку всего на нескольких миллионах строк). Как-то писал скрипт удаления "ненужных" данных. Сталкивался с обеими проблемами. Скрипт запускался на выходных, и удалял несколько десятков миллионов строк. Т.е. проблемы блокировок и прочего не волновали, в приоритете была скорость. Чтобы наглядно продемонстрировать, написал небольшой скрипт. MS SQL Server. Кратко что он делает: T_TABLE - таблица с данными T_RESULT - таблица с результатами эксперимента P_INSERT_ROWS @Count - вставляет в таблицу T_TABLE @Count строк P_DALATE_ROWS @Count, @Pack - удаляет из таблицы T_TABLE все строки в транзакциях по @Pack штук, и записывает затраченное время в таблицу T_RESULT. Финальный скрипт запускает в цикле вставку 1 000 000 строк, и их удаления пачками по 1, 10 .. 1 000 000 штук. И затем выводит содержимое T_RESULT. Скрипт: USE tempdb; GO IF OBJECT_ID('P_INSERT_ROWS', 'P') IS NOT NULL DROP PROC P_INSERT_ROWS; IF OBJECT_ID('P_DELETE_ROWS', 'P') IS NOT NULL DROP PROC P_DELETE_ROWS; IF OBJECT_ID('T_TABLE', 'U') IS NOT NULL DROP TABLE T_TABLE; IF OBJECT_ID('T_RESULT', 'U') IS NOT NULL DROP TABLE T_RESULT; GO CREATE TABLE T_TABLE ( id INT IDENTITY(1,1), Number INT, String NVARCHAR(4000) ) CREATE INDEX IN_T_TABLE_NUMBER ON T_TABLE(Number ASC) CREATE TABLE T_RESULT ( execute_time DATETIME, Cnt INT, Pack INT ) GO CREATE PROC P_INSERT_ROWS @Count INT AS ;WITH CTE AS( SELECT 1 N UNION ALL SELECT N+1 FROM CTE WHERE N<@Count ) INSERT T_TABLE SELECT N, LEFT(REPLICATE(N, 100),4000) FROM CTE OPTION(MAXRECURSION 0) GO CREATE PROC P_DELETE_ROWS @Count INT, @Pack INT AS DECLARE @TTT DATETIME = GETDATE(); DECLARE @NumberStart INT = 1 WHILE @NumberStart < @Count BEGIN BEGIN TRAN; DELETE FROM T_TABLE WHERE Number BETWEEN @NumberStart AND @NumberStart + @Pack - 1 OPTION(RECOMPILE); COMMIT TRAN; SET @NumberStart += @Pack; END; INSERT T_RESULT SELECT GETDATE()-@TTT execute_time, @Count Cnt, @Pack Pack GO SET NOCOUNT ON; DECLARE @Count INT = 1000000, @Pack INT = 1 WHILE @Pack <= @Count BEGIN EXEC P_INSERT_ROWS @Count EXEC P_DELETE_ROWS @Count, @Pack SET @Pack *= 10; END SELECT * FROM T_RESULT GO Содержимое T_RESULT: execute_time Cnt Pack 1900-01-01 00:15:05.183 1000000 1 1900-01-01 00:01:56.003 1000000 10 1900-01-01 00:00:32.200 1000000 100 1900-01-01 00:00:22.370 1000000 1000 1900-01-01 00:00:21.940 1000000 10000 1900-01-01 00:00:22.663 1000000 100000 1900-01-01 00:01:10.327 1000000 1000000 Как видим, лучший результат по времени выполнения имеем, когда удаляется в одной транзакции пачками по 10000 штук, т.е. это намного эффективнее, чем удалять по одной записи в транзакции, и эффективнее, чем удалить все строки в одной транзакции. Эксперимент не строгий, запуски влияют друг на друга, но всё равно показательно. UPD: Добавлю, что на практике, транзакции в реальной жизни не часто бывают большими. Кроме запросов вида DELETE FROM T, пожалуй.:) В этом случае да, чаще всего если есть возможность обернуть несколько действий в одну транзакцию - лучше обернуть.Ответ 2
Я полагаю, что такая постановка вопроса изначально неверна. Транзакция по сути своей это атомарная операция с точки зрения бизнес-логики, а не с точки зрения БД. Именно так. Простейший классический пример с дебетом и кредитом счетов. Дебет и кредит разных счетов должны производиться в пределах одной транзакции, безотносительно вопросов к производительности. То есть списание денег с одного счета и зачисление той же суммы на другой счет - это одна транзакция (одна атомарная операция с точки зрения бизнес-логики). Если мы начнем эту атомарность разбивать на 2 операции апеллируя к производительности - это путь в никуда и приведет к нарушению целостности данных (как в примере с дебетом/кредитом: при падении системы, деньги с одного счета спишутся, а на другой не зачислятся). Посему, я бы считал, что границы транзакций должны определяться бизнес-логикой приложения, но никак не исходя из других соображений. Другой вопрос, что если логика позволяет границы транзакций проводить и так и эдак, то совершенно очевидно, что чем короче транзакция, тем она более производительна. Механизм реализации транзакций предполагает в классическом варианте, блокировку таблиц на уровне записей затрагиваемых транзакцией. Чем длиннее транзакция - тем больше записей блокируется. Чем больше записей блокируется, тем ниже производительность.Ответ 3
самый главный скажу что надо каждый раз отдельно открывать транзакцию потому что транзакция определена 1 атомарная операция. Если ты будешь использовать за нескольких изменение за 1 транзакцию то у тебя будет в базе проблемы. может создаться 'Dead Lock' ситуация. нагрузка будет при открытом состоянии транзакция. кто соединяется в этот базу и исполняет какие-то изменения , изменение остается неподтвержденной. а это что означает будет проблема не сохраненная информация. Потому что когда ты будешь это подтверждать мы не знаем. я хочу отметить еще один момент транзакция определена одному реальному атомарные операцию.это может состоять много операций за одним атомарным процессом. (можно использовать ' Insert' и 'Update' одном (атомарном) транзакция Ну это уже твой операция то есть что ты делаешь в предметной области). Если будет мини-проект и только ты будешь этом использоваться тогда нет проблемы. Думать о дальнейшем!
Комментариев нет:
Отправить комментарий