Re: Row locking and serializability

In article <KAnf3.108267$_m4.1357169_at_news2.giganews.com>,   "David Cressey" <dcressey_at_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.Kasperski_at_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.

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Mon Jul 05 1999 - 21:17:03 CDT