Thursday, November 15, 2018

MySQL NDB Cluster row level locks and write scalability

MySQL NDB Cluster uses row level locks instead of a single shared commit lock in order to prevent inconsistency in simultaneous distributed transactions. This gives NDB a great advantage over all other MySQL clustering solutions and is one reason behind cluster’s unmatched ability to scale both reads and writes. 

NDB is a transactional data store. The lowest and only isolation level available in NDB is Read Committed. There are no dirty reads in NDB and only committed rows can be read by other transactions. 

All write transactions in NDB will result in exclusive row locks of all individual rows changed during the transaction. Any other transaction is allowed to read any committed row independent of their lock status. Reads are lock-free reads.

The great advantage is that committed reads in NDB never block during writes to the same data and always the latest committed changes are read. A select doesn't block concurrent writes and vice versa. 

This is extremely beneficial for write scalability. No shared global commit synchronization step is needed to ensure transaction consistency across distributed data store instances. Each instance instead handles its own row locks - usually only locking a few out of many rows. Due to NDB’s highly parallel and asynchronous design many rows can be committed in parallel within a distributed instance and across multiple instances. 

As a side effect interleaved reading transactions can read committed rows of write transactions before all rows of that writing transaction are committed. The set of rows returned may represent a partially committed transaction and not a snapshot of a single point in time. Pending transactions never change the state of the data before they are committed. All rows of committed transactions are atomically guaranteed to be network durable and consistent in all distributed instances of the data.

If more consistent reads are needed then read locks used in SELECT... IN SHARE MODE / SELECT .... FOR UPDATE can be used to get a serialized view of a set of rows.

No comments: