December 26, 2015 at 12:59 pm
I'd like to write to a log table, while within a transaction, and not have it affected by a rollback. Is there any way to "pause" a transaction, so some actions aren't part of the transaction?
December 26, 2015 at 1:13 pm
You could write whatever you want to write to the logging table to a table variable within the transaction, and then write from the table variable to the logging table outside the transaction.
Table variables are not affected by transaction rollbacks (see "Limitations and Restrictions" at https://msdn.microsoft.com/en-us/library/ms175010.aspx).
Cheers!
December 26, 2015 at 1:16 pm
That's an interesting approach, although when dealing with nested sprocs and nested transactions, can get a little complex, since AFAIK, you can only pass a table variable as readonly.
December 26, 2015 at 1:57 pm
Yes, nesting does make things a bit more complicated, as you have to be sure to use that logging technique at each level if each level has something you need to log even if a ROLLBACK occurs further down in the nesting levels.
The nice thing is that you can just implement separately at each level in a uniform way, because table variables' scope is restricted to the level at which they're created. Because of that, you don't have to worry about naming conflicts.
It's still possible to do, and not really different from doing it with a single scope; it's just more tedious.
Just a quick example of how that could work:
CREATE TABLE some_table (ID int);
CREATE TABLE some_log (ID int, date_modified datetime);
GO
CREATE PROCEDURE parent_call
AS
DECLARE @id_logging TABLE (id int, date_modified datetime);
DECLARE @id int=1;
BEGIN TRANSACTION;
INSERT INTO @id_logging (id, date_modified)
VALUES (@id, GETDATE());
INSERT INTO some_table VALUES (@id);
EXECUTE child_call 2;
COMMIT
INSERT INTO some_log (id, date_modified)
SELECT id, date_modified FROM @id_logging;
GO
CREATE PROCEDURE child_call
@id int
AS
DECLARE @id_logging TABLE (id int, date_modified datetime);
BEGIN TRANSACTION
INSERT INTO @id_logging (id, date_modified)
VALUES (@id, GETDATE());
INSERT INTO some_table VALUES (@id);
ROLLBACK
INSERT INTO some_log (id, date_modified)
SELECT id, date_modified FROM @id_logging;
GO
EXECUTE parent_call;
SELECT * FROM some_table;
SELECT * FROM some_log ORDER BY date_modified desc;
DROP TABLE some_table, some_log;
DROP PROCEDURE child_call, parent_call;?
I'm not aware of any other way of persisting logging information despite a ROLLBACK without pushing it to a variable, but that's not to say it doesn't exist, of course 🙂
Cheers!
December 26, 2015 at 3:14 pm
Here is a quick demonstration of my preferred pattern, more or less the same as Jacob's
😎
USE tempdb;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'dbo.TBL_TEST_TRAN') IS NOT NULL DROP TABLE dbo.TBL_TEST_TRAN;
IF OBJECT_ID(N'dbo.TBL_TEST_LOG') IS NOT NULL DROP TABLE dbo.TBL_TEST_LOG;
CREATE TABLE dbo.TBL_TEST_TRAN
(
TRAN_VALUE INT NOT NULL CONSTRAINT CNSTR_DBO_TBLE_TEST_TRAN_TRAN_VALUE_LT_100 CHECK (TRAN_VALUE < 100)
,TRAN_COUNT INT NOT NULL CONSTRAINT DFLT_DBO_TBLE_TEST_TRAN_TRAN_COUNT DEFAULT (@@TRANCOUNT)
);
CREATE TABLE dbo.TBL_TEST_LOG
(
TRAN_VALUE INT NOT NULL
,TRAN_COUNT INT NOT NULL CONSTRAINT DFLT_DBO_TBL_TEST_LOG_TRAN_COUNT DEFAULT (@@TRANCOUNT)
,TRAN_DATE DATETIME2(7) NOT NULL CONSTRAINT DFLT_DBO_TBL_TEST_LOG_TRAN_DATE DEFAULT (SYSDATETIME())
);
GO
DECLARE @TRANTEMP TABLE
(
TRAN_VALUE INT NOT NULL
,TRAN_COUNT INT NOT NULL DEFAULT (@@TRANCOUNT)
,TRAN_DATE DATETIME2(7) NOT NULL DEFAULT (SYSDATETIME())
);
BEGIN TRY
INSERT INTO @TRANTEMP(TRAN_VALUE)
VALUES (99)
BEGIN TRAN XXXX
INSERT INTO dbo.TBL_TEST_TRAN(TRAN_VALUE)
VALUES (99)
COMMIT TRAN XXXX
BEGIN TRAN XYZ
INSERT INTO dbo.TBL_TEST_LOG(TRAN_COUNT,TRAN_DATE,TRAN_VALUE)
SELECT TRAN_COUNT,TRAN_DATE,TRAN_VALUE FROM @TRANTEMP
COMMIT TRAN XYZ
END TRY
BEGIN CATCH
ROLLBACK TRAN XXXX
BEGIN TRAN XYZ
INSERT INTO dbo.TBL_TEST_LOG(TRAN_COUNT,TRAN_DATE,TRAN_VALUE)
SELECT TRAN_COUNT,TRAN_DATE,TRAN_VALUE FROM @TRANTEMP
COMMIT TRAN XYZ
END CATCH
DELETE FROM @TRANTEMP;
SELECT * FROM TBL_TEST_LOG
BEGIN TRY
INSERT INTO @TRANTEMP(TRAN_VALUE)
VALUES (199);
BEGIN TRAN XXXX;
INSERT INTO dbo.TBL_TEST_TRAN(TRAN_VALUE)
VALUES (199);
COMMIT TRAN XXXX;
BEGIN TRAN XYZ;
INSERT INTO dbo.TBL_TEST_LOG(TRAN_COUNT,TRAN_DATE,TRAN_VALUE)
SELECT TRAN_COUNT,TRAN_DATE,TRAN_VALUE FROM @TRANTEMP;
COMMIT TRAN XYZ;
END TRY
BEGIN CATCH
ROLLBACK TRAN XXXX;
BEGIN TRAN XYZ;
INSERT INTO dbo.TBL_TEST_LOG(TRAN_COUNT,TRAN_DATE,TRAN_VALUE)
SELECT TRAN_COUNT,TRAN_DATE,TRAN_VALUE FROM @TRANTEMP;
COMMIT TRAN XYZ;
END CATCH
SELECT * FROM dbo.TBL_TEST_LOG;
SELECT * FROM dbo.TBL_TEST_TRAN;
December 27, 2015 at 10:55 am
I don't think either of those solutions would work, because if a calling sproc were to rollback, any write from the table variable to the log table would be rolled back. However, it does give me the idea of how it needs to be handled, although it's a PITA.
I like the idea of writing into a table variable. I'm thinking that before any sproc returns, if @@TRANCOUNT = 0, it should write that table variable to the real log table, otherwise it should write it into a temp table. Any time a sproc calls another, the calling sproc should first clear out that temp table, and when that call returns, the calling sproc should add the temp table to its table variable.
December 27, 2015 at 10:58 pm
If this is for a batch job, then make it easy on yourselves. Set the job up to capture any output from the run to a file. I guarantee that nothing will roll that back. Not even a server reboot.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 27, 2015 at 11:48 pm
SQL Server is fully ACID (Atomicity, Consistency, Isolation, Durability), if you need functionality outside the ACID then use Jeff's suggestion and output the log to a file, that temp table idea wouldn't work either as temporary tables are fully in the scope of the transaction and a rollback affects them the same as normal tables.
😎
December 28, 2015 at 12:22 am
Hi Eirikur,
I don't think you understood my reply. When there are nested sprocs, writing the table variable to the log file can only occur by the outer-most calling sproc, since any other attempts could be rolled back.
Since table variables can only be readonly parameters, the only mechanism for passing them back to the calling sproc is through a result set, or through a table or temp table (which can be rolled back). So I'm proposing that a temp table be used only to pass whatever's in that table variable back to the calling sproc, to be immediately read into the calling sproc's table variables...
OuterSproc:
DECLARE @logInfo TABLE ...
...do stuff, including adding to @logInfo...
DELETE FROM #LogInfoTempTable
EXECUTE InnerSproc ...
INSERT INTO @logInfo ... SELECT * FROM #LogInfoTempTable
...do stuff, including adding to @logInfo...
COMMIT (or even ROLLBACK)
INSERT INTO RealLogTable ... SELECT * FROM @logInfo
InnerSproc:
DECLARE @logInfo TABLE ...
...do stuff, including adding to @logInfo...
INSERT INTO #LogInfoTempTable ... SELECT * FROM @logInfo
RETURN 0
Above, I wanted to show functionality, so I left out all the nested transaction ugliness. Still the idea is that even if there's a rollback to a saved (inner) transaction in the inner sproc, whatever the inner sproc added to its @logInfo will get added to the calling sproc's @logInfo, to eventually be written to the real log table.
December 28, 2015 at 3:04 am
"Rollback to a saved inner transaction"
Careful there. SQL doesn't actually have nested transactions. It's a syntactical lie. If there's a rollback inside nested transactions, everything is rolled back, not to an inner transaction.
http://sqlinthewild.co.za/index.php/2015/11/03/a-mess-of-nested-transactions/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 28, 2015 at 11:53 am
Hi Gail,
Yeah, I only call them "nested transactions". I'm doing the single transaction thing with SAVE TRANSACTIONs in the called sprocs (depending on whether @@TRANCOUNT is 0), but thanks for the warning.
December 29, 2015 at 2:25 am
Eirikur Eiriksson (12/27/2015)
if you need functionality outside the ACID then use Jeff's suggestion and output the log to a file,
While I totally agree with a file approach I must note that a file is just another type of a remote server. If you save your log records on another instance of SQL Server (or any other DB engine) using an openquery call than they won't be affected by a rollback.
_____________
Code for TallyGenerator
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply