Re: Predicate locking - Mailing list pgsql-hackers

Dan Ports  wrote:
> On Tue, May 03, 2011 at 01:36:36PM +0900, Vlad Arkhipov wrote:
>> Then I commited the both and the second one raised an exception:
>> ERROR: could not serialize access due to read/write dependencies
>> among transactions
>> SQL state: 40001
>>
>> However the second transaction does not access the records that
>> the first one does. If I had predicate locks I could avoid this
>> situation by locking the records with the specified id.
> 
> Yes, you're right -- the current implementation of SSI only locks
> indexes at the granularity of index pages. So although those
> transactions don't actually access the same records, they're
> detected as a conflict because they're on the same index page. Of
> course, on a larger table this might be less likely to happen.
> 
> Getting this down to index-key and index-gap lock granularity is on
> the todo list. Our focus in the initial SSI development has been to
> get something that's functionally correct and stable before
> optimizing it. I'm hoping to get some time to work on index-key
> locking for 9.2, as I expect it will make a significant performance
> difference.
Well, if Vlad had the feature he's requesting, he almost certainly
would have had blocking and then a deadlock (after the deadlock
checking delay expired) on this particular test. The reason is that
in a table with just a few very narrow rows, any cost based optimizer
will choose a table scan, to bypass the overhead of going through the
index to get to the single page.  As you suggested, on a table with
enough rows to make index use beneficial, it would have a decent
chance of avoiding serialization failures.  Index-gap locking would
not have helped in this example for the same reason.
The fact is, to get protections such as Vlad seeks using either
blocking-style locks or SSI you have a chance of serialization
failure (if you count deadlocks as a form of serialization failure,
which they are).  And I'm not aware of any production-quality
database product which doesn't use locks on accessed objects to do
this, so really Vlad's complaint here breaks down to the fact that
we're using a cost-based optimizer which will choose a table scan
rather than a rules-based optimizer which will go through an index to
get to a single page.  Going through the extra read for the indexed
access here would probably slow things down more, in a real load,
than the transaction retries.
If you look at the paper which won an ACM SIGMOD "best Paper" award
and the benchmarks in it which were independently confirmed by an ACM
committee, you'll see that the SSI techniques used in the PostgreSQL
9.1 SERIALIZABLE transaction level benchmarked as far faster (in far
more realistic tests than the one on this thread) and with better
concurrency than the blocking sort of locking Vlad is requesting.
In a way this argument reminds me of the recurring "I need hints!"
argument -- someone is insisting on doing things using the technique
to which they've become accustomed in other products and complaining
that PostgreSQL deals with the issue in a different way which is
arguably better.
But getting back to Dan's point -- I know he's eager to get the btree
locking down to "next key" granularity, which will reduce the false
positive rate, and I have my eye on a few other optimizations.  I
expect that in its current form serializable mode will perform better
than blocking-based techniques for many workloads, but I also expect
it will get even better over the next few releases.
-Kevin