SQLite Learning Notes (vi) && shared cache

Introduction

Typically, SQLite has a separate pager object for each connection, the pager object manages the cache information for the connection, the cache size can be set by pragma cache_size instruction, and the default is 2000 page, each page is 1024B. This leads to the same data file, where multiple connections maintain their own cache, which in high concurrency can result in large amounts of memory being used. SQLite as an embedded database, commonly used in embedded devices, memory may be relatively limited, in order to deal with this problem, SQLite provides a method, by having multiple connections common one pager object, share the same cache.

When this feature is turned on, multiple connections can share a pager object, which in some way reduces memory consumption and the number of file IO times. Multiple connection objects for one thread, and multiple connection objects for multiple threads, can share the cache in this way. Because SQLite is embedded in the application in a dynamic library, each application has its own independent process space, so the attribute cannot be used between processes, the same process can share a cache, and each process maintains its own cache. The implementation of the cache will be followed by a separate article. Shows the structure diagram after the shared cache is enabled.

Figure 1

As you can see from Figure 1, the two connections in Process1 share the Btshared object, the Btshared object corresponds to a pager object, and the cache is managed by the pager object, so all connections in the entire PROCESS1 have a common cache, The Page,io operation is transparent to the upper module through the pager module operation.

Implementation Principle

When calling interface Sqlite3_open_v2 to open a connection, declare the connection in shared cache mode by specifying the parameter Sqlite_open_sharedcache. Previous SQLite Series (ii): Single table primary key query test in the general performance test the chapter mentions that when the shared cache mode is turned on, the concurrency performance of the application program is greatly reduced, and the CPU can only use one core in multi-threaded cases. So in actual use, you weigh the memory and parallelism to determine whether the shared cache mode is turned on.
Our test scenarios are read-only and in theory there should be no concurrency conflicts, so why not parallel? This depends on the implementation of the shared cache. As you can see from Figure 1, each connection has a Btree object, and multiple Btree objects share the cache through a shared btshared object, and the Btshared object maintains its members through Mutux, including page cache management, Table-lock information, etc. So this mutex is a hotspot, in high concurrency scenarios, multiple threads accessing the Btshared object at the same time, due to competing mutexes, can not fully concurrency, resulting in poor parallelism.

Table-lock

by Default, SQLite only through file lock can realize read-write mutex, reading concurrency effect, about this I am in the SQLite series (v): SQLite blocking mechanism has been explained. So why introduce Table-lock? This is also given by the shared cache. In shared cache mode, multiple Btree objects correspond to the same Btshared object, and when the update is performed, the cache in pager is first modified, at which time the update transaction is added Reserved-lock, and the shared-lock of the read transaction is not mutually exclusive. In order to avoid reading dirty pages, in shared cache mode, the Table-lock is added to avoid the simultaneous access of read and write transactions to the same cache, causing dirty reads to occur. Before a user accesses a page of a particular table, it first calls Sqlite3btreelocktable to set a read-lock (select operation) or Write-lock (DML operation) on the table and throws Sqlite_ if a conflict is found. Locked error. In this way, it is ensured that multiple threads do not read and write to a table at the same time. The sqlite3btreelocktable part of the function is implemented as follows:

Sqlite3btreeenter (P);//Determine if lock is conflicting rc = Querysharedcachetablelock (P, ITab, LockType); if (RC==SQLITE_OK) {  //locking  rc = Setsharedcachetablelock (P, ITab, LockType);}  

As can be seen from the above code, if the lock conflict, the direct report sqlite_locked error, otherwise add the lock. Note that the lock and lock process is protected by the mutex of the Btshared object (Sqlite3btreeenter,sqlite3btreeleave), so the lock process is serial and the Table-lock list is not manipulated concurrently by multiple threads.

Querysharedcachetablelock Code Logic

/* If Some other connection are holding an exclusive lock, the** requested lock could not be obtained.*/if (pbt->pwriter!= P && (Pbt->btsflags & bts_exclusive)!=0) {       sqlite3connectionblocked (p->db, pbt->pwriter- >DB);     return Sqlite_locked_sharedcache;}

Setsharedcachetablelock logic

for (piter=pbt->plock; Piter; piter=piter->pnext) {    if (piter->itable==itable && piter-> pbtree==p) {       plock = Piter;    break;  }} /*create a table lock*/if (!plock) {   Plock = (Btlock *) Sqlite3malloczero (sizeof (Btlock));   if (!plock) {       return sqlite_nomem;  }  plock->itable = Itable;plock->pbtree = P;plock->pnext = Pbt->plock;pbt->plock = PLock;}

Traverse the Btshared object already has the chain table, compare itable and corresponding Pbtree is the same as itself (whether it has been added), if not, then request the lock object, join the list.

Lock Process

We know that there are two types of SQLite log mode, the default delete mode and the Wal mode, below I will introduce the open shared cache mode, the update operation of the lock process, the main change is table-lock.

Normal log mode + shared cache mode

1. Open transaction: Shared-lock[sqlite3btreebegintrans]
2. DML operations:

• File Lock, Reserved-lock
• Table-lock,
  Locks the corresponding table, and the read and write locks of the same table are mutually exclusive
• Read the page corresponding to the table

3. Submit:

• Add Execlusive-lock [Sqlite3pagercommitphaseone, brush log]
• Delete log files
• Release Execlusive-lock
• Release Table-lock

Wal log mode + shared cache mode

1. Open transaction, Shared-lock[sqlite3btreebegintrans]
2. DML operations

• Data file lock, Shared-lock
• Wal log file Write-lock
• Table-lock

3. Submit

• Release Table-lock
• Release the log file Write-lock
• Releasing the data file Shared-lock

SQLite Learning Notes (vi) && shared cache