Как часть транзакции сделать безоткатной?

← →
LAMER Pro (2003-07-09 09:05) [0]

Уважаемые Мастера!
Наверняка Вам уже приходилось решать проблему аудита системы. Я мало знаком с SQL, а сейчас пишу ТЗ на программу, в которой необходимо фиксировать попытки пользователя произвести определённый набор действий с информацией в БД. В каждой ХП размещаю добавление записи в журнал аудита, но ведь при откате должны "отмениться" ВСЕ измениния в БД? Как быть - мне нужен аудит ВСЕХ действий, даже закончившихся неудачей?
Система ПОКА планируется "клиент-сервер", т.к. пользователй мало (50-70). Или придётся переходить на трёхзвенку?
Планирую использовать Delphi7 + MSSQL2K + ADO2.7 + MS Svr2K, клиенты Win98/Win2K
Буду рад любой идее.

← →
DenK_vrtz (2003-07-09 09:07) [1]

commit чаще делай, я думаю в MSSQL есть такое понятие

← →
Max Zyuzin (2003-07-09 09:26) [2]

>LAMER Pro © (09.07.03 09:05)
Делай заполнение журнала аудита в отдельной транзакции, специальной для именно этой таблички, Там думаю откаты тебе не понадобятся.

← →
LAMER Pro (2003-07-09 10:26) [3]

to DenK_vrtz, Max Zyuzin
Если можно, то объясните подробнее, т.к. я вычитал следующее:
"Создание вложенной транзакции производится автоматически, если пользователь выполняет команду BEGIN TRAN до того, как будет завершена предыдущая транзакция. Не трудно прийти к выводу, что создание вложенной транзакции возможно только при использовании режима явного определения транзакции. В режиме автоматического начала транзакции каждая команда выполняется как отдельная транзакция, которая завершается сразу же после выполнения команды. Таким образом, образование вложенной транзакции просто невозможно. При работе в режиме неявного определения транзакции пользователь использует только команды ROLLBACK TRAN и COMMIT TRAN, тогда как команда BEGIN TRAN выполняется системой автоматически."
"Отдельно необходимо рассмотреть поведение вложенных транзакций в случае отката или фиксирования транзакций нижнего уровня. При использовании вложенных процедур каждая процедура нижнего уровня рассматривается как единственная команда, которая может завершиться либо удачно, либо нет. При удачном завершении транзакции нижнего уровня выполняется следующая команда текущей транзакции. Если же одна из вложенных транзакций любого уровня закончится неудачно (будет откачена), то это приведёт к откату всей вложенной транзакции. При этом, согласно требованиям ACID, ПРОИСХОДИТ ОТКАТ ВСЕХ ТРАНЗАКЦИЙ, в том числе и успешно завершённых."
Я это понял так: если в транзакции хотя бы один оператор сгенерит исключение, то будут отменены ВСЕ транзакции, даже завершённые по commit внутри хранимой процедуры.
Если я ошибаюсь, то помогите разобраться...

← →
Fay (2003-07-09 10:31) [4]

Типа этого
begin tran tran1 insert into Table2 select 2, "blablabla" commit tran tran1 begin tran tran2 insert into table1 select 1, "Ky-Ky" rollnack tran tran2

← →
uuuu (2003-07-09 10:47) [5]

Сделай два коннекшена к одной базе. Откаты в одном будешь фиксировать в другом. и во втором естественно не будет откатов.

← →
Max Zyuzin (2003-07-09 11:28) [6]

>LAMER Pro © (09.07.03 10:26)
Ты читаешь не ту статью :) транзакции должны быть не вложенеми а параллено идти не завсия друг от друга

← →
LAMER Pro (2003-07-09 17:17) [7]

to Fay

Это относится и к хранимым процедурам?

CREATE PROC addEvent @uid, -- пользователь
@eid, -- событие
@oid int -- объект
AS
INSERT ...
GO

CREATE PROC Foo @i int
AS
...
ROLLBACK TRAN
GO

CREATE PROC chkDocument @oid int -- объект
AS
EXEC addEvent @@UserID, 17, @oid
BEGIN TRAN tr1
...
EXEC Foo 14
...
COMMIT TRAN tr1
GO

Как сделать здесь так, чтобы действия ХП addEvent сохранились в БД даже при "провале" Foo при выполнении chkDocument

Плз....

← →
Smashich (2003-07-09 17:21) [8]

используй триггеры

← →
LAMER Pro (2003-07-09 17:27) [9]

