When using load balancing, you can change the assigned workload server from the secondary server to the primary server programmatically, at either the session level or the transaction level.
▪ At the session level, the workload server can be changed to the primary server by using any of the following statements:
– SET WRITE
– SET ISOLATION LEVEL REPEATABLE READ
– SET ISOLATION LEVEL SERIALIZABLE
If the statement is the first statement in a transaction, the statement takes effect immediately, otherwise, the statement takes affect at the beginning of the next transaction.
▪ At the transaction level, any of the following statements change the workload server to the primary server for the duration of the transaction:
– SET TRANSACTION WRITE
– SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
– SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
If the statement is the first statement in a transaction, the statement takes effect immediately, otherwise, the statement takes affect at the beginning of the next transaction.
After the transaction has been executed at the primary server, the workload server reverts to the server assigned for the session.
You can revert the effect of the SET [TRANSACTION] WRITE statement by using any of the following statements:
▪ SET [TRANSACTION] READ WRITE
▪ SET [TRANSACTION] ISOLATION LEVEL READ COMMITTED