Dear Ramakrishna,
"two different transactions can update the same version of the row"
This answer itself is wrong.
In my point of view, the drawback of MVCC is just holding multiple versions of tuple in a table which leads to slowness in application access. the more your table is bloated the more it takes to retrieve data.it has to scan so much of _VM, so many pages which is time consuming.
The other drawback is anyway space.
There are a couple of workarounds to address the issue is what you should tell your recruiter.
Any RDBMS has its own mechanism to address Isolation property and each mechanism has it own flaws.
On Mon, Jul 13, 2020 at 10:41:28AM +0200, Francisco Olarte wrote:
> Rama:
>
> On Mon, Jul 13, 2020 at 9:52 AM Rama Krishnan <raghuldrag@gmail.com> wrote:
> > I m preparing for interview one of the recruiter asked me mvcc drawbacks as i told due to mvcc it use more space and need to perform maintenance activity.
> > Another one is the same data causes an update conflict because two different transactions can update the same version of the row.
> > he told its wrong, kindly tell me will you please tell me its correct or wrong?
>
> I'm not sure I understand your question too well, you may want to
> refresh/expand.
>
> One interpretation is, on a pure MVCC contest, two transactions, say 5
> and 6, could try to update a tuple valid for [1,) and end up
> generating two new tuples, [5,), [6,) and closing the original at
> either [1,5) or [1,6) .
>
> That's why MVCC is just a piece, locking is other. On a MVCC the
> tuples are locked while a transaction manipulates them. Other
> transactions may read them, which is why readers do not block writers,
> but two updates on the same tuple serialize.
You might want to look at this:
https://momjian.us/main/presentations/internals.html#mvcc
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com
The usefulness of a cup is in its emptiness, Bruce Lee