PolarDB：如何支援熱點更新情境

背景

資料庫中資料更新的順序為LOCK -> UPDATE -> UNLOCK，當對資料庫中的同一條記錄有大量修改請求時，會造成大量的鎖爭搶與鎖等待。請求量增加會導致TPS下降，延遲飆升。例如秒殺情境中對於商品庫存的扣減。

為解決以上問題PolarDB-X推薦您使用在資料庫核心中進行批處理的方案，即對該條記錄進行的更新操作使用組提交，其資料更新的順序變為LOCK-> GROUP UPDATE -> UNLOCK，從而減少鎖爭搶。結合流水線處理等最佳化，可以大大提高該情境的TPS，詳細資料請參見測試結果。

版本限制

僅支援PolarDB-X企業版MySQL 5.7執行個體。

注意事項

熱點更新功能（INVENTORY HINT）只支援單分區事務情境，不支援跨資料庫情境。

前置參數配置

1. 開啟HOTSPOT相關功能，使用高許可權帳號執行。

   SET GLOBAL HOTSPOT=ON;
   SET GLOBAL HOTSPOT_LOCK_TYPE=ON

2. 關閉共用ReadView。

   若已開啟共用ReadView，則應當先將共用ReadView進行關閉，而後使用熱點更新的能力。

   -- 查看共用ReadView是否開啟，false是關閉狀態，true是開啟狀態
   SHOW VARIABLES LIKE '%SHARE_READ_VIEW%';

   如果共用ReadView是開啟狀態，則可通過如下SQL全域關閉：

   SET GLOBAL SHARE_READ_VIEW = FALSE;

   重要

   全域關閉共用ReadView可能會導致一部分查詢變慢。

3. 修改事務類型。

   -- 全域層級設定
   SET GLOBAL TRANSACTION_POLICY = 'XA'; 
   SET GLOBAL ENABLE_XA_TSO = FALSE;
   -- 或設定為session層級
   SET SESSION TRANSACTION_POLICY = 'XA'; 
   SET SESSION ENABLE_XA_TSO = FALSE;

   檢查事務類型：

   SHOW VARIABLES LIKE 'TRANSACTION_POLICY';

   說明

   • 如果事務類型不是XA，則需要將事務類型切換為XA，然後才能使用熱點更新能力，命令如下（或者在每次使用前設定Session層級）。

   • 該修改會導致事務類型變更為XA，請自行判斷該變化是否會產生非預期結果。

使用文法

在業務的UPDATE語句中添加INVENTORY HINT，並將該語句置為最後的一條。

BEGIN;
UPDATE /*+ commit_on_success rollback_on_fail target_affect_row(number)*/ table_reference 
    SET assignment_list
    [WHERE where_condition];
COMMIT | ROLLBACK; -- 取決於更新成功還是失敗，選擇其一即可

參數說明：

使用樣本

1. 樣本表。

   CREATE TABLE table_test  (
       id INT AUTO_INCREMENT PRIMARY KEY,
       c INT NOT NULL
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
   
   CREATE TABLE table_test_2  (
       id INT AUTO_INCREMENT PRIMARY KEY,
       c INT NOT NULL
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. 添加commit_on_success以使用組提交等針對熱點更新情境的最佳化（id為主鍵，使用如下語句對id=1的記錄進行更新時，若更新成功則自動認可）。

   BEGIN;
   UPDATE /*+ commit_on_success*/ table_test SET c = c - 1 WHERE id = 1;
   COMMIT;

   說明

   COMMIT | ROLLBACK按需選擇其一即可。

3. 使用rollback_on_fail，可使得更新失敗時自動進行復原。

   BEGIN;
   UPDATE /*+ commit_on_success rollback_on_fail*/ table_test SET c = c - 1 WHERE id = 1;
   COMMIT;

4. 使用target_affect_row(number)，使得該UPDATE語句的預期更新行數為number，若不等於number，則更新失敗。

   BEGIN;
   UPDATE /*+ commit_on_success rollback_on_fail target_affect_row(1)*/ table_test SET c = c - 1 WHERE id = 1;
   COMMIT;

5. 在帶有INVENTORY HINT的UPDATE語句前，可對同一個物理庫中的表進行DML操作。

   BEGIN;
   INSERT INTO table_test_2 VALUES (1,1);
   UPDATE /*+ commit_on_success rollback_on_fail target_affect_row(1)*/ table_test SET c = c - 1 WHERE id = 1;
   COMMIT;

查看熱點組提交是否生效

• 使用如下SQL查看組提交狀態，Group_update_leader_count增加則說明觸發了熱點組提交的最佳化邏輯。

  SHOW GLOBAL STATUS LIKE "%Group_update%";
  +---------------------------------------+--------+
  | Variable_name                         | Value  |
  +---------------------------------------+--------+
  | Group_update_fail_count               | 54     |
  | Group_update_follower_count           | 962869 |
  | Group_update_free_count               | 2      |
  | Group_update_group_same_count         | 0      |
  | Group_update_gu_leak_count            | 0      |
  | Group_update_ignore_count             | 0      |
  | Group_update_insert_dup               | 0      |
  | Group_update_leader_count             | 168292 |
  | Group_update_lock_fail_count          | 0      |
  | Group_update_mgr_recycle_queue_length | 0      |
  | Group_update_recycle_queue_length     | 0      |
  | Group_update_reuse_count              | 23329  |
  | Group_update_total_count              | 2      |
  +---------------------------------------+--------+

• 使用show physical full processlist查看UPDATE的狀態，是否出現HOTSPOT字樣。

  SHOW physical FULL processlist WHERE command != 'Sleep';
  +-------+------+-----+---------+-------------+---------+------+-------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | Group | Atom | Id  | User    | db          | Command | Time | State                   | Info                                                                                                                                                                                         |
  +-------+------+-----+---------+-------------+---------+------+-------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  |     0 |    0 |  56 | diamond | test_000001 | Query   |    0 | hotspot wait for commit | /*DRDS /127.0.0.1/12e774cab8800000-128/0// */UPDATE  /*+COMMIT_ON_SUCCESS ROLLBACK_ON_FAIL TARGET_AFFECT_ROW(1) */ `test_hotline_lZTr` AS `test_hotline` SET `b` = (`b` + 1) WHERE (`a` = 1) |
  |     0 |    0 | 822 | diamond | test_000001 | Query   |    0 | query end               | /*DRDS /127.0.0.1/12e774c4e9400000-563/0// */UPDATE  /*+COMMIT_ON_SUCCESS ROLLBACK_ON_FAIL TARGET_AFFECT_ROW(1) */ `test_hotline_lZTr` AS `test_hotline` SET `b` = (`b` + 1) WHERE (`a` = 1) |
  |     0 |    0 | 831 | diamond | test_000001 | Query   |    0 | hotspot wait for commit | /*DRDS /127.0.0.1/12e774c551000000-509/0// */UPDATE  /*+COMMIT_ON_SUCCESS ROLLBACK_ON_FAIL TARGET_AFFECT_ROW(1) */ `test_hotline_lZTr` AS `test_hotline` SET `b` = (`b` + 1) WHERE (`a` = 1) |
  |     0 |    0 | 838 | diamond | test_000000 | Query   |    0 | starting                | show full processlist                                                                                                                                                                        |
  +-------+------+-----+---------+-------------+---------+------+-------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

熱點更新效能測試