Sample Header Ad - 728x90

Locking/Isolation - MariaDB

1 vote
2 answers
1138 views
In MariaDB the default isolation level is repeatable read. I understand this means when I open a transaction I will not see any concurrent writes to the DB. I also have a situation where I need to ensure concurrent changes are not made to rows. For this, I can acquire an exclusive lock. My question is really about how the two work in conjunction. If I acquire the lock inside my update transaction it means it is possible I open the transaction and am blocked because another process has the exclusive lock, when I obtain the lock changes may have been made to the row which I would not see but might need to be aware of. I am hesitant to change the isolation level as this is a specific scenario. At the moment I have settled on acquiring the lock before I begin my update transaction but this makes releasing the lock more problematic as it would not be released once my transaction commits. Am I missing something obvious? So basically I open a transaction SELECT FOR UPDATE. Then open a second transaction to make the update. Semi-related: Does the above mean that optimistic locking when your transaction level is repeatable read is kind of a no-go? Optimistic locking involves checking a version/timestamp to ensure no other transaction has modified it since it was initially read. With repeatable reads, we wouldn't have visibility of other transactions until our transaction is complete which defeats the purpose. Or does it just mean I should be acquiring locks outside of the transactions?
Asked by Daniel Benzie (113 rep)
Oct 2, 2023, 01:09 PM
Last activity: Oct 5, 2023, 01:09 PM