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

Christopher,

"Christopher Browne" <cbbrowne_at_acm.org> kirjoitti viestissä news:bv1hof$n0den$4_at_ID-125932.news.uni-berlin.de...
> Martha Stewart called it a Good Thing when Ed Avis <ed_at_membled.com> wrote:
> > "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?
>
> 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:
>
> T1:
> If the number of entries in table 1 when Transaction T1 starts is <
> 10 then insert into table1 values (...);
>
> T2:
> 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.

the usual mathematical definition of serializability is that the execution of the transactions is equivalent to one where they are executed strictly serially, that is, one after another. There is no equivalent serial execution of T1 and T2, because then the later one would not insert a row.

This definition of serializability does not talk about attributes, or integrity constraints expressible in the language of the DBMS. Many integrity constraints really only exist in the head of the application programmer, they are not declared in the database schema. The application programmer writes the transactions in a way such that if the transactions are executed serially, they preserve the integrity constraints.

> --
> "cbbrowne","@","acm.org"

Best regards,

Heikki Tuuri
Innobase Oy
http://www.innodb.com
Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Received on Mon Jan 26 2004 - 04:47:57 CST