Can't see rows inserted by a running transaction when isolation level is READ_UNCOMMITTED

Question

I have applications that insert rows into table A concurrently. Each application inserts rows in batch mode (using a JDBC prepared statement) using a single transaction per batch (to avoid rebuilding index after each INSERT). The rows present in each batch are completely independent, the transaction is used only for optimization. Each inserted row has its primary key set automatically (AUTO_INCREMENT).

I have another application that processes the rows from table A based on their IDs. The application processes range [ID1,ID2], then processes range [ID2+1,ID3], [ID3+1,ID4] and so on. Each range, e.g. [ID1,ID2] may contain rows inserted during different transactions, and possibly some of these transactions may not be committed yet. For example, in range [ID1,ID2], rows [ID1,ID1+N] may have been inserted during a not yet committed transaction while rows [ID1+N+1,ID2] may have been inserted during an already committed transaction. Therefore, when selecting rows in range [ID1,ID2], the transaction isolation level is set to READ_UNCOMMITTED so that uncommitted rows are visible.

The issue is that sometimes, the non committed rows are not visible and therefore are never processed.

The issue seems to appear when the SELECT is executed a very short time after the INSERTs. I made a test where one connection inserts multiple rows in a batch wrapped as a transaction, and before committing the transaction, after waiting some time, another connection queries the rows with READ_UNCOMMITTED as transaction isolation level, and the rows are visible. Therefore, I conclude that even if a row has been inserted and the auto increment counter lock released, the row may not be visible to other transactions although READ_UNCOMMITTED is set as transaction isolation level.

Answer 1

With my small test script no records show up before the commit if I login from another console. I can select the just inserted records in the same session, though. So I assume that before the commit you might access rows which are already in the table, but now new rows or changes before they are committed.

<?php

require_once('db.php');

q( 'drop table if exists t' );
q( 'create table t (id integer not null auto_increment primary key, v datetime) engine=innodb' );

q( 'set transaction isolation level read uncommitted' );
q( 'start transaction' );
q( 'insert into t (v) values (now()),(now()),(now())' );

echo q1( 'count(*)', 't', 'true'); // translates to "select count(*) from t where true"; 
// echoes "3" to the console

// wait for input
$handle = fopen ("php://stdin","r");
$line = fgets($handle);

// with a mysql client from a 2nd console at this point no new records show in table t

q( 'commit' );

// after this point all new records show up in table t from a 2nd session.