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

Hi!

"Christopher Browne" <cbbrowne_at_acm.org> kirjoitti viestissä news:bi6u68$5fght$1_at_ID-125932.news.uni-berlin.de...
> A long time ago, in a galaxy far, far away, Dieter Nöth <dnoeth_at_gmx.de>
wrote:
...
> > PostgreSQL Docu 9.2.2. Serializable Isolation Level: "The
> > Serializable mode provides a rigorous guarantee that each
> > transaction sees a wholly consistent view of the database. However,
> > the application has to be prepared to retry transactions when
> > concurrent updates make it impossible to sustain the illusion of
> > serial execution."

I have understood that the SERIALIZABLE transaction isolation level of PostgreSQL and Oracle is not serializable in the mathematical sense. Thus, the above excerpt from the PostgreSQL documents is misleading.

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.

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.

...
> I think MVCC _is_ a pretty big win; it does get rid of most need for
> locking, and, with suitable selection of serialization modes, can give
> good data back virtually without blocking.
>
> But none of this allows people to totally ignore how their tools work
> when coping with particularly complex situations involving
> concurrency. You can improve how you MANAGE complexity; you can't
> simply make the issues vanish.
> --
> select 'cbbrowne' || '@' || 'ntlug.org';

Best regards,

Heikki Tuuri
Innobase Oy
http://www.innodb.com
Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a non-free hot backup tool for MySQL Download MySQL-4.0 from http://www.mysql.com Received on Sun Aug 24 2003 - 05:57:49 CDT