2

I need to have a sequence number (not for primary key nor auto_increment) in MariaDB using Galera Cluster.

I was thinking of having a table with sequence name, current value and using stored procedures to return the next value.

I am not sure a Galera Cluster will support this (http://www.sqlines.com/oracle-to-mysql/sequence):

CREATE FUNCTION NextVal (vname VARCHAR(30))
  RETURNS INT
BEGIN

UPDATE _sequences
  SET next = (@next := next) + 1
  WHERE name = vname;

  RETURN @next;
END

It works in my tests but I don´t know if I can guarantee this. Should I continue this path or should I take care of this another way?

supercoco
  • 512
  • 2
  • 7
  • 25
  • 2
    MariaDB 10.3 will support [Oracle-like sequences](https://mariadb.com/kb/en/library/create-sequence/). I assume this is for a current release of MariaDB? – markusjm Nov 30 '17 at 10:57
  • 1
    GREAT! Hope it works in a Galera cluster – supercoco Nov 30 '17 at 15:08
  • I would make `vname` the `PRIMARY KEY` of `_sequences`. Off hand, the function looks Galera-safe. But note -- this will give you _unique_ values, but not necessarily _consecutive_, nor _ordered_, values. Please explain why `AUTO_INCREMENT` is not "good enough". – Rick James Dec 01 '17 at 00:18
  • @RickJames what if i need *consecutive* values? And yet keep it galera-safe ? Auto-increment is not good because it's not consecutive in galera or even regular master-master replication – poncha Jun 06 '22 at 09:52
  • @poncha - Are you using a single Primary node? Or are you writing to all nodes? – Rick James Jun 06 '22 at 19:38
  • @RickJames i'm writing through all nodes – poncha Jun 08 '22 at 06:56

1 Answers1

0

Any technique that involves getting consecutive numbers in a multi-primary (M-M, Galera, InnoDB Cluster, etc) must synchronously wait for all writable nodes to reply before providing the next sequence number. This will take millisecond(s). Or longer if the nodes are geographically far apart.

Can you afford such delays? If you don't need more than, say, 100 new numbers per second, then some ad hoc mechanism should work adequately. Off hand, your stored function seems viable for such.

Be sure to check for errors even on COMMIT. If you get a deadlock, replay the transaction.

I suggest grabbing the "next" number outside any transaction, else the time taken for the rest of the transaction will stall all other nodes.

However, grabbing the number in its own transaction can lead to cases where row using that sequence number is available in some node after the next number has arrived. That is, item 123 seems to be very briefly missing even though 124 is already in the table. If this is unacceptable, we need a more complex (and slower) solution.

(I have seen applications with either of the two cases mentioned. I don't know which one applies to your application.)

Rick James
  • 135,179
  • 13
  • 127
  • 222