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

VC,

"VC" <boston103_at_hotmail.com> kirjoitti viestissä news:3XgVb.195516$Rc4.1657371_at_attbi_s54...
> Heikki,
>
> ----- Original Message -----
> From: "Heikki Tuuri" <Heikki.Tuuri_at_innodb.com>
> Newsgroups:
> comp.databases.oracle.server,comp.databases.oracle.misc,comp.databases
> Sent: Saturday, February 07, 2004 3:48 PM
> Subject: Re: Avoiding any locks in SQL Servers - read and
understand....its
> magic.
>
>
> > VC,
> >
> > "VC" <boston103_at_hotmail.com> kirjoitti viestissä
> > news:e1cVb.195950$5V2.1102862_at_attbi_s53...
> > > Hello Heikki,
> > >
> > > "Heikki Tuuri" <Heikki.Tuuri_at_innodb.com> wrote in message
> > > news:l_bVb.636$lh.428_at_read3.inet.fi...
> > > > Ed,
> > > >
> > > > "Ed Avis" <ed_at_membled.com> kirjoitti viestissä
> > > > news:l1smhmj0cx.fsf_at_budvar.future-i.net...
> > > > > "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> writes:
> > > > >
> > > > > >To date, every example I have seen of Oracle failing to handle
> > > > > >serialization properly has required the construction of an
> incorrect
> > > > > >data model, so I haven't been able to get excited about the
issue.
> > > > >
> > > > > To me, this doesn't make much sense. If the database fails to
> handle
> > > > > transaction ordering correctly (according to the database-theory
> > > > > definition of 'serializable'), then it is not much consolation to
> wave
> > > > > hands and say this is an incorrect data model. It's a bad data
> model
> > > > > only because it's a case that the database gets wrong!
> > > > >
> > > > > I suppose it would be okay if on executing the SQL, the programmer
> saw
> > > > > a warning:
> > > > >
> > > > > This transaction cannot be guaranteed to execute in a
> serializable
> > > > > order, even though you have chosen the 'serializable'
isolation
> > > > > level. Please see the documentation for more details.
> > > > >
> > > > > but it isn't like that, the database is quietly choosing to
misorder
> > > > > the operations in two transactions, and that's something I would
> > > > > expect more from MySQL than an industrial-strength RDBMS like
> Oracle,
> > > >
> > >
> > > > MySQL/InnoDB IS an industrial-strength transaction processing DBMS.
> > Wonder
> > > > when Oracle and PostgreSQL catch up ;).
> > > >
> > >
> > > Is it some kind of joke ? MySQL/InnoDB does not implement
SERIALIZABLE.
> >
> > it does. Please see http://www.innodb.com/ibman.php#Set.transaction
>
> OK, let us see...
>
>
> mysql> create table t1(id int, x int) type=innodb;
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> insert into t1 values(1, 50);
> Query OK, 1 row affected (0.00 sec)
>
> mysql> commit;
>
> Session 1:
> ---------
> mysql> set transaction isolation level serializable;
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> SET AUTOCOMMIT = 0;
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> select * from t1;
> +------+------+
> | id | x |
> +------+------+
> | 1 | 50 |
> +------+------+
> 1 row in set (0.00 sec)
>
> mysql> update t1 set x=0;
> Query OK, 1 row affected (0.00 sec)
> Rows matched: 1 Changed: 1 Warnings: 0
>
> mysql> select * from t1;
> +------+------+
> | id | x |
> +------+------+
> | 1 | 0 |
> +------+------+
> 1 row in set (0.00 sec)
>
>
>
> Session 2
> ----------
>
> mysql> set transaction isolation level serializable;
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> select * from t1;
> ERROR 1046: No Database Selected
> mysql> use test
> Database changed
> mysql> select * from t1;
>
> Session is blocked. because the whole table 't1' is locked.

InnoDB uses the usual two-phase locking with next-key row-level locks in this case. Since Session 1 updated the only row in the table, it is locked, and in Session 2, the SELECT cannot read that row until Session 1 commits.

Ok, I see that there is room for optimization here: if Session 2 is in the AUTOCOMMIT=1 mode, then we know that the SELECT of Session 2 is a read-only transaction, and we can perform the read as a consistent non-locking read, since we can serialize the read-only transaction of Session 2 before the update transaction of Session 1.

Thank you! I have now changed InnoDB so that future versions have this optimization about AUTOCOMMIT=1 :). Note that if you are performing more complex read-only transactions, with several SELECTs in the same transaction, then you can do them on the default REPEATABLE READ level, so that all the SELECTS are consistent non-locking reads. The read-only transaction is nicely serialized at the snapshot it is reading. The explicit SERIALIZABLE setting is only needed for transactions which are not read-only.

> In my book, this kind of behaviour is called SERIAL not SERIALIZABLE.
>
> It's hardly an achievement, Oracle had the same mode a decade ago which
was
> deprecated due to lack of popular demand.

Oracle's old SERIALIZABLE mode used table-level locks, which made it very inconvenient for the user. InnoDB and MS SQL Server use row-level locks to implement SERIALIZABLE.

> VC

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 Sun Feb 08 2004 - 05:07:27 CST