6 ms·

I've had similar philosophical thoughts about a transaction rollback. Consider this definition from go's database/sql package. func (tx *Tx) Rollback() er

I've had similar philosophical thoughts about a transaction rollback. Consider this definition from go's database/sql package. func (tx *Tx) Rollback() error Rollback can fail, indicated as such by it can return an error. What are the cases where rollback could fail and what's the recovery mechanism? Does a failed rollback mean (logically) that the transaction is still open and uncommitted? But really, invoking rollback can never fail to abort the transaction, so the error result has little use. Now, obviously the error isn't totally useless as an error can be returned to indicate, say, the connection to the database was dropped. But even in that case, the definition of a transaction's validity/lifetime means that even if rollback fails, the transaction is in the same state as if rollback had succeeded.

thriftwy 2y ago
Obviously, transaction may fail to be rolled back and leave dangling locks. Not sure about the specifics.
- marcosdumay 2y ago
  That's a bug in your database, not something you specify on your API.
  - thriftwy 2y ago
    What if it's a connection problem? DBs are normally remote. It's also OK to specify potential bug surface on your API.
    - thwarted 2y ago
      The bug being referred to isn't the connection problem, the bug is that in the event of a loss of communication with the client, the transaction isn't fully cleaned up, including the release of held locks, when the transaction enters an abortive state.
      - thriftwy 2y ago
        It often takes minutes to hours for a connection to be reset when one of its sides went away. So no, it's quite often that one side will get these exceptions while the other one is locked up for prolonged period of time.
- thwarted 2y ago
  I don't think that "leave dangling locks" is obviously a possibility from rollback failing. What's the method to clean up those locks? Rollback harder? Commit? If a transaction is ever aborted and there's a chance that locks specific to the transaction (MVCC locks necessary for transactional semantics) are still held then it would be safest to always issue this mythical "clean up locks" statement after every rollback attempt. The default final state of a transaction that is not explicitly committed (and even autocommit is still explicit) is that it is aborted, purely because the only way a transaction can be committed is by commiting it vs there are an infinite number of ways and reasons for the transactions to abort. If the rollback is successful or not there is logically no way that the state of the transaction after rollback being invoked is that the transaction is still usable for something.
- pmarreck 2y ago
  I unexpectedly shuddered at the term “dangling locks”
marcosdumay 2y ago
Or the Java SQL connection interface, that can throw SQLException on close(). Yes, if your connection throws an error when closing, I guess it stops being usable. Has anybody ever did something useful with that exception?
- lxgr 2y ago
  Depending on the durability guarantees of the database/connection in question and whether it uses request pipelining, I could imagine it indicating something like "non-clean connection shutdown; your last few commands might not have been processed"? There's a similar situation in raw socket programming in some OSes: You might be done writing all your outbound data into the socket (i.e. your last write() call returned indicating success), but the receiving application might crash before being able to read all of your data. By implementing an application-level shutdown command that the other side acknowledges, that can be avoided; maybe some SQL protocol implementations do that, and that exception is thrown if the shutdown request is never acknowledged.
hyperman1 2y ago
I've seen this happen in Oracle using two phase commit, when the transaction coordinator drops dead before transmitting a commit or rollback decision. The transaction remained in doubt for months before someone noticed. You can ask around and manually coordinate a decision, and tell the databasecwhat you decided.
- thwarted 2y ago
  Oh that's interesting, and probably manifests a bunch of issues related to storage and rollback segments being consumed with the transaction staying open for months. But that's not the same thing as rollback failing (although it is the invocation of rollback failing) as neither rollback nor commit was fully issued and received, so it makes sense that the transaction would stay open (given that the database doesn't tie transactions to the a connection or a session, and for a two phase commit scenario I'd expect the transaction coordinator to "own" the transaction so it's lifetime isn't necessary tied to a session).
  - hyperman1 2y ago
    Yeah, the story is a bit incomplete. The full chain was application->transaction master -> 20 or so slave databases. It was a weekly batch dispatching data to the 20 slaves, starting with a full delete of each slave. Someone decided to literally pull the plug and replace the master database server node from the rack, while the batch was still running. He assumed the other server nodes would pick up where this one left off. So the batch log of the application first complained about the master disappearing, then about the rollback failing on another master node because it wasn't the coordinator and had no idea of this transaction. It also means the decision about the commit/rollback was irrelevant, as next week's batch run had deleted the records in question. Presumably, some ephemeral records were hanging around, deciding if they were deleted either in week X or week X+1.
    - Filligree 2y ago
      How did you discover the dangling transaction?
