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

Heikki,

"Heikki Tuuri" <Heikki.Tuuri_at_innodb.com> wrote in message news:PLoVb.128$is1.7_at_read3.inet.fi...
> VC,
>

OK, I see what you are doing. However, the schedule below will still be serial:

use test;
drop table t;
create table t(id int, x int) type=innodb; insert into t values(1,50);
insert into t values(2,100);
commit;

T1:
use test;
set transaction isolation level serializable; set autocommit=0;
update t set x=-1 where id=1;

T2:
use test;
set transaction isolation level serializable; set autocommit=0;
update t set x=-1 where id=2;

• T2 hangs despite trying to update a different row.

.. in the absence of the unique index on id the whole table will be locked. So we can say that InnoDB does implement some kind of the SERIALIZABLE IL with the following properties:

1. Concurrent selects against the same row are allowed in the absence of any updates to the same row;
2. A row update essentially allows only SERIAL access for concurrent read transactions since concurrent selects will also be blocked (the writer will block readers);
3. An un-indexed table will allow only SERIAL access to any row since the whole table will be locked by a single update.

Looks quite similat to MS SQL Server's Serializable(although I am not sure about page level locks for InnoDB).

Rgds.

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
>
>
Received on Sun Feb 08 2004 - 09:53:18 CST