Re: Transactions and Locking

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.

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
Received on Mon Oct 06 1997 - 00:00:00 CDT