Mfcttrf

I've been developing T-SQL for several years and am always digging in further, continuing to learn all I can about all aspects of the language. I recently started working at a new company and have received what I think is an odd suggestion regarding transactions. Don't ever use them. Instead, use a workaround that simulates a transaction. This is coming from our DBA who works in one database with a lot of transactions and subsequently, a lot of blocking. The database I primarily work in does not suffer from this issue and I see transactions have been used in the past.

I understand that blocking is expected with transactions as it's in their nature to do so and if you can get away without using one, by all means do it. But I have many occasions where each statement MUST execute successfully. If one fails they all must fail to commit.

I’ve always kept the scope of my transactions as narrow as possible, always used in conjunction with SET XACT_ABORT ON and always within a TRY/CATCH.

Example:

CREATE SCHEMA someschema;
GO


CREATE TABLE someschema.tableA
(id INT NOT NULL IDENTITY(1, 1) PRIMARY KEY, 
 ColA VARCHAR(10) NOT NULL
);
GO

CREATE TABLE someschema.tableB
(id INT NOT NULL IDENTITY(1, 1) PRIMARY KEY, 
 ColB VARCHAR(10) NOT NULL
); 
GO


CREATE PROCEDURE someschema.ProcedureName @ColA VARCHAR(10), 
 @ColB VARCHAR(10)
AS
SET NOCOUNT, XACT_ABORT ON;
BEGIN
BEGIN TRY
 BEGIN TRANSACTION;

 INSERT INTO someschema.tableA(ColA)
 VALUES(@ColA);

 INSERT INTO someschema.tableB(ColB)
 VALUES(@ColB);

--Implement error
 SELECT 1/0 

 COMMIT TRANSACTION;
END TRY
BEGIN CATCH
 IF @@trancount > 0
 BEGIN
 ROLLBACK TRANSACTION;
 END;
 THROW;
 RETURN;
END CATCH;
END;
GO

Here is what they suggested that I do.

GO



CREATE PROCEDURE someschema.ProcedureNameNoTransaction @ColA VARCHAR(10), 
 @ColB VARCHAR(10)
AS
SET NOCOUNT ON;
BEGIN
BEGIN TRY
 DECLARE @tableAid INT;
 DECLARE @tableBid INT;

 INSERT INTO someschema.tableA(ColA)
 VALUES(@ColA);
 SET @tableAid = SCOPE_IDENTITY();

 INSERT INTO someschema.tableB(ColB)
 VALUES(@ColB);
 SET @tableBid = SCOPE_IDENTITY();

--Implement error
 SELECT 1/0 

END TRY
BEGIN CATCH
 DELETE FROM someschema.tableA
 WHERE id = @tableAid;

 DELETE FROM someschema.tableB
 WHERE id = @tableBid;

 THROW;

 RETURN;
END CATCH;
END;
GO

My question to the community is as follows. Does this make sense as a viable workaround for transactions?

My opinion from what I know about transactions and what the solution is proposing is that no, this isn’t a viable solution and introduces many points of failure.

In the suggested workaround, I see four implicit transactions occurring. The two inserts in the try and then two more transactions for the deletes in the catch. It does “undo” the inserts but without rolling back anything so nothing is actually rolled back.

This is a very basic example to demonstrate the concept they are suggesting. Some of the actual stored procedures I have been doing this in make them exhaustively long and difficult to manage because “rolling back” multiple result sets vs two parameter values in this example becomes quite complicated as you could imagine. Since "rolling back" is being done manually now, the opportunity to miss something because real.

Another issue that I think exists is for timeouts or severed connections. Does this still get rolled back? This is my understanding of why SET XACT_ABORT ON should be used so that in these cases, the transaction will roll back.

Thanks for your feedback in advance!

You can not not use transactions in SQL Server (and probably any other proper RDBMS). In the absence of explicit transaction boundaries (begin transaction ... commit) each SQL statement starts a new transaction, which is implicitly committed (or rolled back) after the statement completes (or fails).

Transaction simulation suggested by the person who presents him- or herself as your "DBA" fails to ensure three out of four requisite properties of transaction processing, because it addresses only "soft" errors and is not capable of dealing with "hard" errors, such as network disconnects, power outages, disk failures, and so on.

• Atomicity: fail. If a "hard" error occurs somewhere in the middle of your pseudo-transaction, the change will be non-atomic.




• Consistency: fail. It follows from the above that your data will be in an inconsistent state following a "hard" error.




• Isolation: fail. It is possible that a concurrent pseudo-transaction changes some of the data modified by your pseudo-transaction before yours completes.




• Durability: success. Changes you make will be durable, the database server will ensure that; this is the only thing your colleague's approach cannot screw up.

Locks are a widely used and empirically successful method to ensure ACIDity of transactions in all sorts or RDBMSes (this site being an example). I find it very unlikely that a random DBA can find a better solution to the concurrency problem than hundreds, possibly thousands of computer scientists and engineers who have been building some interesting database systems over the last, what, 50? 60 years? (I realise this is somewhat fallacious as an "appeal to authority" argument, but I'll stick to it regardless.)

In conclusion, ignore your "DBA"'s advice if you can, fight it if you have the spirit, and come back here with specific concurrency problems if they arise.

If transactions are blocking each other, above isn't a good fix to the problem.

Here's a way to help alleviate the blocking: https://www.sqlservercentral.com/articles/using-indexes-to-reduce-blocking-in-concurrent-transactions

There could be inefficient code within the transactions. Are there parts that could be moved outside of the transaction? Select statements shouldn't be inside transactions. Only the actual inserts/updates/deletes should be in there.