Re:Practical Guide to SQL Transaction Isolation
by virtuabhi on Wednesday August 02, 2017 @11:16AM
(#14911194)
Thanks, got it. It is nice to see database systems directly offering a locking system for use in applications.
Re:Practical Guide to SQL Transaction Isolation
by SoMisanthrope on Wednesday August 02, 2017 @01:33PM
(#14912755)
Does that give you a row-level lock that extends through the chain of referential integrity, for the Order?
Re:Practical Guide to SQL Transaction Isolation
by IgorPartola on Wednesday August 02, 2017 @02:00PM
(#14913026)
No. These advisory locks don't know anything about what you are locking. You name and you use the lock, not the RDBMS. That is you could give the lock the name `my-silly-lock-name` and that would totally work. As long as two bits of your code use the same lock name, they'll be executed in series. Hell, the code inside the code guarded by these locks doesn't even have to touch the DB. This is like a separate service your RDBMS provides that doesn't touch any tables, sequences, etc.
Essentially these are mutexes identified by strings that are managed by your RDBMS. That's all.
Think of these locks as mutexes in threaded code: they don't mean anything on their own, but they simply guard against the rest of your code. Contrived semi-pseudocode example:
Note that the two functions will never step on each others' toes because the first thing they do is acquire a lock.