Home » RDBMS Server » Performance Tuning » DB deadlock issue
DB deadlock issue [message #297519] Fri, 01 February 2008 04:02 Go to next message
rballal
Messages: 12
Registered: February 2008
Junior Member
Hi

I have a table with unique key contraint enabled for 3 feilds (combinationally unique).

We have multiple threads inserting data into the table parallally.

This leads to deadlock over a period of time.

What would be the best way to hanlde this?

Thanks,
rballal
Re: DB deadlock issue [message #297523 is a reply to message #297519] Fri, 01 February 2008 04:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Always insert in the same order of the key.

Regards
Michel
Re: DB deadlock issue [message #297528 is a reply to message #297523] Fri, 01 February 2008 04:16 Go to previous messageGo to next message
rballal
Messages: 12
Registered: February 2008
Junior Member
Did you mean serialise the data insertion?

Re: DB deadlock issue [message #297531 is a reply to message #297523] Fri, 01 February 2008 04:23 Go to previous messageGo to next message
rballal
Messages: 12
Registered: February 2008
Junior Member
Sorry i missed one information here, to achieve better performance we dont commit the data immediately after INSERT , we wait for n records (each thread waits for n records )and then commit the data.
Re: DB deadlock issue [message #297540 is a reply to message #297531] Fri, 01 February 2008 04:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It depends on what you do.
There is no general answer but the one I gave.
And this one: take care and think about what you do.

Regards
Michel
Re: DB deadlock issue [message #297701 is a reply to message #297519] Sat, 02 February 2008 11:15 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
So... don't take this the wrong way, but I think you are lying to us.

I am no expert on deadlock, but unless you are doing something "special", I do not know how "just inserts" can cause deadlock. From my experience, deadlock always involves getting locks in the database either by DELETE or by UPDATE or by SELECT ... FOR UPDATE. You have not said you are doing either in your code.

The only time I think inserts can actually be a cause of deadlock is when you are using BITMAP indexes. But here again you have not indicated you are using this feature. Here is a link that talks a little about this:

http://www.akadia.com/services/ora_bitmapped_index.html

Lastly you say you are using parallel threads, but lets be clear here, you are using "concurrent transactions". You are not infact using Oracle parallel inserts as if you were you would not be able to commit after X rows. Thus your scenario is that you have multiple transactions running conncurrently, all trying to insert data at the same time.

So, what exactly are you not telling us?
Also, what is the mechanism you use to decide what rows are given to each insert process? That is, how do you split the incomming data up across your transactions?

I suggest the placement of some logging in your code so you can see where it is actually "deadlocking".

Kevin
Re: DB deadlock issue [message #297710 is a reply to message #297701] Sat, 02 February 2008 12:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can have a deadlock with a simple table and just 2 inserts in 2 sessions:
SQL> create table t (id integer constraint t_pk primary key, val varchar2(100));

Table created.

SQL> -- session 1
SQL> begin
  2    dbms_output.put_line(systimestamp||' session 1: inserting 1');
  3    insert into t values (1,'session 1');
  4    dbms_output.put_line(systimestamp||' session 1: 1 inserted - wait 10 seconds');
  5    dbms_lock.sleep (10);
  6    dbms_output.put_line(systimestamp||' session 1: inserting 2');
  7    insert into t values (2,'session 1');
  8    dbms_output.put_line(systimestamp||' session 1: 2 inserted - end');
  9  end;
 10  /
02/02/2008 19:41:01.453 +01:00 session 1: inserting 1
02/02/2008 19:41:01.453 +01:00 session 1: 1 inserted - wait 10 seconds
02/02/2008 19:41:11.453 +01:00 session 1: inserting 2
begin
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 7

SQL> -- session 2
SQL> begin
  2    dbms_output.put_line(systimestamp||' session 2: inserting 2');
  3    insert into t values (2,'session 2');
  4    dbms_output.put_line(systimestamp||' session 2: 2 inserted - wait 10 seconds');
  5    dbms_lock.sleep (10);
  6    dbms_output.put_line(systimestamp||' session 2: inserting 1');
  7    insert into t values (1,'session 2');
  8    dbms_output.put_line(systimestamp||' session 2: 1 inserted - end');
  9  end;
 10  /
02/02/2008 19:41:09.406 +01:00 session 2: inserting 2
02/02/2008 19:41:09.406 +01:00 session 2: 2 inserted - wait 10 seconds
02/02/2008 19:41:19.406 +01:00 session 2: inserting 1
02/02/2008 19:41:23.828 +01:00 session 2: 1 inserted - end

PL/SQL procedure successfully completed.

Regards
Michel
Re: DB deadlock issue [message #297726 is a reply to message #297710] Sun, 03 February 2008 00:49 Go to previous messageGo to next message
rballal
Messages: 12
Registered: February 2008
Junior Member
yes , this is the same issue that we are facing.
Re: DB deadlock issue [message #297727 is a reply to message #297726] Sun, 03 February 2008 00:58 Go to previous messageGo to next message
rballal
Messages: 12
Registered: February 2008
Junior Member
Pls advice on how to avoid these kinds of deadlocks?.

I understand one solution would be to commit immediately after inserting data, but here we want to achieve better performance that is our application must be able to insert the data at the rate of >500 records per sec, which we could not achieve using immediate commit, but with deffered commit (commit after n records are inserted ) we are able to achieve this.

Only when we get duplicate records due to retransmission of packets we are having this issue of deadlock.

Looking for your inputs on this.

Thanks a lot for all your help

rb
Re: DB deadlock issue [message #297734 is a reply to message #297727] Sun, 03 February 2008 02:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Pls advice on how to avoid these kinds of deadlocks?.

I think I already answered twice to this question.

Regards
Michel
Re: DB deadlock issue [message #297741 is a reply to message #297523] Sun, 03 February 2008 04:09 Go to previous messageGo to next message
rballal
Messages: 12
Registered: February 2008
Junior Member
Hi Michel,

I am a novice in this feild.. Sad

Could you pls elaborate on this with example:

"Always insert in the same order of the key."

Thanks in advance,
rb
Re: DB deadlock issue [message #297748 is a reply to message #297741] Sun, 03 February 2008 05:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you understand why I have a deadlock in my tiny example?

Regards
Michel
Re: DB deadlock issue [message #298023 is a reply to message #297519] Mon, 04 February 2008 11:23 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Nice example, of course so obvious it escaped me.

My appoligies to you RB, for not thinking this was the root of your problem.

As Michel points out, once you see why the example is causing deadlock, you will understand the earlier post about "the order of keys being loaded".

Walk through Michel's examples one line at a time and keep in mind the following:

1) each code snippet was started from a different sqlplus sessions
2) assume the second code snippet was started one second after the first one.

** so... Michel did not run both anonymous blocks from the same sqlplus session one after the other, they were interleaved from two different sqlplus sessions. Keeping the two facts above as context, desk check the processes. Create an "insert time line" to see what is happening.

Kevin
Re: DB deadlock issue [message #298062 is a reply to message #298023] Mon, 04 February 2008 20:51 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
One possibility is to make sure that rows with the same key are always processed by the same thread.

So if you have 4 threads, instead of randomly dividing the rows amongst them, use a deterministic method. eg. Keys starting with "1" go to thread number 1. Or you could use the ORA_HASH() function.

Ross Leishman
Re: DB deadlock issue [message #298112 is a reply to message #298062] Tue, 05 February 2008 00:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
So if you have 4 threads, instead of randomly dividing the rows amongst them, use a deterministic method.

Yes this is exactly what Oracle does in parallel query not to prevent from deadlock but for performances purpose but the goal is the same: to not collide.

Regards
Michel
Re: DB deadlock issue [message #298224 is a reply to message #297519] Tue, 05 February 2008 06:44 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I would still like to see RB do an INSERT TIMELINE to show us he gets it.

Kevin
Re: DB deadlock issue [message #298409 is a reply to message #297519] Wed, 06 February 2008 01:26 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Just wondering:

Inserting a record with the same primary key as a previously inserted record would cause a DUP_VAL_ON_INDEX error if it was done within the same session.

How is this handled in OP's situation?

Clearly in OP's case the same keys are inserted more than once - in different sessions/threads - but there is nothing mentioned on how this exception handled.

Furthermore, by waiting to COMMIT after n-records have been inserted you'll begging for this deadlock situation.

It's rather a design-problem: how to design your application to cope with possible duplicates when inserting data.

Easiest solution at this point: COMMIT after each insert!
Re: DB deadlock issue [message #298415 is a reply to message #298409] Wed, 06 February 2008 01:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Easiest solution at this point: COMMIT after each insert!

Aaargh! Worst solution!
A statement is NOT a transaction.
Goodbye performances! Welcome in hell!

Regards
Michel
Re: DB deadlock issue [message #298421 is a reply to message #298415] Wed, 06 February 2008 01:41 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Michel Cadot wrote on Wed, 06 February 2008 08:33
Quote:
Easiest solution at this point: COMMIT after each insert!

Aaargh! Worst solution!
A statement is NOT a transaction.
Goodbye performances! Welcome in hell!

Regards
Michel



Ah, but at this point we don't know for sure what would be "one transaction" in OP's case. It's possible that "ONE INSERT" equals "ONE TRANSACTION".

It all depends on the design of the application:
- Why multiple threads?
- How is it possible to have to insert duplicate keys in that particular table?

All I'm saying - like you did - is that we're lacking information to give a decent answer that fits the OP's problem.

And I prefer a slower system over a system that crashes with deadlock situations Wink





Re: DB deadlock issue [message #298430 is a reply to message #298421] Wed, 06 February 2008 01:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I prefer a slower system over a system that crashes with deadlock situations

It depends on your point of view.
For a developer, of course, he just has to say this is DBA problem now, DBA just has to make the system faster (which is most likely an impossible task if workload is high, but why developer should care?).
For a DBA, he would prefer deadlocks that clearly show the application is badly written and don't "crash" the system just the wrong application (but some applications I saw handle deadlocks in their logic as they know they are wrong!).

Regards
Michel
Re: DB deadlock issue [message #298441 is a reply to message #298430] Wed, 06 February 2008 02:09 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Michel Cadot wrote on Wed, 06 February 2008 08:55
Quote:
I prefer a slower system over a system that crashes with deadlock situations

It depends on your point of view.
For a developer, of course, he just has to say this is DBA problem now, DBA just has to make the system faster (which is most likely an impossible task if workload is high, but why developer should care?).
For a DBA, he would prefer deadlocks that clearly show the application is badly written and don't "crash" the system just the wrong application (but some applications I saw handle deadlocks in their logic as they know they are wrong!).

Regards
Michel



I admit: I'm a developer rather than a DBA Wink

And I've done my share of DBA work as well. I've done enough rewriting of badly written applications to get them a better performance.

With "crashing" applications there is also a third party involved: operations (in our shop that is). Those guys (and girls) who monitor all applications to make sure they're running when they have to. To report when errors occurred to make sure they're fixed so they can restart the proper applications.

Is it allowed to say that in this case it is clearly a badly designed/written one?
Re: DB deadlock issue [message #298443 is a reply to message #298441] Wed, 06 February 2008 02:13 Go to previous message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is it allowed to say that in this case it is clearly a badly designed/written one?

That is what I say since my first post.

Regards
Michel
Previous Topic: Incremental logic on a complex query
Next Topic: Deletion records - Performance
Goto Forum:
  


Current Time: Wed Dec 07 02:47:33 CST 2016

Total time taken to generate the page: 0.06619 seconds