How Indices Cope with MVCC?

 

Greetings Overflowers,

  • To my understanding (and I hope I'm not right) changes to indices cannot be MVCCed.
  • I'm wondering if this is also true with big records as copies can be costly.
  • Since records are accessed via indices (usually), how MVCC can be effective ?
  • Do, for eg, indices keep track of different versions of MVCCed records ?

    • Any recent good reading on this subject ? Really appreciated !

    Regards

  • Index itself can have both the records which can be pruned before returning. So in this case ondex alone can't be used to get the records (the MVCC done by PostGres). InnoDB/Oracle keeps only one version of data/index and using undo section it rebuilds the older versions for older transactions.

  • You wont have too many copies when DB is use in general as periodically copies will be garbage collected (In PostGres) and Oracle/InnoDB will have undo sections which will be reused when transaction is aborted/committed. If you have too many long running transaction obviously you will have problems.

  • Indexes are to speed up the access, find the record faster, without touch all of them, Index need not be accurate in first pass, you may need to look at tuple to see if its valid in one particular transaction or not (like in PostGres). racle or InnoDB even index is versioned so you can get data from indexes itself.

    • Read this to get detail idea of two ways of implementing MVCC (PresGres and Oracle/InnoDB).

    InnoDB MVCC and comments here are useful too

    PS: I'm not expert in mysql/oracle/postgres internal, still learning how things work.

    链接地址: http://www.djcxy.com/p/30604.html

    上一篇: 读取未提交的mvcc数据库

    下一篇: 指数如何应对MVCC？