Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Avoiding any locks in SQL Servers - read and understand....its magic.

Re: Avoiding any locks in SQL Servers - read and understand....its magic.

From: Christopher Browne <>
Date: 25 Jan 2004 22:59:27 GMT
Message-ID: <bv1hof$n0den$>

Martha Stewart called it a Good Thing when Ed Avis <> wrote:
> "Heikki Tuuri" <> writes:
>>I have understood that the SERIALIZABLE transaction isolation level
>>of PostgreSQL and Oracle is not serializable in the mathematical
>>If we have two instances (say T1 and T2) of the following transaction
>>running concurrently:
>>if SELECT COUNT(*) FROM table1 is < 10 then
>> INSERT INTO table1 VALUES (...);
>>and if table1 originally had 9 rows, then both will insert rows and
>>the table will have 11 rows after that. Thus, an integrity constraint
>>of having at most 10 rows in table1 fails. The execution is not
>>equivalent to any serial execution of T1 and T2, which means that the
>>PostgreSQL and Oracle SERIALIZABLE allows executions which are really
>>not serializable.
> Is there an example of plain SQL (not using 'if' or other PL/SQL
> constructs) which is not serializable in SERIALIZABLE mode?

And note that this scenario involves not one, but TWO misreadings of SQL.

  1. It is treating aggregates as if they were attributes. They are not.
  2. It assumes that there is a serialization problem.

The proper interpretation of the transactions, with time thrown in as a variable, is more or less the following:

  If the number of entries in table 1 when Transaction T1 starts is <   10 then insert into table1 values (...);

  If the number of entries in table 1 when Transaction T2 starts is <   10 then insert into table1 values (...);

If T1 and T2 each start before the other one finishes, with 9 values in the table, then there will indeed be 2 entries inserted, and there is nothing mis-serialized about it.

COUNT(*) is not an attribute.

If it is actually needful to keep the number of entries to 10 or less, then there needs to be a table with a counter, thereby:

create table counter_table (
  tname character varying,
  quantity integer,
  maxquantity integer default 10,
  constraint max_quantity check (quantity <= maxquantity) );

   update counter_table set quantity = quantity + 1 where tname = 'T1';    insert into table1 values (...);
COMMIT; That puts a constraint onto a database attribute in a manner that will allow a violation to be detected.

> (I am assuming that if/then/else is not part of the SQL standards,
> if this is not the case, please let me know.)


> Is there some better mode SERIALIZABLE_YES_REALLY_I_MEAN_IT
> available?
>>MySQL/InnoDB has a similar MVCC concurrency control method as Oracle
>>and PostgreSQL. But for MySQL/InnoDB, the SERIALIZABLE level really
>>is serializable, because InnoDB in that case converts all plain
>>SELECTs to use next-key locks on index records.
> An integrity advantage for MySQL over Oracle. Shows that
> preconceptions can be wrong :-).

In the absence of a script that actually demonstrates the behaviour that was discussed, it seems appropriate to remain more than a mite skeptical. I don't see why the use of "next-key locks" would change the results of a COUNT(*) query, unless, of course, it involved being in a "READ UNCOMMITTED" mode, which heads in the opposite direction to "SERIALIZABLE," allowing a whole gamut of phantom read anomalies.

If  all the salmon caught in  Canada in one  year were laid end to end
across  the  Sahara  Desert,  the   smell would  be absolutely  awful.
--DeMara Cabrera
Received on Sun Jan 25 2004 - 16:59:27 CST

Original text of this message