commit or rollback on a statement based replication, effect on auto increment id 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.
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 &gt; 1 -- another reason for gaps in the ids.