Why does Magento have a stock index?

Question

I might be being a bit short sighted but I can't find the reasoning behind Magento having a stock index when cataloginventory_stock_status and cataloginventory_stock_status_idx are identical in structure.

The only differences I can find at table level are:

1. There's a small variation in the number of rows
2. cataloginventory_stock_status has 3 foreign key constraints not found on the index table.

I assume there's going to be some reason relating to locking or certain processes where it would affect checkout but cannot find information as to why.

Answer 1

The indexing process writes the values at first in the _idx table so it won't interfere with the read actions on the main table while its running.
When all the values are inserted in the _idx table then all the values are copied to the main table.

Take a look at how Mage_CatalogInventory_Model_Resource_Indexer_Stock::reindexAll looks like.
Also see my comments in the code below:

public function reindexAll()
{
    $this->useIdxTable(true); //tell the indexer to use the _idx table
    $this->beginTransaction();
    try {
        $this->clearTemporaryIndexTable(); //clear data from the _idx table

        foreach ($this->_getTypeIndexers() as $indexer) {
            $indexer->reindexAll(); //reindex everything in the _idx table
        }

        $this->syncData(); //clear the main table and insert the values from the _idx table.
        $this->commit();
    } catch (Exception $e) {
        $this->rollBack();
        throw $e;
    }
    return $this;
}