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

VC,

"VC" <boston103_at_hotmail.com> kirjoitti viestissä 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);

remember that you can run read-only transactions on the default REPEATABLE READ isolation level, and the resulting execution is still serializable. Thus, an update does not block concurrent read-only transactions.

> 3. An un-indexed table will allow only SERIAL access to any row since the
> whole table will be locked by a single update.

Yes, it is highly recommended to add indexes to your table so that your queries do not need to scan the whole table.

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

MS SQL Server also implements next-key locks like InnoDB. But in InnoDB there is no need for lock escalation to page level or table level locks, because the row-level locks fit in very small space. The other difference to MS SQL Server is that InnoDB is multiversioned, which allows you to run read-only transactions without any locking, and still achieve serializable execution.

> Rgds.
>
> 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 - 13:34:09 CST