posted on Dec, 14 2022 @ 04:58 AM
Hey, I want to ask a question. Ik I can ask separately but It's urgent. Is it true that all Participant database modifications must employ Pessimistic
Locking because of Two-Phase Commit?
I'm talking about the application of 2PC to heterogeneous distributed transactions. Assume I want to write to both databases A and B at the same time.
Now, reads and writes to databases A and B might come from numerous concurrent users. In a high throughput scenario, we'd want A and B to employ
Optimistic Locking (row versioning, for example) rather than Pessimistic Locking to avoid blocking high-volume concurrent read-only operations.
But, as this
article says if A and B are both participants in a 2PC protocol as a
whole, does that imply that they MUST employ Lock throughout each of their PREPARE phases for those relevant data changes? And basically, need to
retain that lock till the COMMIT is completed? Because each transaction is forced to be divided into two halves, and you simply can't do "prepare to
write a value that I read now is the most up-to-date value" because when you commit it later, it might very well be modified if the resource isn't
locked.
Does this indicate that in distributed transaction setups with numerous databases, each database's concurrent throughput will be constrained merely
because they must cooperate with other databases, even though they could have employed Optimistic Locking on their own?