CODEWITHSUNDEEP
mysqltransactionsmariadbisolation-levelgalera
StaticNoiseLog
StaticNoiseLog

Reputation: 1400

MariaDB Galera Cluster: INSERT ... SELECT locking broken?

I have found an inconsistency in behaviour between a regular MariaDB installation and a MariaDB Galera cluster. Locking does not work as expected for INSERT ... SELECT statements with the Galera cluster. This causes duplicate IDs in our application.

All connections use isolation level REPEATABLE-READ (the default), verified with this query:

SELECT * FROM information_schema.session_variables WHERE variable_name = 'tx_isolation';

Setup for test:

CREATE TABLE `TestTab` (`id` int(10) unsigned NOT NULL, `name` varchar(100) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Expected behaviour: Two parallel connections should create two records with two distinct values in the 'id' field.

Command sequence demonstrating the expected behaviour with a regular MariaDB installation (non-cluster):

Connection 1                                                         Connection 2

SET autocommit=0;
START TRANSACTION;
INSERT INTO TestTab (`id`, `name`)
  SELECT IFNULL(max(id)+1, 1), 'insert 1' FROM TestTab Limit 0,1;


                                                                     SET autocommit=0;
                                                                     START TRANSACTION;
                                                                     INSERT INTO TestTab (`id`, `name`)
                                                                       SELECT IFNULL(max(id)+1, 1), 'insert 2' FROM TestTab Limit 0,1;
                                                                     -- *** GOOD *** INSERT blocks (ensures repeatable read for the SELECT)
COMMIT;
                                                                     -- only now connection 2 completes the the INSERT
                                                                     COMMIT;
SELECT * FROM TestTab;
                                                                     SELECT * FROM TestTab;


                                                    +----+----------+
                                                    | id | name     |
                                                    +----+----------+
                                                    |  1 | insert 1 |
                                                    |  2 | insert 2 |
                                                    +----+----------+

Command sequence demonstrating the problem with a MariaDB Galera cluster:

Connection 1                                                         Connection 2

SET autocommit=0;
START TRANSACTION;
INSERT INTO TestTab (`id`, `name`)
  SELECT IFNULL(max(id)+1, 1), 'insert 1' FROM TestTab Limit 0,1;


                                                                     SET autocommit=0;
                                                                     START TRANSACTION;
                                                                     INSERT INTO TestTab (`id`, `name`)
                                                                       SELECT IFNULL(max(id)+1, 1), 'insert 2' FROM TestTab Limit 0,1;
                                                                     -- *** BAD *** INSERT completes immediately, ignoring connection 1
COMMIT;
                                                                     COMMIT;
SELECT * FROM TestTab;
                                                                     SELECT * FROM TestTab;

                                                                     SELECT * FROM TestTab;

                                                    +----+----------+
                                                    | id | name     |
                                                    +----+----------+
                                                    |  1 | insert 1 |
                                                    |  1 | insert 2 | -- !!! same id for both records !!! --
                                                    +----+----------+

Obviously, the problem with the Galera cluster is that both records get the same value in the 'id' field. On the Galera cluster the INSERT ... SELECT pattern for generating unique IDs is broken.

To me this looks like a bug, or at least like very undesirable and unexpected behaviour.

Is this something that can be fixed with a different configuration of the Galera cluster?

I tried raising the isolation level to SERIALIZABLE for both sessions and that fixes the problem. But this is an extremely undesirable workaround, performance does matter for us here. And it seems that I was just lucky anyway: SERIALIZABLE isolation level is honored only between transactions issued on the same node and thus should be avoided. (quote from the Galera Cluster documentation)

What better workarounds can you think of?

Should I try to report this as a bug with MariaDB?

Upvotes: 2

Views: 1505

Answers (1)

Rick James
Rick James

Reputation: 142296

Be aware that Galera does not check in with the other nodes until COMMIT time. Meanwhile, the MAX(id) happily gets what will be an old value and does nothing to prevent someone else from looking at MAX(id).

Possible solutions (I have not tested any of these, nor have I check to see if the same problem and solution can happen on a single server without Galera.):

  • UNIQUE(id) -- This should cause the COMMIT to abort.
  • Use READ-COMMITTED to see the latest MAX(id).
  • Move the SELECT out of the INSERT and use FOR UPDATE.

Upvotes: 1

Related Questions