| Author |
Message
|
| pratik_oze |
 Posted: Tue Oct 21, 2008 10:37 am Post subject: deadlock in oracle db while processing messages in WMB6.0 |
 |
|
Apprentice
Joined: 22 Jun 2004
Posts: 34
|
Hi,
Here is the deadlock situation I am facing.We receive swift message into our wmb flow where we unbulk that single message into number of messages. Now in this flow we call a procedure (CREATE PROCEDURE getNextUniqueId(OUT uniqueId INTEGER) LANGUAGE DATABASE EXTERNAL NAME "DB_DBA.DB.DB_GET_NEXT_UNIQUE_ID")
In the getNextUniqueId script ( PROCEDURE MRA_GET_NEXT_UNIQUE_ID(uniqueID OUT NOCOPY NUMBER)
AS
BEGIN
SELECT DB_DBA.DB_UNIQUEID_SEQ.NEXTVAL
INTO uniqueID
FROM DUAL;
END DB_GET_NEXT_UNIQUE_ID;) we get the unique id for each message generated.
------------------------------------------------------------
now the design is such that we have WMB on 4 unix boxes parallely processing number of messages at a given time but all these 4 talk to same database. So my assumption is that 2 messages were given the same unique id and hence the 2 sessiosn were trying to write the data to same row of the xyz table in the db, which resulted in deadlock and message failure.
----------------------------------------------
please provide inputs.
Last edited by pratik_oze on Tue Oct 21, 2008 11:59 am; edited 1 time in total |
|
| Back to top |
|
 |
| fjb_saper |
| Posted: Tue Oct 21, 2008 11:14 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Make sure that your DB procedure retrieving the next id forces synchronization of the different threads 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| pratik_oze |
| Posted: Tue Oct 21, 2008 11:24 am Post subject: |
|
|
Apprentice
Joined: 22 Jun 2004
Posts: 34
|
Thanks,
Can you please help me in understanding how this can be done?
I also found something intresting on the internet
===================
Row-level locks are primarily used to prevent two transactions from modifying the same row. When a transaction needs to modify a row, a row lock is acquired.
There is no limit to the number of row locks held by a statement or transaction, and Oracle does not escalate locks from the row level to a coarser granularity. Row locking provides the finest grain locking possible and so provides the best possible concurrency and throughput.
The combination of multiversion concurrency control and row-level locking means that users contend for data only when accessing the same rows, specifically:
Readers of data do not wait for writers of the same data rows.
Writers of data do not wait for readers of the same data rows unless SELECT ... FOR UPDATE is used, which specifically requests a lock for the reader.
Writers only wait for other writers if they attempt to update the same rows at the same time.
Note:
Readers of data may have to wait for writers of the same data blocks in some very special cases of pending distributed transactions.
A transaction acquires an exclusive row lock for each individual row modified by one of the following statements: INSERT, UPDATE, DELETE, and SELECT with the FOR UPDATE clause.
A modified row is always locked exclusively so that other transactions cannot modify the row until the transaction holding the lock is committed or rolled back. However, if the transaction dies due to instance failure, block-level recovery makes a row available before the entire transaction is recovered. Row locks are always acquired automatically by Oracle as a result of the statements listed previously.
If a transaction obtains a row lock for a row, the transaction also acquires a table lock for the corresponding table. The table lock prevents conflicting DDL operations that would override data changes in a current transaction.
=====================================
Does this make sense for my problem? |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Wed Oct 22, 2008 12:26 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Entirely, what happens if you change your select into a select for update and do an explicit commit once you have acquired the seqnum? Of course this change would all be in the oracle procedure....
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| pratik_oze |
| Posted: Wed Oct 22, 2008 2:34 am Post subject: |
|
|
Apprentice
Joined: 22 Jun 2004
Posts: 34
|
| Won't this slow down the processing of messages and thus create a performance issue? |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Wed Oct 22, 2008 6:20 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
| pratik_oze wrote: |
| Won't this slow down the processing of messages and thus create a performance issue? |
Shoudn't. That call as I understand it is real fast...
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| pratik_oze |
| Posted: Thu Oct 23, 2008 10:53 am Post subject: |
|
|
Apprentice
Joined: 22 Jun 2004
Posts: 34
|
Still waiting for solution  |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Thu Oct 23, 2008 3:21 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
| pratik_oze wrote: |
| Still waiting for solution |
So have you tried the suggested one and to what result?
_________________
MQ & Broker admin |
|
| Back to top |
|
|
|
|
