| Author |
Message
|
| divithshetty |
 Posted: Mon Nov 15, 2010 9:06 am Post subject: Database node |
 |
|
Novice
Joined: 12 Sep 2010
Posts: 13
|
Hi,
I have a single database node of which transaction property is set to 'Automatic'.
I have a sql query which updates some table. Immediately after that insert statement i have a select query on the same table which will retrive the row which i just inserted(I am doing this to retrive the primary key ID with which it got inserted).
I would like to know is it safe to do this kind of operation. If not i would like to know the possible issues i might encounter. I am currently tracking some issues. So thinking can this be the issue.
Thanks in advance  |
|
| Back to top |
|
 |
| Vitor |
| Posted: Mon Nov 15, 2010 9:10 am Post subject: Re: Database node |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| divithshetty wrote: |
| If not i would like to know the possible issues i might encounter. |
Clearly transactionality & the UoW boundaries can be a problem.
| divithshetty wrote: |
| I am currently tracking some issues. |
Care to share?
| divithshetty wrote: |
| So thinking can this be the issue. |
What do you mean by "this"? If you mean "doing a dirty read on an uncommitted update" then that's a issue, but a database one not a broker one.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| divithshetty |
| Posted: Thu Nov 18, 2010 3:36 am Post subject: |
|
|
Novice
Joined: 12 Sep 2010
Posts: 13
|
But i was thinking that reading the uncommited data within the same transaction shouldnt cause any issue or atleast it should return what i just inserted.
Here is what i am doing:
Psedu code:
Step 1 : Insert into TableA values(.....putTime)
Step 2 : ID = select MAX(ID) from tableA where time = putTime
Step 3 : Insert into TableB( ID .....) //ID is the primary key
Its throwing unique constraint while inserting into tableB
If i open my sqlDeveloper and apply filter against TableA with the putTime i am getting one row. And i confirmed this is the row i was looking for, with one more column in TableA which stores the payload.
Now i have 2 questions
1) Even thought my database node transaction property is set to Automatic. why the message didnt get rolled back.
2) Why the select query didt retrun anything (I am assuming this as in the step 3 it threw unique constrating and there are no rows with the primary key as ID in TableB
2nd issue can happen if Message broker spans a new thread to insert and without waiting for the control to return from insert statement broker is executing the next esql statement. ie in my case a select query on TableA (Ofcourse this is my assumption )
Can someone give me any idea why i am facing this issue?
Thanks, |
|
| Back to top |
|
|
| divithshetty |
| Posted: Thu Nov 18, 2010 3:39 am Post subject: |
|
|
Novice
Joined: 12 Sep 2010
Posts: 13
|
Forgot to tell one important thing.
This exception happens once in a while.  |
|
| Back to top |
|
|
| Vitor |
| Posted: Thu Nov 18, 2010 5:14 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| divithshetty wrote: |
| 1) Even thought my database node transaction property is set to Automatic. why the message didnt get rolled back. |
I think there's some information missing from your explaination that could answer this. Especially given what "Automatic" means.
| divithshetty wrote: |
| 2) Why the select query didt retrun anything (I am assuming this as in the step 3 it threw unique constrating and there are no rows with the primary key as ID in TableB |
Again, I feel there's something missing, because:
| divithshetty wrote: |
| 2nd issue can happen if Message broker spans a new thread to insert and without waiting for the control to return from insert statement broker is executing the next esql statement. ie in my case a select query on TableA (Ofcourse this is my assumption ) |
The only way broker's going to spawn a new thread is if these inserts are not in the same flow. Separate flows will be in separate units of work.
I'm also interested to see your use of put time. Not my first choice for a unique way of identifying a row.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| divithshetty |
| Posted: Thu Nov 18, 2010 6:10 am Post subject: |
|
|
Novice
Joined: 12 Sep 2010
Posts: 13
|
All the logic is inside a single Database node. So that means my assumption of spawning a new thread is wrong.
And regarding the commit issue. I am not able to find anything for that. More over this is not a major issue for me.
My main concern is Why the logic sometimes doesnt work?
Actually the broker is running in clustered environment. There are two brokers each broker has one execution group.
And regarding the putTime thing... We retrieve the put time from MQMD and generate a string in the format HHMMSSss. So we believe if we read the table based on this putTime and select the MAX(ID) out of it, then it should be the latest row we entered into the database. And since this is the error handling flow, the no of messages put on the queue will be very less. |
|
| Back to top |
|
|
| Vitor |
| Posted: Thu Nov 18, 2010 7:17 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| divithshetty wrote: |
| And regarding the commit issue. I am not able to find anything for that. More over this is not a major issue for me. |
Given what you're trying to do it should be a critical issue. Why do you think it's not a big thing? What are you doing to sideline the issue?
| divithshetty wrote: |
| My main concern is Why the logic sometimes doesnt work? |
Well I would have said because of the commit issue, but you claim to have fixed that.
| divithshetty wrote: |
| Actually the broker is running in clustered environment. There are two brokers each broker has one execution group. |
So all the logic isn't in a single Database node running in a single thread. There are at least 2 threads (unless the brokers are accessing different databases), possibly more if the execution group has been set to use multiple instances.
| divithshetty wrote: |
| And regarding the putTime thing... We retrieve the put time from MQMD and generate a string in the format HHMMSSss. So we believe if we read the table based on this putTime and select the MAX(ID) out of it, then it should be the latest row we entered into the database. |
And that's my point.
| divithshetty wrote: |
| And since this is the error handling flow, the no of messages put on the queue will be very less. |
Fair point. The trouble will start when someone decides to use the same deisgn for auditing.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| divithshetty |
| Posted: Thu Nov 18, 2010 8:42 am Post subject: |
|
|
Novice
Joined: 12 Sep 2010
Posts: 13
|
Yes All the logic is there in two places. ie in one single database node, which is deployed in two brokers. Currently there are no multiple instances of the flow. But that shouldnt cause this behaviour isnt it?
Because when a message has been put into any queue any one of the broker will pick the message and the processing completes in the same broker itself. I mean one message flow is considered as one thread.
Please correct me if i understood in a wrong way. |
|
| Back to top |
|
|
| Vitor |
| Posted: Thu Nov 18, 2010 8:57 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| divithshetty wrote: |
| Currently there are no multiple instances of the flow. |
So there are 2 instances.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| divithshetty |
| Posted: Thu Nov 18, 2010 9:02 am Post subject: |
|
|
Novice
Joined: 12 Sep 2010
Posts: 13
|
yes
The environment is like this ->
Broker 1
-->ExecutionGroup1
---->MyFaillureFlow
Broker 2
-->ExecutionGroup1
---->MyFailureFlow
ANd while deploying the additional instance property of MyFailureFlow in the bar file is not altered. So totally there are 2 instances. |
|
| Back to top |
|
|
| divithshetty |
| Posted: Fri Nov 19, 2010 2:16 am Post subject: |
|
|
Novice
Joined: 12 Sep 2010
Posts: 13
|
| My question is how will it affect my case. As any one of the message flow picks the message it will completely processed by itself. So i dont see any issue here. Please correct me if i am wrong. |
|
| Back to top |
|
|
|
|
