A very basic usages of transactions in order to ensure that only one transaction is active at a time.
use master
go
declare @trancount INT = @@TRANCOUNT
, @i INT = NULL
begin try
print 'start'
print @trancount
IF @trancount = 0
begin
BEGIN TRANSACTION
print 'trans'
print @trancount
end
print 'working'
print @trancount
-- Uncoment this line to create exception
-- set @i = 1/0
IF @trancount = 0
BEGIN
COMMIT
print 'commit'
print @trancount
END
END TRY
BEGIN CATCH
print 'rolling'
print @trancount
print ERROR_MESSAGE()
IF @trancount = 0
BEGIN
ROLLBACK
print 'roll'
print @trancount
END
END CATCH
use master
go
declare @trancount INT = @@TRANCOUNT
, @i INT = NULL
begin try
print 'start'
print @trancount
IF @trancount = 0
begin
BEGIN TRANSACTION
print 'trans'
print @trancount
end
print 'working'
print @trancount
-- Uncoment this line to create exception
-- set @i = 1/0
IF @trancount = 0
BEGIN
COMMIT
print 'commit'
print @trancount
END
END TRY
BEGIN CATCH
print 'rolling'
print @trancount
print ERROR_MESSAGE()
IF @trancount = 0
BEGIN
ROLLBACK
print 'roll'
print @trancount
END
END CATCH