[pgpool-general: 211] Re: PgPool II in load_balance_mode break transaction boundary?

Tatsuo Ishii ishii at postgresql.org
Sun Feb 5 11:24:42 JST 2012


> Always disabling load balance in an explicit transaction is not
> acceptable since Java applications use explicit transactions by
> default. For your concern:
> 
>> (2) Immediately starts another transaction:
>> begin;
>> select sum(amt) from order_detail where order_id=1;
>> insert into order_summary for order_id = 1
>> commit;
> 
> I suggest to issue dummy DML before SELECT so that it does not route
> to slaves. Probably a SET command is enough for this purpose.

Or there's only 1 SELECT in a transatcion, using /*NO LOAD BALANCE*/
comment might be more convenient.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese: http://www.sraoss.co.jp
> 
>> I have set up two 9.1.2 database nodes  with streaming replication between them. Then I set up pgPool II 3.1.1 in another machine. The pgPool II is configured to run with master_slave_mode and load_balance_mode ON:
>> 
>> #------------------------------------------------------------------------------
>> # CONNECTION POOLING 
>> #------------------------------------------------------------------------------
>> connection_cache = on
>> reset_query_list = 'ABORT; DISCARD ALL'
>> 
>> #------------------------------------------------------------------------------
>> # REPLICATION MODE
>> #------------------------------------------------------------------------------
>> replication_mode = off 
>> replicate_select = off 
>> insert_lock = off
>> lobj_lock_table = ''
>> replication_stop_on_mismatch = off 
>> failover_if_affected_tuples_mismatch = off 
>> 
>> # - Degenerate handling -
>> 
>> replication_stop_on_mismatch = off
>> failover_if_affected_tuples_mismatch = off
>> 
>> #------------------------------------------------------------------------------
>> # LOAD BALANCING MODE
>> #------------------------------------------------------------------------------
>> load_balance_mode = on 
>> ignore_leading_white_space = on 
>> white_function_list = ''
>> black_function_list = 'currval,lastval,nextval,setval,add_login_audit'
>> 
>> #------------------------------------------------------------------------------
>> # MASTER/SLAVE MODE
>> #------------------------------------------------------------------------------
>> master_slave_mode = on 
>> master_slave_sub_mode = 'stream'
>> # - Streaming -
>> 
>> sr_check_period = 10
>> sr_check_user = 'replicator'
>> sr_check_password = 'contigo'
>> # delay_threshold is in bytes, set here to ~ 10MB
>> delay_threshold = 1000000 
>> log_standby_delay = 'if_over_threshold'
>> 
>> 
>> I want to verify that explicit transaction should be routed to MASTER database only. So I use psql on the machine running pgPool II to connect to my test database MONSOON_91X:
>> 
>> psql -U postgres -h 127.0.0.1 MONSOON_91X
>> psql (9.1.2)
>> Type "help" for help.
>> 
>> MONSOON_91X=# show pool_nodes;
>>  node_id |   hostname    | port | status | lb_weight 
>> ---------+---------------+------+--------+-----------
>>  0       | 192.168.6.152 | 5432 | 2      | 0.500000
>>  1       | 192.168.6.153 | 5432 | 2      | 0.500000
>> 
>> 
>> Then I issue the several sequence of SQLs.
>> 
>> (1) commands issued:
>> BEGIN;
>> SELECT * FROM PIUS;
>> UPDATE PIUS SET NAME='ABC' WHERE ID=2;
>> SELECT * FROM PIUS
>> 
>> (2) commands issued;
>> BEGIN;
>> SELECT * FROM PIUS;
>> SELECT NEXTVAL('CUSTOMER_SEQ');
>> SELECT * FROM PIUS;
>> 
>> In both case, most of the time, even with an explicit BEGIN:,  the first SELECT can be routed to the SLAVE (i.e. hot standby). In test 1, the UPDATE SQL is routed to the MASTER and the SELECT after it is also routed to the MASTER.
>> 
>> In test 2, the SELECT NEXTVAL is routed to the master BUT the SELECT after it is routed to the SLAVE.
>> 
>> I wonder if within a transaction, this behavior may break the transaction boundary or integrity if SLAVE lags behind MASTER significantly due to network error. For example, if I issue two transactions:
>> 
>> (1) Transaction 1
>> begin;
>> insert into order_detail for order_id = 1, order_line = 1, amt=10.00;
>> insert into order_detail for order_id = 1, order_line = 2, amt=9.00;
>> commit;
>> 
>> (2) Immediately starts another transaction:
>> begin;
>> select sum(amt) from order_detail where order_id=1;
>> insert into order_summary for order_id = 1
>> commit;
>> 
>> If the above two transactions are very close to each other and SLAVE lags behind MASTER significantly, then the first select in transaction 2 may get nothing.  Any advice on this?
>> 
>> Thanks a lot,
>> 
>> Pius
>> _______________________________________________
>> pgpool-general mailing list
>> pgpool-general at pgpool.net
>> http://www.pgpool.net/mailman/listinfo/pgpool-general
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general


More information about the pgpool-general mailing list