Gregory Stark <stark(at)enterprisedb(dot)com> wrote:
> "Albe Laurenz" <laurenz(dot)albe(at)wien(dot)gv(dot)at> writes:
>
>> So I think one would have to add intention locks for rows
>> considered in the WHERE clause to guarantee true serializability.
>
> Does the paper explain how to deal with rows "considered" in the
> WHERE clause which don't yet exist? Ie, "SELECT count(*) WHERE foo"
> needs to take out a lock which would cause any transaction which
> inserts a new record where foo is true to be abort.
The issue is mentioned, along with the note, quoted in my original
post, of why they were able to dodge the issue in the Berkeley DB
implementation.
> In MSSQL this requires locking the page of the index where such
> records would be inserted (or the entire table if there's no index).
This is the only form of predicate locking I've seen in real-world
production databases which provide true serializable behavior.
> In Predicate locking schemes this requires a separate storage
> structure for storing such predicates which can be arbitrarily
> complex expressions to check any new tuple being inserted against.
I've never seen that done in real-world production databases, although
I'm sure it's pretty in theory.
> Are these intention locks predicate locks, in that they're not
> associated with actual pages or records but with potential records
> which might be inserted in the future?
They are predicate locks in the sense that they detect all conflicts
which could occur based on the actual predicate, though they tend to
indicate conflicts in some situations where a rigorous (and expensive)
analisys of the actual predicates might not; but please note that such
locks would be SIREAD locks, which don't block any data modification,
but are only used to detect dangerous update patterns.
-Kevin