0

How commit or rollback is safe on statement based replication in terms of auto_increment column?

If I execute below queries, shoudn't there be discrepancy between master and slave in auto_increment column in case of statement based replication.

CREATE TABLE t1 ( c1 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 CHAR(1)) ENGINE = INNODB;

Session 1 begin; INSERT INTO t1 (c2) VALUES ('a'), ('b'), ('c'), ('d'); should increase c1 to 4 in master while no affect in slave as not yet commited, so not yet written to binlog

Session 2 begin; INSERT INTO t1 (c2) VALUES ('a'), ('b'), ('c'), ('d'); commit; should have c1 start from 5 in master and from 1 in slave

Session 1 rollback or commit doesn't matter

https://dev.mysql.com/doc/refman/5.7/en/binary-log.html

Binary logging is done immediately after a statement or transaction completes but before any locks are released or any commit is done. This ensures that the log is logged in commit order. Within an uncommitted transaction, all updates (UPDATE, DELETE, or INSERT) that change transactional tables such as InnoDB tables are cached until a COMMIT statement is received by the server. At that point, mysqld writes the entire transaction to the binary log before the COMMIT is executed.

https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html

In all lock modes (0, 1, and 2), if a transaction that generated auto-increment values rolls back, those auto-increment values are “lost”. Once a value is generated for an auto-increment column, it cannot be rolled back, whether or not the “INSERT-like” statement is completed, and whether or not the containing transaction is rolled back.

0

I think you will find an extra "statement" in the binlog that assigns the auto_inc value for the subsequent statement. This guarantees that the Slave has the same ids for SBR.

For RBR (Row-Based-Replication), there is no issue, since the id is part of the INSERT.

In addition to the possibility of "burning" ("losing") ids, the ids can be replicated out of order. This because ids are grabbed as needed, but replication is in COMMIT-order. (This applies to SBR and RBR.)

For multi-master, auto_increment_increment > 1 -- another reason for gaps in the ids.

  • Are you saying, as soon as an insert-like statement is executed on master, whether comitted or not, a binlog statement to increase auto-increment of slave is raised in case of SBR (Also, does it happen for RBR as well, though it would be insignificant for RBR). Also, your answer made me curious about, how multi-master would handle auto-increment for SBR. – vaibhav gupta Jan 4 '18 at 18:47
  • Dual-master, for example, avoids collisions by setting auto_increment_increment = 2 and auto_increment_offset to different values (1 vs 2). – Rick James Jan 4 '18 at 20:58
  • for multi-master, i m asking about SBR and its effect on autoincrment. Let's say master1 with odd auto_inc has 3 rows inserted, so its next auto_inc is 7, after that master2 with even auto_inc has its first row inserted, how would that be replicated on slave using SBR. – vaibhav gupta Jan 5 '18 at 5:10
  • Yes, it will be replicated, presumably with id=2. It is ok for id=2 to arrive at the slave after ids 1,3,5; this does not violate the mandate provided by AUTO_INCREMENT (uniqueness). Set up a test case and see what happens. – Rick James Jan 5 '18 at 14:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.