未提交事務導致AUTO_INCREMENT修改失敗的問題
測試環境某個資料庫的表由於數據處理，在插入數據時指定了很大的id，導致現在自增id值變得很大，現在需要將該表的id自增值重置。
可以看到當前該表的max id為47，但是AUTO_INCREMENT=999910004。根據業務方要求，需要把AUTO_INCREMENT重置為100。
查看該表max id目前是47
mysql> select max(id) from t_offline_auth;
+---------+
| max(id) |
+---------+
| 47 |
+---------+
1 row in set (0.00 sec)
mysql> show create table t_offline_authG
*************************** 1. row ***************************
Table: t_offline_auth
Create Table: CREATE TABLE `t_offline_auth` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT "主鍵",
`order_id` varchar(32) NOT NULL COMMENT "訂單號",
`gmt_create` datetime DEFAULT NULL COMMENT "創建時間",
`operator` int(11) DEFAULT NULL COMMENT "操作人",
`is_deleted` smallint(4) NOT NULL DEFAULT "0" COMMENT "是否刪除 0未刪除 1已刪除",
PRIMARY KEY (`id`),
KEY `idx_t_offline_auth_orderid` (`order_id`)
1 row in set (0.00 sec)
我們執行變更自增主鍵值的命令alter table t_offline_auth AUTO_INCREMENT=100 ，發現執行的時候是hang起來的，發現該操作在拿metadata lock,理論上該操作只會修改內存里的值，不會該表數據文件。
Cmd ID State User Host DB Time Query
| 83025739 | root | localhost | checkup | Query | 409 | Waiting for table metadata lock | alter table t_offline_auth AUTO_INCREMENT=100 |
剛開始以為和innodb_autoinc_lock_mode有關係，調整的innodb_autoinc_lock_mode的時候發現無法調整。
mysql> select @@innodb_autoinc_lock_mode;
+----------------------------+
| @@innodb_autoinc_lock_mode |
+----------------------------+
| 1 |
+----------------------------+
1 row in set (0.00 sec)
mysql> set @@innodb_autoinc_lock_mode=2;
ERROR 1238 (HY000): Variable "innodb_autoinc_lock_mode" is a read only variable
後來懷疑是有未提交的事務導致的，我們查詢INNODB_TRX，發現91105921909這個事務一直都沒有提交。
mysql> SELECT * FROM information_schema.INNODB_TRX;
+-------------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
| trx_id | trx_state | trx_started | trx_requested_lock_id | trx_wait_started | trx_weight | trx_mysql_thread_id | trx_query | trx_operation_state | trx_tables_in_use | trx_tables_locked | trx_lock_structs | trx_lock_memory_bytes | trx_rows_locked | trx_rows_modified | trx_concurrency_tickets | trx_isolation_level | trx_unique_checks | trx_foreign_key_checks | trx_last_foreign_key_error | trx_adaptive_hash_latched | trx_adaptive_hash_timeout | trx_is_read_only | trx_autocommit_non_locking |
+-------------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
| 91118057382 | RUNNING | 2018-03-20 17:46:42 | NULL | NULL | 0 | 82323529 | NULL | NULL | 0 | 0 | 0 | 360 | 0 | 0 | 0 | READ COMMITTED | 1 | 1 | NULL | 0 | 9993 | 0 | 0 |
| 91105921909 | RUNNING | 2018-03-20 15:39:56 | NULL | NULL | 0 | 83001320 | NULL | NULL | 0 | 0 | 0 | 360 | 0 | 0 | 0 | READ COMMITTED | 1 | 1 | NULL | 0 | 10000 | 0 | 0 |
+-------------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
2 rows in set (0.00 sec)
然後kill掉這個事務，上面的SQL就可以執行了。
mysql> alter table t_offline_auth AUTO_INCREMENT=100;
Query OK, 0 rows affected (9 min 15.77 sec)
Records: 0 Duplicates: 0 Warnings: 0
如何監控未提交的事務，可以通過下面這個SQL來監控，超過一定時間未提交的事務就會被捕捉到，然後可以考慮加入到監控平台監控起來：
mysql> SET @threshold = 10;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT
p. USER,
LEFT(
p. HOST,
LOCATE(":", p. HOST)- 1
)HOST,
p.id,
TIMESTAMPDIFF(SECOND, t.TRX_STARTED, NOW())duration,
COUNT(
DISTINCT ot.REQUESTING_TRX_ID
)waiting,
p.DB db,
p.COMMAND command
FROM
INFORMATION_SCHEMA.INNODB_TRX t
JOIN INFORMATION_SCHEMA. PROCESSLIST p ON(p.ID = t.TRX_MYSQL_THREAD_ID)
LEFT JOIN INFORMATION_SCHEMA.INNODB_LOCK_WAITS ot ON(
ot.BLOCKING_TRX_ID = t.TRX_id
)
WHERE
t.TRX_STARTED + INTERVAL @threshold SECOND
GROUP BY
LEFT(
p. HOST,
LOCATE(":", p. HOST)- 1
),
p.id,
duration
HAVING
duration >= @threshold
OR waiting > 0;
輸出結果：
+---------------+--------------+----------+----------+---------+---------------+---------+
| user | host | id | duration | waiting | db | command |
+---------------+--------------+----------+----------+---------+---------------+---------+
+---------------+--------------+----------+----------+---------+---------------+---------+
1 row in set (0.02 sec)
TAG:DBARUN社區 |