"VC" <boston103_at_hotmail.com> wrote in message
news:SgrLc.154260$XM6.64529_at_attbi_s53...
>
> "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
>
>
And if it is a page locking database then it will lock whole pages of
indexes (as the index is traversed) witch will effectively lock the entire
table (or about 95% of it)
Jim
Received on Wed Jul 21 2004 - 14:26:56 CDT
