MySQL Forums
Forum List  »  Replication

Re: About AUTO-INC In different mode (1,2 or 3)
Posted by: five small
Date: April 21, 2021 11:50PM

So sorry i can't understand, let me put an example,and this is from MySQL official

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

-------------------------------------------------------------------------------
innodb_autoinc_lock_mode = 1

there are two transactions:
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");

I want to know whether there are Auto-Inc locks for "Mixed-insert"? And in this situation ,Is statement-based replication repeatable?
-------------------------------------------------------------------------------
innodb_autoinc_lock_mode = 2

there are two transactions:
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");

and according to MySQL offical,my tx1 may be like this:

mysql> SELECT c1, c2 FROM t1 ORDER BY c2;
+-----+------+
| c1 | c2 |
+-----+------+
| 1 | a |
| x | b |
| 3 | c |
| y | d |
+-----+------+
The values of x and y are unique and larger than any previously generated rows. However, the specific values of x and y depend on the number of auto-increment values generated by concurrently executing statements.

So I want to know what's difference between mode=1 and mode=2 ?

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: About AUTO-INC In different mode (1,2 or 3)
120
April 21, 2021 11:50PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.