In addition to the built-in transparent failover functionality, the Transparent Connectivity functionality in the solidDB® JDBC and ODBC drivers provide support for load balancing of read-only workloads.
Load balancing is based on the fact that there are two synchronized (HotStandby) databases that running at the same time. All read queries provide the same result regardless of whether it is executed in the Primary or the Secondary database.
When the load balancing is activated, the JDBC or ODBC driver uses two physical connections, one to each database, and allocates the query load to the workload connection. The workload connection is selected based on query type (such as read or write), and the then-current load in the database servers.
The load balancing implementation is based on the following principles:
▪Read-only queries can be executed in either database, if the isolation level is READ COMMITTED.
▪Read queries that need high isolation level (repeatable read, select for update) are executed in the Primary database.
▪Write queries are always executed in the Primary database.
▪Read queries after any write operation within the same transaction are executed in the Primary database. Using the Primary database ensures that updated rows are visible for subsequent reads.
▪Internal read/write level consistency of the databases is ensured so that after a write transaction is committed, the secondary database is not used for reading from the same connection until the secondary database is up-to-date for that write transaction. Using the same connection consistently ensures that if the 1-safe or 2-safe received HotStandby replication protocol is used, the next read transaction does not see committed data from the previous write transaction.
solidDB® makes the selection of the workload connection automatically; the load balancing is automatic and transparent to the application. Especially read-centric applications can easily balance out the load between the two database servers, and use the full processor capacity of both servers.
The load balancing is activated with the solidDB® ODBC driver by setting the PREFERRED_ACCESS connection attribute to value READ_MOSTLY; or with the solidDB® JDBC driver by setting the property called solid_preferred_access to value READ_MOSTLY. Each application that connects to the database can choose to use the load balancing functionality, or choose to use the Primary database for all queries (default). You can also control load balancing dynamically on session or transaction level by altering the transaction isolation level of the servers.