4RealFreedom 2y ago
Not exactly the same but an engineer I was working with wasn't handling a failure case properly and leaving transactions open. It was reported that the DB would stop working after a certain amount of time. I knew transactions were hanging but I didn't understand why. I sat down with the engineer and QA trying to figure out the problem for well over a day. QA was running their test suite over and over. We started removing pieces one at a time until we found the problem.
shpx 2y ago
Pending and rolled back are different transaction states. The former uses resources and locks and can prevent other transactions from happening. Rolling back a transaction twice probably indicates an error in your program, which Go's API is giving you a chance to report to yourself. Ultimately every network request can fail because of the Two Generals' Problem. Actually, every single operation on a computer can fail (as in, do something other than what you were promised or promised yourself it would do). Nothing in life is guaranteed, the environment is adversarial. The fact that we create machines that can be predicted with 0.00000000001% certainty and carve out a safe environment for them and feed them energy is not common and unnatural and all computers will also eventually succumb to entropy. The network is just where that is common enough that considering that another computer can die in our programs can be more useful than completely ignoring the possibility. This possibility is represented as a non-zero number called "error" in the Go API you've posted, so that you have the option of branching your program to a different execution path if the database you're communicating with stops existing or at least stops being reachable.
- haiku2077 2y ago
  Falsehoods programmers believe: - Sleep(1) sleeps for exactly 1 second. - Sleep(1) sleeps for approximately 1 second. - Sleep(1) sleeps for at least 1 second. - Sleep(1) sleeps for an unknown, but short amount of time. Certainly not weeks, months or years.
  - deleted 2y ago
    [deleted]
  - teddyh 2y ago
    More Falsehoods programmers believe about time: <https://gist.github.com/timvisee/fcda9bbdff88d45cc9061606b4b923ca https://gist.github.com/timvisee/fcda9bbdff88d45cc9061606b4b...>
  - Dylan16807 2y ago
    "Approximately 1 second" is fine. You just have to keep in mind that other influences can block your program from running. Those other influences can trigger even if you don't sleep!
haiku2077 2y ago
An example of how a rollback could fail is if the database is accessed over a network and the connection times out due to a networking problem.
deleted 2y ago
[deleted]
Groxx 2y ago
>Does a failed rollback mean (logically) that the transaction is still open and uncommitted? Yes, it means exactly that until something else manages to close the transaction. Generally that's done by noticing that the connection has failed, or a timeout has expired, assuming "roll back unless confirmed" is the default behavior (usually true, but not always). The reason you might want to explicitly tell something to roll back is to shorten ^ that latency, or to wait for it to complete before doing something else that requires it to be gone.
stanac 2y ago
Remands me of "Operation failed successfully" errors. It was a meme, but in some cases I had to implement failing an operation in a test service fake and log something like that.

thriftwy 2y ago

Obviously, transaction may fail to be rolled back and leave dangling locks. Not sure about the specifics.

marcosdumay 2y ago
That's a bug in your database, not something you specify on your API.
- thriftwy 2y ago
  What if it's a connection problem? DBs are normally remote. It's also OK to specify potential bug surface on your API.
  - thwarted 2y ago
    The bug being referred to isn't the connection problem, the bug is that in the event of a loss of communication with the client, the transaction isn't fully cleaned up, including the release of held locks, when the transaction enters an abortive state.
    - thriftwy 2y ago
      It often takes minutes to hours for a connection to be reset when one of its sides went away. So no, it's quite often that one side will get these exceptions while the other one is locked up for prolonged period of time.
thwarted 2y ago
I don't think that "leave dangling locks" is obviously a possibility from rollback failing. What's the method to clean up those locks? Rollback harder? Commit? If a transaction is ever aborted and there's a chance that locks specific to the transaction (MVCC locks necessary for transactional semantics) are still held then it would be safest to always issue this mythical "clean up locks" statement after every rollback attempt. The default final state of a transaction that is not explicitly committed (and even autocommit is still explicit) is that it is aborted, purely because the only way a transaction can be committed is by commiting it vs there are an infinite number of ways and reasons for the transactions to abort. If the rollback is successful or not there is logically no way that the state of the transaction after rollback being invoked is that the transaction is still usable for something.
pmarreck 2y ago
I unexpectedly shuddered at the term “dangling locks”

marcosdumay 2y ago

Or the Java SQL connection interface, that can throw SQLException on close(). Yes, if your connection throws an error when closing, I guess it stops being usable. Has anybody ever did something useful with that exception?

