[Bizgres-general] Transactional Statement Queuing

[Jim C. Nasby]

On Sat, Apr 22, 2006 at 01:23:37PM +1200, Mark Kirkwood wrote:
> Mark Kirkwood wrote:
> > Jim C. Nasby wrote:
> > 
> >> Another possibility is bringing query plan cost into the picture. For
> >> example, it seems silly to hold off on a query that we know will be very
> >> fast just because there's a bunch of monster queries ahead of it.
> >>
> > 
> > Yeah - I'm thinking some sort of minimum cost threshold might be a good 
> > idea,  so that (for instance) the other users' OLAP session don't freeze 
> > simply because they are looking up values in the time dimension while 
> > setting up their main query to run...
> 
> Altho this sounds good, on reflection I might have fallen straight
> into the deadlock trap (c.f. from the original mail):
> 
> 
> > Request for Active Slots will be made at start of a transaction and
> > will
> > be held until *end of transaction*. Holding an active slot is possible
> > even if nothing is being executed while "Idle in Transaction". This
> > behaviour ensures that we do not get resource deadlocks caused by
> > statements holding resources not being able to execute. [Discuss]
> >

Well, for SELECTs we could just leave it waiting, which is what would
have happened anyway. For DML, if a statement that was promoted ahead of
the queue hits any lock waits (or maybe if it has to wait for more than
some period of time), we roll it back and put it back in the queue.
-- 
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