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

"Heikki Tuuri" <Heikki.Tuuri_at_innodb.com> writes:

>I have understood that the SERIALIZABLE transaction isolation level
>of PostgreSQL and Oracle is not serializable in the mathematical
>sense.

>If we have two instances (say T1 and T2) of the following transaction
>running concurrently:
>
>BEGIN;
>if SELECT COUNT(*) FROM table1 is < 10 then
> INSERT INTO table1 VALUES (...);
>COMMIT;
>
>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?

(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 :-).

-- 
Ed Avis <ed_at_membled.com>