3 ms·

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 tran

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?
    - hyperman1 2y ago
      By accident. I had found a book from tom kyte in a garage sale, and was looking around in all kinds of views it described.

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?
  - hyperman1 2y ago
    By accident. I had found a book from tom kyte in a garage sale, and was looking around in all kinds of views it described.