Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Transactions and Locking
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