43

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!

61

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 come up with 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.

14

There are some errors which are so severe that the CATCH block is never entered. From the documentation

Errors that have a severity of 20 or higher that stop the SQL Server Database Engine task processing for the session. If an error occurs that has severity of 20 or higher and the database connection is not disrupted, TRY...CATCH will handle the error.

Attentions, such as client-interrupt requests or broken client connections.

When the session is ended by a system administrator by using the KILL statement.

...

Compile errors, such as syntax errors, that prevent a batch from running.

Errors that occur ... because of deferred name resolution.

A lot of these are easy to produce through dynamic SQL. Undo statements such as you've shown will not protect your data from such errors.

  • 2
    Right -- and if nothing else, the client dying while executing the code would constitute an error "so severe that the CATCH block is never entered". No matter how much you trust the software (not just your own code, but EVERY part of ALL of the software stacks involved), there's always the possibility of a hardware failure (again, potentially anywhere along the chain) stopping you cold at any moment. Keeping this in mind is a good defense against the kind thinking that leads to this kind of "workaround". – dgould Sep 13 at 16:31
  • 2
    Also, you might be a deadlock victim. Your CATCH blocks run but throw if they try to write to the database. – Joshua Sep 13 at 20:38
10

i-one: The workaround that is suggested to you makes possible (at least) violating "A" of the ACID. For example, if SP is being executed by a remote client and connection breaks, then partial "commit"/"rollback" may happen, because of server can terminate session between two insertions/deletions (and abort SP execution before it reaches its end).

Does this make sense as a viable workaround for transactions?

dan-guzman: No, the CATCH block never gets executed in the case of a query timeout because the client API cancelled the batch. Without a transaction, SET XACT_ABORT ON cannot rollback anything other than the current statement.

tibor-karaszi: You have 4 transactions, meaning more logging to the transaction log file. Remember that each transaction requires a synchronous write of the log records up to that point i.e. you get worse performance from also that aspect when using many transactions.

rbarryyoung: If they are getting a lot of blocking then they either need to fix their data design, rationalize their table access order, or use a more appropriate isolation level. They are assuming that their problems (and failure to understand it) will become your problem. The evidence from millions of other database is that it will not.

Also, what they are trying to manually implement is effectively a poor-mans optimistic concurrency. What they should do instead is to use some of the best optimistic concurrency in the world, already built into SQL Server. This goes to the isolation point above. In all likelihood they need to switch from whatever pessimistic concurrency isolation level they are currently using to one of the optimistic concurrency isolation levels, SNAPSHOT or READ_COMMITTED_SNAPSHOT. These will effectively do the same thing as their manual code, except it will do it correctly.

ross-presser: If you have extremely long running processes -- like something happens today and next week something has to follow up, and if next week's thing fails then today's has to fail retroactively -- you may want to look into sagas. Strictly speaking this is outside of the database, as it requires a service bus.

5

Bad idea code is just going to be more expensive to fix down the line.

If there are blocking problems using explicit transaction (rollback/commit), point your DBA to the internet for some great ideas to address the issues.

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

Indexes reduce the number of seeks that must occur in a table/page to find a row/set of rows. They are generally seen as a method to reduce execution times for SELECT * queries and rightly too. They are not considered suitable for tables involved in large number of UPDATES. In fact INDEXES are found to be unfavorable in these cases as they increase the time taken to complete UPDATE queries.

But this is not always the case. Delving slightly deep into the execution of an UPDATE statement we find that it too involves executing a SELECT statement first. This is a special and an often seen scenario where queries update mutually exclusive sets of rows. INDEXES here can lead to significant increase in performances of the database engine contrary to popular belief.

4

The fake transaction strategy is dangerous because it allows concurrency issues that transactions specifically prevent. Consider that in the second example any of the data might be changed between statements.

The fake transaction deletes are not GUARANTEED to run or succeed. If the database server turns off during the fake transaction, some but not all of the effects will remain. They are also not guaranteed to succeed in the say way a transaction rollback is.

This strategy might work with inserts, but definitely would not work with updates or deletes (no time-machine SQL statements).

If strict transaction concurrency is causing blocking there are lots of solutions, even ones that reduce the protection level... these are the correct way to solve the problem.

Your DBA is offering a solution that might work OK if there was only one user of the database, but is absolutely unfit for any kind of serious usage.

4

This is not a programming issue, rather it is an interpersonal/miscommunication issue. Most likely your "DBA" is worried about locks, not transactions.

