Re: Row locking and serializability

From: Vadim Tropashko <vadimtro_at_yahoo.com>
Date: Mon, 19 Jul 1999 18:11:58 -0700
Message-ID: <3793CCDE.E12F8BDD@yahoo.com>

As this was not sarcasm, it requres little bit more elaboration:

Let have a flights table like this:

CREATE TABLE flight
 (

  num                        NUMBER,
  seat                       NUMBER,

  ...
 );

Even though the number of entries in this table is big, the query:

select * from flight
where num = myFlight -- important for update;

never locks more than 550 rows (B747 capacity:-). Other transactions could still happilly update all the other flights while the current transaction makes a reservation for 'myFlight'.

Ruud de Koter wrote:

> Hi Vadim,
>
> You must be joking. This kills both performance and scalability
> with a single action. Please insert some indication of sarcasm
> or irony when you write something like this....
>
> Regards,
>
> Ruud de Koter
>
> Vadim Tropashko wrote:
> >
> > iggy_fernandez_at_my-deja.com wrote:
> >
> > > The question that remains unanswered is how
> > > serializability may be manually achieved when
> > > using Oracle.
> >
> > This one seems easy -- just lock "the whole" table:
> >
> > alter session set isolation_level=serializable;
> > select * from passengers for update;
> > insert into passengers values 'Z';
> > commit;
> >
> > Thransaction above is serializable indeed.
>
> --
> --------------------------------------------------------------------------------------
> Ruud de Koter HP OpenView Software Business Unit
> Senior Software Engineer IT Service Management Operation
> Telephone: +31 (20) 514 15 89 Van Diemenstraat 200
> Telefax : +31 (2) 514 15 90 PO Box 831
> Telnet : 547 - 1589 1000 AV Amsterdam, the Netherlands
> Email : ruud_dekoter_at_hp.com
>
> internet: http://www.openview.hp.com/itsm
> http://www.openview.hp.com/assetview
> intranet: http://ovweb.bbn.hp.com/itservicemanager
> --------------------------------------------------------------------------------------
Received on Mon Jul 19 1999 - 20:11:58 CDT