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.
