MariaDB Cluster – data consistency for updates and auto_increment

galeramariadb

I have two different MariaDB servers and plan to merge data into one database and create MariaDB Cluster with 3 nodes. I read about Galera and multi-master replication – which MariaDB Cluster use and have some questions. And I not use transactions, foreign keys, etc…

1) If I understand it well – unlike of MySQL Cluster on Galera I do not have to worry about auto increment values. Config auto_increment_offset is not needed with Galera. Is this true?

2) I read many articles and discussions about data consistency and replication but still have questions. If application not use transaction and for example run 10x query "UPDATE a SET b=b+1 WHERE c=1 LIMIT 1". Can I be 100% sure that "SELECT b FROM a WHERE c=1 LIMIT 1" return right value? On one slideshare presentation I read – "Data does not belong to a node – Node belong to data". So, it's true that Galera replicator take all care about consistency and no changes in application is needed?

Best Answer

1 - Yes, Galera takes care of auto increments for you, but you will get lots of missed ids, so make sure your fields are big enough (e.g. you might need a smallint somewhere whereas without missing ids you could have got away with a tinyint).

2 - Yes, no changes should be needed to the application, except for occasionally you might get deadlocks. You should come up with a system to check error messages automatically and if you get errors like Deadlock found when trying to get lock; try restarting transaction and Lock wait timeout exceeded; try restarting transaction then redo the query and it should go through ok. The data will remain consistent. There is also a Galera setting for automatic retries, but we found it was better to try them again yourself. Obviously you shouldn't be using mysql_query() at all, but for the purposes of a simple to follow example, you could swap your queries to use something like this instead of your usual query function

function vio_mysql_query_deadlock_safe($query, $db = null) {
    $deadlock_retries_done = 0;
    $deadlock_retries_max = 10;
    while ($deadlock_retries_done < $deadlock_retries_max) {
        if (is_null($db)) {
            $q = mysql_query($query);
        } else {
            $q = mysql_query($query, $db);
        }
        if ($q === false) {
            $error = mysql_error();
            if ($error == 'Deadlock found when trying to get lock; try restarting transaction' || $error == 'Lock wait timeout exceeded; try restarting transaction') {
                $deadlock_retries_done++;
                continue;
            } else {
                throw new Exception($error . '. Query: ' . $query);
            }
        }
        return $q;
    }
    throw new SQLException($error . '. Re-tried with deadlock ' . $deadlock_retries_done . ' times. Query: ' . $query);
}
Related Question