00-output.txt

1 => 3 # This row is read into a buffer on $db->query()
2 => 6 # Expected 4 instead of 6. This row is only fetched after the second UPDATE finished.

# https://www.sqlite.org/pragma.html says "The default isolation level for SQLite is SERIALIZABLE."
# which should prevent this from happening?

# Is this a PDO bug?

# Is this a known Sqlite issue that should be documented on
# http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/known-vendor-issues.html ?

# Answer: 
# "The answer is that this behavior is undefined."
# See https://sqlite.org/isolation.html
# Paragraph "No Isolation Between Operations On The Same Database Connection"

01-pdo_sqlite.php

<?php
$db = new PDO('sqlite::memory:');
$db->exec("CREATE TABLE tbl (id INTEGER PRIMARY KEY, someint INTEGER)");
$db->exec("INSERT INTO tbl (id, someint) VALUES (1, 3)");
$db->exec("INSERT INTO tbl (id, someint) VALUES (2, 4)");
$result = $db->query('SELECT * FROM tbl');
$db->exec('UPDATE tbl SET someint = 5 WHERE id = 1');
$db->exec('UPDATE tbl SET someint = 6 WHERE id = 2');
foreach ($result as $row) {
  printf("%d => %d\n", $row['id'], $row['someint']);
}

02-doctrine_sqlite.php

<?php
// composer require doctrine/dbal
require __DIR__.'/vendor/autoload.php';

$file = __DIR__.'/doctrinetest.sqlite3';
!file_exists($file) || unlink($file);

$db = \Doctrine\DBAL\DriverManager::getConnection([
    'path' => $file,
    'driver' => 'pdo_sqlite',
]);

$db->exec("CREATE TABLE tbl (id INTEGER PRIMARY KEY, someint INTEGER)");
$db->exec("INSERT INTO tbl (id, someint) VALUES (1, 3)");
$db->exec("INSERT INTO tbl (id, someint) VALUES (2, 4)");
$result = $db->query('SELECT * FROM tbl');
$db->exec('UPDATE tbl SET someint = 5 WHERE id = 1');
$db->exec('UPDATE tbl SET someint = 6 WHERE id = 2');
foreach ($result as $row) {
  printf("%d => %d\n", $row['id'], $row['someint']);
}

Same on python: https://gist.github.com/anonymous/cf30c11fec8cb082deaa

Ref doctrine/dbal@f5386a9

$db->setTransactionIsolation($db::TRANSACTION_SERIALIZABLE); // Default, thus no-op?
$db->exec("CREATE TABLE tbl (id INTEGER PRIMARY KEY)");
$db->exec("INSERT INTO tbl (id) VALUES (1)");
$db->exec("INSERT INTO tbl (id) VALUES (2)");
$result = $db->query('SELECT * FROM tbl');
$db->exec('UPDATE tbl SET id = 3 WHERE id = 1');
$db->exec('UPDATE tbl SET id = 4 WHERE id = 2');
foreach ($result as $row) {
  printf("%d\n", $row['id']);
}

outputs

1
3
4