to Smashich
триггер если вставить, обновить...
а если кто-то ломится читать и мне нужно знать когда это было, с какого компа и т.п.?

← →
Smashich (2003-07-09 17:38) [10]

если ломиться читать то зачем тебе транзакции? потдтверждение чтения это я пока тока в The Bat! видел;)
вообще то маловато данных о том как ты хочешь логи вести, поэтому посоветовать тебе что то кроме выполнения паралельных, а не вложенных транзакций не знаю что. тоесть после тада уже выполнения транзакции основной ызывай транзакцию которая в лог запишет чего надо

← →
handra (2003-07-09 17:39) [11]

Пример из BOL:
BEGIN TRANSACTION royaltychange UPDATE titleauthor SET royaltyper = 65 FROM titleauthor, titles WHERE royaltyper = 75 AND titleauthor.title_id = titles.title_id AND title = "The Gourmet Microwave" UPDATE titleauthor SET royaltyper = 35 FROM titleauthor, titles WHERE royaltyper = 25 AND titleauthor.title_id = titles.title_id AND title = "The Gourmet Microwave" SAVE TRANSACTION percentchanged /* After having updated the royaltyper entries for the two authors, the user inserts the savepoint percentchanged, and then determines how a 10-percent increase in the book"s price would affect the authors" royalty earnings. */ UPDATE titles SET price = price * 1.1 WHERE title = "The Gourmet Microwave" SELECT (price * royalty * ytd_sales) * royaltyper FROM titles, titleauthor WHERE title = "The Gourmet Microwave" AND titles.title_id = titleauthor.title_id /* The transaction is rolled back to the savepoint with the ROLLBACK TRANSACTION statement. */ ROLLBACK TRANSACTION percentchanged COMMIT TRANSACTION /* End of royaltychange. */

Думаю все ясно...

← →
LAMER Pro (2003-07-09 18:19) [12]

to handra
спасибо, сейчас буду разбираться.

Одновременно ко всем вопрос-идея!
Можно ли с сервера писать текстовый файл логов?

← →
Smashich (2003-07-09 18:21) [13]

через UDF(не помню как они у сиквела именуются) можна

← →
LAMER Pro (2003-07-09 18:25) [14]

to handra
нет, всё-таки меня не понимают в этом мире...
это линейное выполнение транзакции с откатом до save point (точки сохранения). Вобщем, придётся отложить в сторону "грифель" и пробовать, пробовать, пробовать самому.
Блин, ещё бы этот SQL знать... каждую инструкцию придётся смотреть в BOL.

← →
LAMER Pro (2003-07-09 19:10) [15]

to Smashich
UDF-User Define Function - функции определяемые пользователем, следовательно это не встроенная возможность - писать свою DLL, регистрировать (опять как?) в SQL, потом пользоваться?
Или разработчики Microsoft сами понасоздавали UDF?
По-моему как-то проще должно быть.

← →
Fay (2003-07-09 19:12) [16]

2handra
И чем это лучше?

← →
Тих (2003-07-09 22:14) [17]

>Можно ли с сервера писать текстовый файл логов?
Можно, например
1) Через СОМ. sp_OACreate и пр. (см. в Books Online) - создаешь ком-объектик Scripting.FileSystemObject, через него - текстовый файл - и пишешь. При таком раскладе есть грабли с многопроцессорными серверами - не пройдет.

2) Приделать Linked Server через ODBC-провайдер для текстовых файлов, прилинковать его через вьюшку, навроде create view viewLog as select top 1 * from openquery(LOG_TXT, "select * from log.txt"), инсертить логи в эту вьюшку. Тоже не без граблей - на время инсерта в этот файл он лочится эксклюзивно.

3) Немножко изучить API MSSQL и в самом деле написать свою dll, см. add_addextendedproc, Creating Extended Stored Procedures.

← →
KSergey (2003-07-10 08:51) [18]

К стати, по поводу Extended Stored Procedures - есть пример на дельфи, на sql.ru
Там по поводу нотифицирования клиентских приложений, но во всяком случае каркасик можно стырить.

← →
LAMER Pro (2003-07-10 12:54) [19]

to Тих
> 1) Через СОМ. sp_OACreate и пр. (см. в Books Online) - создаешь ком-объектик Scripting.FileSystemObject, через него - текстовый файл - и пишешь. При таком раскладе есть грабли с многопроцессорными серверами - не пройдет.

Это через MTS делается или внутри SQL-сервера?
В чём будут проблемы на многопроцессорных серверах?
Где можно ознакомиться с "грабельками"? - может быть при задействовании такой функциональности, которая мне ненужна?

