Update hot rows

Background information

When you execute a statement to update a row in a database, the database system locks the row, updates the row, and then unlocks the row. If a database receives a large number of requests to update the same row, lock contentions and lock waits occur. If the number of requests increases, the transactions per second (TPS) decreases. As a result, the latency surges. For example, during a flash sales activity, the inventory of a specific commodity is frequently updated.

You can process multiple update requests at a time in the database kernel. Commit multiple update requests on the same row at a time. The database system locks the row, performs multiple update operations on the row, and then unlocks the row. This reduces lock contentions. Features such as pipeline processing are enabled to provide a higher TPS. For more information, see the "Test hot row updates" section of this topic.

Enable hot row updates

1. Enable hotspot and hotspot_lock_type. In the PolarDB-X console, navigate to the instance management page of an instance. In the left-side navigation pane, choose ConfigurationManagement > Parameter settings. On the page that appears, enable hotspot and hotspot_lock_type. You can also execute the SET GLOBAL statement. We recommend that you do not use this statement if you can perform the preceding operations in the console.

   set global hotspot=on;
   set global hotspot_lock_type=on
   

1. Change the transaction type to XA. Include the inventory hint in the UPDATE statement.

   BEGIN;
   SET drds_transaction_policy=xa; // Before you use the inventory hint, use this statement to set the transaction type to XA. This setting is not required if the default transaction type is XA.
   UPDATE /*+ commit_on_success rollback_on_fail target_affect_row(number)*/ table_reference 
       SET assignment_list
       [WHERE where_condition];
   COMMIT | ROLLBACK; #If the update is successful, the update is committed. If the update fails, the update is rolled back.
   

Precautions

• The WHERE clause must be used in the same manner as the primary key or the unique key. The clause does not support tables that contain global secondary indexes (GSIs). The clause supports tables that contain local secondary indexes (LSIs).

• If ReadView is enabled, disable ReadView before you use the inventory hint.

• You cannot use the inventory hint if the requests in the transaction are sent to multiple database shards.

• You must execute COMMIT | ROLLBACK statements to commit or roll back transactions.

Parameter description for the inventory hint

• commit_on_success (required)If the statement execution is successful, the statement and the previous uncommitted statements are committed.

• rollback_on_failIf the statement execution fails, the statement and the previous uncommitted statements are rolled back.

• target_affect_row(number)If you use this parameter, the system checks whether the number of updated rows is the same as expected. If the number is not as expected, the update fails.

Examples

• Use commit_on_success to enable group commit for hot row updates. id is the primary key. When you execute the following statements to update the rows in which the id value is 1, the transaction is automatically committed when data is updated.

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

• Use rollback_on_fail to automatically roll back failed updates.

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

• Use target_affect_row(number) to check whether the number of updated rows is equal to the number argument. If the number of updated rows is not the same as expected, the update fails.

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

• Before you execute an UPDATE statement that contains the inventory hint, you can execute DML statements on the tables in the same physical database shard.

  BEGIN;
  SET drds_transaction_policy=xa;
  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 | ROLLBACK;
  

Check whether the inventory hint takes effect

• Execute the show global status like "%Group_update%" statement to view the details of the group commit task. If the value of the Group_update_leader_count variable keeps increasing, a group commit task is triggered to optimize hot row updates.

  mysql> 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      |
  +---------------------------------------+--------+
  13 rows in set (0.01 sec)
  

• Execute the show physical full processlist statement to view the status of the update task. Check whether the hotspot keyword appears in the State column.

  mysql> 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                                                                                                                                                                        |
  +-------+------+-----+---------+-------------+---------+------+-------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  4 rows in set (0.33 sec)
  

Test hot row updates

Create a test table

CREATE TABLE sbtest(id INT UNSIGNED NOT NULL PRIMARY KEY, c BIGINT UNSIGNED NOT NULL);

Test statement

UPDATE /*+ COMMIT_ON_SUCCESS ROLLBACK_ON_FAIL TARGET_AFFECT_ROW(1) */ sbtest SET c=c+1 WHERE id = 1;

Test tool

sysbench Instance specifications

The PolarDB-X instance contains two nodes. Each node has 4 CPU cores and 8 GB memory. Test results {#concept-2119884-entry-7vx-zdw-184}

• The unit of the values in the preceding table is TPS.

• The TPS of hot row updates is related to the instance specifications, the number of concurrent requests, and the update statement. The test results are provided only for your reference.