580240 - Executing multiple async statements creates an immediate transaction even for read statements

Status: RESOLVED WONTFIX

(Toolkit :: Storage, defect)

Description

[Marco Bonardo [:mak]]

when we do dbConn.asyncExecute(statements, ...) storage creates an immediate transaction (http://mxr.mozilla.org/mozilla-central/source/storage/src/mozStorageAsyncStatementExecution.cpp#564), this causes a RESERVED lock to be acquired immediately on the database, regardless when the first write will be executed.
So in case we have 4 statements and the first 2 are read-only the database is locked without a reason for about half of the time.
We should use DEFERRED for now, see https://bugzilla.mozilla.org/show_bug.cgi?id=563538#c40

Comment 1

[Marco Bonardo [:mak]]

Attachment: patch v1.0

Comment 2

[Marco Bonardo [:mak]]

asking blocking to reduce statements locking contentions from bug 563538. one-line change with low known risk.

Comment 3

[Shawn Wilsher :sdwilsh]

(In reply to comment #0)
> when we do dbConn.asyncExecute(statements, ...) storage creates an immediate
> transaction
> (http://mxr.mozilla.org/mozilla-central/source/storage/src/mozStorageAsyncStatementExecution.cpp#564),
> this causes a RESERVED lock to be acquired immediately on the database,
> regardless when the first write will be executed.
> So in case we have 4 statements and the first 2 are read-only the database is
> locked without a reason for about half of the time.
However, the RESERVED lock is for the sqlite3 object, so that means any statement executed on it, regardless of the thread, can execute.  We aren't being locked out by the transaction here, but rather by the serialization.

This fix won't change anything as far as I can tell because transactions are per sqlite3 object, not per thread.

Comment 4

[Marco Bonardo [:mak]]

SQLite docs are not crystal clear about this point, they say there can only be 1 reserved lock _at a time_ (http://www.sqlite.org/lockingv3.html#reserved_lock), so if anything other requires a reserved lock (to write) it will hang waiting to be able to acquire one even if the async statement is reading. So, from what I get in docs, if the awesomebar gets a reserved lock, and some other statements wants to get a reserved lock to write, it would hang.

Can you clarify with someone from sqlite team? unless you already did.

Comment 5

[Shawn Wilsher :sdwilsh]

Yes, but the connection gets the reserved lock, and all access to the connection is serialized.  SQLite does not have the concept of locks per thread.

Comment 6

[Shawn Wilsher :sdwilsh]

Confirmed with drh that what I'm saying is correct.  As a result, I'm going to WONTFIX this.