SQLite Forum

Attached read-only database causes BEGIN IMMEDIATE to fail
Login
Chronological

Attached read-only database causes BEGIN IMMEDIATE to fail **BUG with FIX**

(1.1) By Keith Medcalf (kmedcalf) on 2020-10-12 04:53:43 edited from 1.0 [link] [source]

I do not know if this is documented anywhere, but when a connection has an attached database that is read-only issuing a BEGIN IMMEDIATE (or BEGIN EXCLUSIVE) will fail with an error that an attempt is being made to write to a read-only database.

SQLite version 3.34.0 2020-10-09 04:10:22
Enter ".help" for usage hints.
sqlite> .databases
main: D:\a.db r/w
temp: "" r/w
_tz_: d:\source\sqlite\tz.db r/o
sqlite> begin;
sqlite> create table t(x);
sqlite> commit;
sqlite> begin immediate;
Error: attempt to write a readonly database
sqlite> rollback;
Error: cannot rollback - no transaction is active
sqlite>

Note that a standard deferred transaction has no problem because no lock is actually taken on the read-only database until it is read or written (since that transaction form really does nothing other than turn off the autocommit machinery).

Can/should BEGIN IMMEDIATE/EXCLUSIVE only take out read (shared) locks on readonly attached databases and throw the "cannot write" error when an actual attempt is made to write?

I also note that if an attached database is immutable (which claims to disable all locking on that database https://sqlite.org/c3ref/open.html under the URI description for immutable) that BEGIN IMMEDIATE/EXCLUSIVE still fails even though no locks should be taken against that database at all, ever.

(2) By Keith Medcalf (kmedcalf) on 2020-10-12 04:51:52 in reply to 1.0 [source]

In build.c I believe the following change will fix this:

void sqlite3BeginTransaction(Parse *pParse, int type){
  sqlite3 *db;
  Vdbe *v;
  int i;

  assert( pParse!=0 );
  db = pParse->db;
  assert( db!=0 );
  if( sqlite3AuthCheck(pParse, SQLITE_TRANSACTION, "BEGIN", 0, 0) ){
    return;
  }
  v = sqlite3GetVdbe(pParse);
  if( !v ) return;
  if( type!=TK_DEFERRED ){
    for(i=0; i<db->nDb; i++){
      sqlite3VdbeAddOp2(v, OP_Transaction, i, sqlite3BtreeIsReadonly(db->aDb[i].pBt) ? 0 : (type==TK_EXCLUSIVE)+1);
      sqlite3VdbeUsesBtree(v, i);
    }
  }
  sqlite3VdbeAddOp0(v, OP_AutoCommit);
}

by making the OP_Transaction P2 0 if the underlying Btree is ReadOnly ...