SQLite transaction doesn't work as expected

Question

I prepared 2 files, "1.php" and "2.php".

"1.php" is like this.

<?php
$dbh = new PDO('sqlite:test1');
$dbh->beginTransaction();

print "aaa<br>";
sleep(55);
$dbh->commit();

print "bbb";
?>

and "2.php" is like this.

<?php
$dbh = new PDO('sqlite:test1');
$dbh->beginTransaction();

print "ccc<br>";
$dbh->commit();
print "ddd";
?>

and I excute "1.php". It starts a transaction and waits 55 seconds.

So when I immediately excute "2.php", my expectation is this:

1. "1.php" is getting transaction and
2. "1" holds a database lock
3. "2" can not begin a transaction
4. "2" can not get database lock so
5. "2" have to wait 55 seconds

BUT, but the test went another way. When I excute "2",then

1. "2" immediately returned it's result
2. "2" did not wait

so I have to think that "1" could not get transaction, or could not get database lock.

Can anyone help?

Answer 1

As I understand it, SQLite transactions do not lock the database unless

• a. you make them EXCLUSIVE (they are DEFERRED by default), or
• b. you actually access the database

So either you explicitly call

$dbh->exec("BEGIN EXCLUSIVE TRANSACTION");

or you make a write operation (INSERT/UPDATE) to the DB before you start to sleep().

To cite the documentation (emphasis mine):

Transactions can be deferred, immediate, or exclusive. The default transaction behavior is deferred. Deferred means that no locks are acquired on the database until the database is first accessed. Thus with a deferred transaction, the BEGIN statement itself does nothing. Locks are not acquired until the first read or write operation. The first read operation against a database creates a SHARED lock and the first write operation creates a RESERVED lock.