[Bizgres-general] Another use case

[Jim C. Nasby]

On Wed, Aug 09, 2006 at 06:18:30PM +1200, Mark Kirkwood wrote:
> Long running low priority queries can hold resources (typically DML 
> locks, but others are possible) that block (or merely hinder) high 
> priority queries. So effectively we get an inversion of the priority 
> scheme we want. Clearly, much less of a problem for SELECT only workloads.
 
See also http://en.wikipedia.org/wiki/Priority_inversion

> > When a statement is planned, thereby generating an execution plan, the
> > required locks for the plan are taken before the plan is returned.  At
> > that point, the statement would be queued and will wait for it's turn to
> > execute.
> >
> > If two statements reside in two different queues, their locks may
> > conflict, which will cause an interaction between the two statements.
> > This is an unwanted and uncontrollable coupling of the two queues.
> >
> > An approach to prevent this would be to enqueue statements with similar
> > lock types together as a class.  This would allow for reordering within
> > the queue class to pre-empt deadlocks and better estimate fair share of
> > processing.
> >
> >
> >   
> Sorry if I'm being thick here - So we want to take resource locks at 
> planning time? The current implementation does not do this. The resource 
> lock is taken in PortalRun, so a plan has been generated and returned 
> *before* a statement is queued. There is some subtlety with cursors, but 
> the previous statement still holds!

No, the point is that generating the plan takes locks, and if you don't
release those lock before sticking the query in a queue, you'll get
interactions with other queries (queued and unqueued) because of those
locks. It's another form of priority inversion.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby at pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461