| Author |
Message
|
| billybong |
 Posted: Fri Dec 14, 2007 2:47 am Post subject: DB SELECT returns no row due to MQ commit before DB commit |
 |
|
Disciple
Joined: 22 Jul 2005
Posts: 150
Location: Stockholm, Sweden
|
Hi guys!
We use an event-based framework which are based on events that triggers certain actions to be done in the message broker. Some of the events are sent as MQ messages which are read by an eventprocessor that takes appropriate actions as a result based on an event -> action model.
We've come up with a problem that has to do with the timing of the commits in the WMB. More specifically, when an insert is done in an action that later raises the event about the object being inserted the eventprocessor flow will recieve this event and do a DB SELECT based on the object id in the event.
Now, since the MQCommit is done before the DB commits we've noticed in some cases that the SELECT will be done very quickly, before the DBCommit in the first thread is complete. This SELECT will not find the INSERTED row and therefore take no action on it.
We've worked around the problem for now by doing a manual commit in the flow before the flow exits, but this leaves the flow vulnerable to phantom rows in the database should MQ or the broker fail between our manual commit and the broker ones.
Does anyone know if its possible to alter the SELECT or INSERT statement to block the SELECT reader until all COMMITS are done on the table?
We're using Oracle 10 on Solaris, WMB 6.0 on Solaris.
_________________
IBM Certified Solution Developer - WebSphere Message Broker V6.1
IBM Certified Solution Developer - WebSphere Integration Developer V6.0
IBM Certified System Administrator - WebSphere MQ V6.0
IBM Certified Solution Developer - WebSphere DataPower |
|
| Back to top |
|
 |
| elvis_gn |
| Posted: Fri Dec 14, 2007 3:26 am Post subject: |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
Hi billybong,
I'm not too much of a Db person, but I think there should be something like a pre and post commit trigger for table updation in Db....this should make sure that your event is ONLY generated once the update into the table is made successfully.
Create Trigger
Regards. |
|
| Back to top |
|
|
| billybong |
| Posted: Fri Dec 14, 2007 3:50 am Post subject: |
|
|
Disciple
Joined: 22 Jul 2005
Posts: 150
Location: Stockholm, Sweden
|
Thanks for your reply elvis_gn, even though I dont think DB triggers will solve my problem. The event is sent as an MQ message and will therefore be commited by the message broker when exiting the flow. After the MQ commit the DB commit will be run, which would in the case of a trigger just fire some DB event that will also be run after the MQ message has been put to queue.
The eventprocessor will read the MQ message and try to do a select before both the commit and the trigger has been run.
And no, we cant do a local commit on the database node doing the insert since we would like to be able to roll back the transaction in case of errors further down the flow.
_________________
IBM Certified Solution Developer - WebSphere Message Broker V6.1
IBM Certified Solution Developer - WebSphere Integration Developer V6.0
IBM Certified System Administrator - WebSphere MQ V6.0
IBM Certified Solution Developer - WebSphere DataPower |
|
| Back to top |
|
|
| Vitor |
| Posted: Fri Dec 14, 2007 4:02 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| billybong wrote: |
| And no, we cant do a local commit on the database node doing the insert since we would like to be able to roll back the transaction in case of errors further down the flow. |
You could commit locally and build an error flow which produced a compensating transaction....
....some miles away from an optimum solution but ought to work. 
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| elvis_gn |
| Posted: Fri Dec 14, 2007 4:18 am Post subject: |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
Hi billybong,
| billybong wrote: |
| The event is sent as an MQ message and will therefore be commited by the message broker when exiting the flow |
How is the first event for running the flow(generating the MQ Message) to update the database created ?
We need to know how your eventprocessor framework is working....are MQ messages the event creators or are DB table triggers generating MQ messages ?
Regards. |
|
| Back to top |
|
|
| billybong |
| Posted: Fri Dec 14, 2007 5:03 am Post subject: |
|
|
Disciple
Joined: 22 Jul 2005
Posts: 150
Location: Stockholm, Sweden
|
We're using the IBM Dublin developed EPP (Enterprise Payment Platform) which is a framework for dealing with states in objects.
What happens is once a transmission (MQ message) is recieved from an MQ queue its mapped into an internal format by a flow that deals with the physical messages. After the mapping is done, an MQ message is put to a queue which contains an event stating that a transmission and transaction has been mapped. This message is recieved by an event processing flow which reads the message and based on a model decides to call an action (a subflow found by the routetolabel node). This action possibly then raises another event after its work is completed to notify the eventprocessor of the work it just did. This event will be written to an MQ queue to end the unit of work if the developer chooses that the event should do so.
_________________
IBM Certified Solution Developer - WebSphere Message Broker V6.1
IBM Certified Solution Developer - WebSphere Integration Developer V6.0
IBM Certified System Administrator - WebSphere MQ V6.0
IBM Certified Solution Developer - WebSphere DataPower |
|
| Back to top |
|
|
|
|
