locked
Always on replica and cached views RRS feed

  • Question

  • Hi All,

    I've a little suspect on returned rows from sql VIEWS created with(nolock) and pooled c# connections.

    My Installation is synch-commit availability mode

    at IIW web layer there are 50 up to 100 active connection (applicationIntent=readOnly),

    LINQ driven query are sent with bind variable, against VIEW_MY_data

    It could be possible that somehow sql view data is taken from buffer manager of replica  and NOT up to date , without re-read actual data ?

    I've noticed a suspicious value and a not found result from my Tracelog with a timestmp of 6 second later than , timestamp column on DBMS.

    Primary and slave are connected over a couple of Infinband.

    Ciao

    Diego


    Diego scaravaggi


    • Edited by dscaravaggi Tuesday, January 29, 2019 5:47 PM wrong Signature
    Tuesday, January 29, 2019 5:39 PM

Answers

  • At the end the mistery is resolved.

    The availability group is working as directed, the data is transferred to the secondary log flush, but It is not usable in a deterministic way.

    https://dba.stackexchange.com/questions/84420/does-synchronous-commit-availability-mode-ensure-consistency-between-replicas

    I missed the scope of secondary replicas, I supposed that I could use them like and "Oracle RAC", 

    Now we will cahnge the workload balance, we will route to secondaries only OFFLINE reporting with a requisite to show data at least "20 minutes old".

    For next project we will plan to use a sharding base cluster like postgresql citusdata


    Diego scaravaggi (Freelancer)

    • Marked as answer by dscaravaggi Wednesday, February 6, 2019 3:59 PM
    Wednesday, February 6, 2019 3:59 PM

All replies

  • Hi again

    some update from test Lab.

    Scenario 1:  applicationIntent=readOnly removed,, OK test passed  , linq always returned fresh data

    Scenario 2:   Linq modified in order to access underlying tables instead of view, OK test Passed

    Scenario 3: use sql view , linq returned staled obsolete data, KO test failed


    Diego scaravaggi (Freelancer)

    Wednesday, January 30, 2019 8:29 AM
  • Hi Diego,

    >>I've noticed a suspicious value and a not found result from my Tracelog with a timestmp of 6 second later than , timestamp column on DBMS.
    Where did you find the value? On the primary replica or secondary replicas? Sometimes changes from primary replica may not reflect on the secondary replica in time. There may be a bottleneck in the synchronization work flow. Please refer to Determine why changes from primary replica are not reflected on secondary replica for an Always On availability group.

    Best Regards,
    Puzzle
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, January 30, 2019 9:20 AM
  • Hi Puzzle,

    thanks for your link, the availability group is in Synchronous commit

    here is output of sys.dm_hadr_database_replica_states, the weird behavior is regarding accessing data via sql view instead of plain sql query.

    I've some doubt about c# ado.net library like a cache effect on prepared views.

    database_id 12
    group_id A07B28CC-60CD-4E16-A55B-923116B67A17
    replica_id 3987A2D9-013C-4A0C-9442-E68D4A7DDCFC
    group_database_id D97E9071-1A01-44C7-AF0F-BA218E868495
    is_local 0
    is_primary_replica 0
    synchronization_state 2
    synchronization_state_desc SYNCHRONIZED
    is_commit_participant 1
    synchronization_health 2
    synchronization_health_desc HEALTHY
    database_state NULL
    database_state_desc NULL
    is_suspended 0
    suspend_reason NULL
    suspend_reason_desc NULL
    recovery_lsn 4294967295429490000000000
    truncation_lsn 10034000000686500000
    last_sent_lsn 1E+19
    last_sent_time 2019-01-29 14:42:02.787
    last_received_lsn 10043000000394400000
    last_received_time 2019-01-29 14:42:02.787
    last_hardened_lsn 10043000000399100000
    last_hardened_time 2019-01-30 10:37:10.067
    last_redone_lsn 10043000000383000000
    last_redone_time 2019-01-30 10:37:08.997
    log_send_queue_size 60
    log_send_rate 0
    redo_queue_size 57
    redo_rate 4299
    filestream_send_rate 0
    end_of_log_lsn 10043000000394400000
    last_commit_lsn 10043000000383000000
    last_commit_time 2019-01-30 10:37:08.997
    low_water_mark_for_ghosts 73719706
    secondary_lag_seconds 0


    Diego scaravaggi (Freelancer)

    Wednesday, January 30, 2019 9:48 AM
  • Today we will schedule an additional stress test for scenario 2 vs Scenario 3

    and only after we will raise a support ticket to maps

     

    Diego scaravaggi (Freelancer)

    Friday, February 1, 2019 10:14 AM
  • At the end the mistery is resolved.

    The availability group is working as directed, the data is transferred to the secondary log flush, but It is not usable in a deterministic way.

    https://dba.stackexchange.com/questions/84420/does-synchronous-commit-availability-mode-ensure-consistency-between-replicas

    I missed the scope of secondary replicas, I supposed that I could use them like and "Oracle RAC", 

    Now we will cahnge the workload balance, we will route to secondaries only OFFLINE reporting with a requisite to show data at least "20 minutes old".

    For next project we will plan to use a sharding base cluster like postgresql citusdata


    Diego scaravaggi (Freelancer)

    • Marked as answer by dscaravaggi Wednesday, February 6, 2019 3:59 PM
    Wednesday, February 6, 2019 3:59 PM