Народ ! Помогите внести ясность !
Перед тем как запостить нижесказанное поискал и почитал на форуме все, что касается сабжа.
Вроде все понял, но как то уверенности нет

Ниже приводится скрипт, создающий объекты некоторой тестовой БД, особо отмечу, что ни какой сути, практического смысла
приводимая БД не несетнет (типа мужик для чего тебе все это нужно) - это просто тестовый примерчик

-- СКРИПТИК

CREATE TABLE [dbo].[SysTran] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Date] [datetime] NOT NULL ,
[ProcName] [varchar] (50) NOT NULL ,
[State] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[SysData] (
[TranID] [int] NOT NULL ,
[DataNumber] [int] NOT NULL ,
[DataString] [varchar] (8000) NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[SysTran] WITH NOCHECK ADD
CONSTRAINT [DF_SysTran_Date] DEFAULT (getdate()) FOR [Date],
CONSTRAINT [DF_SysTran_ProcName] DEFAULT ('') FOR [ProcName],
CONSTRAINT [DF_SysTran_State] DEFAULT (0) FOR [State],
CONSTRAINT [PK_SysTran] PRIMARY KEY NONCLUSTERED
(
[ID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[SysData] WITH NOCHECK ADD
CONSTRAINT [DF_SysData_DataString] DEFAULT ('') FOR [DataString]
GO

ALTER TABLE [dbo].[SysData] ADD
CONSTRAINT [FK_SysData_SysTran] FOREIGN KEY
(
[TranID]
) REFERENCES [dbo].[SysTran] (
[ID]
)
GO

SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO

CREATE PROCEDURE [Error]
@Msg varchar(12
AS
RAISERROR('Ошибка: %s',18,1,@Msg) with NOWAIT
GO

CREATE PROCEDURE [BeginTransaction]
@ProcName varchar(50) = ''
AS
set nocount on
if LTRIM(RTRIM(@ProcName))='' begin
exec Error '[BeginTransaction] Не указано имя процедуры ...'
return -1
end
-- ******************************************************************
-- Определение следующего ID типа IDENTITY
-- с заполнением дыр в диапазоне
DECLARE @TranID int
DECLARE @minidentval int
DECLARE @nextidentval int
begin transaction
SET IDENTITY_INSERT SysTran ON
SELECT @minidentval = MIN(IDENTITYCOL) FROM SysTran with (TABLOCKX)
IF @minidentval = IDENT_SEED('SysTran')
SELECT @nextidentval = MIN(IDENTITYCOL) + IDENT_INCR('SysTran')
FROM SysTran o with (TABLOCKX)
WHERE IDENTITYCOL BETWEEN IDENT_SEED('SysTran') AND 2147483647 AND
NOT EXISTS (SELECT * FROM SysTran oo with (TABLOCKX)
WHERE oo.IDENTITYCOL = o.IDENTITYCOL +
IDENT_INCR('SysTran'))
ELSE
SELECT @nextidentval = IDENT_SEED('SysTran')
set @TranID = @nextidentval
insert SysTran (ID, ProcName, State)
values (@TranID, @ProcName, 0)
if @@Error <> 0 begin
rollback transaction
exec Error '[BeginTransaction] Неизвестная ошибка ...'
return -1
end
SET IDENTITY_INSERT SysTran OFF
-- ******************************************************************
commit transaction
select TranID = @TranID
set nocount off
return @TranID
GO

CREATE PROCEDURE [CommitTransaction]
@TranID int
AS
set nocount on
if not exists(select * from SysTran where ID=@TranID) begin
exec Error '[CommitTransaction] Открытая транзакция не найдена ...'
return -1
end
declare @ProcName varchar(50),
@ExecStr varchar(50)
begin transaction
select @ProcName = ProcName from SysTran with (TABLOCKX) where ID=@TranID
set @ExecStr = @ProcName+' '+convert(varchar(10),@TranID)
exec(@ExecStr)
if @@Error <> 0 begin
rollback transaction
exec Error '[CommitTransaction] Ошибка в обработчике транзакции ...'
return -1
end
delete from SysData where TranID=@TranID
if @@Error <> 0 begin
rollback transaction
exec Error '[CommitTransaction] Неизвестная ошибка ...'
return -1
end
delete from SysTran where ID=@TranID
if @@Error <> 0 begin
rollback transaction
exec Error '[CommitTransaction] Неизвестная ошибка ...'
return -1
end
commit transaction
return 0
set nocount off
GO

CREATE PROCEDURE [RollBackTransaction]
@TranID int
AS
set nocount on
if not exists(select * from SysTran where ID=@TranID) begin
exec Error '[RollBackTransaction] Открытая транзакция не найдена ...'
return -1
end
begin transaction
delete from SysData with (TABLOCKX) where TranID=@TranID
if @@Error <> 0 begin
rollback transaction
exec Error '[RollBackTransaction] Неизвестная ошибка ...'
return -1
end
delete from SysTran with (TABLOCKX) where ID=@TranID
if @@Error <> 0 begin
rollback transaction
exec Error '[RollBackTransaction] Неизвестная ошибка ...'
return -1
end
commit transaction
return 0
set nocount off
GO

CREATE PROCEDURE [SendDataTransaction]
@TranID int,
@TranData varchar(8000)
AS
set nocount on
if not exists(select * from SysTran where ID=@TranID) begin
exec Error '[SendDataTransaction] Открытая транзакция не найдена ...'
return -1
end
declare @TranNumber int
set @TranNumber = 0
begin transaction
SELECT @TranNumber = MAX(DataNumber) FROM SysData with (TABLOCKX) WHERE TranID=@TranID
set @TranNumber=ISNULL(@TranNumber, 0)
set @TranNumber=@TranNumber+1
insert SysData (TranID, DataNumber, DataString)
values (@TranID, @TranNumber, @TranData)
if @@Error <> 0 begin
rollback transaction
exec Error '[SendDataTransaction] Неизвестная ошибка ...'
return -1
end
commit transaction
return 0
set nocount off
GO

CREATE PROCEDURE [Handler]
@TranID int
AS
select * from SysData where TranID = @TranID
GO

CREATE PROCEDURE [Test]
AS
declare @TranID int
exec @TranID = BeginTransaction 'Handler'
exec SendDataTransaction @TranID, 'String1'
exec SendDataTransaction @TranID, 'String2'
exec SendDataTransaction @TranID, 'String3'
exec CommitTransaction @TranID
GO

-- КОНЕЦ СКРИПТИКА

ЗАПУСК ТЕСТА ПРОИЗВОДИТСЯ ВЫПОЛНЕНИЕМ ПРОЦЕДУРЫ [TEST]

--

Примерчик - живой, проверенный, можно сразу в QA для запуска ...

ВОПРОСЫ:

1. Правильно ли (на нужном ли месте) расставлены операторы BEGIN TRANSACTION - COMMIT - ROLLBACK.
Я имею в виду не только операторы SELECT, но и SET IDENTITY_INSERT [xxx] ON/OFF и все прочие детали
типа IDENT_SEED('xxx'), IDENT_INCR('xxx') - ведь это тоже по сути обращения к таблице (или к схеме ?)
и Ваши соображения на эту тему.
2. Неразрывно с п.1 использование WITH (TABLOCKX, ...) - надо ли во вложенных SELECT на туже таблицу
вешать этот самый WITH (TABLOCKX, ...)
3. Правильно ли производится обработка ошибок ?
4. Как я понял оператор RAISEERROR не приводит к возникновению исключения и после его выполнения выполняются
все следующие за ним операторы, даже не смотря на наличие SET XACT_ABORT ON.
5. @@Error - используется только для чтения, так это или нет ? Для того чтобы мне вызвать исключение необходимо
вставить в код что то типа: set @a=1/0 (или подобные решения на основе нарушения DRI и т.д.) или существует все
таки некий цивилизованный метод ...
6. В примерчике, в процедуре [CommitTransaction] есть такой кусочек:

set @ExecStr = @ProcName+' '+convert(varchar(10),@TranID)
exec(@ExecStr)
if @@Error <> 0 begin
rollback transaction
exec Error '[CommitTransaction] Ошибка в обработчике транзакции ...'
return -1
end

В связи с чем @@Error может стать не 0 и как грамотно поступить в этом случае. Можно конечно вызывать типа так:

declare @Var int
set @ExecStr = 'exec @Var = '+@ProcName+' '+convert(varchar(10),@TranID)
exec(@ExecStr)

и проверять @Var, но я так не пробовал и вопрос: будет ли такое работать ?


Вроде все ...

P.S. Есть один нюанс по поводу того где и как (в каких условиях) это используется.
Клиенты (1-100шт) коннектятся к серверу приложений и последний ходит к БД.
(используется один юзер БД).

P.S.S. Народ ! Давайте последний раз и капитально разомнем это вопрос (сабж).
Я специально не включал в примерчик триггера - хотя в связке с вышесказанным
это тоже очень интересная тема, если есть силы и желание можно и на эту тему
помять.

Думаю всем будет интересны любые замечания по коду примера - эффективность, надежность и.д.
Любые мысли по поводу правильности - грамотности кода, как можно сделать по другому ...