Skip to Content

Where are my transactions? A puzzle for the curious…

I am in the midst of the preparation for a major ASE upgrade.  To control downtime better in a 24/7 environment this one will rely on replication server.  As part of testing we have a batch processing running a heavy load on the DB – the same that will be run in the real environment.  It is a multi-process application submitting various concurrent calls – dml & sp – to a DB.  The batching is performed on a database connected to the replication server (MSA) and is performed on upgraded (ASE 16) or current (ASE 15) versions of the same server to test both ASE and RS performance in each case.

So the setting are:  one batch processing, a pair of ASEs (16 & 15), one replication server (latest release), MSA, transactions submitted only at the primary ASE side, the application is switched from one ASE to the other to test and tune the topology with both versions of ASEs serving as primary.

Thus far all is good (with lots of rock climbing done so far).

Having distilled the tests and the topology to the final stage I’ve started to get row count mismatch errors between source and the target for one of my DSIs.  Apparently these were due to particular application design / missing unique indices on the primary (set rowcount N + DML on the primary may affect different result set on the replicate – hence the need for unique indices for RS).  To test this I’ve reconfigured the RS to ignore row count differences and set up a procedure to compare all the source/target tables to have at least the same row count.

Here is where the fun began.

Unless one installs data assurance and configures it to verify primary and replicate are in sync all the time few will test mismatches between primary and the replicate ASEs.  Usually one relies for this on the replication server – if the rep server log is clear and DSIs work smooth most probably all is fine.  Although comparing row to row primary to replicate misses the point of updates performed on primary tables without unique indices with row count validation turned off (and no autcorrection turned on) it does give and approximate state of synchronization of the ASEs linked to a rep server.  If all the rows on the primary are there on the replicate for all tables most probably all is fine again.

Testing the replication from ASE 15 to ASE 16 with my batching application with no row count validation turned on proved to be effective – no row count mismatches bringing DSI down and no row count mismatches post processing.  Sort of cool.  Testing the replication from ASE 16  to ASE 15 with the same settings and under the same batch processing all of a sudden revealed a table with consistent mismatch in rows between the primary and the replicate.  What’s more – the table with row mismatch was the one having unique index AND post processing replicate side had MORE rows than the primary.

Hm.  Definitely did not expect that to happen.  Sounds like I’m losing some of my transactions somewhere?  Is there a hole in the replication server???  Reluctantly I turned the row count validation back on – knowing that for this table this will have no effect (can cause having less rows on the replicate but definitely not more rows as a result of set rowcount N on the primary).  Rerun the test.   Nope.  No change – the same difference.  Started to scratch the forehead harder…  Concurrency?   Consistency?  Parallelism?  Turned to TS for additional clues…

My RS is configured to run in the old-fashioned parallel_dsi mode with serialization method set to no_wait and partitioning rule set to origin_sessionid (to compensate to N-to-1 throughput killer with single-threaded DSI and bulk transactions split/rerun with no partitioning).  In theory (and based on documentation) this should not cause any consistency issues between the primary and the replicate.  This is now my second major suspect…

While I’m waiting for the test to kick off (takes time to reset and rerun) I was wondering – any of you guys might have messed with these things?  Any additional ideas how to identify from which hole in the rep server my transactions are leaking out?  It would be far from lying to say that I feel a bit dizzy about this situation.  I love my transactions.  I would definitely want to see all of them on the other side of the bridge…

All I’ve done for now is to revert to default serialization method, no partitioning and added DML triggers on the primary table to see DML order for transactions submitted on the primary. 




