Re: GET_LOCK in ndb clusters
Hi Pete,
"...
Then the ndb storage engine will take exclusive locks on the affected rows and the behaviors will be as expected even with clients operating simultaneously across the ndb cluster attached to multiple mysqld processes. "
You do not need any extra locking related syntax for INSERT, UPDATE, DELETE statements.
The changes to the row(s) affected will always take exclusive row locks on the affected rows and will therefore be serialised with each other at the data nodes, no matter which MySQLD(s) issue the statements.
" I just want to be sure about where the LOCK IN READ MODE and LOCK IN EXCLUSIVE MODE Mikael mentioned factors in... because we're presently not specifying these. "
Read operations (SELECT) by default run with CommittedRead isolation which takes no row locks and returns the last committed row version.
Read operations can be made to take shared row locks (LOCK IN SHARE MODE) or exclusive row locks (FOR UPDATE) if desired. For some applications it is useful to e.g. read data with an exclusive lock first, then update it, depending on what was read. The exclusive lock guarantees that the data read does not change while the update is being prepared.
For your use case, you may not need any read operations, or you may not need any read operations that take locks, it may well be sufficient just to use INSERT, UPDATE, DELETE.
---
I think that Mikael was envisaging an implementation of the MySQL 'table lock' concept, where access to a table can be serialised. One way to implement that could be to have all accessors take a row lock on a row named for the table. A read operation with either a Shared or Exclusive lock could be used for this. If all table accessors followed this protocol then by holding a shared row lock on this row we could be sure that there were no concurrent writers, and by holding an exclusive row lock on this row we could be sure there were no concurrent readers or writers.
In the envisaged scheme, the row locks are being used as if they were table locks.
In your scheme, the row locks are used to serialise modifications to a table. The content of the table represents the locks held.
There are pros + cons to using the row locks themselves to represent locks, vs using them to serialise access to data that represents locks. In both cases, row locks ensure atomicity.
Frazer