Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Database or store to handle 30 Mb/sec and 40,000 inserts/sec

Re: Database or store to handle 30 Mb/sec and 40,000 inserts/sec

From: Galen Boyer <galen_boyer_at_yahoo.com>
Date: 18 Feb 2006 16:15:02 -0600
Message-ID: <u8xs8dtyv.fsf@rcn.com>


On Sat, 18 Feb 2006, tonyrogerson_at_sqlserverfaq.com wrote:
> And another one....
>
> http://www.akadia.com/services/ora_locks_survival_guide.html
>
> Application developers can eliminate all risk of enqueue deadlocks by
> ensuring that transactions requiring multiple resources always lock
> them in the same order. However, in complex applications, this is
> easier said than done

Yes, it isn't an easy task, but one which can be done by solid programming practices.

> , particularly if an ad hoc query tool is used.

Well, that isn't an application, so, sure, deadlocks can happen.

> To be safe, you should adopt a strict locking order, but you must also
> handle the ORA-60 exception appropriately. In some cases it may be
> sufficient to pause for three seconds, and then retry the
> statement. However, in general, it is safest to roll back the
> transaction entirely, before pausing and retrying.
>
> Note, the owness on 'application developers',

That is my point. But, in SQLServer, you can't depend on fixing your code to solve deadlocks, while in Oracle, you can. You have to put architecture in place to react to deadlocks occurring.

> also note 'handle the ORA-60 exception' appropriately.

Please explain how this has to do with deadlocks?

> This advice is the same in ANY database, make sure objects are always
> in the same order when you are manipulating the data in them.
>
> You can avoid deadlocks in SQL Server exactly using the same methods
> and locking behaviour you use in Oracle 'period', I not sure as to
> what qualifies you to give the SQL Server advice - may be you have
> some experience or are justing repeating hear-say, most probably the
> latter.
>
> So you are now saying that Oracle installations need a DBA to work,

What I said was that the deadlock problems you tried to show as issues on Oracle can be solved without putting "deadlock architecture" in place. Let me ask you, when you are designing applications with your team members, do you or do you not have design sessions around deadlock handling? I sure did when I was coding SQLServer apps.

> actually that is my experience - its usually several though, thats
> definitely not true in the SQL Server world! The DBA role is vanishing
> quickly,

Whooo Hooo!!! Whooptedoo!!! I'm sure they are hiding things so damn much that nobody will know what to do when the simplest of errors happen.

> their skills are moving more into Business Intelligence (comes as part
> of the SQL Server product) and some also do development, its mostly BI
> though.
>
> Anyway, we can keep going on with these exchanges; I've proved my
> point a couple of times that Oracle does have deadlocks

All databases have deadlocks, but this isn't what I was debating. The debate is the fact that solidly written SQLServer apps have to have deadlock architectures in place, while Oracle apps do not need them.

> and that you as a coder need to cater for them or certainly code round
> them.

Not in Oracle, you do not.

-- 
Galen Boyer
Received on Sat Feb 18 2006 - 16:15:02 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US