You must be Logged on to comment or reply to a post.
  • Hm.  Reverting to default serialization method did not change aught - same diff primary/replicate. 

    Will have to narrow down even more:  monOOA primary, monOOA replicate, primary DMLS counted (there is discrepancy between RS reporting based on transactions it parsed and final row count).  monState (commits/rollbacks?). 

    Getting even more weird from test to test....

  • Lots of holes/questions ...

    - Why an ASE blog post as opposed to a RS discussion?

    - ASE version (in particular the 15.x server)?

    - How are you defining PKs for your tables? table-level repdefs? auto-PK generation by repagent?

    For the database housing the table in question ...

    - How do you ensure the same exact DDL in both PDB and RDB? (dump-n-load? ddlgen+bcp? some other means?)

    - Are you using the same function string class for both DSIs?

    - How did you verify both DSIs are configured the same? Are you using RS-level configs, connection-level configs, or a mix?

    - Any chance you've got different settings for dsi_keep_triggers and/or bulk copy?

    - Are there any triggers in the database and if so do they have the same enabled/disabled status in both databases?

    - Should triggers be firing in the RDB?

    As for the differences in the table ... if we know the types of differences (missing rows vs extra rows vs different rows) it might help zero in on the root cause ...

    - Are rows not being deleted from the RDB?

    - Or are extra rows being created in the RDB?

    - Have you confirmed contents of rows with the same PK, ie, updates are flowing properly?

    - Assuming you start your tests with the same set of data in the PDB and RDB tables, have you run a diff on the post-test copies of the RDB tables to see where you've got differences (extra rows, missing rows, different row contents)?

    - Assuming you start your tests with the same set of data in the PDB and RDB tables, have you run a diff on the post-test copies of the PDB tables to verify no differences?

    • Mark,

      ASE blog post hit more attention - but valid point..

      ASE 15.0.3 & ASE  RS 15.7.1 SP 303 (302 had SIG 11 for some things we run)/

      RD are custom defined based on the shortest unique index - nothing auto-generated.

      Both ASEs are loaded from the same dump prior to testing - with ASE 16 being upgraded before getting attached to RS.

      Same function string (default).

      Same config (again custom tailored - only ASE name changes from 15 to 16 and vice versa)

      Mix of connection / rs level configs.

      No triggers on the said tables (will have one now for each DML to track DML sequence and compare to MDA data).

      No, triggers are off and bulk is on in both directions.

      My suspicion is that rows are not being deleted from the RDB - will verify it now in the new test based on monOOA.  Nothing peculiar about rows not deleted - PK is functioning fine, manual operation will delete these without an issue (to be verified again in another round of tests "just in case").

      Running the diffs is what discovered the diff for the current table - no other row count diffs elsewhere.  Hence the surprise.

      Going to re-run the test over again (it takes a few hours + coordination between several teams and extra time to re-initiate all).  Hopefully will have a bit more clarity after this one.

      Thanks for looking into it.  Very strange phenomena for me.


  • Ok.  Now the mismatch makes sense.  Based on the monOpenObjectActivity, only inserts have made it to the replicate.  6K deletes and 6K updates went down the drain. 

    Now the ONLY thing left is... to understand where these transaction have disappeared...

    Very nice indeed.

    • Are *all* deletes/updates failing to replicate, or just some?  Are the failed deletes/updates by any chance DML statements that affect multiple rows, and if so is there a threshold at which the statements stop replicating (eg, multi-row DML statements that affect >10 rows don't get replicated)?

      You've stated earlier that manual deletes *do* flow through, right?  Were these single-row DML statements, or multi-row DML statements?

      Any chance you've got the table marked for SQL statement replication but the MSA/DB repdef is only replicating SQLDML=I(nserts)? (ie, are PDB rep markings/repmodes/thresholds identical; are the MSA/DB repdefs' SQLDML settings identical)

      Does a repagent trace show all delete/update statements being sent to the RS? Any queues showing discarded/ignored txns? [ie, where are the txns being 'lost']

      In this post - SAP Sybase Replication Server - the author mentions a SAP note stating SRS is not (yet) supported for ASE 16. [I haven't seen the note, nor worked (yet) with SRS+ASE 16, so can't comment on validity of the note.]  If your system is truly identical then I'm wondering if there could be an issue w/ SRS support for ASE 16 repagent activity (eg, multi-row insert/delete statements not being replicated properly)? [very odd bug if true]


      I'm assuming the test code is 100% identical, and run under the same login, so no reason to believe that 'set replication on/off' is being (conditionally) invoked?

      • 1) SRS 15.7.1 does support ASE 16

        2) Most likely this is Multiple DSI and either we have:

             a) manual implementation of multiple DSI without protecting rs_lastcommit

             b) DOL locking without iso_3 and resulting optimistic locking issues with uncommitted insert by-pass

        If (a) (e.g. not using SRS Multiple DSI which is part of ASO), then either multiple rs_lastcommits (one per DSI) is necessary or fstrings have to be modified to handle a modified rs_lastcommit.     Probable - but suspect Andrew covered this.

        If (b) - more likely cause - set the SRS isolation level to 3 (separate RS config for isolation level vs. serialization).   This avoids the 0 row updates/deletes - missing update problem (there was a TechNote on this about a decade ago)

  • I'm still investigating this.  To me it looks more and more like a problem with a single replication definition affecting one particular table.  All DMLs work fine for all tables.  For this particular table only inserts are working.

    What I've noticed dumping the Q content rerunning the update manually (which - again - did not get to the replicate from ASE 16 as primary but did perfectly well from ASE 15 as primary) is that although based on rs_helprep replication definition is installed and active the command built in the Q shows clearly that it has not been used - and not propagated anywhere.

    Now my challenge is to understand why this particular replication definition is ignored by RS (and whether there are others that are?).  Based on rs_helprep the replication definition is precisely the same for ASE15 and ASE16.  For ASE15 it works perfectly well, for ASE16 it (and delete/update transactions on the table) is ignored.  Pretty weird.


  • Bingo:  without rep def the transactions move fine...  That IS weird!

    However:  after reinstalling the same rep def again - transactions began to move perfectly well.

    How in the world one may make sure that all the rep defs are functioning properly???  Should they not simply work?  What do I do with this information now?  That's insane!

  • I am going to test installing subscriptions for all of my rep defs.  At least with subscriptions you may check status to see if anything went wrong...

  • Well,

    Now I feel pretty sad.  Having installed both rep defs and subscriptions (and having marked tables for replication) - all returned to normal (and, actually, performance-wise latency and backlog shrunk to minimum).  Sad thing about it is that this is now not a pure MSA topology but 10% MSA and 90% traditional rep server.  MSA - only for tables that may not have rep defs due to DDL idiosyncrasies.  But now I'm back to manual maintenance for per-object rep defs/subscriptions, which MSA was aimed to solve...

    Not sure if to be glad to have a workaround or be upset to having lost automatic DDL maintenance.  Although, if you think about it, for text/image/rawdata columns one has to install rep defs/ subscription anyway...

    🙁 .

    Not nice.

    • That's because you are ASE 15.0.3 - long time dead.   If you had ASE 15.7 ESD #1 or higher, the RepAgent includes pkey info in the LTL stream and then you don't need repdefs.

      • Old versions are fun.  Takes time to leave them.  Sometime the process of leaving them is more challenging than continuing to use them...

        ps. reminds me that stream replication documentation is still nowhere to be found...

      • I'd add an additional reservation to "no longer need for rep defs." 

        Although its true that rep defs are today "optional" BUT in terms of performance - MSA with no rep defs is slower than MSA with rep defs and the latter is, again, slower than "traditional" RS with rep defs + subscriptions. 

        I have just finished the same batch processing test in both direction (15 to 16 & 16 to 15) with both rep defs and subscriptions in place.  Same test - the only difference in having or having not subscriptions to rep defs.  It saved me 2 to 5 GB of queue space depending on replication direction and brought latency back to where it is supposed to be - close to zero. 

        The question to the engineering board is:  why having rep defs & subscriptions is SO much better than having none if these are optional.  The question to the product users is:  is it worth trading in performance for maintenance ease (same old question popping up in different circumstances).

        Frankly - I don't believe I would end up testing performance with both rep defs and subscriptions installed for an MSA topology.  Although rep defs DID matter based on various tests I've done having subscriptions were nowhere near what I planned to test.  Had I not hit a strange situation when my transactions mysteriously disappear due to a stale rep def (?) I'd find myself still struggling with latency. 

        I love hitting bugs - they teach you so much.  Through suffering learning?


        • Again, you are using 15.03 without pkey in LTL - so repdefs are required.   However, without repdefs, you need to set sts_fullcache_rs* to on for rs_objects, rs_columns and a few others.   Otherwise the overhead you are seeing without repdefs is due to STS cache misses that SRS tries to look up in RSSD only to find nothing.    With sts_fullcache_rs* on, it knows if there is a cache miss that the object doesn't exist.     The problem with sts_fullcache_rs* on is that it slows down maintenance and can consume a large amount of memory on larger schemas (reminds me that nothing is free).

          Stream replication so far is only supported as part of Always-On/HADR - where it is documented in the latest rev of the docs for HADR.   I have requested as part of the functional spec for external replication support that the stream rep connection syntax be documented for custs that want to use a local topology/DR only model as well as low latency rep to other non-HA targets (e.g. OLAP).  Current timeframe is approximately Q4.

          • Not really.  I am talking about replicating from ASE to ASE 15.0.3.  Rep defs indeed are not required - but not having them comes with a price.


            1. If the DB contains text/image columns there is no choice - you must have rep defs and mark columns with setrepcol.

            2. If the DB contains reserved words there is no choice - you must treat these separately too ("quoted" support by default available?).

            WRT sts_cache - isn't getting swapped from sts cache results in information message in RS errorlog?

            I am running a controlled test to see the precise impact of MSA/MSA+RD/MSA+RD+SUB.  Will post it when done.

          • Hello Jeff,

            You advised to set "sts_fullcache_rs* to on for rs_objects, rs_columns and a few others" when not using repdefs with database replication.

            What are the few others please ?

            I just converted some repservers to 15.7.1 sp302. I had repdefs in the past to make the replciation server aware of the primary key, but now that this isn't necessary anymore, I was wondering if I could just drop all repdefs, also those with text/image columns.

            All ASE's involved are 15.7 sp135 (24750 and 25300).

            I've seen some issues in the forums here about problems with  text columns, but those seemed to be related to ASE 16.



          • You cannot drop repdefs for tables with text/image.  The text/image data will not be replicated.  At least that's what I experienced with my testing + documentation states clearly that rep defs are must (the documentation may be obsolete though).

          • mmmm.... I had tried it after reading your posts.


            primary and secondary ASE 15.7 EBF 25300 SMP SP135

            repserver 15.7.1 sp 302.

            database repdef and database subscription.

            at primary :

            create table test_text(a int, t text null);

            --> at secondary : table created

            at primary :

            insert test_text values (1, 'qdjkqfjdlkqsfjmqlksdfql fjqskdfjqmdkf ');

            --> at secondary : select * from test_text












                    qdjkqfjdlkqsfjmqlksdfql fjqskdfjqmdkf

            at primary :

            update test_text set t = 'ayezuraozeiruayeoriuayeriuayerioayzeiruyazyr' where a = 1;

            --> at secondary : select * from test_text













            I've run the same queries on a database replicated via a 15.2 repserver and that gives the same result.

            Until now, I have never encountered problems with replicating text columns.

            I wonder if something has changed in ASE 16.

          • Luc,

            That's nice of you to test it and update here.  I am surprised DB rep defs was enough in your case - can you post the command you used to set it up?  I will test it in my environment to verify.

            This is what documentation says:

            Replication Server Administration Guide Volume 1

            10 Manage Replicated Tables

            10.7 Replicate text, unitext, image, and rawobject Columns:

            "To replicate text, unitext, image, and rawobject columns, follow these steps:

            ○Use create replication definition to create a replication definition for a table that contains text, unitext, image, or rawobject columns.

            See Replication Server Reference Manual > Replication Server Commands > create replication definition.

            ○Use sp_setreptable to mark the table for replication.

            SeeReplication Server Reference Manual > Adaptive Server Commands and System Procedures > sp_setreptable."

            I've moved to test other things with my rep server - but I will revisit this issue again out of curiosity to make sure I did not mislead you with my results (I replicated from to 15.0.3 and vice versa).  I must confess I did not test it with 15.7.  Honestly, I was pretty sure text columns will be replicated just fine with DB rep def.  So much so I did not even test it in the beginning not until I realized that some of my data was missing on the replicate.  As said - will verify it again (even though in my case I will still have to cling to the bulky method of per-table repdefs. subscriptions, &c to avoid data gaps I see which are not explained/solved otherwise).



            ps.  I have to concede that your test is valid and I've misled you:  I've replicated from 15.0.3 to and vice versa table with text column.  If you rely on DB replication definition alone - replication works fine (that's what  you've tested).  If you add replication definition alone for either side text columns stop being replicated unless you mark table for replication explicitly (override DB rep def).  If your replication definition does not reference text column - rep server sends to the replicate everything except text columns.  If you reference text columns explicitly (always_replicate or replicate_if_changed) connection breaks down on rep def/column status out of sync until you sync it manually with sp_setreptable + sp_setrepcol. 

            pps.  In general - replicating from 15.7/16 you may rely on pure MSA configuration (DB rep defs) - should work fine.  In my case, unfortunately, I need rep defs to replicate from 15.0.3 to 16.x (to force RS to use primary keys & control Q size) and sp_setreptable (to have my text columns replicated) - and to replicate from 16.x to 15.0.3 I need all of this + subscriptions to avoid hitting some strange bug with my transactions silently disappearing.  I hope I could at least simplify my setup for 16->15 replication but I'm not sure the case will ever be solved.  I will have to retest again to see if I may find something peculiar around the single table that for some reason looses transactions.  Very weird.

  • Ok.  So that's the thing for to rep def or not to rep def decision with MSA (reminds me of the post I did on this in REP space some time ago).

    This is the result for the test done with no replication definitions (MSA 16->15, replicating transactions):


    Top left:  primary log, top right stable Q size during the test.  The rest a bunch of graphs on primary/replicate concurrency/rows affected.

    This is the result for the same test done with replication definitions in place (MSA 16->15, replicating transactions):


    Top left:  primary log, top right stable Q size during the test.  The rest a bunch of graphs on primary/replicate concurrency/rows affected.

    One may clearly see that with rep defs in place both primary log is less clogged and the Q is significantly smaller.

    The difference stems from the way MSA processes "replicate_minimal_columns."  Or rather how if FAILS TO replicate minimal columns.

    I've been in this mess already (with a case opened at TS).  In order to set replicate minimal columns options on with replication definitions one must specify it in rep def.  If one uses these rep defs with MSA one must IN ADDITION specify "send standby all columns" - an awkward way to cause MSA work with ALL setting from rep def rather than only reading it PK.  This combination of settings puzzled me when I worked on the case "why replicate minimal columns" setting has been ignored by MSA and my outbound Q has been full of columns that never changed.

    I'm not sure if this is a result of a bug or of some missing configuration setting but replicate_minimal_columns with MSA that has no appropriately configured rep defs has no effect on what lands in the outbound queue.  MSA has the replicate_minimal_columns turned on by default.  Ergo, even if one does nothing what one must see in the outbound queue is ONLY the set of columns  that changed.  For some reason this NEVER happens, unless one installed rep defs to control the outbound queue size.

    This is precisely what one sees above: 

    * with no rep defs - Q grows to 7+ GB, greater latency, more pressure on the primary log

    * with rep defs installed and configured to OVERRIDE DB rep def - Q is much smaller, less latency, less pressure on the primary log.

    Bug or not bug - I will let TS to decide.  For me, unfortunately, there is no choice.  If I want to use MSA and battle Q size/latency I have to use REP DEFs as well.  Subscriptions are there ONLY to workaround another feature - having some of the rep defs turning stale and obscuring transactions (TS to look into this too - case for this is also in place).  The situation recurred during the current test as well. 

    That's the summary for the curious as to where my transactions disappear and why for god's sake I still use REP DEFS with ASE 16 as primary for the replication server. 

    As an additional aside I may add this:  I have suggested TS to add a feature to RS to have a config parameter to affect INBOUND queue as well.  As of today whether or not the replicate minimal columns works, there is NO WAY WHATSOEVER to strip the inbound queue of the columns that have not changed.  Well, there are some ASO options to mitigate this - but why, say, ascci compressing the superfluous data in the inbound queue if one might simply not put them there in the first place???  Auto-correction?  For those using it there are config changes to be done anyway.  This is 80/20 decision.



    ps.  I HOPE there is a config parameter to return minimal columns support to MSA back (is there?) and I HOPE there is a way to rectify "stale" (forgive me coining a term for it) replication definitions.   As of now I have to move on and prepare for migration project.

    • You've stated that MSA (w/out repdefs) "FAILS TO replicate minimal columns".

      Is this really true, ie, are you seeing all columns being sent to the RDB (eg, viewing contents of monSysSQLText)?

      I'm guessing that if you look at the actual SQL being submitted to the RDB you'd find that minimal column processing *IS* being performed (assuming MSA configured with the default replicate_minimal_columns='on').  At which point we need to look at how/when minimal column processing occurs ...


      With RS 15.5 replicate_minimal_columns processing was pushed out to the DSI/connection level.  Net result is the DSI receives all columns so that it can implement minimal column processing for scenarios where a) there is no repdef or b) there is a repdef but said repdef doesn't contain a 'replicate minimal columns' clause.

      Obviously (?) the queues *are* going to grow in size if all columns (before/after images) are being supplied to the DSI.

      As you've found with your tests, if you put repdefs in place (w/ the 'replicate minimal columns' clause) then minimal column processing is performed up front thus reducing the volume of data being forwarded to the outbound queue for DSI processing.  Net result should be reduced stable device space usage when using repdefs w/ the 'replicate minimal columns' clause.

      So, MSA *DOES* implement minimal column processing.


      The RS DBA now has a choice of where to implement minimal column processing ...

      @ the repdef level : reduces stable device space usage but requires the DBA manage an inventory of repdefs

      @ the DSI/connection level : increases stable device space usage but eliminates the need for PK-only repdefs (assuming PDB is ASE 15.7+ => repagent supplies PK column details)

      • Actually, I think this is a lot of misunderstanding:

        1 - If minimal columns are enabled, the RepAgent User strips the unmodified columns as part of the normalization (NRM) process.   You may not see this if you dump the queue because the sysadmin commands translate the queue back into human readable values.  

        2 - Unless the repdef is marked with 'send standby' clause - MSA (and WS?) ignore it.   This allows repdefs for non-WS implementations to implement datatype mappings, etc. and not interfere with values.

        3 - I suspect the RepAgent in ASE 16 was not set to send PK info - it is in the docs what the setting is

        4 - Without pkey info, we store all non-LOB before/after column images as pkey - can be quite huge if wide tables.

        • 1 - if minimal columns are enabled at what level? I'm assuming you mean @ the repdef level, otherwise how would the repagent user (sans repdefs) know what the DSI setting is?  what about a DSI at a downstream repserver?  what about multiple RDBs w/ DSIs configued with different minimal column settings?

          2 - sure, once we step away from PK-only repdefs and start looking at all the other scenarios where repdefs are required then it starts getting more interesting 🙂

          3 - good point; as with this entire thread we've been provided with no technical details so we're left with assumptions and guesses 🙁  [Do you have the command/config to disable sending of PK column info by the repagent? I'm not finding anything obvious in the RS 15.7.1 SP302 reference manual]

          4 - (continuation of #3) agreed, would need to see the technical details

      • @MaP:  I'd expect minimal columns to behave same way with or without rep defs.  The "default" MSA behavior to me is unexpected (and suboptimal performace-wise).  Wrt actual replicate command - dynamic_sql masks it all in LWP format - will have to reconfigure RS to unmask it first - not sure worth digging in as performance is better (and less bugs hit) with non-msa-type repdef/sub setting.

        @JT:  3.  not sure what you refer to.  RA uses PK without rep defs - ergo the setting, whatever it is, is on.  There is none related to sp_config_rep_agent as far as I've seen in the doco.  I may have missed it.  The doco is pretty haphazard and with infocenter slowly dying out not sure how the SAP ONE searching engine will help.

        WRT technical details/guessing - may provide any of this to help in discussion.  Did not do so since there is a lot of technical details - sending it all would be superfluous. 

        The net result replicating from to 15.0,3 with RS 15.7.1 SP 303 I will HAVE to use both rep defs AND subs to avoid racking my head why things work slow and why transactions get lost.  Quite frankly, from the end-user perspective it does not make any difference if the product is supposed to do things right - it matters if it is up to the task it is used for.