Замена транзакций в MSSQL (part 2)

← →
Dimedrol © (2005-05-16 12:41) [0]

Коллеги, я уже поднимал тему транзакций в MSSQL в этом форуме,
но все-таки хочу вернуться к данной теме еще раз.

Сначала - изложу ситуацию:

Как я уже писал, у меня существует необходимость открывать
транзакцию в MSSQL на довольно продолжительное время (ок. 5 минут)
Как уже прозвучало мнение на форуме, транзакции не
предназначены для такого "длительного использования" :-)
Что-ж... а мне- нужно! :-))

В общем, ситуация такая:

Клиенты (несколько одновременно) загоняют в базу
большой объем данных, вставка которых замедляется обработкой
довольно "увесистыми" Stored процедурами. Так есть.
Уменьшить объем процедур не представляется возможным.
Так вот, в результате этого всего и получается,
что процесс вставки с 1 рабочего места может занимать 5 минут и более.

Данные же настолько важны, что потребовалось на всем этапе вставки
организовать приемлемую отказо-устойчивость, чтобы был соблюден
следующий принцип - или ВСЕ встало, или НИЧЕГО не прошло.
Разумеется для этих целей идеально подходит механизм транзакций.

НО! Довольно скоро всплыли "подводные камни" подобной реализации,
а именно: поскольку в конечном итоге вставка происходит в
несколько (9) таблиц, то даже при открытии самой "легкой" транзакции
на MSSQL - "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED"
рабочие таблицы блокируются настолько, что второму клиенту,
попытавшемуся вставить туда данные, придется ждать, пока первый не
закончит работу.
А в условиях многопользовательской работы, это, есссно, неприемлемо.
:-(

Недовольство достигло критической точки, и было принято решение
переписать логику вставки в базу.
Реализовал я следующим образом:
каждому клиенту при подсоединении к базе выдается некий
Session ID (Random 1..MaxInt), и для каждого "сеанса" вставки
я создаю ему набор необходимых таблиц и stored-процедур
с именами вроде "MyTable4977007" или "MyProcedure117293" и т.п.
где номер в конце один и тот же для одного клиента и его
"сеанса" связи.

Когда все данные вставлены в эти временные таблицы, запускается
процедура "перелива" данных из временных таблиц в постоянные.
Эта процедура происходит на самом сервере и там на свой страх и риск
транзакция уже мною НЕ открывается, да и сама процедура "перелива"
выполняется весьма быстро, что никого не "парит".

Как будто все ОК... НО! Опять проблемы.

Когда я проверял все это с 1 машины - все работало нормально.
Но как только во время сеанса вставки 1 клиента,
подключался другой, (или одновременно, - не важно) то
один или оба клиента "вылетают", получая ошибку SQL-сервера
на мониторе.
Ошибку stored процедуры вроде "invalid string position" (в Google
искал про нее - ничего нет, все ерунда какая-то),
или, моя любимая - "Unknown exception" :-(

Какие мысли ?
Помогите пожалуйста...

← →
Polevi © (2005-05-16 13:20) [1]

в процедуре создаешь 9 временных DECLARE @T1, @T2... таблиц
в них льешь
потом 9 инсертами перекидываешь в рабочие

← →
Dimedrol © (2005-05-16 13:31) [2]

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

← →
Polevi © (2005-05-16 13:43) [3]

ладно

заводишь 9 постоянных таблиц
в каждое добавляшь поле SPID - туда пихаешь @@SPID

при первой порции данных делаешь DELETE FROM Txxx WHERE SPID=@@SPID

при последней в случае успеха перебрасываешь данные и также делаешь DELETE FROM Txxx WHERE SPID=@@SPID

← →
Dimedrol © (2005-05-16 13:53) [4]

"...при последней в случае успеха..."

Мне кажется, что если я открою транзакцию, для определения
успешно ли все закончилось, успешно ли,
то все равно будут тормоза!
Ведь, вставлям-то в одну и ту же таблицу.
Только "SPID" разный будет...

← →
Nikolay M. © (2005-05-16 13:56) [5]

> каждому клиенту при подсоединении к базе выдается некий
> Session ID

@@SPID чем не угодил?

> Ошибку stored процедуры вроде "invalid string position"
> (в Google
> искал про нее - ничего нет, все ерунда какая-то),
> или, моя любимая - "Unknown exception" :-(

Искать ошибку в логике ХП.

По сабжу - почему бы, например, в этих 9 таблицах не завести поле-флажок IsConfirmed и, когда данные льешь, IsConfirmed равен 0, потом в транзакции меняешь его во всех таблицах на 1. Если объем данных значителен, то лучше посмотреть в сторону булковой заливки через bcp.

← →
Ega23 © (2005-05-16 14:02) [6]

Кстати о пцыцах: временная таблица #TempTable в базе TempDB УЖЕ имеет реальное имя с учётом ProcessID, типа
#TempTable______________________________________000000001CE0

← →
Polevi © (2005-05-16 14:04) [7]

какую транзакцию, ты че
у тебя будет каждый раз открываться новая транзакция на каждый кусок данных

CREATE PROCEDURE PutData @PacketNo INT, @SomeData VARCHAR(8000), @Success INT OUT AS
BEGIN
IF @PacketNo=1
DELETE FROM SomeTable1 WHERE SPID=@@SPID
INSERT INTO SomeTable1 (F1,F2,SPID)
SELECT bla,bla,@@SPID
EXEC CheckSuccess @Success
IF @Success AND @PacketNo=LAST_PACKET
INSERT INTO WorkTable
SELECT F1, F2 FROM SomeTable1
IF NOT @Success OR @PacketNo=LAST_PACKET
DELETE FROM SomeTable1 WHERE SPID=@@SPID
END

← →
Dimedrol © (2005-05-16 14:05) [8]

2 Nikolay M. ©

> @@SPID чем не угодил?

Просто не подумал о нем. :-)
В принципе,- что так, что так...

> Искать ошибку в логике ХП.

С 1 юзером. Все ОК! 100%
Думаю, это осложняет поиск...

> По сабжу - почему бы, например, в этих 9 таблицах не завести
> поле-флажок IsConfirmed и, когда данные льешь, IsConfirmed
> равен 0, потом в транзакции меняешь его во всех таблицах на 1.
> Если объем данных значителен, то лучше посмотреть в сторону
> булковой заливки через bcp.

Нуууу... по количеству результирующих записей, вроде и небольшой
1000, может 1500. Но, все эти "записи", это - вызовы Stored procedur...

← →
Ega23 © (2005-05-16 14:08) [9]

← →
Dimedrol © (2005-05-16 15:16) [10]

2 Ega23 ©
Ну разумеется! Это было понятно сразу. :-)
Я же создавал несколько одноименных временных таблиц,
с одним и тем же именем. И конфликтов не было.
Так что тут как раз проблем нет.

2 Polevi ©
На каждый кусок данных открывать транзакцию не имеет смысла.
Они (куски) очень маленькие и их очень много.

Мне и нужна была транзакция чтобы отследить корректность вставки
ВСЕХ многочисленных кусочков данных в течение 1 сессии.

2 Ega23 ©
Даааа... :-)
Может конечно что и поможет.
Но у нас все лицензионное, так что покупку этого продукта
мне вряд ли профинансируют ;-)

> Нуууу... по количеству результирующих записей, вроде и небольшой
> 1000, может 1500. Но, все эти "записи", это - вызовы Stored
> procedur...

Тогда bcp здесь, конечно, не нужен. Но основная идея очевидна: лить эти полторы тысячи записей в буфер и потом проводить быструю транзакцию на актуализацию данных в живых таблицах. С флажком, имхо, будет быстрее. Только апдейт флажка нужно проводить по ПК новых записей, а не по значению поля-флага, чтобы индексы работали, а не скан таблицы производился.

Замена транзакций в MSSQL (part 2) Найти похожие ветки