mercredi 7 janvier 2015

SQLite 3, php, locking table

lets imagine two scripts: one what is getting datas from a table, and another what writes it. While A is running, during the process it must always get the same results, if the B writes to the table, A might goes wrong.


A:



session_start(); session_write_close();

set_time_limit(0);
$file_db = new PDO('sqlite:'.dirname(__FILE__).'/table.sqlite3');
$file_db->setAttribute (PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$file_db->exec('DROP TABLE IF EXISTS messages');
$file_db->exec('CREATE TABLE IF NOT EXISTS messages (id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT)');
$file_db->query('INSERT INTO messages VALUES (1, "'.rand(1,99999).'")');
$file_db->setAttribute(PDO::ATTR_TIMEOUT, 9999999);
$m = date('U');
do
{
$row = $file_db->query('SELECT title FROM messages WHERE ID = 1')->fetch();
if ($row === false)
{
echo 'DELETED!<br>';
}
else
{
echo $row['title'].'; ';
if (rand(1,5) == 1) { echo '<br>'; }
}
if (abs(date('U') - $m) > 10) { break; }
} while(true);
echo '<hr>';
$file_db->setAttribute(PDO::ATTR_TIMEOUT, 0);
foreach ($file_db->query('SELECT title FROM messages WHERE ID = 1') as $row)
{
var_dump ($row); echo '<br>';
}


B:



session_start(); session_write_close();

$i = 0;
set_time_limit(0);
$file_db = new PDO('sqlite:'.dirname(__FILE__).'/table.sqlite3');
$file_db->setAttribute (PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$m = date('U');
do
{
switch (rand(1,2))
{
case 1 :
$file_db->query('DELETE FROM messages');
break;
case 2 :
$file_db->query ('UPDATE messages SET title = 0');
break;
}
if (abs(date('U') - $m) > 1) { break; }
} while(true);


so A is running for 10 seconds, B for 2 seconds, and if you run B while A is running, A echoes zero, or no result. Thats normally normal, but I want to lock that table, how to do that? And what happens if B tries to modify it then, an exception? Cant B just hold until table gets unlocked? I read that PDO::ATTR_TIMEOUT is for it, but it has no effect


Aucun commentaire:

Enregistrer un commentaire