Re: oracle - mysql comparison

"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message news:1090378179.508307_at_yasure...
> VC wrote:
> > As I've demonstrated, only a subset of rows involved in the transaction
has
> > to be locked which naturally can be the whole table.
>
> Patently false. You can not lock rows that have not yet been inserted
> while the transaction is taking place.

As a matter of fact, you can. In the reservation example:

create table PASSENGERS(FLIGHT_NUMBER ..., INFO ...); create index P_Idx on PASSENGERS(FLIGHT_NUMBER); set transaction isolation level serializable

... the statement below will lock not only the existing rows satisfying the FLIGHT_NUMBER=999 predicate, but also the relevant index (P_Idx) range thus preventiing potential *inserts*:

select count(*) into l_cnt from PASSENGERS where FLIGHT_NUMBER=999; --

This mechanism is called key-range locking. It allows to avoid full table locks and is implemented in all the major locking databases. MYSQL, by the way, has it too.

>And you have no means of keeping
> them out of your result set except a full table lock.

See above.

VC Received on Wed Jul 21 2004 - 05:08:50 CDT