Home » SQL & PL/SQL » SQL & PL/SQL » Resolving dead lock error
Resolving dead lock error [message #264490] Mon, 03 September 2007 09:34 Go to next message
lijok
Messages: 68
Registered: April 2005
Member
Oracle version:Oracle Database 10g Release 10.1.0.3.0 - Production

I'm sorry this post is slightly bigger, please read through the whole text to help me. I've tried to minimize as much as possible.
If there is anything that you want specifically please let me know

We have our application exposed through UI and also allow xml transaction postings and this problem in particularly is more noticed in case of xml
transaction postings. There are 2 tables involved here (glentry and basegltotals). Insert on glentry fires a trigger which in turn calls a stored
procedure from package and inserts into basegltotals. Somehow the insertions between these tables in causing a dead lock situation.
The error description are different some times, in one case it explicitly says unique constraint violation. Here are both the error
descriptions

60:ORA-00060: deadlock detected while waiting for resource ORA-06512: at
"AC02_OWNER_01.ACCT_UTILS", line 381 ORA-00001: unique constraint
(AC02_OWNER_01.PK_BASEGLTOTAL) violated ORA-06512: at
"AC02_OWNER_01.GLENTRY_DO_GLTOTALS", line 17 ORA-04088: error during
execution of trigger 'AC02_OWNER_01.GLENTRY_DO_GLTOTALS' insert into glentry
(cny#,RECORD#,tr_type,document,amount,account#,description,location#,user#,entr
y_date,custfield1,custfield2,custfield3,custfield4,batch#,timeperiod,line_no)
values
(:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17)

60:ORA-00060: deadlock detected while waiting for resource ORA-06512: at
"AC02_OWNER_01.ACCT_UTILS", line 281 ORA-06512: at
"AC02_OWNER_01.GLENTRY_DO_GLTOTALS", line 17 ORA-04088: error during
execution of trigger 'AC02_OWNER_01.GLENTRY_DO_GLTOTALS' insert into glentry
(cny#,RECORD#,tr_type,document,amount,account#,description,location#,user#,entr
y_date,custfield1,custfield2,custfield3,custfield4,batch#,timeperiod,line_no)
values
(:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17)


There could be more than one reasons why we could run into dead lock situation and I'm ruling them out one by one.

- application design flaw (In a transaction tables should be locked in the
same order). In our case, its insert into glentry and the trigger on glentry
which inserts into basegltotals.

- unindexed foreign keys - we don't have any.

- we don't use bitmap indexes


The primary key on basegltotals is a little bit unusual, the primary key is a combination of 7 fields. Could this be a cause of this problem?
The definition for basegltotals is as shown below

CREATE TABLE BASEGLTOTALS
(
  CNY#        NUMBER(8) CONSTRAINT NN_BASEGLTOTAL_CNY NOT NULL,
  ACCOUNT#    NUMBER(8) CONSTRAINT NN_BASEGLTOTAL_ACCOUNT NOT NULL,
  LOCATION#   NUMBER(8) CONSTRAINT NN_BASEGLTOTAL_LOCATION NOT NULL,
  DEPT#       NUMBER(8) CONSTRAINT NN_BASEGLTOTAL_DEPT NOT NULL,
  TIMEPERIOD  NUMBER(8) CONSTRAINT NN_BASEGLTOTAL_TIMEPERIOD NOT NULL,
  CURRENCY    VARCHAR2(3 BYTE)                  DEFAULT 'ZZZ' CONSTRAINT NN_BASEGLTOTAL_CURRENCY NOT NULL,
  AMOUNT      NUMBER                            DEFAULT 0 CONSTRAINT NN_BASEGLTOTAL_AMOUNT NOT NULL,
  DEBIT       NUMBER                            DEFAULT 0 CONSTRAINT NN_BASEGLTOTAL_DEBIT NOT NULL,
  CREDIT      NUMBER                            DEFAULT 0 CONSTRAINT NN_BASEGLTOTAL_CREDIT NOT NULL,
  ADJDEBIT    NUMBER                            DEFAULT 0 CONSTRAINT NN_BASEGLTOTAL_ADJDEBIT NOT NULL,
  ADJCREDIT   NUMBER                            DEFAULT 0 CONSTRAINT NN_BASEGLTOTAL_ADJCREDIT NOT NULL,
  BOOKID      VARCHAR2(20 BYTE), 
  CONSTRAINT PK_BASEGLTOTAL
 PRIMARY KEY
 (CNY#, BOOKID, ACCOUNT#, LOCATION#, DEPT#, TIMEPERIOD, CURRENCY)
)


Also scrutinizing some earlier such reported problems confirm that this problem in particular happens mostly when there is a situation of multi books.
What I mean is, in case of multi books, the cursor(my_books_cur) defined below returns always 2 rows and the insert into basegltotals happen in a loop.
Now since the select clause in the cursor is not ordered, it may result in a result set which is ordered differently for the next transaction.
So, if two transactions are simultaneously hitting the glentry for a given company and since the select order can vary for both transactions, will
that cause a problem? and I was told that oracle 10G does not guarantee this ordering to be unique every time

I tried to reproduce the case in a local db by setting the same mutli books environment and having a xml posting transaction with 2 sets of
transactions in the same xml request and having 5-6 such sessions open and try to post it simultaneously. But, I'm unable to reproduce the
case locally and I don't have a clue what else can cause this problem. My DBA tells me it could be a clash of indexes and its very difficult to debug.


(I'm removing all unrelated pl/sql to keep the text to minimum)

--Thi is the procedure called from a package when glentry table insert happens.

	procedure UpdateGLTotalsInsert(
		a_cny#			in glentry.cny#%type,
		a_entry_date	in glentry.entry_date%type,
		a_amount		in glentry.amount%type,
		a_account#		in glentry.account#%type,
		a_location#		in glentry.location#%type,
		a_dept#			in glentry.dept#%type,
		a_adj			in glentry.adj%type,
		a_timeperiod	in glentry.timeperiod%type,
		a_batch#		in glentry.batch#%type,
		a_currency		in glentry.currency%type,
		a_trx_amount	in glentry.trx_amount%type
	) is
		my_time_period  NUMBER;
		my_debit		glentry.amount%type;
		my_credit		glentry.amount%type;
		my_adjdebit		glentry.amount%type;
		my_adjcredit	glentry.amount%type;
		my_trx_debit	glentry.trx_amount%type;
		my_trx_credit	glentry.trx_amount%type;
		my_trx_adjdebit	glentry.trx_amount%type;
		my_trx_adjcredit	glentry.trx_amount%type;
		my_bookid		glbook.bookid%type;
		my_currency		VARCHAR2(3);
		basecurr		VARCHAR2(3);

		cursor my_books_cur is
			select	distinct bk.cny#, bk.bookid
			from 	bookjournals bk, basejournal bj, glbatch gb
			where	bk.cny# = bj.cny# and bk.journalkey = bj.record#
				and bj.cny# = gb.cny# and bj.record# = gb.journal#
				and gb.cny# = a_cny# and gb.record# = a_batch#;

	begin
		BEGIN

			FOR rec IN my_books_cur
			LOOP

				BEGIN
					INSERT INTO basegltotals (cny#, bookid, account#, location#, dept#, timeperiod,
							amount, debit, credit, adjdebit, adjcredit, currency)
						VALUES (a_cny#, rec.bookid, a_account#, nvl(a_location#,0), nvl(a_dept#,0),
							my_time_period, a_amount, my_debit, my_credit, my_adjdebit, my_adjcredit,
							basecurr);
					EXCEPTION
						WHEN DUP_VAL_ON_INDEX THEN
							UPDATE	basegltotals gltotals
							SET gltotals.amount 	= gltotals.amount + a_amount,
								gltotals.debit      = gltotals.debit + my_debit,
								gltotals.credit     = gltotals.credit + my_credit,
								gltotals.adjdebit   = gltotals.adjdebit + my_adjdebit,
								gltotals.adjcredit  = gltotals.adjcredit + my_adjcredit
							WHERE	gltotals.cny# = a_cny#			AND
								gltotals.bookid = rec.bookid		AND
								gltotals.account# = a_account#		AND
								gltotals.location# = nvl(a_location#,0)	AND
								gltotals.dept# = nvl(a_dept#,0)		AND
								gltotals.timeperiod = my_time_period AND
								gltotals.currency = basecurr;
						when others then raise;
				END;
			END LOOP;

		END ;
	end UpdateGLTotalsInsert;

Re: Resolving dead lock error [message #264500 is a reply to message #264490] Mon, 03 September 2007 10:04 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Hi,

If the problem really is that the insert/updates in the

FOR rec IN my_books_cur....


loop deadlock each other, then you could try comiting after each insert/update inside the loop. (or rolling back in the when others block)

But then the procedure should be made a automomous transaction, so the commit is just made for the insert/updates in basegltotals, not for any other uncommited transactions the session might have.
Re: Resolving dead lock error [message #264550 is a reply to message #264500] Mon, 03 September 2007 13:30 Go to previous messageGo to next message
lijok
Messages: 68
Registered: April 2005
Member
But we want the commit to happen only when the transaction as a whole succeeds. Autonomous doesn't seem the right wat to go.
Re: Resolving dead lock error [message #264560 is a reply to message #264550] Mon, 03 September 2007 14:12 Go to previous messageGo to next message
lecorr
Messages: 17
Registered: October 2006
Location: Courbevoie, France
Junior Member
Hi,

First point: as you said, dead lock comes from unordered modifications in the table(s).

So put an ORDER BY on your cursor my_books_cur.

This should solve your problem, if it comes from the procedure.

In the oracle trace, you should find more informations about deadlock source (first of all, the text of the queries). Try to use it to find the root cause of the problem instead of trying to guess.

Regards,

Christian
Re: Resolving dead lock error [message #264600 is a reply to message #264560] Mon, 03 September 2007 22:29 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Post the deadlock trace here (the DBA can get it for you from the UDUMP directory).
Re: Resolving dead lock error [message #264622 is a reply to message #264600] Mon, 03 September 2007 23:44 Go to previous messageGo to next message
lijok
Messages: 68
Registered: April 2005
Member
I will try and get the trace file as early as possible. There might be some delay in getting this file as one of our dba works from Greece and the other from US and I'm working from India office. Thanks for your initiative.
Re: Resolving dead lock error [message #264837 is a reply to message #264622] Tue, 04 September 2007 13:28 Go to previous messageGo to next message
lijok
Messages: 68
Registered: April 2005
Member
Here is part of the trace file, please let me know
DEADLOCK DETECTED ( ORA-00060 )
[Transaction Deadlock]
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-00070025-0004be69       141     913     X            133     928           S
TX-00060018-0006ab9e       133     928     X            141     913           S
session 913: DID 0001-008D-00000C5B session 928: DID 0001-0085-00000D0E
session 928: DID 0001-0085-00000D0E session 913: DID 0001-008D-00000C5B
Rows waited on:
Session 928: no row
Session 913: no row
Information on the OTHER waiting sessions:
Session 928:
  Current SQL Statement:
  
insert into glentry (cny#,RECORD#,tr_type,document,amount,account#,description,location#,user#,entry_date,custfield1,custfield2,cust
field3,custfield4,batch#,timeperiod,line_no) values (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17)

End of information on OTHER waiting sessions.
Current SQL statement for this session:
INSERT INTO BASEGLTOTALS (CNY#, BOOKID, ACCOUNT#, LOCATION#, DEPT#, TIMEPERIOD, AMOUNT, DEBIT, CREDIT, ADJDEBIT, ADJCREDIT, CURRENCY
 ) VALUES (:B12 , :B11 , :B10 , NVL (:B9 , 0), NVL (:B8 , 0), :B7 , :B6 , :B5 , :B4 , :B3 , :B2 , :B1 )
Re: Resolving dead lock error [message #264885 is a reply to message #264837] Tue, 04 September 2007 22:34 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I tried it myself inserting duplicates in two sessions and got the following:
DEADLOCK DETECTED
Current SQL statement for this session:
insert into ross values ('B')
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-00040059-000027a6        10     150     X             11     157           S
TX-00030015-000081c7        11     157     X             10     150           S
session 150: DID 0001-000A-00002005     session 157: DID 0001-000B-00000ED9
session 157: DID 0001-000B-00000ED9     session 150: DID 0001-000A-00002005
Rows waited on:
Session 157: no row
Session 150: obj - rowid = 00000064 - AAAABkAABAAAAF+AAA
  (dictionary objn - 100, file - 1, block - 382, slot - 0)
Information on the OTHER waiting sessions:
Session 157:
  pid=11 serial=18592 audsid=202327 user: 52/CDMDEV
  O/S info: user: c985675, term: pts/1, ospid: 25077, machine: lxapp0046v
            program: sqlplus@lxapp0046v (TNS V1-V3)
  application name: SQL*Plus, hash value=3669949024
  Current SQL Statement:
  insert into ross values ('A')
End of information on OTHER waiting sessions.


The profile of blocker lock type and waiter lock type is the same, but they look a bit different in terms of the "Rows Waited On". Inconclusive.

Since you have actually identified a POSSIBLE cause, that is the one you need to eliminate first before you start looking for weird causes. Stick with @lecorr's advice - try the ORDER BY.

Ross Leishman
Re: Resolving dead lock error [message #264889 is a reply to message #264885] Tue, 04 September 2007 23:04 Go to previous messageGo to next message
lijok
Messages: 68
Registered: April 2005
Member
Yes, we will push to production the cursor change with an order by clause and watch if the dead lock case comes back. Thanks for all your patience and helping me out.

I was actually wondering, how confident is oracle in making this statement in the trace file

"The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL"

Does dead lock always happen because of design flaw only and never because of oracle internals under mysterious circumstances?
Re: Resolving dead lock error [message #264895 is a reply to message #264490] Tue, 04 September 2007 23:26 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>Does dead lock always happen because of design flaw only and never because of oracle internals under mysterious circumstances?

Never use never in any statement.

A deadlock due to application design flaw approaches certainty.

HTH & YMMV
Re: Resolving dead lock error [message #264983 is a reply to message #264895] Wed, 05 September 2007 03:27 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
When we read that statement we translate it in our heads to mean "You're an idiot and you let two sessions UPDATE THE SAME DATA in reverse order". But it's much broader than that.

With table and b-tree index data, if you avoid the situation where two sessions can lock the SAME ROW (or if you cannot avoid that, make sure they lock them in the SAME ORDER), then you will NOT get a deadlock. Foreign key share-locks might be an exception to this though.

Bitmap indexes are different. A deadlock can occur when two sessions are updating rows with index entries that co-exist in the same BLOCK; they are not necessarily the same ROWS. This is (I believe) because bitmap indexes do not support row-level-locking.

It's a bit of a stretch to say that Oracle's inability to row-level-lock bitmap indexes is an application flaw, but Oracle's position is that bitmap indexes are best suited to single session maintenance.

Ross Leishman
Re: Resolving dead lock error [message #265035 is a reply to message #264983] Wed, 05 September 2007 05:19 Go to previous messageGo to next message
lijok
Messages: 68
Registered: April 2005
Member
Leishman,
Thanks for the update

-Lijo
Re: Resolving dead lock error [message #265048 is a reply to message #265035] Wed, 05 September 2007 05:43 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
As for the design part, I have not yet seen an application with client/trigger - updated "sum" tables where those sum tables were not out of sync with the detail data sooner or later, and there had to be some workaround added to re-calculate them on a regular schedule.

When you have a table with sums updated via a trigger when the detail is edited, then the likelyhood that two sessions edit details for the same line in those sums at the same time is high, so the likelyhood for locks and/or deadlocks is also high.

How often does the app commit by the way?

After *each* line in the detail table is modified/added, or only after *all* lines in the detail table that have to be modified/added are?

Might that be another cause for the deadlock?

Previous Topic: Doubts in Procedure
Next Topic: firstname and middel name in capitals?
Goto Forum:
  


Current Time: Mon Dec 05 03:10:37 CST 2016

Total time taken to generate the page: 0.15588 seconds