MySQL的自增id都定義了初始值，然后不斷加步長。雖然自然數沒有上限，但定義了表示這個數的字節長度，計算機存儲就有上限。比如，無符號整型(unsigned int)是4個字節，上限就是2^32 - 1。那自增id用完，會怎么樣？
表定義自增值id
表定義的自增值達到上限后的邏輯是：再申請下一個id時，得到的值保持不變。
mysql> create table t(id int unsigned auto_increment primary key) auto_increment=4294967295;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t values(null);
Query OK, 1 row affected (0.00 sec)
mysql> show create table t;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
t | CREATE TABLE `t` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4294967295 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
//成功插入一行 4294967295
mysql> insert into t values(null);
ERROR 1062 (23000): Duplicate entry '4294967295' for key 't.PRIMARY'
第一個insert成功后，該表的AUTO_INCREMENT還是4294967295，導致第二個insert又拿到相同自增id值，再試圖執行插入語句，主鍵沖突。
2^32 - 1（4294967295）不是一個特別大的數，一個頻繁插入刪除數據的表是可能用完的。建表時就需要考慮你的表是否有可能達到該上限，若有，就應創建成8字節的bigint unsigned。
nnoDB系統自增row_id
若你創建的InnoDB表未指定主鍵，則InnoDB會自動創建一個不可見的，6個字節的row_id。InnoDB維護了一個全局的dict_sys->row_id值。
所有無主鍵的InnoDB表，每插入一行數據，都將當前的dict_sys->row_id作為要插入數據的row_id，然后把dict_sys->row_id加1。
代碼實現時row_id是個長度為8字節的無符號長整型(bigint unsigned)。但InnoDB在設計時，給row_id留的只是6個字節的長度，這樣寫到數據表中時只放了最后6個字節，所以row_id能寫到數據表中的值，就有兩個特征：
- row_id寫入表中的值范圍，是從0到2^48 - 1
- 當dict_sys.row_id=2^48時，如果再有插入數據的行為要來申請row_id，拿到以后再取最后6個字節的話就是0
即寫入表的row_id從0~2^48 - 1。達到上限后，下個值就是0，然后繼續循環。
2^48 - 1已經很大，但若一個MySQL實例活得久，還是可能達到上限。
InnoDB里，申請到row_id=N后，就將這行數據寫入表中；若表中已經存在row_id=N的行，新寫入的行就會覆蓋原有的行。
驗證該結論：通過gdb修改系統的自增row_id。用gdb是為了便于復現問題，只能在測試環境使用。
- row_id用完的驗證序列
- row_id用完的效果驗證
可見，在我用gdb將dict_sys.row_id設置為2^48之后，再插入a=2會出現在表t的第一行，因為該值的row_id=0。
之后再插入a=3，由于row_id=1，就覆蓋了之前a=1的行，因為a=1這一行的row_id也是1。
所以應該在InnoDB表中主動創建自增主鍵：當表自增id到達上限后，再插入數據時會報主鍵沖突錯誤。
畢竟覆蓋數據，就意味著數據丟失，影響數據可靠性；報主鍵沖突，插入失敗，影響可用性。一般可靠性優于可用性。
Xid
redo log和binlog有個共同字段Xid，用來對應事務。Xid在MySQL內部是如何生成的呢？
MySQL內部維護了一個全局變量global_query_id。
每次執行語句時，將它賦值給query_id，然后給該變量+1：
若當前語句是該事務執行的第一條語句，則MySQL還會同時把query_id賦值給該事務的Xid：
而global_query_id是一個純內存變量，重啟之后就清零了。所以同一DB實例，不同事務的Xid可能相同。
但MySQL重啟之后會重新生成新binlog文件，這就保證同一個binlog文件里的Xid唯一。
雖然MySQL重啟不會導致同一個binlog里面出現兩個相同Xid，但若global_query_id達到上限，就會繼續從0開始計數。理論上還是會出現同一個binlog里面出現相同Xid。
因為global_query_id8字節，上限2^64 - 1。要出現這種情況，需滿足：
- 執行一個事務，假設Xid是A
- 接下來執行2^64次查詢語句，讓global_query_id回到A
- 2^64太大了，這種可能只存在于理論中。
- 再啟動一個事務，這個事務的Xid也是A
Innodb trx_id
- Xid由server層維護
InnoDB內部使用Xid，為了關聯InnoDB事務和server
但InnoDB自己的trx_id，是另外維護的事務id（transaction id）。
InnoDB內部維護了一個max_trx_id全局變量，每次需要申請一個新的trx_id時，就獲得max_trx_id的當前值，然后并將max_trx_id加1。
InnoDB數據可見性的核心思想
每一行數據都記錄了更新它的trx_id，當一個事務讀到一行數據時，判斷該數據是否可見，就是通過事務的一致性視圖與這行數據的trx_id做對比。
對于正在執行的事務，你可以從information_schema.innodb_trx表中看到事務的trx_id。
看如下案例：事務的trx_id
S2 的執行記錄：
mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select trx_id, trx_mysql_thread_id from innodb_trx;
+-----------------+---------------------+
trx_id | trx_mysql_thread_id |
+-----------------+---------------------+
421972504382792 | 70 |
+-----------------+---------------------+
1 row in set (0.00 sec)
mysql> select trx_id, trx_mysql_thread_id from innodb_trx;
+---------+---------------------+
trx_id | trx_mysql_thread_id |
+---------+---------------------+
1355623 | 70 |
+---------+---------------------+
1 row in set (0.01 sec)
S2從innodb_trx表里查出的這兩個字段，第二個字段trx_mysql_thread_id就是線程id。顯示線程id，是為說明這兩次查詢看到的事務對應的線程id都是5，即S1所在線程。
t2時顯示的trx_id是一個很大的數；t4時刻顯示的trx_id是1289，看上去是一個比較正常的數字。這是為啥？
t1時，S1還未涉及更新，是一個只讀事務。對于只讀事務，InnoDB并不會分配trx_id：
- t1時，trx_id的值就是0。而這個很大的數，只是顯示用；
- 直到S1在t3時執行insert，InnoDB才真正分配trx_id。所以t4時，S2查到該trx_id的值就是1289。
除了明顯的修改類語句，若在select 語句后面加上for update，也不是只讀事務。
- update 和 delete語句除了事務本身，還涉及到標記刪除舊數據，即要把數據放到purge隊列里等待后續物理刪除，這個操作也會把max_trx_id+1， 因此在一個事務中至少加2；
- InnoDB的后臺操作，比如表的索引信息統計這類操作，也是會啟動內部事務的，因此你可能看到，trx_id值并不是按照加1遞增的。
t2時查到的很大數字是怎么來的？
每次查詢時，由系統臨時計算：
當前事務的trx變量的指針地址轉成整數，再加上248
這樣可以保證：
- 因為同一只讀事務在執行期間，它的指針地址不會變，所以無論在 innodb_trx還是在innodb_locks表里，同一個只讀事務查出來的trx_id就會是一樣的。
- 若有并行只讀事務，每個事務的trx變量的指針地址肯定不同。這樣，不同并發只讀事務，查出來的trx_id就是不同的。
為什么要加248？
保證只讀事務顯示的trx_id值比較大，正常情況下就會區別于讀寫事務的id。但trx_id跟row_id的邏輯類似，定義為8個字節。
理論上還是可能出現一個讀寫事務與一個只讀事務顯示的trx_id相同。不過概率很低，也沒有什么實質危害，不管。
為何只讀事務不分配trx_id？
- 減小事務視圖里面活躍事務數組的大小。因為當前正在運行的只讀事務，不影響數據的可見性判斷。所以，在創建事務的一致性視圖時，InnoDB就只需要拷貝讀寫事務的trx_id。
- 減少trx_id的申請次數。InnoDB執行一個普通的select語句，也要對應一個只讀事務。所以只讀事務優化后，普通查詢語句無需申請trx_id，大大減少并發事務申請trx_id的鎖沖突。
由于只讀事務不分配trx_id，顯然trx_id的增速變慢。
但 max_trx_id 會持久化存儲，重啟也不會重置為0。理論上，只要一個MySQL實例跑得夠久，就可能出現max_trx_id達到2^48 - 1，然后從0開始循環。
達到該狀態后，MySQL就會持續出現一個臟讀bug：
首先把當前的max_trx_id先修改成2^48 - 1。這里是可重復讀。
- 復現臟讀
- 因為系統的max_trx_id被設置成2^48 - 1，所以在session A啟動的事務TA的低水位就是2^48 - 1。
t2時：
- session B執行第一條update語句的事務id=2^48 - 1
- 第二條事務id就是0了，這條update執行后生成的數據版本上的trx_id=0
t3時：
session A執行select的可見性判斷：c=3這個數據版本的trx_id（0），小于事務TA的低水位（2^48 - 1），所以認為該數據可見。
但這是臟讀。
由于低水位值會持續增加，而事務id從0開始計數，導致系統在該時刻后，所有查詢都會出現臟讀。
并且MySQL重啟時max_trx_id也不會清0，即重啟MySQL，這個bug仍然存在。那這bug也是只存在于理論上嗎？
假設一個MySQL實例的TPS是50w，持續這樣，17.8年后就會出現該情況。但從MySQL真正開始流行到現在，恐怕都還沒有實例跑到過這個上限。不過，只要MySQL實例服務時間夠長，就必然會出現該bug。
這也可以加深對低水位和數據可見性的理解。
thread_id
系統保存了一個全局變量thread_id_counter。
每新建一個連接，就將thread_id_counter賦值給這個新連接的線程變量new_id。
thread_id_counter定義為4個字節，因此達到2^32 - 1，就會重置為0，繼續增加。
但不會在show processlist看到兩個相同的thread_id。因為MySQL使用了一個唯一數組。
給新線程分配thread_id時的邏輯：
總結
每種自增id有各自的應用場景，在達到上限后的表現也不同：
- 表的自增id達到上限后，再申請時它的值就不會改變，進而導致繼續插入數據時報主鍵沖突錯誤。
- row_id達到上限后，則會歸0再重新遞增，如果出現相同的row_id，后寫的數據會覆蓋之前的數據。
- Xid只需要不在同一個binlog文件中出現重復值即可。雖然理論上會出現重復值，但是概率極小，可以忽略不計。
- InnoDB的max_trx_id 遞增值每次MySQL重啟都會被保存起來，所以我們文章中提到的臟讀的例子就是一個必現的bug，好在留給我們的時間還很充裕。
作者丨廢柴程序員
來源丨https://www.jianshu.com/p/a6bc14005b52
