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

Martha Stewart called it a Good Thing when "VC" <boston103_at_hotmail.com> wrote:
> 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.
>
> 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.

Interesting. I would have expected session #2 to have gotten the tuple (id,x) = (1, 50), since that is the only data that had been committed.

It sure looks as though Heikki Tuuri's presentation of "serialization" represents an approach where doing concurrent updates becomes pretty problematic. In effect, you have to block both readers and writers in a whole lot of situations, which will quickly become unmanageable for any sort of large scale online processing. Most databases suffer at least a little bit if in isolation level #3; the suffering seems pretty total...

If you look at the MySQL documentation on isolation levels, it's pretty evident that the number of locks being enforced steadily increases as you head towards level #3. Presumably that can be multiplied by the number of processes engaged in database activity. And it's pretty similar to the upgrading of locking that traditionally took place in Sybase SQL Server as well as Microsoft SQL Server.

If a simple update like the one you outline blocks readers with so little going on, I can just imagine what happens if there are 50 or 1000 update processes going on...

The next question worthy to ask is how this all relates to the definitions presented in the SQL92 standard, which is actually the authoritative document.

Tuuri appears to be arguing that since the standard does not conform to his mathematical characterization of a different notion of serialization, then it is not worth looking at it.

-- 
(format nil "~S@~S" "cbbrowne" "cbbrowne.com")
http://www.ntlug.org/~cbbrowne/x.html
"My soul is more than matched; she's overmanned; and by a madman!
Insufferable sting, that sanity should ground arms on such a field!
But he drilled deep down, and blasted all my reason out of me! I think
I see his impious end; but feel that I must help him to it. Will I,
nill I, the ineffable thing has tied me to him; tows me with a cable I
have no knife to cut. Horrible old man!
[...] Oh, life! 'tis now that I do feel the latent horror in thee!"
--Moby Dick, Ch 38