Insert, Update and Delete don't take locks they simply mark the tuples
they change with an xid. Anybody else wanting to "wait on the lock"
just waits on the xid. We do insert a lock row for each xid, but not
one per row changed.
I mean the foreign key checks here. They take a Select for Share Lock right. That's what we are trying to optimize here. Or am i missing something? So by following the suggested methodology, the foreign key checks won't take any locks.
It's worked that way for 5 years, so its too late to modify it now and
this patch won't change that.
The way we do RI locking is designed to prevent holding that in memory
and then having the lock table overflow, which then either requires us
to revert to the current design or upgrade to table level locks to
save space in the lock table - which is a total disaster, if you've
ever worked with DB2.
What you're suggesting is that we store the locks in memory only as a
way of avoiding updating the row.
But that memory would be consumed, only when someone updates the referenced column( which will usually be the primary key of the referenced table). Any normal database programmer knows that updating primary key is not good for performance. So we go by the same logic.
No, updates of referenced columns are exactly the same as now when no
RI checks happening.
If the update occurs when an RI check takes place there is more work
to do, but previously it would have just blocked and done nothing. So
that path is relatively heavyweight but much better than nothing.
As i have already said, that path is definitely heavy weight( like how Robert has made the DDL path heavy weight). If we assume that DDLs are going to be a rare phenomenon, then we can also assume that update of primary keys is a rare phenomenon in a normal database.
The most useful way to help with this patch right now is to run
performance investigations and see if there are non-optimal cases. We
can then see how the patch handles those. Theory is good, but it needs
to drive experimentation, as I myself re-discover continually.
I understand. I just wanted to know, whether the developer considered that line of thought.
Thanks,
Gokul.
