Update opcode rolling back all records in bulk

[Former Member]

Hi,

In our project we are reading in events on a stream called PRF_Result_Linkstream then putting them through a flex window and trying to update a record if it is in the window with the code below. Not all records coming in on the stream are in the window though.

OUTPUT setOpcode(PRF_Result_Linkstream,update);

Unfortunately in seems with the update opcode that if the record is not in the window it forces the roll back of that event plus any events that came in around the same time as the message below shows.

2013-05-08 04:47:18.232 | 453 | container | [SP-4-123006] (3366.905) sp(2488) StoreIndex(Master_Window)::put() -- roll back transaction of size 10

2013-05-08 04:47:18.960 | 453 | container | [SP-4-123002] (3367.633) sp(2488) StoreIndex(Master_Window)::put() bad update, tid=9061

We had the same issue with the delete and managed to get around this using the safedelete opcode. Is there something similar for update? (note we do not wish to upsert, if the record is not in the window we would like to ignore it). Is there a way to stop the records being batched up, so they can be treated individually?

Thanks very much,

Mark Wyatt (DHS Australia).

[vijaigeethach]

Hi Mark,

Here is a way to do it
===================================
/*Updates coming through this stream */
CREATE INPUT STREAM s1 schema (symbol string,volume integer,price float);

/*break the batching using a flex stream and a dictionary this will output 1 row at a time */
CREATE FLEX flex1 IN s1 OUT OUTPUT STREAM break_batch SCHEMA(symbol string,volume integer, price float)
BEGIN
DECLARE
dictionary(long,typeof(s1)) dict;
typeof(s1) record;
long id:=1;
long fill_id:=1;
END;
on s1
{
dict[s1.ROWID]:=s1;
print('inside dictionary',s1.symbol);
fill_id++;
 
};
every 1 second
{
if(fill_id=id)
{
  exit;
}
else
{
  record:=dict[id];
  print('inside every 1 second',record.symbol);
  output setOpcode(record,insert); 
  remove(dict,id);
  id++;

}

};
END;

/*Initial value of the window gets filled up through this stream */
CREATE INPUT STREAM fill_window schema(symbol string,volume integer,price float);

CREATE FLEX flex2 in break_batch,fill_window out output window W1 SCHEMA(symbol string,volume integer, price float)
primary key(symbol)
BEGIN
on break_batch
{
output setOpcode(break_batch,update);
};
on fill_window
{
   output setOpcode(fill_window,insert);
};
END;
====================================
Send in rows in to fill_window stream first using Manual Input->Transaction mode
AAA,10,10
BBB,10,10
CCC,10,10
DDD,10,10

Send in rows in to s1 using Manul Input->Transaction mode
AAA,1,1
BBB,1,1
EEE,1,1

You will see 2 transactions getting updated and 1 getting thrown out.

Please note, this can slow down the system as I am breaking it and sending 1 row at a time every 1 second.  Also I have just run some simple test cases with it and have never implemented
this in a large project. I have a message from Jason and I understand your system to be quite complicated. I am not sure how this will work with heavy load.

If you would like to ask more questions on this and would like more information on this then please open a new message so I can work with you on your issue.
Thanks,
Geetha.

[Former Member]

Hi Geetha,

Many thanks for your suggestion. Mark & I have had a look at your suggestion.

I've also replied separately in the support message to say that I can see how your solution would solve the issue, however, the performance would not be satisfactory as we are planning on receiving more than 4million input events on the input stream that gets passed into the FLEX module.

I'm happy to pursue this message via the support case I raised with you on the SAP support portal, as we can provide you with specific examples & data there.

However, there are still a few questions that we have about this issue generally, that don't relate to these specific example here, so I'll posted these here as well (again duplicated in the support message assigned to you), in case anyone else from SAP/Sybase is able to reply...

These are:

• I'm wondering why the events get batched together in the first place as well - my thinking is that an events processor should treat incoming events individually? However I have no visibility as to how the ESP engine really works.
• Is there any way to tell the ESP server to just ignore any attempted updates where the key doesn't exist, instead of rolling back any transactions that are part of the same batch (i.e. to have the same behaviour as "safedelete", but for updates)?
• In our case, transactions end up becoming part of the same internal batch do not necessarily relate to each other - so I think it's pretty dangerous that ESP considers them as the one batch & then fails all of the "batched" updates if only one is invalid!

Cheers,

Jason.

[JWootton]

