On Fri, 2005-05-27 at 10:13 +0200, Paul J Stevens wrote: > If you're not talking about auto_increment, what do you mean by mysql > supporting sequences. That's the only kind I know of in mysql.
See below. > > Fortunately, if auto_increment were also UNIQUE I would consider it a > > bug in MySQL if it allowed the UNIQUE constraint to be violated just > > because of replication. > > They are unique, and mysql doesn't allow violation. That's what > triggered this whole discussion. Then why not use two tables: CREATE TABLE SEQGEN(i INTEGER PRIMARY KEY AUTO_INCREMENT BLAH); then use: "id UNIQUE" as where you presently auto-increment. 1. begin transaction. 2. insert null into seqgen; 3. commit transaction. 4. begin transaction 5. write record 6. commit transaction > > The replication that we can support (and still support RFC2060) is one > > of: > > * single-master > > * single-lock > > > > There are no other kinds of replication possible (unless this UID > > requirement could go away). > > Wrong if we can avoid primary key collisions. No. If host "a" and host "b" both pick a UID number, they're a good chance that they will occur in the wrong order- a client seeing the uid list after "a" has posted, but before "b" has gets one view- and then "b" slides it's UID in historically. This can't happen because the client will never see it. There are no multi-master strictly-increasing sequence number generators. They don't exist. There are multi-master _sequence-space_ number generators, but UID isn't in sequence-space for many clients. > > single-lock means that when a resource is taken, the other replicants > > are made aware and not allowed to touch that data. essentially we have a > > serialized step. GFS and Oracle do this. PostgreSQL should be able to do > > this. > > > > now, we actually need FULL synchrony on a mailbox when inserting a > > message because those UID numbers must NEVER EVER jump backwards. > > Not if we can make sure that each node in the cluster uses it's own > subset of ids so they will never collide with ids generated by other > nodes. But I think we have established that this will lead us to run out > of ids pretty fast. And reordering for rfc compliance will be a bitch. A collision can occur if host "b" records a message with a lower UID after host "a" recorded it's message with a higher UID _IF_ a client has seen the mailbox in-between. You can't guarantee that when host "b" writes it's message that it will use a UID larger than "a" without one of the two above replication systems. > > this is why multi-master lockless synchronization is impossible. > > > > If the DB can't provide this- that is- if mysql really can break UNIQUE > > referential integrity (during replication), then we should toss UIDs > > altogether - make the UID==ID and set the UIDVALIDITY to the unique- > > identifier as you suggest (host number+timestamp) > > currently UID==message_idnr, UIDVALIDITY==mailbox_idnr simple as that. > > Ok. So now I'm beginning to suspect we can't do multi-master replication > safely without breaking the rfc. Even if we get the guid generation > right, we will still have to deal with making sure the message uids are > derived correctly. That will be very difficult. And even then we haven't > dealt with simultaneous updates on the same row on different masters. > For that reason alone, the mysql docs strongly advise a single master > setup. Agreed. But you can make ONE TABLE a sequence generation point safely (see my SQL above). Pg can serialize "JUST SEQUENCES" when mutlimastering (so you have multimaster records, but singlemaster sequences) -and MySQL can do this if we move sequence generation into the db driver. > Perhaps we should fix the dbmail db api to use different db_params for > reads and writes. That way, we can use a single master for writes, and > the master or one of the slaves for reads. Nope. SQLite and Pg won't benefit from such a setup, and MySQL can non- trivially "imitate" this behavior. Note that only SEQUENCE NUMBERS have to be generated from single-master. The rest of the inserts can safely be done multi-master PROVIDED they can lock the sequence generator (D1=single master, D2=multi): D1 LOCK TABLE SEQUENCEFOO; D1 GET SEQUENCE NUMBER; (see above SQL for this) D2 BEGIN D2 INSERT D2 COMMIT D1 UNLOCK TABLE SEQUENCEFOO; This "temporarily" puts the system into single-master mode- but what happens if D1 dies between D2-commit and D1-unlock? D1 LOCK TABLE SEQUENCEFOO; D1 GET SEQUENCE NUMBER; (see above SQL for this) D2 BEGIN D2 INSERT (but mark unusable) D2 COMMIT D1 UNLOCK TABLE SEQUENCEFOO; D1 LOCK TABLE SEQUENCEFOO D2 BEGIN D2 UPDATE (mark usable) D2 COMMIT D1 UNLOCK TABLE SEQUENCEFOO so now we've just implemented the token-ring shared lock in SQL with two databases (one single-master one multi-master). -- Internet Connection High Quality Web Hosting http://www.internetconnection.net/
