Skip to content

Instantly share code, notes, and snippets.

@bantu

bantu/00-output.txt

Last active Aug 29, 2015
Embed
What would you like to do?
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"
<?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']);
}
<?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']);
}
@Nicofuma

This comment has been minimized.

Copy link

@Nicofuma Nicofuma commented Jun 13, 2015

MySQL output:

1 => 3
2 => 4
@beberlei

This comment has been minimized.

Copy link

@beberlei beberlei commented Jun 16, 2015

@bantu

This comment has been minimized.

Copy link
Owner Author

@bantu bantu commented Jun 16, 2015

@bantu

This comment has been minimized.

Copy link
Owner Author

@bantu bantu commented Jun 18, 2015

$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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.