| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| 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.
Heikki,
> 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 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.
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.
VC
>
> > > 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
>
> Regards,
>
> Heikki
>
>
Received on Sat Feb 07 2004 - 20:13:19 CST
 |
 |