lxgr 2y ago
Depending on the durability guarantees of the database/connection in question and whether it uses request pipelining, I could imagine it indicating something like "non-clean connection shutdown; your last few commands might not have been processed"? There's a similar situation in raw socket programming in some OSes: You might be done writing all your outbound data into the socket (i.e. your last write() call returned indicating success), but the receiving application might crash before being able to read all of your data. By implementing an application-level shutdown command that the other side acknowledges, that can be avoided; maybe some SQL protocol implementations do that, and that exception is thrown if the shutdown request is never acknowledged.

hyperman1 2y ago

I've seen this happen in Oracle using two phase commit, when the transaction coordinator drops dead before transmitting a commit or rollback decision. The transaction remained in doubt for months before someone noticed. You can ask around and manually coordinate a decision, and tell the databasecwhat you decided.

thwarted 2y ago
Oh that's interesting, and probably manifests a bunch of issues related to storage and rollback segments being consumed with the transaction staying open for months. But that's not the same thing as rollback failing (although it is the invocation of rollback failing) as neither rollback nor commit was fully issued and received, so it makes sense that the transaction would stay open (given that the database doesn't tie transactions to the a connection or a session, and for a two phase commit scenario I'd expect the transaction coordinator to "own" the transaction so it's lifetime isn't necessary tied to a session).
- hyperman1 2y ago
  Yeah, the story is a bit incomplete. The full chain was application->transaction master -> 20 or so slave databases. It was a weekly batch dispatching data to the 20 slaves, starting with a full delete of each slave. Someone decided to literally pull the plug and replace the master database server node from the rack, while the batch was still running. He assumed the other server nodes would pick up where this one left off. So the batch log of the application first complained about the master disappearing, then about the rollback failing on another master node because it wasn't the coordinator and had no idea of this transaction. It also means the decision about the commit/rollback was irrelevant, as next week's batch run had deleted the records in question. Presumably, some ephemeral records were hanging around, deciding if they were deleted either in week X or week X+1.
  - Filligree 2y ago
    How did you discover the dangling transaction?

4RealFreedom 2y ago

Not exactly the same but an engineer I was working with wasn't handling a failure case properly and leaving transactions open. It was reported that the DB would stop working after a certain amount of time. I knew transactions were hanging but I didn't understand why. I sat down with the engineer and QA trying to figure out the problem for well over a day. QA was running their test suite over and over. We started removing pieces one at a time until we found the problem.

shpx 2y ago

Pending and rolled back are different transaction states. The former uses resources and locks and can prevent other transactions from happening. Rolling back a transaction twice probably indicates an error in your program, which Go's API is giving you a chance to report to yourself. Ultimately every network request can fail because of the Two Generals' Problem. Actually, every single operation on a computer can fail (as in, do something other than what you were promised or promised yourself it would do). Nothing in life is guaranteed, the environment is adversarial. The fact that we create machines that can be predicted with 0.00000000001% certainty and carve out a safe environment for them and feed them energy is not common and unnatural and all computers will also eventually succumb to entropy. The network is just where that is common enough that considering that another computer can die in our programs can be more useful than completely ignoring the possibility. This possibility is represented as a non-zero number called "error" in the Go API you've posted, so that you have the option of branching your program to a different execution path if the database you're communicating with stops existing or at least stops being reachable.

haiku2077 2y ago
Falsehoods programmers believe: - Sleep(1) sleeps for exactly 1 second. - Sleep(1) sleeps for approximately 1 second. - Sleep(1) sleeps for at least 1 second. - Sleep(1) sleeps for an unknown, but short amount of time. Certainly not weeks, months or years.
- deleted 2y ago
  [deleted]
- teddyh 2y ago
  More Falsehoods programmers believe about time: <https://gist.github.com/timvisee/fcda9bbdff88d45cc9061606b4b923ca https://gist.github.com/timvisee/fcda9bbdff88d45cc9061606b4b...>
- Dylan16807 2y ago
  "Approximately 1 second" is fine. You just have to keep in mind that other influences can block your program from running. Those other influences can trigger even if you don't sleep!

haiku2077 2y ago

An example of how a rollback could fail is if the database is accessed over a network and the connection times out due to a networking problem.

deleted 2y ago

[deleted]

Groxx 2y ago

>Does a failed rollback mean (logically) that the transaction is still open and uncommitted? Yes, it means exactly that until something else manages to close the transaction. Generally that's done by noticing that the connection has failed, or a timeout has expired, assuming "roll back unless confirmed" is the default behavior (usually true, but not always). The reason you might want to explicitly tell something to roll back is to shorten ^ that latency, or to wait for it to complete before doing something else that requires it to be gone.

stanac 2y ago

Remands me of "Operation failed successfully" errors. It was a meme, but in some cases I had to implement failing an operation in a test service fake and log something like that.