Фиксация только определенных изменений, сделанных внутри транзакции, которые могут откатываться
Это значительное изменение по сравнению с оригинальным вопросом, делающее его более сжатым и охватывающим вопросы, поднятые в существующих ответах...
Можно ли сделать несколько изменений в нескольких таблицах внутри одной транзакции и откатить только некоторые изменения?
В приведенном ниже TSQL я НЕ хотел бы, чтобы какие-либо изменения, сделанные "myLogSP", когда-либо были отменены. Но все изменения, сделанные различными myBusinessSP, должны откатываться при необходимости.
BEGIN TRANSACTION
EXEC myLogSP
EXEC @err = myBusinessSPa
IF (@err <> 0) BEGIN ROLLBACK TRANSACTION RETURN -1 END
EXEC myLogSP
EXEC @err = myBusinessSPb
IF (@err <> 0) BEGIN ROLLBACK TRANSACTION RETURN -1 END
EXEC myLogSP
EXEC @err = myBusinessSPc
IF (@err <> 0) BEGIN ROLLBACK TRANSACTION RETURN -1 END
EXEC myLogSP
COMMIT TRANSACTION
RETURN 0
Порядок важен, myLogSP должны происходить между и после myBusinessSP (myLogSP учитывают изменения, сделанные myBusinessSP)
Также важно, чтобы все myBusinessSP происходили внутри одной транзакции, чтобы поддерживать целостность базы данных и разрешать откат всех их изменений при необходимости.
Как будто я хочу, чтобы myLogSP вели себя так, как будто они не являются частью транзакции. Это просто неудобный факт, что они оказываются внутри одного (из-за необходимости вызова между myBusinessSP).
РЕДАКТИРОВАТЬ:
Окончательный ответ - "нет", единственный вариант - изменить код. Либо использовать табличные переменные для ведения журнала (так как переменные не откатываются), либо изменить бизнес-логику на Не требовать транзакций...
7 ответов
Использование SAVEPOINT s, например
BEGIN TRANSACTION
EXEC myLogSP
SAVE TRANSACTION savepointA
EXEC @err = myBusinessSPa
IF (@err <> 0) BEGIN
ROLLBACK TRANSACTION savepointA
COMMIT
RETURN -1
END
EXEC myLogSP
SAVE TRANSACTION savepointB
EXEC @err = myBusinessSPb
IF (@err <> 0) BEGIN
ROLLBACK TRANSACTION savepointB
COMMIT
RETURN -1
END
EXEC myLogSP
SAVE TRANSACTION savepointC
EXEC @err = myBusinessSPc
IF (@err <> 0) BEGIN
ROLLBACK TRANSACTION savepointC
COMMIT
RETURN -1
END
EXEC myLogSP
COMMIT TRANSACTION
РЕДАКТИРОВАТЬ
Основываясь на информации, предоставленной до сих пор (и мое понимание ее), кажется, что вам придется перепроектировать протоколирование SP, либо использовать переменные, либо использовать файлы, либо позволить им запускаться "после факта" как следующим образом:
BEGIN TRANSACTION
SAVE TRANSACTION savepointA
EXEC @err = myBusinessSPa
IF (@err <> 0) BEGIN
ROLLBACK TRANSACTION savepointA
EXEC myLogSPA -- the call to myBusinessSPa was attempted/failed
COMMIT
RETURN -1
END
SAVE TRANSACTION savepointB
EXEC @err = myBusinessSPb
IF (@err <> 0) BEGIN
ROLLBACK TRANSACTION savepointB
EXEC myLogSPA -- the call to myBusinessSPa originally succeeded
EXEC myLogSPB -- the call to myBusinessSPb was attempted/failed
COMMIT
RETURN -1
END
SAVE TRANSACTION savepointC
EXEC @err = myBusinessSPc
IF (@err <> 0) BEGIN
ROLLBACK TRANSACTION savepointC
EXEC myLogSPA -- the call to myBusinessSPa originally succeeded
EXEC myLogSPB -- the call to myBusinessSPb originally succeeded
EXEC myLogSPC -- the call to myBusinessSPc was attempted/failed
COMMIT
RETURN -1
END
EXEC myLogSPA -- the call to myBusinessSPa succeeded
EXEC myLogSPB -- the call to myBusinessSPb succeeded
EXEC myLogSPC -- the call to myBusinessSPc succeeded
COMMIT TRANSACTION
Нам посчастливилось поместить записи журнала в переменные таблицы, а затем вставить их в реальные таблицы после фиксации или отката.
ОК, если вы не используете SQL Server 2008, попробуйте этот метод. Это грязный и обходной путь, но он должен работать. Таблица #temp и переменная таблицы должны быть установлены со структурой того, что возвращает sp.
create table #templog (fie1d1 int, field2 varchar(10))
declare @templog table (fie1d1 int, field2 varchar(10))
BEGIN TRANSACTION
insert into #templog
Exec my_proc
insert into @templog (fie1d1, field2)
select t.* from #templog t
left join @templog t2 on t.fie1d1 = t2.fie1d1 where t2.fie1d1 is null
insert into templog
values (1, 'test')
rollback tran
select * from #templog
select * from templog
select * from @templog
Вы должны в основном выпрыгнуть за пределы текущего контекста. Есть несколько способов сделать это. Один (который я никогда не пробовал) - вызвать CLR, чтобы выполнить вставку.
Возможно, лучший способ - использовать тот факт, что на переменные таблицы не влияют транзакции. Например:
CREATE TABLE dbo.Test_Transactions
(
my_string VARCHAR(20) NOT NULL
)
GO
DECLARE
@tbl TABLE (my_string VARCHAR(20) NOT NULL)
BEGIN TRANSACTION
INSERT INTO dbo.Test_Transactions (my_string) VALUES ('test point one')
INSERT INTO @tbl (my_string) VALUES ('test point two')
INSERT INTO dbo.Test_Transactions (my_string) VALUES ('test point three')
ROLLBACK TRANSACTION
INSERT INTO dbo.Test_Transactions (my_string) select my_string from @tbl
SELECT * FROM dbo.Test_Transactions
SELECT * FROM @tbl
GO
Возможно, вы могли бы поместить вставки / обновления в бизнес-таблицы в их собственную атомарную транзакцию t1 и обернуть каждую из этих транзакций в другую транзакцию t2, которая выполняет обновление таблицы журнала, и t1 (обновления бизнес-таблицы) без каких-либо откатов. Например:
BEGIN TRANSACTION t2
<insert to log>
<execute stored procedure p1>
END TRANSACTION t2
CREATE PROCEDURE p1
AS
BEGIN TRANSACTION t1
<insert to business tables>
<rollback t1 on error>
END TRANSACTION t1
Я считаю, что когда вы откатываете t1 в хранимой процедуре, это не повлияет на вызывающую транзакцию t2.
Переместите оператор BEGIN TRANSACTION в после первой вставки.
Не будет ли самый простой способ перенести вставку журнала за пределы транзакции?
У меня нет ответа для блокировки таблицы, я думаю, что у вас уже есть ответ, должна быть блокировка таблицы, потому что столбец идентификаторов может откатиться.