locking at arbitrary levels.

At 10:31 AM 27-04-2000 -0500, Ross J. Reedstrom wrote:
>----- Forwarded message from "Mikheev, Vadim" <vmikheev(at)sectorbase(dot)com> -----
>NO! This is results of using MV for CC. With MVCC, serializable xaction
>should not assume that it sees _real_ state of database (and this is in
>docs... or maybe in 6.5 HISTORY only, sorry -:)). This breaks "theory of
>serializability" but gives us "consistent reads without blocking writers".
>PostgreSQL provides LOCK/SELECT_FOR_UPDATE if you need in "real"
>serializability - so, you have "workarround". Xactions in systems using
>LOCKS for CC are always "trully serializable" (on serializable level)
>but they have no "workarround" for "consistent reads without blocking
>writers".

But we can have better locking granularity by having a MySQL-like "lock on
arbitrary string" feature (see getlock and setlock). This will allow us to
be serializable and only block at an arbitrary level/granularity.

Example of getlock
getlock('string',timeout)
If 'string' has been locked by someone else, you block for timeout seconds
(and fail if you don't succeed).

getlock('all rows existing or to be created on all tables where key1=X or
key2=Y',10)
getlock('moving from stage 1 to stage 2',60)
getlock('table1,row=2,if data to write=2',5)

This is a crude but very effective way for an application to give a
"summary" of an intended transaction in a single atomic statement, allowing
related transactions to be easily serialised without affecting others.

Say you want to update a row if it exists, and insert it if it doesn't.

Getlock method:
getlock('mytable,field1=2',10);
update mytable set field2=4 where field1=2
if rowsupdated==0 {
insert into mytable (field1,field2) values (2,4)
}
unlock('mytable,field1=2');

Normal method.
lock table mytable
update mytable set field2=4 where field1=2
if rowsupdated==0 {
insert into mytable (field1,field2) values (2,4)
}
unlock table mytable

The reason for "lock table" is to prevent others from doing a duplicate
insert. This is assuming we do not use UNIQUE, because we don't want our
ENTIRE transaction to be rolled back just because of a duplicate, as
previously discussed.

Such a mechanism shifts a lot of responsibility to applications, but in
many cases the application already has that responsiblity anyway (e.g.
ordering of select for updates to prevent deadlocks).

[application is responsible]
Arbitrary lock
MVCC (with various isolation levels)
Table level locks
[database is responsible]

Cheerio,
Link.