Cannot see rows inserted by current transaction when isolation level is READ_UNCOMMITTED

Question

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

I have another application that processes rows from table A based on their identifiers. Application processes range from [ID1,ID2], then a range is processed [ID2+1,ID3], [ID3+1,ID4]etc. Each range, for example. [ID1,ID2]may contain rows inserted during different transactions, and perhaps some of these transactions will not be executed yet. For example, in a range, [ID1,ID2]rows [ID1,ID1+N]can be inserted during a transaction that has not yet been completed, while rows [ID1+N+1,ID2]can be inserted during an already committed transaction. Therefore, when selecting rows in a range [ID1,ID2], the transaction isolation level is set equal READ_UNCOMMITTEDto display uncommitted rows.

The problem is that sometimes unfixed strings are not visible and therefore are never processed.

The problem occurs when SELECTa very short time after INSERTs is executed . I checked when one connection inserts several lines into a package wrapped in a transaction, and before the transaction is completed after waiting for a while, another connection asks for lines with READ_UNCOMMITTEDas the transaction isolation level, and the lines are visible. Therefore, I conclude that even if a row has been inserted and the automatic increment counter lock is released, the row may not be displayed for other transactions, although it is READ_UNCOMMITTEDset as the transaction isolation level.

Answer 1

script , . . , , , , .

<?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.