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: oracle - mysql comparison

Re: oracle - mysql comparison

From: VC <boston103_at_hotmail.com>
Date: Tue, 20 Jul 2004 18:57:47 GMT
Message-ID: <LWdLc.145936$XM6.35524@attbi_s53>


Hi,

"Volker Hetzer" <volker.hetzer_at_ieee.org> wrote in message news:cdjor6$ctg$1_at_nntp.fujitsu-siemens.com...
>
> "VC" <boston103_at_hotmail.com> schrieb im Newsbeitrag
news:RfdLc.109372$WX.92600_at_attbi_s51...
> > Here's another textbook example for you:
> > ==
> > There are two linked accounts (id=1 and id=2) in a bank. A transaction
> > might look as follows:
> >
> > == withdraw from 1
> > select amount into x from accounts where id=1
> > select amount into y from accounts where id=2
> > if x+y >= withdrawal then update accounts set amount=amount-withdrawal
> > where id=1
> > commit
> > =====
> >
> > Any commercial locking scheduler will handle the scenario correctly.
Oracle
> > won't.
> Sorry for butting in but I'm just trying to learn here, so what would go
wrong?
> And what about "select for update" in oracle? It's supposed to lock the
rows it hits.

'Select for update' will indeed work correctly. However, there are at least three problems with this solution:

  1. 'Serializable' is supposed to ensure correct execution by itself without resorting to the 'for update' clause.
  2. Concurrency will be *lower*, funnily enough, than that of a locking database due to selects using exclusive locks (TX) in Oracle instead of shared (S) locks in DB2/Sybase. For example, if x+y < withdrawal, clearly no transaction would not block. Not so in Oracle.
  3. 'For update' is non-standard (least important).

Regards.

VC

>
> Lots of Greetings!
> Volker
Received on Tue Jul 20 2004 - 13:57:47 CDT

Original text of this message

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