| Author |
Message
|
| karthik_ps |
 Posted: Tue Jan 21, 2014 10:04 pm Post subject: MB 8.0.0.2 with Oracle 10g-Configuration issue |
 |
|
Apprentice
Joined: 10 Dec 2008
Posts: 43
|
We are using MB 8.0.0.2 and MQ 7.0.0 with Oracle 10g database.We have two interface accessing a common table.Both the flows are performing INSERT/UPDATE/DELETE operation on the table.After running smoothly for some time both the flows are getting hanged and input messages are getting piled up in input queues.From the database log and trace file it is observed that one of the INSERT/UPDATE/DELETE query is holding the table.Neither any response nor any error is received from database.Once the broker is restarted,both the flows starts processing the messages nomally.
Please suggest any mechanism to terminate the query from executing and releasing the table, so the next message can be processed. |
|
| Back to top |
|
 |
| Tibor |
| Posted: Wed Jan 22, 2014 1:15 am Post subject: |
|
|
Grand Master
Joined: 20 May 2001
Posts: 1033
Location: Hungary
|
You can disconnect any client / session as a mighty DBA, but it would be more useful for finding a solution. Could you send the error message logged in Oracle logs?
Perhaps you can switch ODBC tracing on at WMB side. |
|
| Back to top |
|
|
| karthik_ps |
| Posted: Wed Jan 22, 2014 3:02 am Post subject: |
|
|
Apprentice
Joined: 10 Dec 2008
Posts: 43
|
Below is the details from Oracle logs:-
ACTION: Significant row contention was detected in the TABLE "MBID.TIADATA". Trace the cause of row contention in the application logic using the given blocked SQL.
RATIONALE: The SQL statement was blocked on row locks.
RELEVANT OBJECT: SQL statement with SQL_ID 3npjudt40f783
UPDATE MBID.TIADATA SET STATUS = 'N' WHERE
((((FLIGHTNUMBER)=(:V1))AND((FLIGHTDATE)=(:V2)))AND((DEPARTURESTATION
)=(:V3)))OR(((ARRIVALSTATION)=(:V4))AND((CABINCLASS)=(:V5)))
SYMPTOM: Wait class "Application" was consuming significant database time. (94% impact) |
|
| Back to top |
|
|
| dogorsy |
| Posted: Wed Jan 22, 2014 3:06 am Post subject: |
|
|
Knight
Joined: 13 Mar 2013
Posts: 553
Location: Home Office
|
| karthik_ps wrote: |
Below is the details from Oracle logs:-
ACTION: Significant row contention was detected in the TABLE "MBID.TIADATA". Trace the cause of row contention in the application logic using the given blocked SQL.
RATIONALE: The SQL statement was blocked on row locks.
RELEVANT OBJECT: SQL statement with SQL_ID 3npjudt40f783
UPDATE MBID.TIADATA SET STATUS = 'N' WHERE
((((FLIGHTNUMBER)=(:V1))AND((FLIGHTDATE)=(:V2)))AND((DEPARTURESTATION
)=(:V3)))OR(((ARRIVALSTATION)=(:V4))AND((CABINCLASS)=(:V5)))
SYMPTOM: Wait class "Application" was consuming significant database time. (94% impact) |
That is clear enough, is it not ?! |
|
| Back to top |
|
|
| Tibor |
| Posted: Wed Jan 22, 2014 3:45 am Post subject: |
|
|
Grand Master
Joined: 20 May 2001
Posts: 1033
Location: Hungary
|
| I don't think so, avoiding a database deadlock would be a WMB side task. Of course, you can check whether your process is proper for a parallel database access or not, but that's all. |
|
| Back to top |
|
|
| dogorsy |
| Posted: Wed Jan 22, 2014 3:56 am Post subject: |
|
|
Knight
Joined: 13 Mar 2013
Posts: 553
Location: Home Office
|
| Tibor wrote: |
| I don't think so, avoiding a database deadlock would be a WMB side task. Of course, you can check whether your process is proper for a parallel database access or not, but that's all. |
I don't know what you are talking about. The OP needs to look at his "two interfaces" ( whatever he means by that ),( the two "interfaces" may even not be both WMB), and work out why there is contention. And that is very clear from the error message.
| Quote: |
| ACTION: Significant row contention was detected in the TABLE "MBID.TIADATA". Trace the cause of row contention in the application logic using the given blocked SQL. |
|
|
| Back to top |
|
|
| Tibor |
| Posted: Wed Jan 22, 2014 3:59 am Post subject: |
|
|
Grand Master
Joined: 20 May 2001
Posts: 1033
Location: Hungary
|
| dogorsy wrote: |
| I don't know what you are talking about. The OP needs to look at his "two interfaces" ( whatever he means by that ),( the two "interfaces" may even not be both WMB), and work out why there is contention. |
You are right. My assumption was this deadlock is caused by two or more WMB connections. |
|
| Back to top |
|
|
| dogorsy |
| Posted: Wed Jan 22, 2014 4:16 am Post subject: |
|
|
Knight
Joined: 13 Mar 2013
Posts: 553
Location: Home Office
|
| Tibor wrote: |
You are right. My assumption was this deadlock is caused by two or more WMB connections. |
Even if the deadlock is caused by two or more WMB connections, it is up to the OP to work out why. And the problem title is also wrong, what does this have to do with configuration WMB, Oracle configuration ? |
|
| Back to top |
|
|
| karthik_ps |
| Posted: Wed Jan 22, 2014 5:14 am Post subject: |
|
|
Apprentice
Joined: 10 Dec 2008
Posts: 43
|
Here two different message flow are accessing the table.Earlier the same interfaces were running in db2 database.In db2 database the following parameter were set so we used to receive an deadlock error.
Interval for checking deadlock (ms) (DLCHKTIME) = 10000
Lock timeout (sec) (LOCKTIMEOUT) = 15
But with Oracle database, message broker is not receiving any error message instead all both the message flow are getting hanged.
So can you please suggest how can we achive a similar response with Oracle database.Which parameter from Oracle database needs to be configured.
Also,can we handle this from code level? |
|
| Back to top |
|
|
| dogorsy |
| Posted: Wed Jan 22, 2014 5:15 am Post subject: |
|
|
Knight
Joined: 13 Mar 2013
Posts: 553
Location: Home Office
|
| karthik_ps wrote: |
Here two different message flow are accessing the table.Earlier the same interfaces were running in db2 database.In db2 database the following parameter were set so we used to receive an deadlock error.
Interval for checking deadlock (ms) (DLCHKTIME) = 10000
Lock timeout (sec) (LOCKTIMEOUT) = 15
But with Oracle database, message broker is not receiving any error message instead all both the message flow are getting hanged.
So can you please suggest how can we achive a similar response with Oracle database.Which parameter from Oracle database needs to be configured.
Also,can we handle this from code level? |
You should post this query in the Oracle forum. Nothing to do with message broker.  |
|
| Back to top |
|
|
| Tibor |
| Posted: Wed Jan 22, 2014 5:45 am Post subject: |
|
|
Grand Master
Joined: 20 May 2001
Posts: 1033
Location: Hungary
|
| karthik_ps wrote: |
| Also, can we handle this from code level? |
If you can send us the database handling part of your code, maybe. |
|
| Back to top |
|
|
| dogorsy |
| Posted: Wed Jan 22, 2014 5:49 am Post subject: |
|
|
Knight
Joined: 13 Mar 2013
Posts: 553
Location: Home Office
|
| Tibor wrote: |
| karthik_ps wrote: |
| Also, can we handle this from code level? |
If you can send us the database handling part of your code, maybe. |
Talking rubbish again, are we ? |
|
| Back to top |
|
|
| karthik_ps |
| Posted: Wed Jan 22, 2014 9:16 pm Post subject: |
|
|
Apprentice
Joined: 10 Dec 2008
Posts: 43
|
As far as the database part of the code is concerned it is simple INSERT, UPDATE and DELETE command.I have already provided you the update command.
Is their is any method to restrict the database query execution time from broker level? |
|
| Back to top |
|
|
| dogorsy |
| Posted: Wed Jan 22, 2014 11:58 pm Post subject: |
|
|
Knight
Joined: 13 Mar 2013
Posts: 553
Location: Home Office
|
| karthik_ps wrote: |
As far as the database part of the code is concerned it is simple INSERT, UPDATE and DELETE command.I have already provided you the update command.
Is their is any method to restrict the database query execution time from broker level? |
Do you understand what 'database contention' is ? |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Thu Jan 23, 2014 12:08 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
DB2 and Oracle use different object locking plans.
So it all worked on DB2 and you get deadlocks on Oracle.
IMHO that is all down to the different way the two DB's lock items in the DB.
Go and read up on the locking strategies for both DB's THEN go talk to your Oracle DBA's about possibly changing the locking rules for that tablespace/table.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
|
|
