1

Mysql Reference explains that phantom read cannot be produced because it utilize snapshot for consistent nonlocking read.(A consistent read means that InnoDB uses multi-versioning to present to a query a snapshot of the database at a point in time.)

I understood that this means that any write-operation of other session can't violate current transaction's read.(If I use only 'select' query on my session. Note that for update clause is out of question.)

I've tested this using several where clauses such as 'where id=10', or 'where id>10', and checked any effect from write-operation of other session. The result was consistent as reference said above.

But, What if I have enormous data ? The "snapshot" can be huge as following, which might cause "Out of memory" issue although I query for only one row (e.g. id = 10)

So, My question is "In MySQL, how snapshot is implemented exactly to prevent any problem?(including OOM I said)"

p.s. the version of MySQL I'm using is 8.0

cjlee
  • 378
  • 3
  • 10

1 Answers1

2

The snapshot in MVCC is not a full copy of the data in RAM. That would take too long to create a snapshot, and as you thought, it would be larger than available RAM in some cases. So it doesn't work that way.

Each row version stores the transaction id that created that row. This is used by your transaction's snapshot to check if it can "see" that row version. If the row version is too new, that is it was committed after your snapshot started, then your snapshot doesn't include that row version, so it is not shown in query results. This way a "snapshot" is created virtually instantly when you start a transaction, because nothing needs to be copied.

If other sessions UPDATE or DELETE rows, the row version your snapshot can see has to be preserved, just in case you try to run a query that should view the old version of that row as part of its transaction. So there are multiple versions of rows stored. This does not cause OOM because the row versions are stored on disk.

This means that as data is updated by other sessions, there is an accumulation of row versions. Once your transaction ends (and any other transaction that might need to see them), those outdated row versions can be cleaned up, but if your transaction runs very long without finishing with COMMIT or ROLLBACK, the amount of accumulated row versions becomes large. It still does not lead to OOM, but it can cause expansion of the tablespace on disk.

Read this for more information: https://dev.mysql.com/doc/refman/8.0/en/innodb-multi-versioning.html

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828