Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Транзакции: закрыть внутри процедуры  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 3459
Почему получаю ошибку в таком случае:
процедура открывает транзакцию, вызывает вторую, которая закрывает внутри.
Хотелось сделать так: большая процедура отрабатывает и в конце вызывается такой "чистильщик", которая кроме всего закрывает, если остались открытые

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ptest]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[ptest] AS'
END
GRANT EXECUTE on [dbo].[ptest] to public;
GO
ALTER  PROCEDURE [dbo].[ptest]
@parrollback bit = 0
AS
BEGIN
SET NOCOUNT ON
select @@TRANCOUNT as '@@TRANCOUNT:[ptest] '
if @parrollback is not null and @parrollback>0
	if @@TRANCOUNT>0 rollback tran;
END
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[pcaller]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[pcaller] AS'
END
GRANT EXECUTE on [dbo].[pcaller] to public;
GO
ALTER  PROCEDURE [dbo].[pcaller]
AS
BEGIN
SET NOCOUNT ON
begin tran
select @@TRANCOUNT as '@@TRANCOUNT: before [ptest]'
exec ptest 1
select @@TRANCOUNT as '@@TRANCOUNT: after [ptest] '
if @@TRANCOUNT>0 rollback tran;
END
GO
-------------

exec pcaller 


IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ptest]') AND type in (N'P', N'PC'))
	drop proc pcaller
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ptest]') AND type in (N'P', N'PC'))
	drop proc ptest
27 ноя 19, 12:16    [22026513]     Ответить | Цитировать Сообщить модератору
 Re: Транзакции: закрыть внутри процедуры  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7399
Ролг Хупин,

непонятно - вторая процедура закрывает транзакцию, без её открытия? Почему это происходит?
27 ноя 19, 12:53    [22026574]     Ответить | Цитировать Сообщить модератору
 Re: Транзакции: закрыть внутри процедуры  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 3459
Владислав Колосов
Ролг Хупин,

непонятно - вторая процедура закрывает транзакцию, без её открытия? Почему это происходит?


она по идее для того и предназначена
в вызывающей процедуре открывается транзакция, что-то делается, может обломаться и вторая проверяет и закрывает, если остались открытые.
Реально вторая процедура вызывается в
begin try
...
-- тут вызвается несколько процедур, одна из которых может выскочить с ошибкой
end try
begin catch
exec ptest,1
end catch
27 ноя 19, 13:26    [22026619]     Ответить | Цитировать Сообщить модератору
 Re: Транзакции: закрыть внутри процедуры  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 3459
и ошибка
Msg 266, Level 16, State 2, Procedure ptest, Line 0 [Batch Start Line 34]
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.

Вопрос: почему нельзя так сделать? во второй процедуре закрыть транзакции, точнее откатить?
27 ноя 19, 13:27    [22026622]     Ответить | Цитировать Сообщить модератору
 Re: Транзакции: закрыть внутри процедуры  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
Ролг Хупин,

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

Как пример: http://rusanu.com/2009/06/11/exception-handling-and-nested-transactions/

Вот ответ от человека, который называет себя "Technical Architect working at the Microsoft Technology Center":

https://dba.stackexchange.com/questions/212115/handling-transaction-count-after-execute-indicates-a-mismatching-number-of-begi
Performing a ROLLBACK in a stored procedure that didn't start the transaction is also an error, and will generate this message.
27 ноя 19, 14:24    [22026709]     Ответить | Цитировать Сообщить модератору
 Re: Транзакции: закрыть внутри процедуры  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 3459
Minamoto
Ролг Хупин,

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

Как пример: http://rusanu.com/2009/06/11/exception-handling-and-nested-transactions/

Вот ответ от человека, который называет себя "Technical Architect working at the Microsoft Technology Center":

https://dba.stackexchange.com/questions/212115/handling-transaction-count-after-execute-indicates-a-mismatching-number-of-begi
Performing a ROLLBACK in a stored procedure that didn't start the transaction is also an error, and will generate this message.


про взрыв мозга - согласен, но, скажем, вызывающая процедура вызвает 5 разных, одна из которых генерирует ошибку.
Я вижу в таком случае - на выходе вызывающей остается незакрытая транзакция.
Вот и подумал сделать процедуру типа ptest,
но хотелось бы понять - почему ошибка возникает?
27 ноя 19, 14:33    [22026717]     Ответить | Цитировать Сообщить модератору
 Re: Транзакции: закрыть внутри процедуры  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
