Re: About AUTO-INC In different mode (1,2 or 3)
Posted by:
five small
Date: April 22, 2021 02:31AM
In MySQL offical:
Using auto-increment with replication
...
If you are using statement-based replication, set innodb_autoinc_lock_mode to 0 or 1 and use the same value on the source and its replicas. Auto-increment values are not ensured to be the same on the replicas as on the source if you use innodb_autoinc_lock_mode = 2 (“interleaved”) or configurations where the source and replicas do not use the same lock mode.
...
and then I use next sql to insert and innodb_autoinc_lock_mode=1 :
tx1:insert into t1 values(1,"a"),(null,"b"),(3,"c"),(null,"d");
tx2:insert into t1 values(5,"a"),(null,"b"),(7,"c"),(null,"d");
In my opinion,if there is not a lock,we may get Auto_increment numbers interleaved? I think it's not safe for statement-based replication,