[Bizgres-general] Another use case

[Mark Kirkwood]

Luke Lonergan wrote:
> Mark, 
>
> That's the idea, not sure what the problem of priority inversion is?
>
>   

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.

>> Aside, with his cross platform hat on - is there an 
>> equivalent system call on win32?
>>     
>
> Must be - not sure what it is.  Since WIN is a preemptive multitasking
> system with priority scheduling, there is definitely a call to implement
> priority somewhere...
>
>   

Yep found it -
*
BOOL WINAPI* *SetPriorityClass(* *HANDLE* /hProcess/,*DWORD* 
/dwPriorityClass);

/There seems to be about 6-8 possible values of dwPriorityClass (depends 
on win version).

>
> 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!

Cheers

Mark