So, I'm working in MySQL at the moment, but any SQL answers will probably do, cuz I'm trying to understand the general concepts.
So thread safety is obviously important in concurrent environments. I program primarily in Java and I'm always extremely careful to write code that guards its mutable state to avoid thread conflicts.
In SQL, though, I'm very confused about how to achieve that same level of safety. So I'm gonna start with what I don't know, go on to what I'm confused about, and take it from there.
First, what I do know is transactions. Disable auto commit, use savepoints, rollbacks, etc. Transactions, as I understand them, are atomic at the point of committing them.
But I've also seen references to explicit locking statements and concurrency models (optimistic,pessimistic). And I don't really get where all that fits in. I also don't want to just use transactions for everything and assume it'll be safe. I don't write code unless I understand it in its entirety, I don't want to leave anything to chance.
Moreover, what about triggers, procedures, etc. How do I use them with transactions? How do I ensure atomicity there?
I feel like I'm overcomplicating this a bit, but I'm looking for a comprehensive, clear cut explanation as to how to ensure that multiple threads and users can modify the database safely. Not quite and ELI5, since I understand SQL better than that, but something that really thoroughly explains the process.
Thanks. I haven't found a good match for this question on this site in my search, but if it is a duplicate I apologize and simply ask that a link to the appropriate answer be provided before this question is locked.
