Direct SQL Update in Agent. When is it committed?

[jlgottlieb]

Hi,

I have an Shipment Agent that listneing for a ShipmentModQuantitiesChange. In that agent I am recalcuating some shipment refnums. The agent deletes the refnums then re-insert thems. Most of the time this works just fine. Occasionally it throws a DuplicateKeyException.

We thought that the bean cache was not being refreshed fast enough, so there is a WAIT for 5 seconds between the delete and the insert. Note: in the Direct SQL Update, the box is checked to refresh the cache.

The agent terminates when the exception occurs, but when checking the Shipment_Refnum table, I see that all the refnums that I deleted are still there. I would have expected the deleted refnums to be gone and only the ones after the failed insert to be missing. That does not seem to be the case.

When are the Direct Sql Updates persisted to the database? If the agent fails, are all updates rolled back?

I'm thinking of rewriting the agent to either just update, rather than delete and insert or to write this as a stored procedure to be called instead. I would appreciate any comments as to which is the better way to accomplish this.

Note: I am running OTM 5.5 CU3-RU3.

THANKS!

[bob_romijn]

Re: Direct SQL Update in Agent. When is it committed?

Hi,

As far as my expirience here goes (5.0), it is committed instantly. I have a few of the same agents as you mention (first delete then insert) and they are working fine.
If an agent fails there is no roll-back. Is the SQL processes, it is processed and not rolled back.

Are you sure that the delete function works for these cases? I had such thing before where I found out that in some instances the delete sql worked but there was no record qualified for deletion.... (that costed me 4 hours...)

Of course that was 5.0

Best regards,

Bob

[oliver]

Re: Direct SQL Update in Agent. When is it committed?

Hi,
i have seen similar problems in previous versions, so we always used an update-sql;
maybe add another sql to assure the ref you want to update really exists

Rgds
Oliver

[ianlo]

Re: Direct SQL Update in Agent. When is it committed?

Hi,

The agent terminates when the exception occurs, but when checking the Shipment_Refnum table, I see that all the refnums that I deleted are still there. I would have expected the deleted refnums to be gone and only the ones after the failed insert to be missing. That does not seem to be the case.

When are the Direct Sql Updates persisted to the database? If the agent fails, are all updates rolled back?

I'm thinking of rewriting the agent to either just update, rather than delete and insert or to write this as a stored procedure to be called instead. I would appreciate any comments as to which is the better way to accomplish this.

Hi jlgottlieb

It is my practice to always use stored procedures when doing CUD operations. Only for Select statements do I enter it directly in the DirectSQL agent actions.

I found out that although through experience each DirectSQL statement is committed independently, unless we can verify this in the code, it is a risky assumption. Also there is the complexity of multiple agents possibly executing the DirectSQL in parallel...

So at least for me the general rule is this:

1) CUD statements are all executed in stored procedures as a single logical business operation.
2) No COMMITS or ROLLBACKS in the stored procedures
3) Use as few cursors as possible in the stored procedures and when using cursors I always specify EXPLICIT cursors

In addition, to optimise DirectSQL agent actions:
1) If there are multiple SELECT statements with CUD operations needed, all this goes into a single stored procedure
2) Multiple SELECT statements (unless each returning a different result) are collapsed if possible into a SINGLE select statement.
3) Try to push ALL CRUD database operations to the database by way of stored procedures or fuctions - this is to reduce the data round trip required between the application server and DB server.

Hope this helps!

Ian

[jlgottlieb]

Re: Direct SQL Update in Agent. When is it committed?

Ian,

Thank you for your insight. It was just what I was looking for. I have inherited OTM Administration and would like to try to standardize some practices for agents, recurring processes etc. I would appreicate it if you could expand on the following:

Why no COMMITS or ROLLBACKS in the procedure?

Is there a problem using a cursor for loop? Whate are your reasons for only using EXPLICIT cursors?

[ianlo]

Re: Direct SQL Update in Agent. When is it committed?

Hi jlgottlieb

Why no COMMITS or ROLLBACKS in the procedure?

This is a habit I inherited from my past experience as a developer working with C/C++ and Borland Database Engine libraries. I have found that many developers write code that execute SQL statements without proper transaction control discipline. They inadvertently call CUD SQL statements elsewhere in their code (without transaction blocks) and then call subsequent stored procedures WITH commits / rollbacks in them.

This results in a serious transaction control bug when the stored procedure executes a commit or rollback, and their previous CUD SQL statements also get affected because they are all executed in the same session.

I know I am being paranoid here but without access to the source code, we cannot assume that OTM developers will not make the same mistake. Hence my rule to all my developers - never ever execute a COMMIT or ROLLBACK in PL/SQL code - do it in the calling body of the stored procedure.

Ian,
Is there a problem using a cursor for loop? Whate are your reasons for only using EXPLICIT cursors?

There are no problems using a cursor for loop in OTM. However, for me using EXPLICIT cursors is a matter of code discipline where developers looking at my stored procedures for the first time will be aware of all the cursors I am creating in the procedure.

As far as I know in Oracle 10g, there is no performance difference in using EXPLICIT or IMPLICIT cursors - so either one will do. But for the sake of readibility and discipline, I made it a point to ensure that all cursors are EXPLICIT.

Hope this helps! Most of these guidelines are like I said more a result of my personal experience and lessons learnt as a developer. And since I am in the position to enforce it, I made it a rule for all my developers as well ...

Ian