pxc 事務pre-commit狀態阻塞
環境介紹：
contos 6.9 虛擬機器 + 5.6.42版本資料庫 資料庫架構為：PXC
問題描述：
我執行的操作：如下儲存過程，目的是為了把冷資料歸檔到歷史表 msc_sm_bill_back_20191120
然後再將msc_sm_bill表裡的對應資料刪除！
DELIMITER $$
create procedure msc_bill_backup()
begin
DECLARE id_tmp int(10);
DECLARE done INT DEFAULT FALSE;
DECLARE my_cursor CURSOR FOR (
select id from msc_sm_bill where create_time < '2019-09-01'
);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN my_cursor;
my_loop: LOOP
FETCH my_cursor into id_tmp;
IF done THEN
LEAVE my_loop;
END IF;
insert INTO msc_sm_bill_back_20191120 select * from msc_sm_bill where id=id_tmp;
COMMIT;
END LOOP my_loop;
CLOSE my_cursor;
END $$
DELIMITER ;
然後執行該儲存過程：
call msc_bill_backup;
然後檢視程式狀態，發現很多對該表的insert操作被阻塞，狀態為：wsrep in pre-commit stage
root@localhost : msc1 20:46:34>show processlist;
33144754 | app_msgcenter | 10.1.1.1:54742 | msc1 | Query| 3 | wsrep in pre-commit stage | insert into msc_sm_bill ( system_source_id,business_id,
我們知道 wsrep in pre-commit stage這種狀態的事務是沒有commit成功的，這意味著很多
執行緒已經在執行節點發出commit，但將該SQL傳送到其他節點是處於獨立非同步地進行
certification test、事務插入待執行佇列的狀態，也就是說處於等待排隊的狀態，
分析原因：
該儲存過程是根據遊標一條一條的執行，然後本身MySQL預設自動提交，你又顯示的
commit了（並且commit是在迴圈裡面），如下所示，也就是說每insert一條資料，就需要commit兩次，如下所示：
insert INTO msc_sm_bill_back_20191120 select * from msc_sm_bill where id=id_tmp;
COMMIT;
1.然後我們知道commit的時候會觸發purg 執行緒去重新整理髒資料，很耗資源的，尤其是io資源，
2.在MySQL rr隔離級別下，insert INTO msc_sm_bill_back_20191120 select * from msc_sm_bill where id=1，是會把msc_sm_bill 表id=1的資料給鎖定的，但是這個鎖定時間也
是非常短暫的，並且業務不會更新歷史資料，也就是說id=1的資料被鎖了，也不會有業務去更
新的，怎麼會導致阻塞呢？
由於io等待嚴重，導致事務執行時間變長，然後由於insert的時候 也會上間隙鎖，也就是第一insert 也可能阻塞第二次insert，那麼階梯式兒的阻塞就導致很多處於wsrep in pre-commit stage狀態的事務；
總起來說這次問題原因：
1.儲存過程寫的有問題，頻繁提交，消耗資源，io等待嚴重
2.當時業務insert的量比較大，處於等待最後的事務可能超多50秒而timeout，程式報錯
如下查詢所示，確實是再 上午11點執行的，那個時候是高峰期，量比較大！
select count(*) , DATE_FORMAT(create_time,'%H') from msc_sm_bill where create_time>'2019-11-20' group by DATE_FORMAT(create_time,'%H')
116 06
234 07
471 08
587 09
3712 10
3156 11
773 12
533 13
464 14
620 15
623 16
1278 17
563 18
521 19
479 20
425 21
121 22
然後關於insert INTO msc_sm_bill_back_20191120 select * from msc_sm_bill where id=1 會鎖msc_sm_bill表資料的驗證：
二：關於insert into t_bak select * from t where id=11 鎖的區別：
rr隔離級別下，這樣操作是非常危險的，他會鎖id=11這條資料，但是隔離級別（read commited）
下，就不會鎖定任何資料，只是會加表的後設資料鎖！MySQL預設隔離級別是rr,但是
Oracle預設隔離級別是read commited；
實驗一：隔離級別 REPEATABLE-READ，
會話1：
root@localhost : liuwenhe 17:10:44>select @@global.tx_isolation,@@tx_isolation;
+-----------------------+-----------------+
| @@global.tx_isolation | @@tx_isolation |
+-----------------------+-----------------+
| REPEATABLE-READ | REPEATABLE-READ |
+-----------------------+-----------------+
root@localhost : liuwenhe 17:33:00>start transaction;
Query OK, 0 rows affected (0.00 sec)
root@localhost : liuwenhe 17:36:21>insert into t_bak select * from t where id=11;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
會話2 嘗試更新id=11的資料，發現被鎖，但是更新其他的不會被鎖，
前提是id有索引
root@localhost : liuwenhe 17:10:44>select @@global.tx_isolation,@@tx_isolation;
+-----------------------+-----------------+
| @@global.tx_isolation | @@tx_isolation |
+-----------------------+-----------------+
| REPEATABLE-READ | REPEATABLE-READ |
+-----------------------+-----------------+
root@localhost : liuwenhe 17:36:43>update t set name='li' where id=11; --等待
root@localhost : liuwenhe 17:36:43>update t set name='li' where id=12 --成功
會話3查詢鎖的資訊,發現確實有鎖等待
root@localhost : information_schema 17:41:16>select * from INNODB_LOCKS;
+--------------+-------------+-----------+-----------+----------------+------------+------------+-----------+----------+-----------+
| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
+--------------+-------------+-----------+-----------+----------------+------------+------------+-----------+----------+-----------+
| 6904:244:3:4 | 6904 | X | RECORD | `liuwenhe`.`t` | PRIMARY | 244 | 3 | 4 | 12 |
| 6897:244:3:4 | 6897 | S | RECORD | `liuwenhe`.`t` | PRIMARY | 244 | 3 | 4 | 12 |
+--------------+-------------+-----------+-----------+----------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)
實驗2 在READ-COMMITTED隔離級別下，是沒有問題的， 不會鎖定任何資料
會話1：在READ-UNCOMMITTED和READ-COMMITTED隔離級別級別下執行insert into t_bak select * from t 都不會鎖任何資料，如下只寫出來READ-COMMITTED級別下的現象！
where id=11; 具體如下：
root@localhost : (none) 17:45:53>select @@global.tx_isolation,@@tx_isolation;
+-----------------------+------------------+
| @@global.tx_isolation | @@tx_isolation |
+-----------------------+------------------+
| READ-COMMITTED | READ-COMMITTED |
+-----------------------+------------------+
1 row in set, 2 warnings (0.00 sec)
root@localhost : (none) 17:45:57>start transaction;
Query OK, 0 rows affected (0.00 sec)
root@localhost : (none) 17:46:03>use liuwenhe
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
root@localhost : liuwenhe 17:46:11>insert into t_bak select * from t where id=11;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
會話2：發現可以正常更新
root@localhost : liuwenhe 17:42:11>update t set name='li' where id=11;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
但是你嘗試alter表結構會阻塞
root@localhost : liuwenhe 17:47:32>alter table t add key idx_name (name);
會話3：發現等待後設資料鎖 Waiting for table metadata lock
root@localhost : information_schema 17:53:30>show processlist;
+----+-------------+-----------+--------------------+---------+---------+--------------------------------------------------------+---------------------------------------+-----------+---------------+
| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined |
+----+-------------+-----------+--------------------+---------+---------+--------------------------------------------------------+---------------------------------------+-----------+---------------+
| 3 | system user | | NULL | Connect | 2271468 | Waiting for master to send event | NULL | 0 | 0 |
| 4 | system user | | NULL | Connect | 607512 | Slave has read all relay log; waiting for more updates | NULL | 0 | 0 |
| 14 | root | localhost | liuwenhe | Query | 72 |
Waiting for table metadata lock | alter table t add key idx_name (name) | 0 | 0 |
| 17 | root | localhost | information_schema | Query | 0 | starting | show processlist | 0 | 0 |
| 20 | root | localhost | liuwenhe | Sleep | 450 | | NULL | 0 | 1 |
+----+-------------+-----------+--------------------+---------+---------+--------------------------------------------------------+---------------------------------------+-----------+---------------+
5 rows in set (0.00 sec)
經實驗Oracle也不會鎖定任何資料，因為Oracle也是read commited的隔離級別！但是
和MySQL不同的是 Oracle不會阻塞 alter ,具體如下：
Oracle：
會話1 ：
SQL> insert into test2 select * from test1 where id=13;
1 row created.
會話2 執行alter 和update 都可以成功！
SQL> update test1 set id =14 where id=12;
1 row updated.
SQL> alter table test1 add name varchar2(10);
Table altered.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29654823/viewspace-2665200/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 事務狀態持久化持久化
- java執行緒的五大狀態，阻塞狀態詳解Java執行緒
- Percona XtraDB Cluster高可用與狀態快照傳輸(PXC 5.7 )
- 隨筆：MySQL 查詢事務狀態欄位說明MySql
- Oracle 巢狀事務 VS 自治事務Oracle巢狀
- sql server中巢狀事務*SQLServer巢狀
- golang的巢狀事務管理Golang巢狀
- 架構設計（五）：有狀態服務和無狀態服務架構
- Flutter 安卓狀態列那點事兒Flutter安卓
- 關於 MySQL 的巢狀事務MySql巢狀
- java spring巢狀事務詳情和事務傳播型別JavaSpring巢狀型別
- CSS 和 JS 阻塞二三事CSSJS
- Laravel 之巢狀事務 transactions 實現Laravel巢狀
- SQL Server中存在真正的“事務巢狀”SQLServer巢狀
- 一個輪子搞定 Fragment 和狀態列那些事Fragment
- git hooks之——pre-commitGitHookMIT
- 前端狀態管理與有限狀態機前端
- 充電喚醒顯示充電狀態燈注意事項
- Go：git gofmt pre-commit hookGoGitMITHook
- 基於websocket的celery任務狀態監控Web
- Elastic 使用Heartbeat監測服務執行狀態AST
- 基於 swoole 的 websocket 服務一：狀態同步Web
- flink學習（加餐）——job任務狀態變化
- React 狀態管理：狀態與生命週期React
- 狀態機
- 狀態列
- 狀態碼
- 狀態管理
- PerconaXtraDBClusterStrictMode(PXC5.7)
- 從任務中心看狀態機功能元件設計元件
- 使用Nagios打造專業的業務狀態監控iOS
- Amazon EKS 上有狀態服務啟用儲存加密加密
- Blazor+Dapr+K8s微服務之狀態管理BlazorK8S微服務
- 基於 swoole 的 websocket 服務實現狀態同步Web
- 十、Redis事務、事務鎖Redis
- 分散式事務之Spring事務與JMS事務（二）分散式Spring
- Vuex 單狀態庫 與 多模組狀態庫Vue
- 事務插入和唯一性約束，後插入者是報錯還是阻塞？