On Tue, Oct 13, 2015 at 10:37 PM, dinesh kumar <dineshkumar02@gmail.com> wrote: > In an existing wait policies like WAIT(default) and NO WAIT, > one can be sure to determine(Using ROW_COUNT daignostics counter), > how many required tuples he processed in a transaction. > But this is not case when it comes to SKIP LOCKED.
Sure it is. You didn't process the ones that you skipped. This is no different than if you say WHERE a = 5. Depending on plan choice and table contents, you may have "skipped" a large number of rows where a != 5, or you may have skipped none at all.
Yes, True.
But, using SKIP LOCKED we may bypass the tuples where a = 5, If those were locked by parallel operations.
> In my view, SKIP LOCKED is a nice feature, which gives only the available OR > unlocked tuples. > But those are not the complete required tuples for the given SQL statement. > Isn't it ?!
They better be.
Agreed.
If you wanted the locked tuples, you shouldn't have asked to skip them.
Kindly let me know if I am going in a wrong way.
I see this feature as an add on to do the parallel DML operations.
There won't be any problem, if operations are mutually exclusive.
I mean, each session operates on unique set of tuples.
In the above case, we don't even need of SKIP LOCKED wait policy.
But, when it comes to mutually depend operations, isn't it nice to provide,
how much were locked by the other sessions. OR atlest a HINT to the other session like,
GET DIAGNOSTICS var = DID_I_MISS_ANYTHING_FROM_OTHER_SESSIONS;
I agree that, adding counter will take a performance hit.
Rather going to my actual proposal on providing the counter value,
isn't it good to provide a boolean type HINT, if we miss atleast a single tuple.
