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'

S2 的執(zhí)行記錄：

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是一個很大的數(shù)；t4時刻顯示的trx_id是1289，看上去是一個比較正常的數(shù)字。這是為啥？
t1時，S1還未涉及更新，是一個只讀事務。對于只讀事務，InnoDB并不會分配trx_id：

• t1時，trx_id的值就是0。而這個很大的數(shù)，只是顯示用
• 直到S1在t3時執(zhí)行insert，InnoDB才真正分配trx_id。所以t4時，S2查到該trx_id的值就是1289。

除了明顯的修改類語句，若在select 語句后面加上for update，也不是只讀事務。

• update 和 delete語句除了事務本身，還涉及到標記刪除舊數(shù)據(jù)，即要把數(shù)據(jù)放到purge隊列里等待后續(xù)物理刪除，這個操作也會把max_trx_id+1， 因此在一個事務中至少加2
• InnoDB的后臺操作，比如表的索引信息統(tǒng)計這類操作，也是會啟動內(nèi)部事務的，因此你可能看到，trx_id值并不是按照加1遞增的。

t2時查到的很大數(shù)字是怎么來的？
每次查詢時，由系統(tǒng)臨時計算：當前事務的trx變量的指針地址轉(zhuǎn)成整數(shù)，再加上248

這樣可以保證：

• 因為同一只讀事務在執(zhí)行期間，它的指針地址不會變，所以無論在 innodb_trx還是在innodb_locks表里，同一個只讀事務查出來的trx_id就會是一樣的
• 若有并行只讀事務，每個事務的trx變量的指針地址肯定不同。這樣，不同并發(fā)只讀事務，查出來的trx_id就是不同的。

為什么要加248？

保證只讀事務顯示的trx_id值比較大，正常情況下就會區(qū)別于讀寫事務的id。但trx_id跟row_id的邏輯類似，定義為8個字節(jié)。
理論上還是可能出現(xiàn)一個讀寫事務與一個只讀事務顯示的trx_id相同。不過概率很低，也沒有什么實質(zhì)危害，不管。

為何只讀事務不分配trx_id？

• 減小事務視圖里面活躍事務數(shù)組的大小。因為當前正在運行的只讀事務，不影響數(shù)據(jù)的可見性判斷。所以，在創(chuàng)建事務的一致性視圖時，InnoDB就只需要拷貝讀寫事務的trx_id
• 減少trx_id的申請次數(shù)。InnoDB執(zhí)行一個普通的select語句，也要對應一個只讀事務。所以只讀事務優(yōu)化后，普通查詢語句無需申請trx_id，大大減少并發(fā)事務申請trx_id的鎖沖突

由于只讀事務不分配trx_id，顯然trx_id的增速變慢。
但 max_trx_id 會持久化存儲，重啟也不會重置為0。理論上，只要一個MySQL實例跑得夠久，就可能出現(xiàn)max_trx_id達到2^48 - 1，然后從0開始循環(huán)。

達到該狀態(tài)后，MySQL就會持續(xù)出現(xiàn)一個臟讀bug：
首先把當前的max_trx_id先修改成2^48 - 1。這里是可重復讀。

復現(xiàn)臟讀

因為系統(tǒng)的max_trx_id被設置成2^48 - 1，所以在session A啟動的事務TA的低水位就是2^48 - 1。

t2時:

• session B執(zhí)行第一條update語句的事務id=2^48 - 1
• 第二條事務id就是0了，這條update執(zhí)行后生成的數(shù)據(jù)版本上的trx_id=0

t3時：

session A執(zhí)行select的可見性判斷：c=3這個數(shù)據(jù)版本的trx_id（0），小于事務TA的低水位（2^48 - 1），所以認為該數(shù)據(jù)可見。

但這是臟讀。
由于低水位值會持續(xù)增加，而事務id從0開始計數(shù)，導致系統(tǒng)在該時刻后，所有查詢都會出現(xiàn)臟讀。

并且MySQL重啟時max_trx_id也不會清0，即重啟MySQL，這個bug仍然存在。那這bug也是只存在于理論上嗎？
假設一個MySQL實例的TPS是50w，持續(xù)這樣，17.8年后就會出現(xiàn)該情況。但從MySQL真正開始流行到現(xiàn)在，恐怕都還沒有實例跑到過這個上限。不過，只要MySQL實例服務時間夠長，就必然會出現(xiàn)該bug。

這也可以加深對低水位和數(shù)據(jù)可見性的理解。

thread_id

系統(tǒng)保存了一個全局變量thread_id_counter

每新建一個連接，就將thread_id_counter賦值給這個新連接的線程變量new_id。

thread_id_counter定義為4個字節(jié)，因此達到2^32 - 1，就會重置為0，繼續(xù)增加。

但不會在show processlist看到兩個相同的thread_id。因為MySQL使用了一個唯一數(shù)組

給新線程分配thread_id時的邏輯：

總結(jié)

每種自增id有各自的應用場景，在達到上限后的表現(xiàn)也不同：

• 表的自增id達到上限后，再申請時它的值就不會改變，進而導致繼續(xù)插入數(shù)據(jù)時報主鍵沖突錯誤
• row_id達到上限后，則會歸0再重新遞增，如果出現(xiàn)相同的row_id，后寫的數(shù)據(jù)會覆蓋之前的數(shù)據(jù)
• Xid只需要不在同一個binlog文件中出現(xiàn)重復值即可。雖然理論上會出現(xiàn)重復值，但是概率極小，可以忽略不計
• InnoDB的max_trx_id 遞增值每次MySQL重啟都會被保存起來，所以我們文章中提到的臟讀的例子就是一個必現(xiàn)的bug，好在留給我們的時間還很充裕
  

到此這篇關于線上MySQL的自增id用盡怎么辦的文章就介紹到這了,更多相關MySQL自增id用盡內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！