Row locking and serializability
iggy_fe...@my-deja.com
transactions is, in some sense, a "safe mode" of operation that
guarantees data consistency. It is not at all intuitively obvious that
concurrent execution of transactions is an equally safe mode of
operation. The SQL92 standard provides for the "SET TRANSACTION
ISOLATION LEVEL SERIALIZABLE" command which gives the user the ability
to specify that the concurrent execution of transactions should produce
the same results as "some" serially executed sequence of those same
transactions.
The following excerpt is from Chapter 3 of the Oracle8 Application
Developer's Guide. I have the following questions
(1) The author mentions a "greater degree of transaction isolation than
defined by SQL92 SERIALIZABLE mode". Has Oracle correctly interpreted
the SQL92 definition of serializibility?
(2) "Readers" do not block "writers" in an Oracle database. Is it
possible to guarantee serializability without requiring readers to
block writers?
(3) Oracle only places locks on rows that satisfy query criteria and
does not place read locks on pages that were scanned in the process of
executing a query. Is it possible to guarantee serializability without
placing read locks on all such pages?
(4) I was able to perform the referential integrity experiments against
an Ingres 6.4/06 database without experiencing the anomalies described
in the Oracle documentation. Is Oracle alone in its interpretation of
the SQL92 defininition of serializability?
--- begin excerpt ---
Because Oracle does not use read locks, even in SERIALIZABLE
transactions, data read by one transaction can be overwritten by
another. Transactions that perform database consistency checks at the
application level should not assume that the data they read will not
change during the execution of the transaction (even though such
changes are not visible to the transaction). Database inconsistencies
can result unless such application-level consistency checks are coded
carefully, even when using SERIALIZABLE transactions.
Consider two different transactions that perform application-level
checks to maintain the referential integrity parent/child relationship
between two tables. One transaction reads the parent table to determine
that a row with a specific primary key value exists before inserting
corresponding child rows. The other transaction checks to see that no
corresponding detail rows exist before proceeding to delete a parent
row. In this case, both transactions assume (but do not ensure) that
data they read will not change before the transaction completes.
Time T1: Transaction A: Read parent (it exists)
Time T2: Transaction B: Read child rows (not found)
Time T3: Transaction A: Insert child row(s)
Time T4: Transaction B: Delete parent
Time T5: Transaction A: Commit work
Time T6: Transaction B: Commit work
Note that the read issued by transaction A does not prevent transaction
B from deleting the parent row. Likewise, transaction B's query for
child rows does not prevent the insertion of child rows by transaction
A. Therefore the above scenario leaves in the database a child row with
no corresponding parent row. This result would occur even if both A and
B are SERIALIZABLE transactions, because neither transaction prevents
the other from making changes in the data it reads to check
consistency.
As this example illustrates, for some transactions, application
developers must specifically ensure that the data read by one
transaction is not concurrently written by another. This requires a
greater degree of transaction isolation than defined by SQL92
SERIALIZABLE mode.
--- end excerpt ---
Disclaimers: (1) My employer may have opinions very different from
mine. (2) My opinions may prove to be significantly incorrect. (3)
Oracle itself is the final authority on the capabilities on the Oracle
product line.
Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.
iggy_fe...@my-deja.com
the Oracle documentation. Here are the results.
At Time T1, Transaction A placed a read lock on the page containing the
parent record.
At Time T2, Transaction B placed read locks on all pages scanned in the
process of determining whether child rows existed.
At Time T3, Transaction A was prevented from inserting child rows
because of the read locks acquired by Transaction B at Time T2, and was
forced to wait.
At Time T4, Transaction B was prevented from deleting the parent record
because of the read lock acquired by Transaction A at Time T1 and was
forced to wait. This resulted in a deadlock between Transaction A and
Transaction B. Ingres detected the deadlock by rolling back one of the
transactions. Only one transaction succeeded.
Oracle's position apparently is that the SQL92 standard defines
transaction isolation levels IN TERMS OF three phenomena that must be
prevented between concurrently executing transactions. The standard
states that the exclusion of these phenomena for transactions executing
at isolation level serializable is A CONSEQUENCE OF the requirement
that such transactions be serializable.
--- begin excerpts from "Oracle8 Server Concepts" ---
"The ANSI/ISO SQL standard (SQL92) defines four levels of transaction
isolation with differing degrees of impact on transaction processing
throughput. These isolation levels are defined in terms of three
phenomena that must be prevented between concurrently executing
transactions."
"Serializable transactions see only those changes that were committed
at the time the transaction began, plus those changes made by the
transaction itself through INSERT, UPDATE, and DELETE statements.
Serializable transactions do not experience nonrepeatable reads or
phantoms."
"Although Oracle serializable mode is compatible with SQL92 and offers
many benefits compared with read-locking implementations, it does not
provide semantics identical to such systems. Application designers must
take into account the fact that reads in Oracle do not block writes as
they do in other systems. Transactions that check for database
consistency at the application level may require coding techniques such
as the use of SELECT FOR UPDATE. This issue should be considered when
applications using serializable mode are ported to Oracle from other
environments."
--- end excerpts ---
--- begin excerpt from SQL92 standard ---
The execution of concurrent SQL-transactions at isolation level
SERIALIZABLE is guaranteed to be serializable. A serializable execution
is defined to be an execution of the operations of concurrently
executing SQL-transactions that produces the same effect as some serial
execution of those same SQL-transactions. A serial execution is one in
which each SQL-transaction executes to completion before the next SQL-
transaction begins.
The isolation level specifies the kind of phenomena that can occur
during the execution of concurrent SQL-transactions. The following
phenomena are possible:
1) P1 ("Dirty read"): SQL-transaction T1 modifies a row. SQL-
transaction T2 then reads that row before T1 performs a COMMIT. If T1
then performs a ROLLBACK, T2 will have read a row that was never
committed and that may thus be considered to have never existed.
2) P2 ("Non-repeatable read"): SQL-transaction T1 reads a row. SQL-
transaction T2 then modifies or deletes that row and performs a COMMIT.
If T1 then attempts to reread the row, it may receive the modified
value or discover that the row has been deleted.
3) P3 ("Phantom"): SQL-transaction T1 reads the set of rows N that
satisfy some <search condition>. SQL-transaction T2 then executes SQL-
statements that generate one or more rows that satisfy the <search
condition> used by SQL-transaction T1. If SQL-transaction T1 then
repeats the initial read with the same <search condition>, it obtains a
different collection of rows.
Level P1 P2 P3
READ UNCOMMITTED Possible Possible Possible
READ COMMITTED Not Possible Possible Possible
REPEATABLE READ Not Possible Not Possible Possible
SERIALIABLE Not Possible Not Possible Not Possible
Note: The exclusion of these phenomena for SQL-transactions executing
at isolation level SERIALIZABLE is a consequence of the requirement
that such transactions be serializable.
--- end excerpt ---
The full text of the standard is available at
"http://sunsite.doc.ic.ac.uk/pub/computing/programming/languages/perl/db
/refinfo/sql2/sql1992.txt".
Disclaimers: (1) My employer may have opinions very different from
mine. (2) My opinions may prove to be significantly incorrect. (3)
Oracle itself is the final authority on the capabilities of the Oracle
Jonathan Lewis
the word 'some' in the phrase
should produce the same results as "some" serially
executed sequence of those same transactions.
The 'obvious' sequence to choose is the sequence
in which the transactions started, and this is what
Oracle does.
Since Oracle does not lock rows for reading,
and since it has a read-consistency model,
it is relatively easy to implement serializable
transactions in that
Data which has been changed by another transaction
can always be restored to the state it was in when
my transaction started if I only want to read it.
Data which I want to change, but which has been changed
by another transaction since I started my transaction
will cause my transaction to fail.
Not all transactions are serialisable. Setting the transaction
to serialisable means only that if it can be made serialisable
it will succeed and if it can't be made serialisable it will fail.
Regarding your success with Ingres, and having just seen
your follow-up post, the issue is merely a question of what
you are used to and consider normal' (no pun intended).
Oracle does not take out read locks, but it seems that
Ingres does so automatically. You execution of part 4
did not match the test described by Oracle, it deadlocked
and rolled back.
The manual does point out that the anomaly (which occurs
only if readers do not block writers) can be avoided by
acquiring 'explicit read locks' i.e. select for update, thus
emulating the Ingres behaviour to some extent, and then coding
the application correctly to cater for the potential of collision.
--
Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk
Vadim Tropashko
http://www.acm.org/pubs/citations/proceedings/mod/223784/p1-berenson/
It mentiones "Oracle Read Consistency" as well.
iggy_fe...@my-deja.com wrote:
> It is intuitively obvious that serial (sequential) execution of
> transactions is, in some sense, a "safe mode" of operation that
> guarantees data consistency. It is not at all intuitively obvious that
> concurrent execution of transactions is an equally safe mode of
> operation. The SQL92 standard provides for the "SET TRANSACTION
> ISOLATION LEVEL SERIALIZABLE" command which gives the user the ability
> to specify that the concurrent execution of transactions should produce
> the same results as "some" serially executed sequence of those same
> transactions.
>
Vadim Tropashko
David Cressey
8.
They are with reference to Oracle RDB, which used to be DEC RDB.
>(1) The author mentions a "greater degree of transaction isolation than
>defined by SQL92 SERIALIZABLE mode". Has Oracle correctly interpreted
>the SQL92 definition of serializibility?
I think so. I've seen this in DEC RDB literature some years ago.
>(2) "Readers" do not block "writers" in an Oracle database. Is it
>possible to guarantee serializability without requiring readers to
>block writers?
>
Yes, by the use of what I will call "virtual snapshots". This means that
the before image of every write (inserts, deletes, and updates) must be
maintained somewhere, so long as there is any reader who ought to be able
to "see" that before image. Read processes need to detect versions of data
that are newer than their snapshot, and the DBMS must retrieve the correct
version from the snapshot. Of course, this should be transparent
to all the processes.
Warning: My use of the word "snapshot" is not consistent with the usage of
that word in Oracle RDBMS
documentation. It is consistent with the use of that word in Oracle RDB
documentation, which is
different.
>(3) Oracle only places locks on rows that satisfy query criteria and
>does not place read locks on pages that were scanned in the process of
>executing a query. Is it possible to guarantee serializability without
>placing read locks on all such pages?
Oracle RDB places locks on pages, but these locks are not under control of
the user, unlike row locks. That is, they are not held to the end of the
transaction. It is the responsibility of the engine to synchronize
access to pages, such that there are no buried updates to pages, in the
background. This increases overhead, but it also increases concurrency.
The overall effect on responsiveness is often positive.
>(4) I was able to perform the referential integrity experiments against
>an Ingres 6.4/06 database without experiencing the anomalies described
>in the Oracle documentation. Is Oracle alone in its interpretation of
>the SQL92 defininition of serializability?
>
I don't think so. How did you manage to guarantee concurrency of the events
that would have created the buried updates? I've always found it very
difficult to arrange concurrency type tests.
Regards,
Dave
David Cressey
which transactions ENDED.
That is, the timestamp on the COMMIT. This is harder to implement,
because if you assign a
unique and sequential transaction number to every transaction at its start,
those transaction numbers won't give you the order I'm suggesting.
There are some interesting consequences to choosing this order.
Transaction that ended in a ROLLBACK, and READ ONLY transactions represent
special cases.
You don't care about them with regard to final state of the database, buit
if you wanted to
guarantee that such transactions, if repeated in serial mode would RECEIVE
the same data,
now you've got something else to think about. Why would anybody care?
Well, after all, this is the database THEORY forum, so maybe there's a
theoretical interest somewhere.
Regards,
Dave Cressey
Jonathan Lewis wrote in message
<928412631.17530.1...@news.demon.co.uk>...
Michael Ortega-Binderberger
> I have answers to your questions, but they are not with reference to Oracle
> 8.
> They are with reference to Oracle RDB, which used to be DEC RDB.
>
> >(1) The author mentions a "greater degree of transaction isolation than
> >defined by SQL92 SERIALIZABLE mode". Has Oracle correctly interpreted
> >the SQL92 definition of serializibility?
>
> I think so. I've seen this in DEC RDB literature some years ago.
I disagree. The fact that as the original mail suggests, you can
construct and example where it breaks down, means that Oracles
serializability is not what is commonly assumed.
> >(2) "Readers" do not block "writers" in an Oracle database. Is it
> >possible to guarantee serializability without requiring readers to
> >block writers?
> >
>
> Yes, by the use of what I will call "virtual snapshots". This means that
> the before image of every write (inserts, deletes, and updates) must be
> maintained somewhere, so long as there is any reader who ought to be able
> to "see" that before image. Read processes need to detect versions of data
> that are newer than their snapshot, and the DBMS must retrieve the correct
> version from the snapshot. Of course, this should be transparent
> to all the processes.
>
> Warning: My use of the word "snapshot" is not consistent with the usage of
> that word in Oracle RDBMS
> documentation. It is consistent with the use of that word in Oracle RDB
> documentation, which is
> different.
Really called shadowing. However extremly error prone to implement,
and usually buys you nothing in speed. I have no knowledge if oracle
is using this but I would almost lean on the no side.
> >(3) Oracle only places locks on rows that satisfy query criteria and
> >does not place read locks on pages that were scanned in the process of
> >executing a query. Is it possible to guarantee serializability without
> >placing read locks on all such pages?
>
> Oracle RDB places locks on pages, but these locks are not under control of
> the user, unlike row locks. That is, they are not held to the end of the
> transaction. It is the responsibility of the engine to synchronize
> access to pages, such that there are no buried updates to pages, in the
> background. This increases overhead, but it also increases concurrency.
> The overall effect on responsiveness is often positive.
>
Row locks are still not under user control (at least should not).
As to when locks can safely be released, this depends on the
characteristics of the schedule you come up with. You can get away
with releasing locks earlier if you want to sacrifice some
properties such as ACA. In general I believe this is not a good
idea, although for performance reasons this could be done and would
work best in a mostly read environment I believe.
How do you say that inclreases overhead and also concurrency?
This isn't clear to me.
> >(4) I was able to perform the referential integrity experiments against
> >an Ingres 6.4/06 database without experiencing the anomalies described
> >in the Oracle documentation. Is Oracle alone in its interpretation of
> >the SQL92 defininition of serializability?
> >
>
> I don't think so. How did you manage to guarantee concurrency of the events
> that would have created the buried updates? I've always found it very
> difficult to arrange concurrency type tests.
Yes, its hard to make these tests, but apparently Ingres does a
proper job of ensuring serializability, which should make
constructing such an example impossible.
Michael
>
>
> Regards,
> Dave
>
>
>
>
---
Michael Ortega-Binderberger mi...@acm.org, mi...@ics.uci.edu
Department of Computer Science Office (949)824-7231, Fax (949)824-4056
U. of Illinois, Urbana Champaign http://www.ics.uci.edu/~miki
On loan to University of California, Irvine ICQ: 25412074
"Any addition to the truth is a subtraction from it."
Alexander Solzhenitsyn
Hal Berenson
updates (while Oracle does) inside a snapshot transaction precisely because
such a scheme does not fully support the notion of serializability. There
has been a large amount of debate about this ever since the ANSI SQL
committee incorrectly described Serializability as part of the SQL92
standard. The error crept into the standard as a result of trying to
restate the requirements for serializability to not require pessimistic
locking. This is a complex topic that was the subject of a paper at the
1995 SIGMOD. Pat O'Neil and I initiated writing this paper, and very
significantly the co-authors included Jim Gray (who authored the original
paper describing serializability and isolation levels), Phil Bernstein (who
has written more on this topic than any other author, and particularly about
optimistic techniques such as snapshots), and Jim Melton (who wrote the
description of isolation levels that is in SQL92).
You can find the paper (A Critique of ANSI SQL Isolation Levels) in the
proceedings of SIGMOD '95 or at
http://research.microsoft.com/scripts/pubDB/pubsasp.asp?RecordID=5
--
All opinions expressed here are mine and mine alone
David Cressey <dcre...@valinet.com> wrote in message
news:mc963.922$We5....@news2.giganews.com...
> I have answers to your questions, but they are not with reference to
Oracle
> 8.
> They are with reference to Oracle RDB, which used to be DEC RDB.
>
> >(1) The author mentions a "greater degree of transaction isolation than
> >defined by SQL92 SERIALIZABLE mode". Has Oracle correctly interpreted
> >the SQL92 definition of serializibility?
>
> I think so. I've seen this in DEC RDB literature some years ago.
>
>
> >(2) "Readers" do not block "writers" in an Oracle database. Is it
> >possible to guarantee serializability without requiring readers to
> >block writers?
> >
>
> Yes, by the use of what I will call "virtual snapshots". This means that
> the before image of every write (inserts, deletes, and updates) must be
> maintained somewhere, so long as there is any reader who ought to be able
> to "see" that before image. Read processes need to detect versions of
data
> that are newer than their snapshot, and the DBMS must retrieve the correct
> version from the snapshot. Of course, this should be transparent
> to all the processes.
>
> Warning: My use of the word "snapshot" is not consistent with the usage
of
> that word in Oracle RDBMS
> documentation. It is consistent with the use of that word in Oracle RDB
> documentation, which is
> different.
>
>
> >(3) Oracle only places locks on rows that satisfy query criteria and
> >does not place read locks on pages that were scanned in the process of
> >executing a query. Is it possible to guarantee serializability without
> >placing read locks on all such pages?
>
>
> Oracle RDB places locks on pages, but these locks are not under control
of
> the user, unlike row locks. That is, they are not held to the end of the
> transaction. It is the responsibility of the engine to synchronize
> access to pages, such that there are no buried updates to pages, in the
> background. This increases overhead, but it also increases concurrency.
> The overall effect on responsiveness is often positive.
>
>
>
> >(4) I was able to perform the referential integrity experiments against
> >an Ingres 6.4/06 database without experiencing the anomalies described
> >in the Oracle documentation. Is Oracle alone in its interpretation of
> >the SQL92 defininition of serializability?
> >
>
> I don't think so. How did you manage to guarantee concurrency of the
events
> that would have created the buried updates? I've always found it very
> difficult to arrange concurrency type tests.
>
>
> Regards,
> Dave
>
>
Jonathan Lewis
The example simply has:
transaction A inserts a (child) row
transaction B deletes a (parent) row
Arguably the fact that there is a business rule that state:
child rows should not exist unless the parent row exists
has nothing to do with serialisable transactions.
If we change transaction A to :
lock the parent row and insert a child row if the parent is locked
and transaction B to:
delete the parent row if no children exist
then we have two transactions which are inherently
not serialisable and the discussion becomes pointless.
I believe the point of this particular section of the manual
was simply to demonstrate that serialisable transactions
do not, BY THEMSELVES, guarantee referential integrity.
--
Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk
Michael Ortega-Binderberger wrote in message ...
David Cressey
the SQL92 definition of serializability, not that Oracle's implementation
agrees with what is commonly assumed about serializability.
More imortantly, as Hal Berenson's reply suggests, the SQL92
interpretation
is not a good criterion for an implementation that is free from flaws. I
would add, that what is commonly assumed about serializability, at least
in my working environment, would form an even worse criterion for
assessing serializability.
Regards,
Dave Cressey
David Cressey
Rdb
function the same way. If anyone was likely to infer that from my remarks,
your explicit statment to the contrary should help set them straight.
With regard to Oracle and snapshot transactions, I'm not following
you. What the equivalent in Oracle of an Rdb snapshot transaction?
I understand Rdb snapshots, but I don't see a similar construct in
Oracle.
With regard to the substance of the paper you mentioned, I'm not
going to ask for a summary here. I'll go off and read the paper
itself, as time permits.
I agree with your statement that it's a complex subject. I think a
lot of people underestimate the complexity.
Regards,
Dave Cressey
Hal Berenson wrote in message ...
David Cressey
the user" I meant that a user process event, namely the
transaction's end, caused the lock's release.
The user's process doesn't control the lock, but the user
process can delay the end of the transaction indefinitely.
Not so with page locks. The right to insert a new record
into a page is transferred between various processes
that are all Rdb, including Rdb processing in the context
of the user's process.
These processes can all signal each other, and respond
to asynchronous traps, in a way that lets them share the
page in a disciplined way, without depending on the completion
of a user's code.
Michael Ortega-Binderberger wrote in message ...
iggy_fe...@my-deja.com
definition of serializability"
Here is an extract from the paper referenced by Vadim Tropashko and Hal
Berenson who have been contributing to this thread. The paper was
published in the proceedings of SIGMOD 95 and is called "A Critique of
ANSI SQL Isolation Levels". Hal Berenson is one of the authors of the
paper, the others being Phil Bernstein of Microsoft, Jim Gray of U.C.
Berkeley, Jim Melton of Sybase, Elizabeth O'Neil of UMass/Boston and
Patrick O'Neil also of UMass/Boston. The comments made by these
researchers lead to the conclusion that the proposition (that Oracle
has correctly interpreted the SQL92 definition of serializability) is
false.
--- begin extract ---
ANSI SQL defines four levels of isolation by the matrix of Table 1.
Each isolation level is characterized by the phenomena that a
transaction is forbidden to experience (loose or strict
interpretations). However the ANSI SQL specifications do not define the
SERIALIZABLE isolation level solely in terms of these phenomena.
Subclause 4.28, "SQL-transactions", in [ANSI] notes that the
SERIALIZABLE isolation level must provide what is "commonly known as
fully serializable execution." The prominence of the table compared to
this extra proviso leads to a common misconception that disallowing the
three phenomena implies serializability.
--- end extract ---
Disclaimers: (1) My employer may have opinions very different from
mine. (2) My opinions may prove to be significantly incorrect. (3)
Oracle itself is the final authority on the capabilities of the Oracle
product line.
In article <Yxr83.7714$G01.1...@news3.giganews.com>,
"David Cressey" <dcre...@valinet.com> wrote:
> I was agreeing with the proposition that Oracle has correctly
interpreted
> the SQL92 definition of serializability, not that Oracle's
implementation
> agrees with what is commonly assumed about serializability.
>
> More imortantly, as Hal Berenson's reply suggests, the SQL92
> interpretation
> is not a good criterion for an implementation that is free from
flaws. I
> would add, that what is commonly assumed about serializability, at
least
> in my working environment, would form an even worse criterion for
> assessing serializability.
>
> Regards,
> Dave Cressey
>
> Michael Ortega-Binderberger wrote in message ...
>
> >I disagree. The fact that as the original mail suggests, you can
> >construct and example where it breaks down, means that Oracles
> >serializability is not what is commonly assumed.
>
>
iggy_fe...@my-deja.com
More accurately, we have a "non-serial" (i.e. interleaved) history that
is not "serializable", i.e. does not produce the same results as some
"serial" (i.e non-interleaved) history of the transactions involved. A
requirement of the SQL92 standard and the challenge before Oracle and
all other database vendors (relational or otherwise), is the prevention
of all non-serial tranaction histories that are not serializable. The
standard does not require the application developer to use any special
manual techniques to guarantee serializability of non-serial
transaction histories.
RE: "serialisable transactions do not, BY THEMSELVES, guarantee
referential integrity"
Serializability (of non-serial transaction histories) only provides the
(important) guarantee that the application developer's intentions have
been fulfilled (in the sense that each non-serial transaction history
is required to produce the same results as some serial history of the
transactions involved). To the extent that the database engine performs
integrity checks automatically (referential integrity checks or checks
of other types), these checks may be considered an integral part of the
application developer's source code for the purposes of this discussion.
Here is another example of a non-serial transaction history which is
non-serializable but which the Oracle engine cannot prevent. We might
term this the "airline overbooking example".
--- begin example ---
The "airline overbooking" example:
(The business rule which the application developer in this example is
trying to enforce, is a rule that states that "only 100 passenger
reservations may be accepted for a flight")
At Time T1, Transaction A performs the query "select count(*) from
passenger_reservations where flight_number=123 and
flight_time='2000/01/01'". Assume that the result is 99. Transaction A
then assumes that it may book an additional passenger on the indicated
flight.
At Time T2, Transaction B performs the same query and obtains the same
result. Transaction B also assumes that it may book an additional
passenger on the indicated flight.
At Time T3, Transaction A books an additional passenger on the
indicated flight, raising the total number of passenger reservations
for this flight to 100.
At Time T4, Transaction B books an additional passenger on the
indicated flight, raising the total number of passenger reservations
for this flight to 101. This violates the business rule ("only 100
passenger reservations may be accepted for a flight") which the
application developer's source code was trying to enforce by checking
the total number of prior reservations before making an additional
reservation.
At Time T5, Transaction A commits its work.
At Time T6, Transaction B commits its work.
--- end example ---
Disclaimers: (1) My employer may have opinions very different from
mine. (2) My opinions may prove to be significantly incorrect. (3)
Oracle itself is the final authority on the capabilities of the Oracle
product line.
In article <928961624.26673.1...@news.demon.co.uk>,
"Jonathan Lewis" <jona...@jlcomp.demon.co.uk> wrote:
> There is an interesting philosophical question there.
>
> The example simply has:
> transaction A inserts a (child) row
> transaction B deletes a (parent) row
>
> Arguably the fact that there is a business rule that state:
> child rows should not exist unless the parent row exists
> has nothing to do with serialisable transactions.
>
> If we change transaction A to :
> lock the parent row and insert a child row if the parent is locked
> and transaction B to:
> delete the parent row if no children exist
>
> then we have two transactions which are inherently
> not serialisable and the discussion becomes pointless.
>
> I believe the point of this particular section of the manual
> was simply to demonstrate that serialisable transactions
> do not, BY THEMSELVES, guarantee referential integrity.
>
> --
>
> Jonathan Lewis
> Yet another Oracle-related web site: www.jlcomp.demon.co.uk
>
Jonathan Lewis
definition of serializability"
> Subclause 4.28, "SQL-transactions", in [ANSI] notes that the
> SERIALIZABLE isolation level must provide what is "commonly known as
> fully serializable execution."
Would it be possible to extract from 4.28 the strict definition of
"fully serializable execution" so that we can all see it please.
Interesting point: the Abstract to the paper by
Berenson, Hal ; Bernstein, Phil ; Gray, Jim ;
Melton, Jim ; O'Neil, Elizabeth ; O'Neil, Patrick,
dated June 1995 reads:
ANSI SQL-92 [MS, ANSI] defines Isolation Levels in terms of
phenomena:
Dirty Reads, Non-Repeatable Reads, and Phantoms.
I haven't been able to download the paper yet so I presume from
your extract that this was the editor's interpretation not the
authors' statement.
A side-issue on your comment about application code
and database engine
> To the extent that the database engine performs
> integrity checks automatically (referential integrity checks or checks
> of other types), these checks may be considered an integral part of the
> application developer's source code for the purposes of this discussion.
Application code would have to obey the three denial rules,
so could not be used to do pre-emptive checks of business
rules such as the 100-seat limit on the aircraft.
On the other hand, a database constraint (or commit-time
trigger perhaps) could be allowed to be allowed to cheat
and check for e.g. uncommitted rows (which is how Oracle
caters for one of the problems of parent/child RI constraints).
So there is likely to be a significant difference between
application level code and embedded database code.
Finally a thought experiment -
Table X holds 20,000,000 rows
Time T1 Tx A inserts one row
Time T2 Tx B inserts one row
Time T3 Tx A selects count(*) from X - answer 20,000,001
Time T4 Tx B selects count(*) from X - answer 20,000,001
Time T5 Tx A commits
Time T6 Tx B commits
This is a very simple example of an interleaved history
which would not return the same result if the two
transactions were re-arranged to operate serially
(as far as Oracle was concerned)
The way I see it, the only thing you can do in this case
is to ensure that transaction B cannot insert a row until
transaction A has committed - i.e. make the interleaved
sequence one that cannot occur. Which tends to suggest
that all sorts of transactions can be serialisable only if
they actually are serialised: a loss of concurrency
that most database users would not be too happy with.
I must say I would be interested in downloading the
paper - any chance you could email it to me ?
Thanks.
iggy_fe...@my-deja.com
Here's what I have. I am referring to the copy of the SQL92 standard that I
found at the address
"http://sunsite.doc.ic.ac.uk/pub/computing/programming/languages/perl/db/refi
nfo/sql2/sql1992.txt". The revision date on this document is July 1992.
Please also refer to my postings of June 1 for a longer excerpt.
--- begin extract ---
The execution of concurrent SQL-transactions at isolation level SERIALIZABLE
is guaranteed to be serializable. A serializable execution is defined to be
an execution of the operations of concurrently executing SQL-transactions
that produces the same effect as some serial execution of those same
SQL-transactions. A serial execution is one in which each SQL-transaction
executes to completion before the next SQL-transaction begins.
--- end excerpt ---
The first sentence in the above paragraph is "legalese" that equates the use
of the word "serializable" (in the context of isolation levels) with the
rigourous definition that immediately follows. The standard goes on to define
three anomalies that transactions executing at various isolation levels may
(and may not) experience and concludes this characterization of isolation
levels with the following remark.
--- begin excerpt ---
Note: The exclusion of these phenomena for SQL-transactions executing at
isolation level SERIALIZABLE is a consequence of the requirement that such
transactions be serializable.
--- end excerpt ---
Note in particular, the use of the word "consequence".
Here are the four questions I asked originally.
(1) The Oracle8 concepts manual mentions a "greater degree of transaction
isolation than defined by SQL92 SERIALIZABLE mode". Has Oracle correctly
interpreted the SQL92 definition of serializibility? (2) "Readers" do not
block "writers" in an Oracle database. Is it possible to guarantee
serializability without requiring readers to block writers? (3) Oracle only
places locks on rows that satisfy query criteria and does not place read
locks on pages that were scanned in the process of executing a query. Is it
possible to guarantee serializability without placing read locks on all such
pages? (4) I was able to perform the referential integrity experiments
against an Ingres 6.4/06 database without experiencing the anomalies
described in the Oracle documentation. Is Oracle alone in its interpretation
of the SQL92 definition of serializability?
I now believe that the short answers to the first three questions are No, No
and No. The answer to the second and third questions are found in the
"fundamental serialization theorem" (also discussed in the paper by Berenson
et al) which lists "predicate" locks among the necessary and sufficient
conditions for serializability. (A predicate lock is a lock on the entire
universe of objects that satisfy the query conditions (predicates), not just
those physically present in the database.) I have posted a request on other
newsgroups for information on the behavior of database engines other then
Oracle, when they encounter non-serial transaction histories that cannot be
serialized. I will update this thread with detailed answers to the above
questions once I have more information.
RE: Research paper by Berenson et al
Try http://research.microsoft.com/~gray/isolation.doc. This is in MS-Word
format and the server response is above average.
Disclaimers: (1) My employer may have opinions very different from mine. (2)
My opinions may prove to be significantly incorrect. (3) Oracle itself is the
final authority on the capabilities of the Oracle product line.
In article <929284005.7896.0...@news.demon.co.uk>,
"Jonathan Lewis" <jona...@jlcomp.demon.co.uk> wrote:
> RE: "the proposition that Oracle has correctly interpreted the SQL92
> definition of serializability"
>
> > Subclause 4.28, "SQL-transactions", in [ANSI] notes that the
> > SERIALIZABLE isolation level must provide what is "commonly known as
> > fully serializable execution."
>
> Would it be possible to extract from 4.28 the strict definition of
> "fully serializable execution" so that we can all see it please.
>
> Interesting point: the Abstract to the paper by
> Berenson, Hal ; Bernstein, Phil ; Gray, Jim ;
> Melton, Jim ; O'Neil, Elizabeth ; O'Neil, Patrick,
> dated June 1995 reads:
>
> ANSI SQL-92 [MS, ANSI] defines Isolation Levels in terms of
> phenomena:
> Dirty Reads, Non-Repeatable Reads, and Phantoms.
>
> I haven't been able to download the paper yet so I presume from
> your extract that this was the editor's interpretation not the
> authors' statement.
>
> A side-issue on your comment about application code
> and database engine
>
> > To the extent that the database engine performs
> > integrity checks automatically (referential integrity checks or checks
> > of other types), these checks may be considered an integral part of the
> > application developer's source code for the purposes of this discussion.
>
> --
>
> Jonathan Lewis
> Yet another Oracle-related web site: www.jlcomp.demon.co.uk
>
>
Vadim Tropashko
I don't see how your airline booking example works. In time domain
addressing aproach (i believe it's the same thing as "snapshot" isolation
approach taken by Oracle since release 7.3) the database record history is
divided into time intervals:
<98,[t0,t1)>, <99,[t1,*)>
In other words, at time t0 passenger count has been updated to 98, and
later, at time t1 the most recent update - count=99 - has been made. That is
the current value.
Each transaction is assigned a time at which it begins. In your example,
transaction A at time t2 (excuse me for shifting your timeline by 2:-)
modifies the history like this:
<98,[t0,t1)>, <99,[t1,t2)>, <99,[t2,*)>
As you see, even though the count value didn't change, the history is
extended to the present.
Next transaction B reads the count and, therefore, modifies history into:
<98,[t0,t1)>, <99,[t1,t2)>, <99,[t2,t3)>, <99,[t3,*)>
Now transaction A runs in the past and cannot update the count (while B
still can!).
IMHO the difference between Snapshot Isolation and Locking (which "gang of
six" seems proved to exist) is much smaller than the effect you describe.
Here is a relevant extract from
"http://www.cs.umb.edu/~isotest/isotest.ps":
----------------- begin ------------------
A much more difficult question arose when ORACLE was gearing up to use
Snapshot Isolation
(called SERIALIZABLE Isolation in Oracle 7.3 [OR95, OR7.3]) in the TPCC
benchmark [TPCC].
The TPCC auditors Tom Sawyer and Francois Raab both communicated to us
their thoughts on
being asked to validate the Snapshot Isolation would leave no Isolation
holes in the TPCC trans
action profiles. There are several different transaction types present in
TPCC (NewOrder
Transaction, Payment Transaction, OrderStatus Transaction, etc.). The
benchmark Isolation
requirement for running TPCC does not require serializability of all
possible transactions, but
only of the transactions present in TPCC. This is exactly the type of
problem the current pro
posal hopes to address: would the lower isolation level (Snapshot Isolation)
provide serializable
behavior for the TPCC application? And what method was used to guarantee
this? Both Tom
Sawyer and Francois Raab indicated verbally that they, ". . . thought about
it really hard for a long
time (days), and ended up confident that there was no isolation problem." In
other words, there
was no technique known to them whereby this result could be demonstrated in
a rigorous fashion.
---------------- end -----------------
Best Regards,
Vadim
Vadim Tropashko
after A and B both have read the data, optimistic method (first-committer-wins)
is applied. A would be allowed to write the change, and B would not. Then one of
two scenarious is possible: B could either be aborted, or restarted with new
timestamp -- could anybody clarify on that, please?
Jonathan Lewis
If there is an interleaved history then the
history can be unwoven to produce two
non-interleaved transactions which (in at
least one order) produce exactly the same
result
then I have to agree that Oracle does not
implement serializable transactions simply
by its command
set isolation level serializable;
I am very interested by the description of
'predicate locks' being one of the necessary
conditions for serialisable transactions - this
answers my 'logical if not physical' question
about Ingres locking pages that do not exist
when it reads currently existing rows that
match a query.
Can I assume also that not only do readers need
to block writers, but writers need to block readers ?
It tends to suggest, of course, that the only
possible implementation of serialisability is
one which serialises all transactions ;)
Jonathan Lewis
>Try http://research.microsoft.com/~gray/isolation.doc. This is in MS-Word
>format and the server response is above average.
Jens Loebling
unforunetly I don't have the article starting this discussion on my news server
so I cant answer directly.
Maybe the following link helps
http://www.csee.umbc.edu/help/oracle8/server803/A54643_01/ch_intro.htm#3932
Best Regards
iggy_fe...@my-deja.com schrieb:
Vadim Tropashko
Isolation and Serializability in SQL92 and Oracle7", July 95, Part No.A33745
discussing these isuues in great detail, while documentation is more like a
summary. Unfortunately, I cannot locate it there any more:-(.
David Cressey
Thanks for posting the excerpt from the SQL-92 paper.
It would appear from the excerpt as though there were no greater level
of serializability possible than the one given in the definition in the
SQL-92
standard.
Now the question becomes whether "transaction isolation" and
"serializability"
refer to the same thing. I think they do not. Obviously, transactions
that are
not serializable with respect to each other may not be considered isolated
from each other.
But the question remains whether a transaction must necessarily block
another
transaction in order to guarantee serializability.
When I first read "readers do not block writers" I read that as meaning
"read only transactions do not block read write transactions" . I didn't
realize
that others would read it as "reads in the context of a read write
transaction
do not block writers".
Reads executed in the context of a read write transaction must lock writers.
Reads executed in the context of a read only transaction need not block
writers,
provided that the read only transaction may be given a consistent view of
the database, by retrieving the value of any data item read as of the
point in time when the read only transaction started.
This is what is done in the case of what DEC RDB called a "snapshot
transaction"
DEC RDB version 1 (sometime around 1982) had snapshot transactions.
There are other issues that have been raised in this thread. I may get to
some of them
in a different message.
(In 1994, DEC sold DEC RDB to Oracle, which renamed it "Oracle RDB". I am
sorry
if this causes any confusion with Oracle RDBMS, which most people know as
"Oracle")
Regards,
Dave
iggy_fe...@my-deja.com wrote in message <7k37ol$rp7$1...@nnrp1.deja.com>...
>RE: Definition of Serializability in Subclause 4.28 of the SQL92 standard.
>
>Here's what I have. I am referring to the copy of the SQL92 standard that I
>found at the address
>"http://sunsite.doc.ic.ac.uk/pub/computing/programming/languages/perl/db/re
fi
<snip>
David Cressey
the thread has pretty much played itself out. Please forgive me
if what follows is beating a dead horse.
I looked up in the Oracle Concepts (Release 8.0) Manual, and
here's what I found under "Preventable Phenomena and Transaction
Isolation Levels"
Quote:
"The ANSI/ISO SQL standard (SQL92) defines four levels of transaction
isolation with differing degrees of impact on transaction processing
throughput. These isolation levels are defined in terms of three
phenomena that must be prevented between concurrently executing
transactions."
It then goes on to outline "dirty read", "nonrepeatable (fuzzy) read"
and "phantom read".
Hre's my question about the SQL92 standard: Did the standard
include the three phenomena as being illustrative or definitive?
Also, consider the following scenario, which is a minor
variation from one previously given in this thread:
user A> set transaction read write;
user B> set transaction read write;
user A> select count(*) from passengers;
user B> select count(*) from passengers;
user A> insert into passengers values (...);
user B> insert into passengers values (...);
user A> commit;
user B> commit;
It's clear that the above is not serializable, because
A and B get the same count in interleaved execution,
while in serial execution they get different counts.
Here's what happens under Oracle 8: It goes through.
No apparent problems. It can't be repeated in serial
fashion, though.
Here's what happens under RDB (v6.1):
user A> set transaction read write;
user B> set transaction read write;
user A> select count(*) from passengers; (process blocks)
user B> select count(*) from passengers;
(short pause, then DEADLOCK error message appears.
user B> rollback;
user A> insert into passengers values (...);
user A> commit;
now user B can try again. If successful, the operations
have been serialized. The failed transaction is, of
course not serializable. I expect that failed transactions
are outside the scope of the definition of serializability.
It's clear that the two systems behave differently. It's
clear to me that one of them is wrong.
I'm still unclear as to whether Oracle has misinterpreted
the SQL-92 standard concerning serializability. It's
less important to me than whether their implementation
has a flaw that needs to be compensated for in certain
circumstances.
David Cressey mailto:Da...@dcressey.com
David Cressey wrote in message ...
Jonathan Lewis
In terms of SQL and serialisability, yes one of them
is wrong (and having read the documents suggested
by previous posters) I now have to agree that Oracle
does allow non-serialisable transactions when the isolation
level is set to serialisable.
There is a 'work around' If you look you the SERIALIS(Z?)ABLE
init.ora parameter, and set this to TRUE, then Oracle
reverts to version 5 behaviour I think, and as with RDB
you would not be able to insert the second row.
This plays merry hell with performance, though.
Of course, this failing in proper serialisability is a
consequence of the massive performance advantage
you get from Oracle's ability to keep readers and writers
from blocking each other, but it does mean you have
to relay on other mechanisms (e.g. integrity constraints)
to avoid the side-effects of errors due to the example
you quote.
Of course, the DEADLOCK message in RBD really
ought to match Oracle's message - 'Unable to
serialize transaction'.
--
Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk
David Cressey wrote in message ...
>From the absence of further messages on this thread, I'm guessing
>the thread has pretty much played itself out. Please forgive me
>if what follows is beating a dead horse.
>
>
iggy_fe...@my-deja.com
> There is a 'work around' If you look you the SERIALIS(Z?)ABLE
> init.ora parameter, and set this to TRUE, then Oracle
> reverts to version 5 behaviour I think, and as with RDB
> you would not be able to insert the second row.
>
> This plays merry hell with performance, though.
From the Oracle 8.1.5 "README" ...
1.7 SERIALIZABLE=TRUE
-----------------------
The init.ora parameter SERIALIZABLE=TRUE is no longer supported in
Oracle8 and beyond. The default behavior henceforth is as if
SERIALIZABLE was set to FALSE. Use the SET TRANSACTION ISOLATION LEVEL
SERIALIZABLE command to Achieve similar transaction isolation behavior.
> Of course, this failing in proper serialisability is a
> consequence of the massive performance advantage
> you get from Oracle's ability to keep readers and writers
> from blocking each other, but it does mean you have
> to relay on other mechanisms (e.g. integrity constraints)
> to avoid the side-effects of errors due to the example
> you quote.
Don't integrity constraints act simply as extensions of your
application program and hence become subject to the Oracle concurrency
model, profiting from its strengths and constrained by its weaknesses?
Disclaimers: (1) My employer may have opinions very different from
mine. (2) My opinions may prove to be significantly incorrect. (3)
Oracle itself is the final authority on the capabilities on the Oracle
product line.
iggy_fe...@my-deja.com
"David Cressey" <dcre...@valinet.com> wrote:
> From the absence of further messages on this thread, I'm guessing
> the thread has pretty much played itself out. Please forgive me
> if what follows is beating a dead horse.
>
I found some recent postings on the Oracle "Metalink" forums (including
a very interesting example of a non-transaction serializable history
permitted by Oracle) and contributed some comments of my own. In
particular, I have a suggestion about how serializability may be
manually achieved when using Oracle (thanks to Vadim Tropashko for
information on the research being conducted at the University of
Massachussets at Boston).
BTW, all the messages in this discussion thread can be found on
"deja.com" at "http://www.deja.com/%5bST_Tracker%3d0508%
5d/thread/484313584.1".
Disclaimers: (1) My employer(s) may have opinions very different from
mine. (2) My opinions may prove to be significantly incorrect. (3)
Oracle itself is the final authority on the capabilities of the Oracle
product line.
From: Marcin.K...@softax.com.pl
Subject: What does it mean 'serializable'
Posted on: 24-Jun-1999 05:24:19
RDBMS Version: 8.0.5
Error Number (if applicable):
Operating System: VMS
Product: Oracle Server
Product Version: 8.0.5
I performed the following experiment:
CREATE TABLE test (id INTEGER);
Now I run two independent sessions in two separate
o column sqlplus'es. I mark each operation within
first session by (1) and within second session by
(2) (orginally I prepared this message with pretty
two column format but I'm unable to put it into
narrow DevConnect message window).
(1) ALTER SESSION SET ISOLATION_LEVEL=SERIALIZABLE;
... session has been modified
(2) ALTER SESSION SET ISOLATION_LEVEL=SERIALIZABLE;
... session has been modified
(1) INSERT INTO test VALUES(1);
... 1 record inserted
(2) INSERT INTO test VALUES(2);
... 1 record inserted
(1) UPDATE test SET id = 3;
... 1 record modified
(2) UPDATE test SET id = 4;
... 1 record modified
(1) COMMIT
... commit succesfull
(2) COMMIT
... commit succesfull
(1) SELECT * FROM test
id
---
3
4
I have been taught that "serialisable" means
"equivalent to some serial execution". In any
serial execution of this transaction one get either
two records of value 3, either two records of value
4. It is obvious when one notice, that first tx
performs:
INSERT INTO test VALUES(1);
UPDATE test SET id = 3;
COMMIT;
and the second
INSERT INTO test VALUES(2);
UPDATE test SET id = 4;
COMMIT;
So why do you call this "serialisable"? It seems to
me we have here "repeatable read" isolation level.
The problem is not theoretical. We have serious
businness problem due to the fact, that Oracle
INSERT and UPDATE does not lock one another (it is
connected with synchronizing two databases with
help of Digital RTR middleware, which - relying on
database locks - considers two simultaneously
commited transactions to operate on completely
independent data and sometimes reorders them on
shadow database.
Regards
From: Oracle, Helen Schoone
Subject: Re : What does it mean 'serializable'
Posted on: 25-Jun-1999 12:59:20
Hi. Oracle's serializable mode is compatible with
SQL92, but Oracle provides nonblocking queries
(i.e. we do not block writes as may be done in
other systems). All queries in a serializable
transaction see the database as of a single point
in time. This isolation level is suitable where
multiple consistent queries must be issued in a
read-write transaction. Please refer to Chapter 22
under Choosing an Isolation Level in the Oracle8
Server Concepts Guide for further information.
The behavior you show in the 1st example looks like
expected behavior to me regardless of the setting
for isolation level as a non-committed transaction
should only be visible to the current session.
It is not clear what you are portraying in the
second example or your statment "In any serial
execution of this transaction one get either
two records of value 3, either two records of value
4." This contridicts your first example.
Regards,
Helen Schoone
Oracle Server EE Analyst
From: Marcin Kasperski
Subject: Re : Re : What does it mean 'serializable' (part 1)
Posted on: 25-Jun-1999 23:43:26
[ As I got "please limit message to 1500 chars, I
cut my answer into pieces. What the ^&#@&@! is
administering this forum? ]
Hmm, let me recall some definitions:
"serial execution" means executing one transaction
after another - without any concurrency. Only one
transaction can be active on database at given
time. Of course such execution is not practical,
but it is used in theoritical analysis.
"serializable execution" means such method of
concurrent executing of a group of transactions,
that the achieved results are identical to results
of some serial execution of those transactions.
There are two possible methods of serial execution
of the transactions I mentioned previously: tx(1)
and then tx(2) - we got two records of value 4 or
tx(2) and then tx(1) - we got two records of value
3. According to the definition of term
"serializable" any serializable execution of those
two transactions should give one of those two
database states.
From: Marcin Kasperski
Subject: Re : Re : What does it mean 'serializable' (part 2)
Posted on: 25-Jun-1999 23:44:01
The scenario I have shown on the beginning of the
previous letter (real example performed on Oracle8
database) yields to database which contains 3 and
4. No serial execution yields to such results,
therefore that execution scanario has not been
serializable - although I set isolation level to
serializable.
It is controversial, whether "serializable"
isolation level is or is not important (in my
businness case it is!). But please, do not call
"serializable" sth that is not serializable.
Let me also mention, that other your product -
Oracle Rdb database - behaves correctly and
its "serialisable" level really is serialisable.
From: Marcin Kasperski
Subject: Re : Re : What does it mean 'serializable'
Posted on: 25-Jun-1999 23:44:22
You misuse term "non-committed transaction should
only be visible to the current session". Non
commited transaction is in some way visible to
other sessions - blocking them if needed. If you
perform UPDATE on some record, other sessions must
wait. I just expect sth similar in case of INSERT.
Finally: according to my quick analysis, if the
transactions are to be serialisable, INSERT should
block all UPDATE's, which would modify the
INSERT'ed record - similarly, as UPDATE of some
record modify other UPDATES of this record.
Regards
From: Ignatius Fernandez
Subject: Re : Re : What does it mean 'serializable'
Posted on: 05-Jul-1999 18:06:25
Check out the discussion on this issue on the
"comp.databases.oracle.server" newsgroup. All the
messages in that thread can be found on "deja.com"
at "http://www.deja.com/%5bST_Tracker%3d0508%
5d/thread/484313584.1".
The opinion of the contributors to the thread
appears to be that Oracle8 does not guarantee
serializability (the requirement that the
interleaved execution of transactions produce the
same result as some serially executed sequence of
these transactions) and is therefore not SQL92
compliant.
It appears that Oracle does in fact protect
transactions from the three famous anomalies i.e.
"dirty reads", "non-repeatable reads" and
"phantoms". However, serializability theory
requires "predicate locks" i.e. locks on the
entire universe of rows that might satisfy the
query "predicates" (selection criteria), not just
locks on rows found in the database. Apparently,
Oracle does not acquire such locks and hence
cannot guarantee serializability.
The TPC standards apparently do not require the
database engine to guarantee serializability, only
protection from these three anomalies. However, to
the best of my knowledge, the Federal Information
Processing Standards (FIPS) require that the
database engine guarantee serializability.
Disclaimers: (1) My employer may have opinions
very different from mine. (2) My opinions may
prove to be significantly incorrect. (3) Oracle
itself is the final authority on the capabilities
on the Oracle product line.
From: Ignatius Fernandez
Subject: Re : Re : What does it mean 'serializable'
Posted on: 05-Jul-1999 18:10:37
The question that remains unanswered is how
serializability may be manually achieved when
using Oracle. Researchers at the University of
Masachusetts at Boston are attempting to answer
this question. Their latest report may be found at
"http://www.cs.umb.edu/~isotest/feb99report.html".
In my assessment, database triggers and integrity
constraints are not the answer because they act
simply as extensions of your program code. I have
a simple theory that you could implement
serializability by logically partititioning your
Oracle database and require that every transaction
that writes to one of these partitions also update
a database record that controls the affected
partition. In a flight reservation system, you
could require that before or after accepting a new
reservation, the program update the "summary
record" for the flight.
Disclaimers: (1) My employer may have opinions
very different from mine. (2) My opinions may
prove to be significantly incorrect. (3) Oracle
itself is the final authority on the capabilities
on the Oracle product line.
iggy_fe...@my-deja.com
iggy_fe...@my-deja.com wrote:
> a very interesting example of a non-transaction serializable history
A "non-serializable transaction history" not a "non-transaction
serializable history"!
Vadim Tropashko
> The question that remains unanswered is how
> serializability may be manually achieved when
> using Oracle.
This one seems easy -- just lock "the whole" table:
alter session set isolation_level=serializable;
select * from passengers for update;
insert into passengers values 'Z';
commit;
Thransaction above is serializable indeed.
Jonathan Lewis
And this, I believe, is exactly the effect that
appeared in Oracle 5 al the time, and appears
in later versions if you set serializable = true.
(which still seems to be there in 8.1.5 despite
the documentation to the contrary !)
As iggy_fernandez has pointed out, the
Microsoft research paper by
Berenson, Hal ; Bernstein, Phil ; Gray, Jim ;
Melton, Jim ; O'Neil, Elizabeth ; O'Neil, Patrick,
dated June 1995
makes comments to the effect that for
serializability, a database would need to
use 'predicate' locks - i.e. a lock would have
to be aquired on all possible rows that might
be returned by a query EVEN IF they do not
yet exist - so locking the entire table is probably
the quick and easy route, so long as no-one else
wants to use the database.
--
Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk
Vadim Tropashko wrote in message <37826BD6...@yahoo.com>...
>iggy_fe...@my-deja.com wrote:
>
>> The question that remains unanswered is how
>> serializability may be manually achieved when
>> using Oracle.
>
Jonathan Lewis
Not entirely.
Code you write has to follow the rules of read consistency,
uncommitted data etc.
Actions performed by Oracle whilst enforcing declarative
integrity constraints may, internally, choose to break these
rules - consequently there are some side-effects of pk/fk
constraints particularly which can be used to effect
manual serialisability.
I doubt if I would use this method in the case of
your problem of the airline booking with 100 seats
per plane, of course. The trivial solution to that is
to record the number of seats currently booked
as a column on the plane row and write triggers
to maintain that number as seats are booked.
--
Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk
iggy_fe...@my-deja.com wrote in message <7lrn5g$16f$1...@nnrp1.deja.com>...
iggy_fe...@my-deja.com
understanding of serializability and of the strengths and weaknesses of
the Oracle locking implementation. As a closing note, here is a message
from an Oracle analyst on the Oracle Metalink forum, in response to a
posting I made there.
Cheers.
Ignatius.
Disclaimers: (1) My employer(s) may have opinions very different from
mine. (2) My opinions may prove to be significantly incorrect. (3)
Oracle itself is the final authority on the capabilities of the Oracle
product line.
From: Oracle, Helen Schoone
Subject: Re : What does it mean 'serializable'
Posted on: 07 Jul 1999 09:58:14
Hi. I read the thread on deja.com referred to
by Ignatius with interest. Quite a debate. While
I understand your concern regarding how Oracle has
interpreted the SQL92 standard and how Oracle has
implemented this capability, my response in this
thread has been with regards to how it is currently
implemented. This implementation is well documented
in both the Oracle8 Concepts and Application
Developers Guides along with examples and
discussion which identify the limitations.
As a side note, there is an enhancement request
(754915) to re-introduce the capability to specify
SERIALIZABLE at the database level.
Regards,
Helen Schoone
Oracle Server EE Analyst
David Cressey
to everyone who participated. I think I learned a lot, too.
I managed to get some feedback from Joe Celko (incompuserve's caseforum).
Joe was on the SQL92 committee.
He says the three problems (dirty read, fuzzy read, and phantom read) were
intended
to illustrate non-serializability, not to define it. He also said that the
SQL group decided
that, because there was another group defining transaction standards
beyween systems,
it would leave the final standard as to what contstitutes serializability in
the other group's
purview.
The idea is that, if both groups defined a standard for serializability,
and they differed,
that would just weaken the standards as a whole.
I apogize in advance if I've made any mistakes in repeating what Joe said.
This business of "correct"
standards is a whole lot subtler than writing correct code.
Regards,
Dave Cressey mailto:da...@dcressey.com
iggy_fe...@my-deja.com wrote in message <7mbtro$dv2$1...@nnrp1.deja.com>...
Ruud de Koter
You must be joking. This kills both performance and scalability
with a single action. Please insert some indication of sarcasm
or irony when you write something like this....
Regards,
Ruud de Koter
Vadim Tropashko wrote:
>
> iggy_fe...@my-deja.com wrote:
>
> > The question that remains unanswered is how
> > serializability may be manually achieved when
> > using Oracle.
>
> This one seems easy -- just lock "the whole" table:
>
> alter session set isolation_level=serializable;
> select * from passengers for update;
> insert into passengers values 'Z';
> commit;
>
> Thransaction above is serializable indeed.
--
--------------------------------------------------------------------------------------
Ruud de Koter HP OpenView Software Business Unit
Senior Software Engineer IT Service Management Operation
Telephone: +31 (20) 514 15 89 Van Diemenstraat 200
Telefax : +31 (2) 514 15 90 PO Box 831
Telnet : 547 - 1589 1000 AV Amsterdam, the Netherlands
Email : ruud_d...@hp.com
internet: http://www.openview.hp.com/itsm
http://www.openview.hp.com/assetview
intranet: http://ovweb.bbn.hp.com/itservicemanager
--------------------------------------------------------------------------------------
Vadim Tropashko
Original posting compared traditional locking implementation to Oracle.
Lets look into locking implementation first. In the
passenger example the statement
select count(*) from passengers;
puts a read lock on the whole table, right? Oracle implementation would not,
but 'select for update' will put the write lock. Putting write lock seems to be
more restrictive at first, but again, writers do not block readers in Oracle,
therefore, these two cases seems to be equivalent from throughput perspective.
Ruud de Koter wrote:
> Hi Vadim,
>
> You must be joking. This kills both performance and scalability
> with a single action. Please insert some indication of sarcasm
> or irony when you write something like this....
>
> Regards,
>
> Ruud de Koter
>
> Vadim Tropashko wrote:
> >
> > iggy_fe...@my-deja.com wrote:
> >
> > > The question that remains unanswered is how
> > > serializability may be manually achieved when
> > > using Oracle.
> >
Vadim Tropashko
Let have a flights table like this:
CREATE TABLE flight
(
num NUMBER,
seat NUMBER,
...
);
Even though the number of entries in this table is big, the query:
select * from flight
where num = myFlight -- important
for update;
never locks more than 550 rows (B747 capacity:-). Other transactions could still happilly
update all the other flights while the current transaction makes a reservation for
'myFlight'.
Ruud de Koter wrote:
> Hi Vadim,
>
> You must be joking. This kills both performance and scalability
> with a single action. Please insert some indication of sarcasm
> or irony when you write something like this....
>
> Regards,
>
> Ruud de Koter
>
> Vadim Tropashko wrote:
> >
> > iggy_fe...@my-deja.com wrote:
> >
> > > The question that remains unanswered is how
> > > serializability may be manually achieved when
> > > using Oracle.
> >
Vadim Tropashko
doesn't lock anything! Now, suppose 101 reservation transaction come altogether, each reads
flight table, find no reserved fligths, place no lock, therefore, reserves a flight, and,
eventually, commit.
The result would be 101 seats. Of course, if at least one transaction commits before 101th
selects, we
are OK.
Again, predicate locks seems to be the exact answer to the problem, although they are
prohibitevely expensive. Both traditional locking, and snapshot isolation are deficient in
that respect.
Vadim Tropashko wrote:
> > > > The question that remains unanswered is how
> > > > serializability may be manually achieved when
> > > > using Oracle.
> > >