Ролг Хупин

Я вижу в таком случае - на выходе вызывающей остается незакрытая транзакция.

а не надо такое видеть.
во всех внутренних в catch -- throw
во внешней в catch -- rollback
27 ноя 19, 14:41    [22026724]     Ответить | Цитировать Сообщить модератору
 Re: Транзакции: закрыть внутри процедуры  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 3459
Yasha123
Ролг Хупин

Я вижу в таком случае - на выходе вызывающей остается незакрытая транзакция.

а не надо такое видеть.
во всех внутренних в catch -- throw
во внешней в catch -- rollback


так работает, но тяга к знаниям закрутила, хотелось понять.
внутренние процедуры писаны не мной, туда Заратустра не позволяет вмешиваться.
27 ноя 19, 14:46    [22026727]     Ответить | Цитировать Сообщить модератору
 Re: Транзакции: закрыть внутри процедуры  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1172
Ролг Хупин,

это поведение by design

описанно в справке по rollback.

можете поиграться:
create or alter proc usp_test1
as 
begin transaction
go
create or alter proc usp_test2
as
if xact_state() <> 0 rollback transaction
go

select @@TRANCOUNT
exec usp_test1
select @@TRANCOUNT
exec usp_test2
select @@TRANCOUNT


add: хотя на самом деле не совсем точно, потому что в справке написано что "This message does not affect subsequent processing"
а по факту транзакция переходит в uncommitable state

Сообщение было отредактировано: 27 ноя 19, 15:55
27 ноя 19, 15:50    [22026791]     Ответить | Цитировать Сообщить модератору
 Re: Транзакции: закрыть внутри процедуры  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30766
Ролг Хупин
но хотелось бы понять - почему ошибка возникает?
Потому что МС установил такое правило.
27 ноя 19, 17:31    [22026888]     Ответить | Цитировать Сообщить модератору
 Re: Транзакции: закрыть внутри процедуры  [new]
aleks222
Member

Откуда:
Сообщений: 851
Yasha123
Ролг Хупин

Я вижу в таком случае - на выходе вызывающей остается незакрытая транзакция.

а не надо такое видеть.
во всех внутренних в catch -- throw
во внешней в catch -- rollback


Садись - неуд.

set xact_abort on;
27 ноя 19, 18:01    [22026913]     Ответить | Цитировать Сообщить модератору
 Re: Транзакции: закрыть внутри процедуры  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
set xact_abort on;

begin tran 
   create table dbo.test(id int);
   drop table dbo.nonexistingtable;
commit;

select *
from dbo.test;

что-то нету роллбэка, а xact_abort on есть
27 ноя 19, 18:42    [22026956]     Ответить | Цитировать Сообщить модератору
 Re: Транзакции: закрыть внутри процедуры  [new]
aleks222
Member

Откуда:
Сообщений: 851
Yasha123
set xact_abort on;

begin tran 
   create table dbo.test(id int);
   drop table dbo.nonexistingtable;
commit;

select *
from dbo.test;

что-то нету роллбэка, а xact_abort on есть


Суслика нет, а он есть.

Фсе работает. Там где надо.
28 ноя 19, 06:37    [22027172]     Ответить | Цитировать Сообщить модератору
 Re: Транзакции: закрыть внутри процедуры  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
aleks222

Суслика нет, а он есть.

и имя ему алекс-двоечник.
роллбэка правда так и нет в моем примере

aleks222

Фсе работает. Там где надо.

еще бы.
надо в паре с try..catch.
который суслик не захотел признать
28 ноя 19, 10:51    [22027363]     Ответить | Цитировать Сообщить модератору
 Re: Транзакции: закрыть внутри процедуры  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 3459
aleks222
Yasha123
set xact_abort on;

begin tran 
   create table dbo.test(id int);
   drop table dbo.nonexistingtable;
commit;

select *
from dbo.test;

что-то нету роллбэка, а xact_abort on есть


Суслика нет, а он есть.

Фсе работает. Там где надо.


в моем примере (см. первый мєсиджь) не работает
28 ноя 19, 11:17    [22027401]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить