Re: oracle - mysql comparison

From: VC <boston103_at_hotmail.com>
Date: Wed, 21 Jul 2004 21:51:00 GMT
Message-ID: <4zBLc.1214$eM2.968@attbi_s51>

"Jim Kennedy" <kennedy-downwithspammersfamily_at_attbi.net> wrote in message news:3szLc.157906$XM6.2871_at_attbi_s53...
>
> "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

That was so ten years ago, but is no true longer today:

---

MSSQL:

1> create table flight(number int, seat int)
2> go
1> insert into flight(1,1)
2> go
1> insert into flight values(1,1)
2> go
1> insert into flight values(1,2)
2> go
1> insert into flight values(1,3)
2> go
1> insert into flight values(2,1)
1> set implicit_transactions on
2> go
1> create index f_idx on flight(number)
2> go
1>

We have 3 rows for Flight 1 and 1 row for Flight 2.

Then:

Session 1:

---

1> set transaction isolation level serializable
2> go
1> set transaction isolation level serializable
2> go
1> select count(*) from flight where number=1
2> go

-----------
          3

1>

Session 2:

---

1> set transaction isolation level serializable
2> go
1> set transaction isolation level serializable
2> go

We can read all the rows:

1> select * from flight
2> go
number seat
----------- -----------

          1           1
          1           2
          1           3
          2           1

We can add seats to Flight 2::

1> insert into flight values(2,2)
2> go

... we can see the old and new rows::

(1 rows affected)
1> select * from flight
2> go
number seat
----------- -----------

          1           1
          1           2
          1           3
          2           1
          2           2

(5 rows affected)

.. but we cannot add any new seats to Flight 1 thanks to key-range locking caused by 'select count(*) from flight where number=1'.

1> insert into flight values(1,4)
2> go
** blocked **

Apparently, no page/table level locking happens here ...

VC
>
>
Received on Wed Jul 21 2004 - 16:51:00 CDT