The other answers already explain why you have to use transactions... I mean that's what RDBMS do, without properly used transactions there is no data integrity, so I'll focus on how to solve the real problem, which is: find out why your "DBA" developed an allergy to transactions and convince him to change his mind.

I think this guy is confusing "a particular scenario where bad code resulted in terrible performance" with "all transactions are bad." I wouldn't expect a competent DBA to make that mistake, so that's really weird. Maybe he had a really bad experience with some terrible code?

Consider a scenario like this:

BEGIN
UPDATE or DELETE some row, which takes locks it
...do something that takes a while
...perform other queries
COMMIT

This style of transaction use holds a lock (or several locks) which means other transactions hitting the same rows will have to wait. If the locks are held for a long time, and especially if lots of other transactions want to lock the same rows, this can really hurt performance.

What you could do is ask him why he has this curiously wrong idea of not using transactions, what types of queries were problematic, etc. Then try to persuade him that you will definitely avoid similar bad scenarios, that you will monitor your lock usage and performance, reassure him, etc.

What he's telling you is "don't touch the screwdriver!" so the code you posted in your question is basically using a hammer to drive a screw. Much better option is to convince him you know how to use a screwdriver...

I can think of several examples... well, they were on MySQL but that should work too.

There was a forum where the full text index took a while to update. When a user submitted a post, the transaction would update the topics table to increase the post count and last post date (thus locking the topic row), then inserted the post, and the transaction would hold the lock until the fulltext index had finished updating and the COMMIT was done.

Since this ran on a rustbucket with way too little RAM, updating said fulltext index often resulted in several seconds of intense random IO on the single slow spinning drive in the box.

The problem was that people who clicked on the topic caused a query to increase the view count on the topic, which also required a lock on the topic row. Thus, no-one could view the topic while its fulltext index was updating. I mean, the row could be read, but updating it would lock.

Even worse, posting would update the post count on the parent forums table and also hold the lock while the fulltext index was updating... which froze the entire forum for a few seconds and caused tons of requests to pile up in the web server queue.

Solution was to take the locks in the correct order: BEGIN, insert the post and update the fulltext index without taking any locks, then quickly update the topic/forum rows with post count and last post date, and COMMIT. That completely solved the problem. It was just moving around a few queries, really simple.

In this case, transactions weren't the problem... It was acquiring an unnecessary lock before a lengthy operation. Other examples of stuff to avoid while holding a lock in a transaction: waiting for user input, accessing lots of uncached data from slow spinning drives, network IO, etc.

Of course, sometimes, you don't have a choice and you have to do a lengthy processing while holding cumbersome locks. There are tricks around this (operate on a copy of the data, etc) but quite often the performance bottleneck comes from a lock that was not intentionally acquired, and simply reordering queries solves the problem. Even better, is being aware of the locks taken while writing the queries...

I won't repeat the other answers but really... use transactions. Your problem is convincing your "DBA", not working around the most important feature of a database...

3

TLDR: Use proper isolation level.

As you correctly noticed the approach without transactions and with "manual" recovery can be very complex. The high complexity means normally much more time to implement it and much more time to fix errors (because complexity leads to more errors in the implementation). It means such approach can cost your customer much more.

The main concern of your "dba" colleague is performance. One of ways to improve it is to use proper isolation level. Suppose you have a procedure that provides some kind o f overview data to the user. Such procedure does not necessarily have to use SERIALIZABLE isolation level. In many cases READ UNCOMMITTED can be quite sufficient. It means, such procedure will not be blocked by your transaction that creates or modifies some data.

I'd suggest you review all existing functions/procedures in your database, evaluate the reasonable isolation level for each one, explain the performance benefits to your customer. Then adjust these functions/procedures accordingly.

2

You can also decide to use In-Memory OLTP tables. They of course still use transactions, but there is no blocking involved.
Instead of blocking all operations will be successful, but during the commit phase engine will check for transaction conflicts and one of the commits may fail. Microsoft uses the term "Optimistic locking".
If the scaling issue is caused by conflict between two write operations, such as two concurrent transactions trying to update the same row, In-Memory OLTP lets one transaction succeed and fails the other transaction. The failed transaction must be re-submitted either explicitly or implicitly, re-trying the transaction.
More at: In memory OLTP

-5

There is a way around using transactions to a limited extent and that is by changing your data model to be more object oriented. So rather than storing for example demographic data about a person in several tables and relating them to each other and requiring transactions, you could have a single JSON document that stores everything you know about that person in a single field. Of course working out far that domain stretches is a another design challenge, best done by developers not DBAs

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.