Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Avoiding any locks in SQL Servers - read and understand....its magic.

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

From: Jim Kennedy <kennedy-downwithspammersfamily_at_attbi.net>
Date: Sun, 08 Feb 2004 18:50:02 GMT
Message-ID: <5muVb.251455$xy6.1311713@attbi_s02>

"VC" <boston103_at_hotmail.com> wrote in message news:OXsVb.5030$QA2.12959_at_attbi_s52...
> 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
> >
> >
>

And this is a feature? This is poor design. Jim Received on Sun Feb 08 2004 - 12:50:02 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US