Re: Usage of RowId...
Date: 1998/04/16
Message-ID: <35370236.490975_at_192.86.155.100>#1/1
A copy of this was sent to "Brian K. Lawton" <Lawton_at_RDAConsultants.Com> (if that email address didn't require changing) On Wed, 15 Apr 1998 18:55:45 -0400, you wrote:
>Let me clarify what I meant by optimistic concurrency. By optimistic
>concurrency, I mean always assume that your update is going to be
>successful. However, if the data/row has changed since the values were
>initially read, then fail the update.
>
[snip]
>SQL Server handles the row level concurrency through the use of a datatype
>called timestamp. The timestamp datatype (which has nothing to do with
>time) is simply a binary value that the server automatically increments each
>time the row is modified.
>
>What I was looking for from Oracle is how to get row level optimistic
>concurrency in the same manner as I do with SQL Server's timestamp datatype
>without having to write my own code to maintain the flag.
>
>Serialization will work, but it comes with a high cost.
>
Why do you believe it come at a high cost? Not in Oracle it doesn't. To do what you want, all you need to do is "alter session set isolation_level = serializable". It still preserves all of the following attributes of oracle:
- non blocking reads.
- writers only block writers of the same exact row.
Serializable in Oracle does not decrease concurrency at all. Consider the following example with two sessions. To replicate, all you need to do is open 2 windows and log in as scott/tiger in each. Then, follow the timeline below:
Session 1 Session 2
---------------------- -----------------------------
SQL> alter session set
isolation_level =
serializable;
Session altered.
SQL> select empno, ename from emp
where rownum = 1;
EMPNO ENAME
----- -----------
7369 SMITH
SQL> update emp set
ename = 'smith'
where empno = 7369;
1 row updated.
SQL> commit;
Commit complete.
SQL> select empno, ename from emp
where rownum = 1;
EMPNO ENAME
----- -----------
7369 SMITH
SQL> update emp set ename='smith'
where empno = 7369;
ERROR at line 1:
ORA-08177: can't serialize access
for this transaction
So, you get the optimistic concurrency control you were looking for and no real
change in the degree of concurrency possible in the database. serializable in
Oracle simply extends our multi-version read consistency model to cover the
entire transaction instead of a single statement.
>Sorry for the confusion.
>
>________________________________________________
>Brian K. Lawton (mailto:Lawton_at_RDAConsultants.com)
>RDA Consultants Limited (http://www.RDAConsultants.com)
>
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
http://govt.us.oracle.com/ -- downloadable utilities
Opinions are mine and do not necessarily reflect those of Oracle Corporation
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Apr 16 1998 - 00:00:00 CEST