Jason - are these events being loaded into ESP as part of a transaction block?  That's what it looks like, and if that's the case then yes, it's all or nothing - i.e. if any events in the transaction block can't be processed, none will be processed. But if they aren't published as part of a transaction block, then they should be completely independent of each other.  Is there a reason you are loading them as transaction blocks?   As long as they aren't loaded into the input stream in transaction blocks, you should see the behavior you are looking for, where the update will fail but it won't affect other events.  (or have I misunderstood something?)

[vijaigeethach]

Hi Jeff,

A transaction block will also be created as a result of a join. I had a chance to go through Jason's code and that is what is happening in their case. 

There are two workarounds I have suggested.

1. The same as the previous one I suggested earlier except that the timer can be changed to 1 milliseconds. In my small setup I am seeing the blocks getting spread out without any issues. I have asked Jason to test this.

2. Use a dictionary and store all the rows that is in the SPLASH ouput windows as a dictionary entry and use the SetOpcode(.., update) only if data is present in the dictionary.

Thanks,

Geetha.

[JWootton]

Thanks for clarifying where the transaction blocks are getting created Geetha.

So Jason, in answer to your questions:

1. Why did the events get batched together in the first place? An event processor should treat incoming events individually?

Answer:  In a CCL Join in ESP, where one incoming event produces two or more output events (i.e. a one-to-many join), the all the events emitted by the join that were produced by a single input event will be bound together in a transaction block. This will also be true with a Flex operator:  all output events emitted as a result of a single "trigger" event will be grouped in a transaction block.  So the event processor does treat incoming events individually (as long as they don't arrive in a transaction block), but derived events end up linked together in a transaction block when they result from the same trigger event.

2. Is there any way to tell the ESP server to just ignore any attempted updates where the key doesn't exist, instead of rolling back any transactions that are part of the same batch (i.e. to have the same behaviour as "safedelete", but for updates)?

Answer:  no, afraid not.  There are potentially work arounds, for example rather than using a Flex to set the opcode to update, knowing that it will fail, can you do an inner join to effect the update? or rather than apply the updates directly to the window, use a flex to match new records to the window and produce a new window? A "safeupdate" opcode isn't a bad idea though.

3. Transactions end up becoming part of the same internal batch do not necessarily relate to each other - so I think it's pretty dangerous that ESP considers them as the one batch & then fails all of the "batched" updates if only one is invalid! 

Comment: I guess the thing is, they are related in that they were all produced by the same Join event. Understanding that is key to not having unexpected results.  This isn't well documented (or widely understood) - we need to fix that. Sorry it tripped you up. And beyond making it clearer:  it's an interesting use case.  Maybe we need a way to "untie" records in a transaction block - or an option to prevent a join (or flex) from producing a transaction block in the first place when it's not desired.

[Former Member]

Hi Jeff,

Thanks for your detailed responses (and Geetha, thanks for providing the supporting info). I understand the "batching" behaviour now.

Regarding the last point:

3. Transactions end up becoming part of the same internal batch do not necessarily relate to each other - so I think it's pretty dangerous that ESP considers them as the one batch & then fails all of the "batched" updates if only one is invalid! 

Comment: I guess the thing is, they are related in that they were all produced by the same Join event. Understanding that is key to not having unexpected results.  This isn't well documented (or widely understood) - we need to fix that. Sorry it tripped you up. And beyond making it clearer:  it's an interesting use case.  Maybe we need a way to "untie" records in a transaction block - or an option to prevent a join (or flex) from producing a transaction block in the first place when it's not desired.

Makes sense how you say they are related in a way, because they were produced by the same Join event. This is not how we assumed the product would work, but at least we know now for any future development.

However, if possible, it would be great if this behaviour was optional, or if there was some kind of provided-way of "unbatching" the records - as you mention yourself. Geetha's solution for "unbatching" the records works fine in our current case, but it does limit performance as the minimum interval for a FLEX stage is 1 millisecond.

So, can I log a enhancement request for this functionality?

And yes, we weren't able to find anything in the documentation to suggest how this "batching" of events can occur in scenarios such as the JOIN here. Would be great if this could be included in the documentation somewhere too.

Thanks again for your support and help on this one.

Cheers,

Jason.

[JWootton]

Good feedback - thanks.  I've logged it as an enhancement request. Not sure how soon we'll get to it - drop me a note if you see it becoming a pressing need.  And I've also put in a request to get an explanation of this added ot the documentation (I think I'll also post a little write up explaining this here in the community).