SQLite Forum

I have a workflow wherein there are several dozen threads performing reads and writes continuously for multiple hours.  Each reader might be fetching dozens of values from each row.  Concurrent readers share a single read-only connection created with full mutex and private cache options <code>(SQLITE\_OPEN\_FULLMUTEX \| SQLITE\_OPEN\_PRIVATECACHE \| SQLITE\_OPEN\_READONLY)</code>, and writers use a single writeable connection also created using full mutex and private cache option flags <code>(SQLITE\_OPEN\_FULLMUTEX \| SQLITE\_OPEN\_PRIVATECACHE \| SQLITE\_OPEN\_READWRITE)</code>.

It came to my attention that every call to <code>sqlite3\_column\_*</code> results in a lock on the db connection mutex.  With this many field fetch operations vying for the mutex, we end up with a huge backlog behind busy ‘step’ calls, and performance can suffer.  I tentatively introduced a new interface within sqlite3 which fetches all desired fields from a row under a single lock of the connection mutex.  This allows each row fetch to quickly get what it needs and get out of the way.

Note that this code is based on v3.27.2 source.
 
```c
/* Support fetching multiple column values under a single lock of the db mutex. */
typedef struct sqlite3_value_lookup sqlite3_value_lookup;
struct sqlite3_value_lookup
{
  int column_index;      // in:  index of the column to fetch: 0-N
  sqlite3_value *value;  // out: the value retrieved
};
SQLITE_API void sqlite3_bulk_values(sqlite3_stmt*, sqlite3_value_lookup *columnData, int columnCount);
 
 
/*
** Fetch multiple column values under a single lock of the db mutex.
** This is akin to using an array to hold multiple sqlite3_column_value results.
*/
SQLITE_API void sqlite3_bulk_values(sqlite3_stmt *pStmt, sqlite3_value_lookup *columnData, int columnCount)
{
  Vdbe *pVm;
  Mem *pOut;
  int itr;
  int i;
 
  pVm = (Vdbe *)pStmt;
  assert( pVm );
  assert( pVm->db );
  sqlite3_mutex_enter(pVm->db->mutex);
  for( itr = 0; itr < columnCount; ++itr ){
    i = columnData[itr].column_index;
    if( pVm->pResultSet!=0 && i<pVm->nResColumn && i>=0 ){
      pOut = &pVm->pResultSet[i];
    }else{
      sqlite3Error(pVm->db, SQLITE_RANGE);
      pOut = (Mem*)columnNullValue();
    }
    if( pOut->flags&MEM_Static ){
      pOut->flags &= ~MEM_Static;
      pOut->flags |= MEM_Ephem;
    }
    columnData[itr].value = (sqlite3_value *)pOut;
  }
  sqlite3_mutex_leave(pVm->db->mutex);
}
```
 
Whether or not this is suitable as a new feature, is there anything wrong with this approach?  Is there anything I’m overlooking?