>>> Gregory Stark <stark@enterprisedb.com> wrote:
> This example is a case of the same issue we were discussing earlier
> involving "predicate locking". To solve it you need a way to lock
> records that your query *isn't* accessing and may not even exist yet
> to prevent them from being turned into (or inserted as) records your
> query should be accessing.
>
> As Kevin described it earlier Sybase locks the index pages
> containing the key range you're accessing preventing anyone from
> inserting new index pointers in that range. If there's no index it
> locks the entire table on every select to prevent any updates or
> inserts in the table until your transaction finishes.
Well, for READ COMMITTED in Sybase it's only until the end of the
statement.
Hmmm.... I'm clearly getting a bit rusty on my Sybase row level
locking rules. I got some details wrong in my example, but the
outcome would be the same. Broader locks though, leading to more
potential blocking.
> I'm unclear what whether it manifests any of the phenomenon which
> are prohibited for READ COMMITTED.
Interesting question. It's behavior not possible in 2 phase locking,
but not explicitly prohibited by the standard. Better watch that kind
of talk, though, or they may go and change the standard again. ;-)
-Kevin