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: Transactions and Locking

Re: Transactions and Locking

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/10/06
Message-ID: <343f19d0.22765024@newshost>#1/1

On Mon, 06 Oct 1997 21:50:44 -0700, Phil Bradley <pbradley_at_pacific.net.sg> wrote:

>
>
>Richard Perks wrote:
>
>> Hi all,
>>
>> I'm fairly new to Oracle7 and we are in the process of converting an
>> application to client/server using oracle. Can anyone explain to me in
>> detail how row level record locking works in Oracle ?
>>
>> I have seen the FOR UPDATE clause of a SELECT statement but I'm not sure how
>> locking is implemented where a user has say a customer window open with a
>> lock applied to the customer table and another window open with a lock
>> applied to an orders table. Can this type of modeless transaction be
>> performed, and if so, what about commiting and rolling back the separate
>> transactions or can only one transaction be active at any one point in time
>> ?
>
>Oracle implements as default the serialisable isolation level defined in SQL
>92. In fact it goes further. What this means in practise is that concurrent
>updates are prevented, and no reads are blocked. The FOR UPDATE clause
>registers your transactions interest in updating the row and prevents any one
>else acquiring a UPDATE lock. Your message implies you are acquiring explicit
>locks. Don't do it.
>

Not really. Oracle implements by default what ansi calls "read committed" isolation. what read committed offers is less then repeatable read and serializable.

In a serializable transaction, issuing the query twice in a transaction would result in the same results, so

SQL> select count(*) from emp;
SQL> select count(*) from emp;

would always return the same number within the same transaction. This is not the case however by default.

Oracle does support the isolation level serializable though. Alter session isolation { SERIALIZABLE | READ COMMITTED } is supported. Serializable transactions will always see the database as it existed when their transaction began.

>Multiple concurrent transactions can exist. All isolated from one another.
>
>The SQL92 transaction model is based on row level locking. Don't even consider
>table level locks except with batch processes that update an entire table.
>

>Phil Bradley
>
>>
>>
>> Any help would be gratefully appreciated,
>>
>> Regards,
>>
>> Richard Perks
>
>

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities



Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Oct 06 1997 - 00:00:00 CDT

Original text of this message

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