> 2) Приделать Linked Server через ODBC-провайдер для текстовых файлов, прилинковать его через вьюшку, навроде create view viewLog as select top 1 * from openquery(LOG_TXT, "select * from log.txt"), инсертить логи в эту вьюшку. Тоже не без граблей - на время инсерта в этот файл он лочится эксклюзивно.

Объясните, пожалуйста, что такое Linked Server, где его "приделывать" на стороне клиента или сервера и как?
Не получится, что при росте лога будет расти время его открытия (ведь текстовый файл - это последовательный доступ)?
Не станет ли критичным ограничение ODBC: "При работе с технологией ODBC пользователь не имеет возможности явно определить начало транзакции, т.к. эта транзакция поддерживает только неявное и автоматическое определение транзакции. При использовании технологий OLE DB и ADO явное определение транзакции разрешено."

3) Немножко изучить API MSSQL и в самом деле написать свою dll, см. add_addextendedproc, Creating Extended Stored Procedures.

Наверное самый надёжный и приемлемый вариант, кроме этого можно использовать не текстовый, а структурированный файл - меньше места, быстрее анализ.
В дальнейшем эти же знания могут помочь решать тривиальные задачи, которые делаются через п%пу в SQL: анализ текстов.

Но всё равно, хочу разобраться с явным и неявным определением транзакций и действиях при откате. Истина где-то рядом...

Спасибо за обстоятельный подход.

← →
LAMER Pro (2003-07-10 13:09) [20]

to Тих

Сам вижу, что по первому вопросу глупость сморозил ;) - заглянул в BOL.

Следует читать так:

> 1) Через СОМ. sp_OACreate и пр. (см. в Books Online) - создаешь ком-объектик Scripting.FileSystemObject, через него - текстовый файл - и пишешь. При таком раскладе есть грабли с многопроцессорными серверами - не пройдет.

В чём будут проблемы на многопроцессорных серверах?
Где можно ознакомиться с "грабельками"? - может быть при задействовании такой функциональности, которая мне ненужна?

← →
DeMoN_Astra (2003-07-10 19:27) [21]

> кроме этого можно использовать не текстовый, а структурированный
> файл - меньше места, быстрее анализ

А гнать лог в бд уже нельзя?

← →
АлексейК (2003-07-11 04:58) [22]

XP в MSSQL не есть одна тразакция, если только она сама не выполняется из XP. Следовательно команды изменения данных в XP будут осуществлтся в разных независимых транзакциях и откат одной не означает отката другой.

← →
jocko (2003-07-11 10:02) [23]

По поводу лога неудачных выполнений: я бы написал процедурку ведения логов с параметрами, и вызвал бы ее но не напрямую внутри транзакции, т.е. exec..., а через sp_executesql (см. F1),
в первом случае вложенные процедуры (транзакции) могут откатываться, во втором нет.

← →
SergSuper (2003-07-11 10:41) [24]

в MSSQL есть одна тразакция на весь bath, вложенных транзакций нет.

если у вас в bath-е объявлена транзакция, потом вызывается пять процедур, а в 6-й будет rollback - результат выполнения предудыщих 5-ти процедур откатится тоже. Вложенность влияет только будет ли делаться commit или просто уменьшится счетчик вложенности, rollback же делается сразу с любого уровня.
Так что АлексейК не прав, предложение jocko не спасает.

В это легко убедиться на простеньком скрипте

create table #t(i int, c varchar(9)) insert #t select 1,"" union select 2,"" union select 3,"" union select 4,"" union select 5,"" go create proc #p1 as update #t set c="ss" where i=1 go create proc #p2 as update #t set c="ww" where i=2 go create proc #p3 as update #t set c="ww" where i=3 rollback tran go create proc #p4 as begin tran update #t set c="ww" where i=4 commit tran go begin tran update #t set c="ww" where i=4 exec ("update #t set c=""ww"" where i=5") exec #p1 exec #p2 exec #p4 select * from #t exec #p3 commit tran --rollback tran select * from #t

← →
jocko (2003-07-11 10:49) [25]

2SergSuper
А ты попробуй.
SET @SQLString = N"exec ..."
sp_executesql @SQLString
только для логов ты специально процедурку напиши, я на этом спотыкался уже...

← →
Smashich (2003-07-11 11:22) [26]

LAMER Pro © (10.07.03 12:54)
> Но всё равно, хочу разобраться с явным и неявным определением транзакций и действиях при откате. Истина где-то рядом...

почитать бы литературку? форум это все таки не курсы повышения квалификации...

