The idea is that a transaction starts when a request starts, and it’s automatically rolled back if there’s an exception. This is behavior you want from the start! It’s half (err, ¼) the point of using a database.
I mostly agreed with the rest of the article but this strikes me as massive blooper. I can see no reason this would be considered good advise.
It's the database equivalent of the GIL, can't figure out what you actually need to lock? Just lock everything!
An example of this going wrong in production, I work in the online gaming (gambling) industry. One of our users won a large jackpot, our code started a transaction, contacted the 3rd party to have them release the funds, recorded the win in the transaction journal, updated the customers balance etc. Then the stored procedure which inserted a row into the outgoing email queue had a small error. This caused all of the database work on our end to rollback.
For what its worth, this was discovered during a reconciliation with the 3rd party and the customer received his winnings.
My point being, this is a horrible way of dealing with database transactions. Only things which are transactional by nature should be carried out within the same transaction. Obviously your code needs to deal with any errors and carry on or report an error as is appropriate.
Indeed, transactions should be as short as possible but no shorter. If your code crashing within a set of queries would leave the code in an inconsistent state you should probably wrap them in a transaction, but if not you should not put them in the same transaction.
> An example of this going wrong in production, I work in the online gaming (gambling) industry. One of our users won a large jackpot, our code started a transaction, contacted the 3rd party to have them release the funds, recorded the win in the transaction journal, updated the customers balance etc. Then the stored procedure which inserted a row into the outgoing email queue had a small error. This caused all of the database work on our end to rollback.
Updating a customer's winnings and recording a win are definitely things that should be together in a transaction - if one happened and not the other, that's a failure.
This is not the fault of using a transaction, this is the fault of not working with what is essentially a distributed transaction. Since you are relying on a message against a third party, you need to build distribution into this system, which is challenging. You might contact the third party after you've completed the internal processing, and you might use two-phase semantics even such that you can "PREPARE TRANSACTION" the work you've done locally, (nearly) guaranteeing that a commit will proceed, then work with your external messaging, then commit locally.
> Updating a customer's winnings and recording a win are definitely things that should be together in a transaction - if one happened and not the other, that's a failure.
Yes, they these clearly do belong in the same transaction. Inserting a congratulations email into the email queue doesn't.
emails typically get fired off as part of a post-commit hook. Pyramid's transaction manager has explicit support for this pattern - as you establish "emails" within your trans, the actual send operation is deferred til after the transaction proceeds.
It's only default behavior. The alternative is for any request that runs more than one query to be inherently but unpredictably unreliable; that is definitely not acceptable.
If you're doing something complicated, you're going to have to tread carefully anyway. No default could have handled your situation cleanly.
For what it's worth, zope transactions (which Pyramid uses) support combining notions of "transaction" from arbitrary other services and tries to do the right thing for a commit and rollback. I've used this to keep a database and mogilefs in sync, for example.
The transaction is internal to the database. It's essentially a list of operations to perform, if something goes wrong it provides a list of things to reverse. If the db transaction fails, then there would need to be some code to handle rolling back operations outside of the database. I don't think db transactions were to fault in your situation.
I'm not sure I really get what you are trying to say. I know what a transaction is, and I know it wasn't the fault of transactions. It was the fault of our poor use of transactions, in the same monolithic transaction pattern that the OP appears to be advocating.
transaction-per-request is by far the best default pattern to use. If some particular methods have special needs, like needing to break up the operations into multiple transactions to deal with third-party communication, those are the exceptions.
The alternatives to transaction-per-request are autocommit, or explicit transactions required at all times. Autocommit is a terrible choice because now you've lost all atomicity and isolation for dependent operations (locking is also not much of an issue. If you're doing VB, then you're on SQL server, which has some of the worst locking behavior - turn on snapshot isolation to make it bearable). Explicit transactions required at all times is a terrible choice because now your app is littered with what is 90% of the time unnecessary boilerplate.
Why not the third (and in my experience most common) alternative? Autocommit but if you explicitly start a transaction then automcommit is turned off until that transaction has been committed.
If you have more than one statement, then autocommit is usually wrong, and what you usually want is transaction-per-request. If you have exactly one statement, then autocommit is exactly identical to transaction-per-request.
Also part of the transaction-per-request pattern is that the transaction is started when the first statement is invoked. So there's no overhead for zero-statement requests either.
It seemed you were arguing against using transactions by default because they didn't roll back a call to a web service. I meant to point out that the issue in your case seems less about db transactions and more about the code and error handling.
It's the database equivalent of the GIL, can't figure out what you actually need to lock? Just lock everything!
An example of this going wrong in production, I work in the online gaming (gambling) industry. One of our users won a large jackpot, our code started a transaction, contacted the 3rd party to have them release the funds, recorded the win in the transaction journal, updated the customers balance etc. Then the stored procedure which inserted a row into the outgoing email queue had a small error. This caused all of the database work on our end to rollback.
For what its worth, this was discovered during a reconciliation with the 3rd party and the customer received his winnings.
My point being, this is a horrible way of dealing with database transactions. Only things which are transactional by nature should be carried out within the same transaction. Obviously your code needs to deal with any errors and carry on or report an error as is appropriate.