MVCC | Implementation of Multiversion Concurrency Control

I have a very strange problem. I create 3 entities with the following data:

If I understand correctly, under MVCC(multi version concurrency control), dead tuples are left in the page, until the Vacuum comes in and mark them "unused", and until "vacuum full" comes in and reorgnize them to defragment the space -- so we use less space for the same data.

I have a table, which in one environment that hasn't done vacuum full has around:

I want to change database (h2 to SQL) in JBPM

from

Assume a use case where we have 3 caches, called companies, employees and customers. A company record owns multiple employee records and multiple customer records. Employees have their own primary key and a foreign key companyId which references their owner company record. We use ignite to place an index on the Employee companyId field, either using SQL Create Index or the @QuerySqlField(index = true) annotation. The customer cache is setup in exactly the same way as the employee cache.

Let's say we want to delete the company, so we want to run the following SQL statements within a single transaction to ensure the company and its child records are either entirely deleted or entirely not deleted if the transaction fails (so we keep referential integrity).

This is a continuation of the problem described here (How do I fix a role-based problem when my role appears to have the correct permissions?)

I have done much more testing and still do not understand the error

Consider the following code:

I've decades of experience with MSSQL but none with Postgres and its MVCC style of concurrency control.

In MSSQL if I had a very large dataset which was read-only, I would set the database to read-only (for safety) and use transaction isolation level read uncommitted, and that should avoid lock contention, which the dataset didn't need anyway.

In Postgres, is there some equivalent? Some way of setting a database to read-only and reassuring PG that is completely safe not to use MVCC, just read without making row copies? Because it seems that MVCC has some considerable overhead which for multiple readers of very large passive data sets seems potentially expensive.

Edit: comments say I misunderstand that copies are only made when writing occurs, not reading as I assumed.

Reading and writing to the same tuple at the same time may cause read logic exceptions because of the non-atomic action of writing to the override of the tuple.

For MVCC in MySql,

Conceptually, because of ReadView, access to the tuple being written can be avoided by visibility rules, and then the race between reads and writes occurring in the same region can be avoided

But in terms of implementation details, I still have a question: Overwriting a field of a tuple will replace the data in the heap. If a read operation comes in, the same area will be read and written, which may result in read and write conflicts (byte copying is not atomic).

How can I avoid this read-write conflict? Is it a lock?

I did not express what I mean very well, I actually want to express the problem of lock competition：

Insert into tableA(age,num) values(1,1) Assume that there is a piece of data in the database.

At this time, under the read commit, two of the following transactions run concurrently at the same time: Transaction 1: select * from tableA Transaction 2: update tableA set age=2

The steps they run in the database are as follows:

1. Transaction 1 accesses the only piece of data in the page: the transaction id that accesses the row of data, and judges that the data is visible through the visibility rule
2. Transaction 2 locates the row of data and finds that the written age field is the same as the size occupied by the current data, so it starts to execute the replacement logic
3. Transaction 2 copies the value of the age field in the current data to undo, then points the undo pointer to the past, and the transaction id is updated
4. Transaction 2 writes the value 2 back to the age field of the current data
5. Transaction 1 starts to access the age field, reads that the current data value is 2, and accesses the value of the num field to 1, and returns (2, 1)
6. Transaction 2 commit

Through the operation of the above steps, you can see that the result returned by transaction 1 is not expected, and the root cause of this is that the action of reading the transaction id and the action of writing data (undo, transaction id, new data) are not mutually exclusive

mvcc is an access design, but the database engine will still compete for the same area when accessing and writing tuples. How does Innodb cleverly avoid read-write conflicts when implementing mvcc?

I read mysql INNODB uses MVCC (optimistic) to solve READ_COMMITTED and REPEATABLE_READ isolation levels (and) 2PL (pessimistic) to solve SERIALIZABLE. But no where it's mentioned how it solves READ_UNCOMMITTED.

• I opened two mysql sessions with READ_UNCOMMITTED isolation level. In both sessions, I tried to update the very same record. In first session, it got updated but in the second session, it was waiting for either first session to commit/rollback. Who locked this? since apparently there is no involvement of MVCC or 2PL here.

• Write-Write conflict -> Also, I saw the same behaviour in all the isolation levels, atleast in READ_COMMITTED and REPEATABLE_READ, which are solved by MVCC, when first session updated a row, then second sessions waits. I know MVCC doesn't locks in case of,

  i) First session reading (and) Second session reading

  ii)First session writing (and) Second session reading

  iii) First session reading (and) Second session writing

which is true to the statement "Readers don't block Writers and Writers don't block Readers". But, in the case of,

iv) First session writing (and) Second session writing - does INNODB lock the transaction and wait until the other commits/rollback's?

Mysql version: 5.7.32 Engine: INNODB