насчет
>UDF-User Define Function - в сиквеле Extended Stored Procedures:) ничего особо сложного в этом нет. и это имхо одно их гудсвенных решений проблемы.

← →
SergSuper (2003-07-11 12:32) [27]

> jocko

Попробовал
Теперь ты попробуй

В принципе можно написать свою расширенную процедуру(которая xp_ и не на T-SQL), брать там новый коннект и чего-то делать. А так в одном коннекте роллбэк убивает сразу всю транзакцию. Т.к. sp_executesql работает в том же коннекте (иначе было бы не видно временных таблиц) то и его действия тоже убиваются.

> Smashich

Может я чего-то не понял, но в MSSQL User Define Function и Extended Stored Procedures - это совершенно разные вещи, не путайте с InterBase

← →
jocko (2003-07-11 13:33) [28]

был не прав :(

← →
LAMER Pro (2003-07-11 14:03) [29]

to Smashich © (11.07.03 11:22)
>форум это все таки не курсы повышения квалификации...

Если я спросил элементарщину, то где Ваш ответ?

Кстати, можете пояснить Ваш "ответ" (с учётом моего условия в вопросе - используется только ХП):
>Smashich © (09.07.03 17:38)
если ломиться читать то зачем тебе транзакции? потдтверждение чтения это я пока тока в The Bat! видел;)
вообще то маловато данных о том как ты хочешь логи вести, поэтому посоветовать тебе что то кроме выполнения ПАРАЛЛЕЛЬНЫХ, а не вложенных транзакций не знаю что. тоесть после тада уже выполнения транзакции основной ызывай транзакцию которая в лог запишет чего надо

Особенно попрошу подробно рассказать про ПАРАЛЛЕЛЬНЫЕ транзакции в ХП и какими операторами можно задавать ПОСЛЕДОВАТЕЛЬНОЕ или ПАРАЛЛЕЛЬНОЕ их выполнение в SQL.

← →
LAMER Pro (2003-07-11 14:22) [30]

to АлексейК (11.07.03 04:58)

>XP в MSSQL не есть одна тразакция, если только она сама не выполняется из XP. Следовательно команды изменения данных в XP будут осуществлтся в разных независимых транзакциях и откат одной не означает отката другой.

Я понимаю работу с ХП следующим образом: при запуске ХП с клиента на сервере АВТОМАТИЧЕСКИ запускается транзакция, следовательно все транзакции, объявленные явно или запуски любых ХП будут вложенными. При ROLLBACK"е происходит откат до самого верхнего уровня транзакции, следовательно ВСЕ транзакции (они ведь вложены в транзакцию верхнего уровня, оформленную как ХП), даже завершившиеся COMMIT"ом должны откатиться. Это приемлемое решение, если мы хотим зафиксировать СВЕРШИВШИЙСЯ ФАКТ, но если мы хотим зафиксировать НАМЕРЕНИЕ (например попытку прочитать документ, к которому доступ запрещён) и транзакция будет прервана, то лог откатится и событие будет потеряно.

Вопрос в том как сделать, чтобы при запуске ХП с клиента на сервере автоматически не запускалась транзакция, чтобы транзакции, объявляемые в ХП явным образом были самым верхним уровнем, ясно, что вызовы процедур внутри их будут вложенными, но это уже не важно - здесь бы COMMIT сработал как надо.
Я не вижу такой возможности, а она очччень нужна.

← →
SergSuper (2003-07-11 14:47) [31]

> LAMER Pro

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

У Вас 2 пути:

1. Написать Extended Stored Procedures, которая будет брать еще коннект и записывать параллельно.

2. Писать в EventLog с помощью процедуры xp_logevent.

1-й путь требует хорошего знания архитектуры MS SQL и черват возможностями подвешивать сервер напрочь

2-й путь намного проще и безобидней, но сообщения Вы будете видеть не в таблицах, а в стандартном просмоторщике событий.

Я бы советовал вообще отказаться от этой идеи, результат не будет стоит затраченного труда.

И кстати обычно меньше всего хочется объяснять элементарщину.

← →
SergSuper (2003-07-11 15:04) [32]

Я понимаю работу с ХП следующим образом: при запуске ХП с клиента на сервере АВТОМАТИЧЕСКИ запускается транзакция

Не правильно. Если установлено SET IMPLICIT_TRANSACTIONS ON, то да, так оно будет. Но обычно это редко устанавливается.
Неявная транзакция запускается когда выполняются команды модификации базы(insert, udpate, delete). Т.е. внутри триггера мывсегда будем в открытой транзакции. При запуске процедуры неявная транзакция не открывается.

