Re: oracle - mysql comparison

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Sun, 18 Jul 2004 22:54:43 -0700
Message-ID: <1090216505.569999@yasure>

VC wrote:

> Please see below:
>
> "Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message
> news:1090027247.885352_at_yasure...
>

>>VC wrote:
>>
>>
>>>Hello Daniel,
>>>
>>>"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message
>>>news:1089945278.134355_at_yasure...
>>>
>>>
>>>>VC wrote:
>>>>
>>>>
>>>>
>>>>>A correct concurrency control/model ensures that all the permitted
>>>>>transactions are serializable.  In this sense, databases like DB2 or
>>>
>>>MSSQL
>>>
>>>
>>>>>implement a correct concurrency model albeit at the expense of lower
>>>>>concurrency in some circumstances.  Funnily enough,  none of  the

>
> Oracle
>

>>>>>isolation levels can make the same promise, i.e. ensure serializable
>>>>>transaction histories, in any of its isolation levels.  Usually,  it's
>>>
>>>quite
>>>
>>>
>>>>>easy to obtain correct results by augmenting an isolation level with
>>>>>something like 'select for update',  though..
>>>>
>>>>And if this is true why, exactly, would anyone care at the expense of
>>>>being able to extract an accurate answer from a database without
>>>>performing table locks on all resources?
>>>>
>>>
>>>
>>>I am not sure what specific part of my message the word *this* ('if this

>
> is
>

>>>true')  refers to, but assuming it's the last sentence then you surely

>
> know
>

>>>that 'select for update' takes write (TX) lock on all the rows involed

>
> A
>

>>>locking scheduler would take *read* row level locks in similar

>
> circumstances
>

>>>automatically.  If my interpretaion is incorrect,  please elaborate.
>>>
>>>VC
>>
>>My point is that in all of the other databases to which you have
>>referred it is impossible to get a result set consistent to a
>>point-in-time without locking the resources (not at the row level but at
>>the table level).
>>
>>Something I would think far more important than anything you brought up.
>>

>
>
> Assuming you are talking here about the locking database's SERIALIZABLE ,
> no, it's not always necessary to lock the whole table(s) in order to obtain
> a consistent result. Sometimes, yes, the whole table has to be locked,
> but not always. I can give an example or two if you care.
>
> On the other hand, Oracle's concurrency control makes solving some problems
> harder than in a locking environment precisely because of Oracle locking
> mecanism simplicity or some may say deficiency, e.g. absence of row-level
> read locks, key-range locking, etc.
>
> As I mentioned in my reply to another poster, in many situations Oracle
> undoubtedly provides higher concurrency than locking database engines, after
> all it was designed to do so although certain compromises have been made.
>
> Regards.
>
> VC

I think your understanding of Oracle is rather slight based on what I assume you mean.

How in mysql can you obtain a result consistent to point-in-time without table locking all resources.

And in Oracle ... I would like a demonstration of how would go about not having a point-in-time consistent result without intentionally corrupting a query result.

Daniel Morgan Received on Mon Jul 19 2004 - 00:54:43 CDT