MySQL Internals Manual  /  ...  /  MyISAM Concurrent Insert

21.5.1 MyISAM Concurrent Insert

Session1                             Session2
========                             ========
  +------------+                       +------------+
  | TABLE      |                       | TABLE      |
  |            |   +---------------+   |            |
  |          s --->| TABLE_SHARE   |<--- s          |
  |    file    |   |               |   |    file    |
  +-----|------+   |               |   +-----|------+
        |          +---------------+         |
        v                                    v
  +------------+                       +------------+
  | ha_myisam  |                       | ha_myisam  |
  |            |                       |            |
  |    file    |                       |    file    |
  +-----|------+                       +-----|------+
        |                                    |
        v                                    v
  +------------+                       +------------+
  | MI_INFO    |                       | MI_INFO    |
  |            |   +---------------+   |            |
  |          s --->| MYISAM_SHARE  |<--- s          |
  |            |   |               |   |            |
  | state -+--------> state.state <--------+- state |
  |        |   |   |               |   |   |        |
  |        v   |   |               |   |   v        |
  | save_state |   |               |   | save_state |
  |            |   |               |   |            |
  |   dfile    |   |   kfile       |   |   dfile    |
  +-----|------+   +-----|---------+   +-----|------+
        |                |                   |
        |                v                   |
        |              .MYI                  |
        |                                    |
        +------------> .MYD <----------------+

MI_INFO::state may either point to MI_INFO::save_state or to MYISAM_SHARE::state.state. The latter is the normal case. Amongst others, state contains data_file_length.

To support concurrent inserts, every statement starts with copying MYISAM_SHARE::state.state to MI_INFO::save_state and lets MI_INFO::state point to the copy. This is done in mi_get_status(). This is called from the hook THR_LOCK::(*get_status)(). Some of the hooks are explained in thr_lock.c. (*get_status)() is called when a thread gets a lock.

The copy back is done in mi_update_status(), which is called from mi_lock_database() when unlocking from a write lock. This, in turn, is called from ha_myisam::external_lock() from unlock_external() from mysql_unlock_tables(). Until 5.1 this was done after thr_multi_unlock(). So it was possible that another thread (or even multiple of them) could thr_lock the table and work with it before the first thread updates the status. In 6.0 the order is reversed. The status should now be accurate when another thread acquires a thr_lock.

However, with concurrent inserts, the trick is that read locks are allowed to proceed while a concurrent insert holds a write lock. So it can copy outdated information when entering the lock. Since it works on its local copy of the state, it won't notice rows that are made available through mi_update_status() after it got the lock.

But there is another chance to miss the row(s). See also Bug#36618 (myisam insert not immediately visible to select from another client). When the concurrent insert ends, it reports success to its client application before it unlocks the table. So there is non-deterministic time span between the seemingly successful ended insert and the final update of the MyISAM status.


User Comments
Sign Up Login You must be logged in to post a comment.