Read with weak consistency

This topic describes how to perform read operations with weak consistency in OceanBase Database.

For applications with low requirements for real-time data, such as batch or statistical applications, you can configure weak consistency reads to avoid impacting the primary replica. You can set the session-level or weak consistency read hint to direct read operations to the standby replica:

• Add a hint to the SELECT statement.

  SELECT /*+read_consistency(weak)*/ * FROM test WHERE c1=1;
  

• Set the system variable at the session level (1=FROZEN, 2=WEAK, 3=STRONG).

  SET @@ob_read_consistency= 2;
  

• Weak reads are supported when the isolation level is set to REPEATABLE READ or SERIALIZABLE. Example:

  obclient> SET transaction read only;
  Query OK, 0 rows affected (0.001 sec)
  obclient> SELECT /*+ read_consistency(weak)*/ * FROM t WHERE a=2;
  +------+------+
  | ID   | A    |
  +------+------+
  | NULL |    2 |
  +------+------+
  1 row in set (0.001 sec)
  

  Notice

  When the isolation level is SERIALIZABLE, the transaction snapshot is retained after the transaction is started and is only released upon transaction commit. Therefore, in the SERIALIZABLE isolation level, you must commit transactions in a timely manner to avoid issues such as increased disk usage or slower read operations.