在業務開發過程中，顯示的開啟事務并且在事務處理過程中對不同的情況進行顯示的COMMIT或ROLLBACK，這是一個完整數據庫事務處理的閉環過程。
這種在應用開發邏輯層面去handle的事務執行的結果，既確保了事務操作的數據完整性，又遵循了業務處理邏輯。所以顯示的提交或回滾事務也是開發規范中的要求，但是也有一些存量的業務系統或開發人員并不能嚴格按照這一規范執行，進而在實際生產過程中引發故障。這里介紹一個因為開啟事務后未顯示的回滾導致DDL阻塞進而引發的問題。
應用系統使用的是MySQL生態的數據庫，業務使用的是分區表，業務在處理時候因為當日的分區沒有創建導致插入報錯，應用邏輯上每日又有對表新增分區的操作，結果是事務沒有顯示回滾導致新增表分區的DDL阻塞，進而又引發后續的問題。
1、MySQL數據庫故障模擬
1.1 創建分區表并插入數據
登錄mysql數據庫并創建分區表
CREATE TABLE tt1 ( id int NOT NULL, sdate date NOT NULL, c1 varchar(4) NOT NULL, PRIMARY KEY (id, sdate)
)
PARTITION BY RANGE columns(sdate) ( PARTITION p20240524 VALUES LESS THAN ('2024-05-25'), PARTITION p20240525 VALUES LESS THAN ('2024-05-26')
);
1.2 顯示的開啟事務并插入數據
mysql> begin;
mysql> select * from tango.tt1;
+----+------------+-----+
| id | sdate | c1 |
+----+------------+-----+
| 1 | 2024-05-25 | aaa |
+----+------------+-----+
1 row in set (0.00 sec)insert into tt1 values(1,'2024-05-25','aaa');
mysql> insert into tt1 values(3,'2024-05-27','ccc');
ERROR 1526 (HY000): Table has no partition for value from column_list
數據庫執行報錯提示插入的記錄分區不存在。
1.3 查看數據庫表中鎖和事務的狀態
mysql> select * from performance_schema.metadata_locks where object_name='tt1';
+-------------+---------------+-------------+-------------+-----------------------+--------------+---------------+-------------+-------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+---------------+-------------+-------------+-----------------------+--------------+---------------+-------------+-------------------+-----------------+----------------+
| TABLE | tango | tt1 | NULL | 140712994313232 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:5768 | 85 | 24 |
| TABLE | tango | tt1 | NULL | 140712994947616 | SHARED_WRITE | TRANSACTION | GRANTED | sql_parse.cc:5768 | 85 | 25 |
+-------------+---------------+-------------+-------------+-----------------------+--------------+---------------+-------------+-------------------+-----------------+----------------+
2 rows in set (0.00 sec)
可以看到表持有SHARED_READ和SHARED_WRITE鎖，并不因為事務執行失敗而釋放，這也是mysql系數據庫內核機制，事務報錯后數據庫層面并沒有執行rollback操作，而是由應用自己決定是rollback還是commit。
1.4 其它業務執行新增分區的DDL操作
mysql> ALTER table tt1 ADD PARTITION ( PARTITION p20240526 VALUES LESS THAN ('2024-05-27') );
此時這個DDL操作會hang住，查看表的元數據鎖情況
mysql> select * from performance_schema.metadata_locks where object_name='tt1';
+-------------+---------------+-------------+-------------+-----------------------+-------------------+---------------+-------------+-------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+---------------+-------------+-------------+-----------------------+-------------------+---------------+-------------+-------------------+-----------------+----------------+
| TABLE | tango | tt1 | NULL | 140712801139968 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:5768 | 123 | 21 |
| TABLE | tango | tt1 | NULL | 140712793308528 | SHARED_WRITE | TRANSACTION | GRANTED | sql_parse.cc:5768 | 123 | 22 |
| TABLE | tango | tt1 | NULL | 140712926580592 | SHARED_UPGRADABLE | TRANSACTION | GRANTED | sql_parse.cc:5768 | 121 | 20 |
| TABLE | tango | tt1 | NULL | 140712928177104 | EXCLUSIVE | TRANSACTION | PENDING | mdl.cc:3753 | 121 | 20 |
+-------------+---------------+-------------+-------------+-----------------------+-------------------+---------------+-------------+-------------------+-----------------+----------------+
5 rows in set (0.00 sec)
可以看到一個pending狀態的鎖狀態，查看對應的SQL語句，知道是新增分區的DDL操作。
mysql> select THREAD_ID,EVENT_ID,EVENT_NAME,TIMER_START,TIMER_END,TIMER_WAIT,LOCK_TIME,SQL_TEXT,STATEMENT_ID from events_statements_current where thread_id=121;
+-----------+----------+---------------------------+------------------+------------------+----------------+-----------+---------------------------------------------------------------------------------------+--------------+
| THREAD_ID | EVENT_ID | EVENT_NAME | TIMER_START | TIMER_END | TIMER_WAIT | LOCK_TIME | SQL_TEXT | STATEMENT_ID |
+-----------+----------+---------------------------+------------------+------------------+----------------+-----------+---------------------------------------------------------------------------------------+--------------+
| 121 | 20 | statement/sql/alter_table | 2670208499587000 | 2687425357664000 | 17216858077000 | 246000000 | ALTER table tt1 ADD PARTITION ( PARTITION p20240526 VALUES LESS THAN ('2024-05-27') ) | 32613 |
+-----------+----------+---------------------------+------------------+------------------+----------------+-----------+---------------------------------------------------------------------------------------+--------------+
1 row in set (0.00 sec)
這里的DDL操作，在mysql數據庫中通過參數lock_wait_timeout控制DDL等待超時時間，超過該時間DDL會報錯。默認該參數配置為31536000s，實際生產業務系統會設置30~60s，一些核心業務系統會設置為5s。但是在DDL阻塞期間，也會影響新的業務的執行。
1.5 影響新的業務操作
mysql> select * from tango.tt1;
該操作也會hang住，查看對應的鎖情況，也是處于pending狀態。也就是阻塞的DDL操作會影響接下去的業務對該表的訪問，直到DDL超時失敗后，后續的業務才會正常。
mysql> select * from performance_schema.metadata_locks where object_name='tt1';
+-------------+---------------+-------------+-------------+-----------------------+-------------------+---------------+-------------+-------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+---------------+-------------+-------------+-----------------------+-------------------+---------------+-------------+-------------------+-----------------+----------------+
| TABLE | tango | tt1 | NULL | 140712801139968 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:5768 | 123 | 21 |
| TABLE | tango | tt1 | NULL | 140712793308528 | SHARED_WRITE | TRANSACTION | GRANTED | sql_parse.cc:5768 | 123 | 22 |
| TABLE | tango | tt1 | NULL | 140712926580592 | SHARED_UPGRADABLE | TRANSACTION | GRANTED | sql_parse.cc:5768 | 121 | 20 |
| TABLE | tango | tt1 | NULL | 140712928177104 | EXCLUSIVE | TRANSACTION | PENDING | mdl.cc:3753 | 121 | 20 |
| TABLE | tango | tt1 | NULL | 140713468045808 | SHARED_READ | TRANSACTION | PENDING | sql_parse.cc:5768 | 120 | 6 |
+-------------+---------------+-------------+-------------+-----------------------+-------------------+---------------+-------------+-------------------+-----------------+----------------+
5 rows in set (0.00 sec)
1.6 在這個場景下存在的問題
- 事務處理報錯時，業務層沒有handle這個報錯，并顯示的去做commit或rollback；
- 表分區的預創建和監控：對于分區表是要有預先創建分區的機制，每天或每月定時窗口創建一批分區，同時分區不足時能夠及時告警出來；
- 數據庫層元數據鎖等待超時：有些不重要的業務系統將lock_wait_timeout設置為600s設置更大，在該故障場景下是存在問題的，相當于DDL阻塞的這期間新的業務也會受到影響。所以將該參數設置到合理區間，比如5~60s是有必要的。
對于MySQL生態的數據庫，事務內執行失敗后數據庫沒有鎖資源沒有釋放本身機制上沒有問題，像國產數據庫中TiDB、GoldenDB都有類似的現象。對于其它數據庫，比如Oracle、PostgreSQL等，針對這個場景是什么樣的表現，接下去以openGauss數據庫為例進行驗證。
2、openGauss數據庫下故障場景模擬
2.1 登錄openGauss單機版數據庫，并創建分區表
gsql -d postgres -p 5432
[opgauss@tango-01 data]$ gsql -d postgres -p 5432
gsql ((openGauss-lite 5.0.2 build 48a25b11) compiled at 2024-05-14 10:41:04 commit 0 last mr release)
openGauss=# create database tango;tango=# CREATE TABLE tt1 (
tango(# id int NOT NULL,
tango(# sdate date NOT NULL,
tango(# c1 varchar(4) NOT NULL
tango(# )
tango-# PARTITION BY RANGE(sdate) (
tango(# PARTITION p20240524 VALUES LESS THAN ('2024-05-25'),
tango(# PARTITION p20240525 VALUES LESS THAN ('2024-05-26')
tango(# );
CREATE TABLEtango=# \dt
Schema | Name | Type | Owner | Storage
--------+------+-------+---------+----------------------------------public | tt1 | table | opgauss | {orientation=row,compression=no}
2.2 開啟事務并插入數據
tango=# begin;
BEGIN
tango=# select * from tt1;id | sdate | c1
----+---------------------+-----1 | 2024-05-25 00:00:00 | aaa
(1 row)tango=# insert into tt1 values(3,'2024-05-28','ccc');
ERROR: inserted partition key does not map to any table partition
提示報錯分區不存在
2.3 另外開啟一個任務執行新增分區操作
tango=# ALTER table tt1 ADD PARTITION p20240526 VALUES LESS THAN ('2024-05-27');
ALTER TABLE
可以看到分區是新增成功的。
2.4 查看這種場景下表的鎖和事務狀態信息
tango=# SELECT l.locktype,l.database,l.relation::regclass,l.page,l.tuple,l.virtualxid,l.transactionid,l.classid,l.objid,l.objsubid,l.pid,l.mode,l.granted FROM pg_locks l JOIN pg_class c ON l.relation = c.oid WHERE c.relname = 'tt1';locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | pid | mode | granted
----------+----------+----------+------+-------+------------+---------------+---------+-------+----------+-----------------+-----------------+---------relation | 16384 | tt1 | | | | | | | | 140405684233984 | AccessShareLock | t
(1 row)tango=# SELECT datname,pid,sessionid,usename,application_name,backend_start,xact_start,query_start,state,query FROM pg_stat_activity where datname='tango';datname | pid | sessionid | usename | application_name | backend_start | xact_start | query_start | state | query
---------+-----------------+-----------+---------+------------------+-------------------------------+-------------------------------+-------------------------------+------
---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------tango | 140405684233984 | 8 | opgauss | gsql | 2024-05-26 15:45:47.008274+08 | 2024-05-26 15:47:40.481015+08 | 2024-05-26 15:47:45.822262+08 | idle
in transaction | select * from tt1;
當執行失敗后，事務處于idle in transaction (aborted)狀態，表鎖持有的鎖也不存在了。
tango=# SELECT l.locktype,l.database,l.relation::regclass,l.page,l.tuple,l.virtualxid,l.transactionid,l.classid,l.objid,l.objsubid,l.pid,l.mode,l.granted FROM pg_locks l JOIN pg_class c ON l.relation = c.oid WHERE c.relname = 'tt1';locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | pid | mode | granted
----------+----------+----------+------+-------+------------+---------------+---------+-------+----------+-----+------+---------
(0 rows)tango=# SELECT datname,pid,sessionid,usename,application_name,backend_start,xact_start,query_start,state,query FROM pg_stat_activity where datname='tango';datname | pid | sessionid | usename | application_name | backend_start | xact_start | query_start | state | query ---------+-----------------+-----------+---------+------------------+-------------------------------+-------------------------------+-------------------------------+------
-------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------
----tango | 140405684233984 | 8 | opgauss | gsql | 2024-05-26 15:45:47.008274+08 | | 2024-05-26 15:49:09.048895+08 | idle
in transaction (aborted) | insert into tt1 values(3,'2024-05-28','ccc');
可以看到openGauss數據庫和MySQL數據庫在這種故障場景下的不同表現，對于openGauss數據庫而言，當事務內處理失敗后，事務已經被數據庫rollback了，事務中所持有的表鎖也相應的釋放了，其它如Oracle、PostgreSQL數據庫是有相同的表現。
其它數據庫因為時間關系暫時不驗證了，總結針對這個場景需要優化的點有：①業務開發時候對事務報錯主動處理，并顯示的執行commit或rollback操作；②數據庫層設置合理的DDL超時時間；③對分區表進行預創建和有效的監控手段；④數據庫的DDL操作和業務處理主流程松耦合，盡量在投產窗口執行。
參考資料：
- https://docs-opengauss.osinfra.cn/zh/docs/5.0.0-lite
