This commit implements GH-8967.
SQLite supports multiple transaction modes. These include:
- DEFERRED (default) only acquires a lock when you start a read/write
- IMMEDIATE acquires a reserved lock
- EXCLUSIVE acquires an exclusive lock (stricter than immediate)
In WAL mode IMMEDIATE and EXCLUSIVE are identical.
One reason for wanting to specify a transaction mode is that SQLite
doesn't respect busy_timeout when a DEFERRED transaction tries to
upgrade a read lock to a write lock. Normally if you try to acquire a
lock and have busy_timeout configured, SQLite will wait for that period
until giving up and erroring out (SQLITE_BUSY). With DEFERRED, if you
have a transaction that first reads and there's a concurrent writer
while it's trying to upgrade to a write lock, you will immediately get
SQLITE_BUSY regardless of your busy_timeout.
Prior to this commit, the only available workarounds were:
- Using $pdo->exec("BEGIN IMMEDIATE TRANSACTION") instead of
$pdo->beginTransaction()
- Doing a dummy write at the start of each transaction so you don't get
stuck with a read lock
Both of those aren't very usable, especially in a framework context
where the user doesn't have complete control over how transactions are
started.
To address that, this commit adds four class constants to Pdo\Sqlite:
- ATTR_TRANSACTION_MODE -- a new attribute
- TRANSACTION_MODE_DEFERRED = 0
- TRANSACTION_MODE_IMMEDIATE = 1
- TRANSACTION_MODE_EXCLUSIVE = 2
These can be used as:
$pdo->setAttribute(
$pdo::ATTR_TRANSACTION_MODE,
$pdo::TRANSACTION_MODE_IMMEDIATE
);
