当隔离级别为READ_UNCOMMITTED时，看不到正在运行的事务插入的行 [英] Can't see rows inserted by a running transaction when isolation level is READ_UNCOMMITTED

问题描述

我有一些应用程序可同时将行插入表 A 中.每个应用程序以批处理方式(使用JDBC准备的语句)以每批处理一个事务的方式插入行(以避免在每个INSERT之后重建索引).每批中存在的行是完全独立的，事务仅用于优化.每个插入的行都会自动设置其主键(AUTO_INCREMENT).

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

我有另一个应用程序，该应用程序根据表A的ID处理表A中的行.应用程序处理范围[ID1,ID2]，然后处理范围[ID2+1,ID3]，[ID3+1,ID4]，依此类推.每个范围，例如[ID1,ID2]可能包含在不同事务期间插入的行，并且其中某些事务可能尚未提交.例如，在范围[ID1,ID2]中，行[ID1,ID1+N]可能已在尚未提交的事务期间插入，而行[ID1+N+1,ID2]可能已在已提交的事务期间插入. 因此，在选择范围为[ID1,ID2]的行时，将事务隔离级别设置为READ_UNCOMMITTED，以便可见未提交的行.

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.

在INSERT之后很短的时间执行SELECT时，似乎出现了此问题.我进行了一个测试，其中一个连接在包装为事务的批处理中插入多行，并且在提交事务之前，等待一段时间后，另一个连接以READ_UNCOMMITTED作为事务隔离级别查询这些行，并且这些行可见.因此，我得出的结论是，即使将READ_UNCOMMITTED设置为事务隔离级别，即使已插入一行并且释放了自动递增计数器锁定，该行也可能对其他事务不可见.

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.

推荐答案

如果我从另一个控制台登录，则使用我的小型测试脚本，在提交之前不会显示任何记录.不过，我可以选择在同一会话中刚刚插入的记录.因此，我假设在提交之前，您可以访问表中已经存在的行，但是现在可以访问新行或在提交之前进行更改.

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.

这篇关于当隔离级别为READ_UNCOMMITTED时，看不到正在运行的事务插入的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！