With TC, the driver uses two methods to direct the transaction load; one to handle read intensive load and the other to handle write intensive load. For load balancing, the logical TC Connection is mapped to a lower level server connection called Workload Connection. The workload connection can change over time and it is normally of no concern to the application. However, if necessary, there is a way to find out what is the current workload connection.
Static load balancing configuration
The load balancing methods are:
PREFERRED_ACCESS=WRITE_MOSTLY – no load balancing (default)
With WRITE_MOSTLY, all transactions are executed at the Primary server. This corresponds to the typical HotStandby operation. WRITE_MOSTLY method is useful with write-intensive loads.
PREFERRED_ACCESS=READ_MOSTLY – load balancing by distributing read-only transactions between Primary and Secondary
With READ_MOSTLY, read-only transactions can be executed at both the Secondary and Primary. Write transactions are always executed at the Primary server.
For read-only transactions, the assigned workload server is selected on the basis of the Cluster.ReadMostlyLoadPercentAtPrimary parameter setting, which specifies the percentage of the total read-mostly load that is directed to Primary.
The default value of the Cluster.ReadMostlyLoadPercentAtPrimaryparameter is 50, which means that by default, half of the connections use the Primary and half the Secondary. This is a preferable value for most mixed loads. If the value is set to zero, all the load is directed at the Secondary. This is suitable in cases where very read-intensive (or read-only) applications use PREFERRED_ACCESS=READ_MOSTLY and (in the same time) write-intensive applications use PREFERRED_ACCESS=WRITE_MOSTLY.
With READ_MOSTLY, the Primary server tells the driver which server to connect to for the workload connection. If the load for a given connection is directed to Secondary, and a write operation is issued, a handover to Primary takes place and the transaction is executed in the Primary server. After the transaction commit, the load is directed back to Secondary. If Secondary fails, the connection fails over from Secondary to Primary.
PREFERRED_ACCESS=LOCAL_READ – load balancing by executing transactions locally when possible
With LOCAL_READ, read-only transactions are always directed to the local server, be it Primary or Secondary. Write transactions are always executed at the Primary server.
The LOCAL_READ method is typically used with SMA setups. The application on the Primary server uses the SMA connection for read and write transactions. The application on the Secondary uses SMA connection for reads and a network-based connection for writes on the Primary.
Important: When using SMA with Transparent Connectivity (TC), if you set the load balancing method to READ_MOSTLY or WRITE_MOSTLY (default), a network connection is used instead of the SMA connection. Thus, when using SMA with TC, always set the load balancing method to LOCAL_READ.
Isolation levels and load balancing
Load balancing operates only at the isolation level READ COMMITTED. If the server’s isolation level (startup) default is set to a different value, the settings PREFERRED_ACCESS=READ_MOSTLY and PREFERRED_ACCESS=LOCAL_READ force the isolation level of this session to READ COMMITTED. The isolation level may be dynamically reset to a higher one (for example, REPEATABLE READ), but then the load balancing is disabled.
Autocommit and load balancing
To use load balancing, autocommit for the session must be disabled.
Controlling load balancing dynamically
When using load balancing (READ_MOSTLY or LOCAL_READ), you can change the assigned workload server from Secondary to Primary programmatically.
At the session level, the workload connection server can be changed to Primary with the following statements:
▪SET WRITE
▪SET ISOLATION LEVEL REPEATABLE READ
▪SET ISOLATION LEVEL SERIALIZABLE
The statement takes effect immediately, if it is the first statement of a transaction, or from the next transaction, otherwise.
At the transaction level, the following statements change the workload connection server to Primary for the time of one transaction:
▪SET TRANSACTION WRITE
▪SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
▪SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
The affected transaction is the one that is started by using the statement, or the next one, in other cases. After the transaction has been executed at the Primary, the workload connection server is reverted to the one assigned for the session.
The effect of the SET [TRANSACTION] WRITE statement may be reverted with the statement SET [TRANSACTION] READ WRITE. Also, the following isolation level statements have the same effect:
▪SET ISOLATION LEVEL READ COMMITTED
▪SET TRANSACTION ISOLATION LEVEL READ COMMITTED
Note With READ_MOSTLY, the proportional distribution of the read loads between the Primary and Secondary is defined with the Cluster.ReadMostlyLoadPercentAtPrimary parameter. By default, the value is set to 50, which means that 50 percent of the read loads are directed to the Primary. Changing the value of the Cluster.ReadMostlyLoadPercentAtPrimary parameter requires a server restart.
When both failure transparency is set (TF_LEVEL is other than NONE) and load balancing is enabled (PREFERRED_ACCESS=READ_MOSTLY or PREFERRED_ACCESS=LOCAL_READ), the applied failover policy is the following:
1 Primary failure: all the load is directed to the new Primary being in the PRIMARY ALONE state.
2 Secondary failure: all the load is directed to the Primary (PRIMARY ALONE)
3 Connection break between the servers; the servers are in the PRIMARY ALONE and SECONDARY ALONE states: if there is an ongoing read-only transaction executing in the Secondary, it is also successfully committed in the Secondary. All the subsequent transactions are directed to the Primary (in PRIMARY ALONE).
When the normal hotstandby operation is resumed (with servers being in PRIMARY ACTIVE and SECONDARY ACTIVE states), the load is rebalanced between the Primary and the Secondary.
Note Even when failure transparency is not enabled (TF_LEVEL=NONE), some rudimentary failover capability is available: failover from Secondary to Primary when the Secondary fails. All other failures result in a communication link failure. Thus, in most failure cases where TF_LEVEL=NONE, the application must reconnect with the same TC Info. To avoid reconnection, enable failure transparency when load balancing is used.
Executing stored procedures under load balancing
All SQL stored procedures are executed in the Primary unless they are specified as read-only procedures with the SQL standard clause SQL_data_access_indication in the procedure declaration.
<SQL_data_access_indication> ::= NO SQL | READS SQL DATA | CONTAINS SQL | MODIFIES SQL DATA
Only the keyword MODIFIES SQL DATA inflicts transaction handover. This is the default behavior.
To avoid unnecessary handovers of read-only procedures and functions, use one of the following values: