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

Heikki,

"Heikki Tuuri" <Heikki.Tuuri_at_innodb.com> wrote in message news:RawVb.959$is1.635_at_read3.inet.fi...
> VC
> > 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.
>

Well, the accent should be on concurrency, (as in the whole table will be locked). The point is one has to index the table in order to achieve some level of concurrency rather than to improve performance. As a corollary, every possible predicate column or any combination therof has to be indexed for the same reason (concurrency).

> > 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.

Well, lock escalation is preventable to a certain degree by using the ROWLOCK hint under MSS.

 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.

Yukon has implemented multiversioning as well. Its SNAPSHOP ILwhich behaves in the same manner as Oracle's SERIALIZABLE.

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 - 18:48:14 CST