Hacker News Reader

Comment by jiggawatts | Hacker News Reader

Database engines I've worked with generally support nested transactions, so you can open a transaction, open another transaction, commit it, then roll back the outer transaction... and it's like nothing ever happened.

This ability to compose transactions is their main benefit over other kinds of concurrency control!

View on HN

Many databases do not actually offer this or pretend to offer this, but it is really just one thing.

For those that don't, it's almost always modeled in the connection-library as "hold it open until commits == opens, or roll back if any roll back". Or it's often easy to build this yourself, around the library.

Which works entirely fine in most cases. You're at greater risk of phantom reads and general "stuff that can occur while you hold open a transaction", but if you're not handling that correctly then you're not handling that correctly. It's only a matter of volume, not existence.

... with a clear exception for cases where you do need to truly end a transaction, like if you're relying on some other thread to do something on a different transaction that needs to see your changes, or when you risk a deadlock somewhere due to not releasing your lock. Those are both a risky patterns for a lot of reasons though, and worth avoiding at design-time if at all possible.

Are you referring to save points as the "pretend to offer this"? If so, why wouldn't they work? If not, what are you referring to?

Literally:

    BEGIN TRAN
    BEGIN TRAN
    COMMIT TRAN
    ROLLBACK TRAN

Yes, but what part about that is pretend? Is there a db that doesn't do the right thing and roll back the outer transaction? If so, can you name it?

Reply on news.ycombinator.com