Опять же можно проверить скриптом
create table #t(i int) go create proc #p as insert #t select 1 begin tran insert #t select 2 go insert #t select 3 exec #p rollback tran select * from #t
в таблице останутся 1 и 3, т.е. процедура вставит 1, затем начнет транзакцию, вставит 2, которая откатится после процедуры.

← →
Dsgnr (2003-07-12 03:41) [33]

Поделюсь собственным опытом. Достаточно много работал над учетными системами. Для аудита использовал разные варианты. Для себя сделал четкий вывод - результаты аудита НЕ ДОЛЖНЫ БЫТЬ В БД. В сравнении с записью в файл никаких преимуществ это не имеет, кроме спорного вопроса об удобстве анализа. Зато возникает большое количество проблем (о которых много в этом треде сказано), которые, как правило, решаются нездоровыми извращениями.
В свете этого оптимальный подход для сиквела - Extended stored proc с сохранением информации в файл.

← →
АлексейК (2003-07-14 05:11) [34]

LAMER Pro © (11.07.03 14:22)
to АлексейК (11.07.03 04:58)

>XP в MSSQL не есть одна тразакция, если только она сама не выполняется из XP. Следовательно команды изменения данных в XP будут осуществлтся в разных независимых транзакциях и откат одной не означает отката другой.

Я понимаю работу с ХП следующим образом: при запуске ХП с клиента на сервере АВТОМАТИЧЕСКИ запускается транзакция, следовательно все транзакции, объявленные явно или запуски любых ХП будут вложенными. При ROLLBACK"е происходит откат до самого верхнего уровня транзакции, следовательно ВСЕ транзакции (они ведь вложены в транзакцию верхнего уровня, оформленную как ХП), даже завершившиеся COMMIT"ом должны откатиться. Это приемлемое решение, если мы хотим зафиксировать СВЕРШИВШИЙСЯ ФАКТ, но если мы хотим зафиксировать НАМЕРЕНИЕ (например попытку прочитать документ, к которому доступ запрещён) и транзакция будет прервана, то лог откатится и событие будет потеряно.

По умолчанию в MSSQL стоит автоматический режим тразакций. то есть при выполнении команды тразакция начинается, как команда выполнятся транзакция заканчивается (фиксированием или откатом).
create procedure test
as
/*началасть тразакция*/
insert into....
select .. from .....
/*закончилась*/
/*началасть тразакция*/
update ... set ... where ....
/*закончилась*/
Команды выполнятся в разных тразакциях, если только это ХП не запустить командой exec из другой хранимой процедуры.
Но можно и явно обявлять тразакции (режим явного определения транзакций)
create procedure test
as
declare @er_result int
begin tran /*начали тразакцию*/
insert into....
select .. from .....
set @er_result:=@@ERROR
update ... set ... where ....
set @er_result:=@er_result+@@ERROR
if @er_result=0
commit tran /*закончилась*/
else
rollback tran
Вот в этом случае две комнады выполнятся в одной транзакции.

Есть также режим неявного использования тразакций. С началом нового соединения открывается тразация, но после очередного выполнения команды не происходит фиксирования тразакции. Посльзователь должен сам явно фиксировать или откатывать транзакции, после чего открывается следующая транзакция. Но система пытается атоматически выполнить фиксирование тразакции, при одном из следующих действий (ater table, create, deletem drop, fetch, grant, insert, open, revoke, select, truncate table, update). Хочу заметить что режим неявного использования тразакция и автоматического не одно и тоже.

← →
roottim (2003-07-14 10:47) [35]

я не специалист по мсскл... но мысли выскажу...
на самом деле можно вести лог в базе через хп или триггеры, но только по факту событий... что значит по комиту.
если дейстыия были отменены, значит ничего и небыло.. что тут плохого...
ну если уж действительно необходима такая слежка за пользователем и за его транзакцией, то могу предложить...
но немогу сказать есть ли.... и работает ли такое в мсскл.
в оракле существует, всем известная, посылка сообщений... (в др бд тоже)..
но есть 2 вида сообщений alert и pipe ... отличие в том что 1-й посылается по комиту тразакции а второй асинхронно (как послали так и ушло)...
вот если есть такой мессаг в мсскл... то остается написать некий сервис (прогу).. которая запускается на сервере ловит сообщения и пишет их куда надо.. (хоть в базу, хоть в файл )

вот такое вот кино как мы делали кино :)

Как часть транзакции сделать безоткатной? Найти похожие ветки