Come eseguire la query SQLite con un lettore di dati senza bloccare il database?

Sto usando System.Data.Sqlite per accedere al database SQLite in C #. Ho una query che deve leggere le righe in una tabella. Durante l’iterazione delle righe e mentre il lettore è aperto, è necessario eseguire determinati aggiornamenti SQL. Sto correndo in un’eccezione “database è bloccato”.

La documentazione di SQLite afferma:

Quando un processo vuole leggere da un file di database, ha seguito la seguente sequenza di passaggi:

  1. Apri il file di database e ottieni un blocco SHARED.

La documentazione afferma inoltre del blocco “SHARED”:

Il database può essere letto ma non scritto. Un numero qualsiasi di processi può contenere blocchi SHARED allo stesso tempo, quindi possono esserci molti lettori simultanei. Ma nessun altro thread o processo è autorizzato a scrivere sul file di database mentre uno o più blocchi SHARED sono attivi.

La FAQ afferma:

Più processi possono avere lo stesso database aperto allo stesso tempo. Più processi possono fare un SELECT allo stesso tempo. Ma solo un processo può apportare modifiche al database in qualsiasi momento, comunque.

Il libro The Definitive Guide to SQLite afferma:

… una connessione può scegliere di avere un livello di isolamento non letto senza usare il pragma read_uncommited . Se è impostato su true , la connessione non inserirà blocchi di lettura sulle tabelle che legge. Pertanto, un altro writer può effettivamente modificare una tabella in quanto la connessione in modalità read-uncommitted non può né bloccarsi né essere bloccata da altre connessioni.

Ho tentato di impostare il pragma per leggere senza commit all’interno dell’istruzione comando query SQL come segue:

PRAGMA read_uncommitted = 1; SELECT Column1, Column2 FROM MyTable 

Un aggiornamento SQL sullo stesso thread che utilizza una connessione diversa non è ancora riuscito con un’eccezione “database è bloccato”. Ho quindi provato a impostare il livello di isolamento per leggere senza impegno sull’istanza di connessione. Ancora nessun cambiamento con la stessa eccezione.

Come posso ottenere che un lettore di dati aperto esegua il loop delle righe nel database senza bloccare il database, in modo che possa eseguire gli aggiornamenti?

Aggiornare:

Entrambe le risposte qui sotto funzionano. Da quel momento, però, mi sono allontanato dall’usare il journal di rollback predefinito utilizzando ora il Write-Ahead Logging, che fornisce una maggiore concorrenza di letture e scritture di database.

Usa la modalità WAL .

Non ero in grado di farlo funzionare utilizzando il fornitore di dati open source da qui . Tuttavia, sono riuscito a farlo funzionare utilizzando la Standard Edition gratuita di dotConnect come segue:

Crea l’importazione di sotto DLL, in modo che possiamo abilitare la cache condivisa per SQLite.

 [DllImport("sqlite3.dll", CallingConvention = CallingConvention.Cdecl)] public static extern int sqlite3_enable_shared_cache(int enable); 

Esegui la funzione sopra per abilitare la cache condivisa. Nota, questo deve essere eseguito solo una volta per l’intero processo – consultare la documentazione di SQLite .

 sqlite3_enable_shared_cache(1); 

Quindi anteporre l’istruzione della query SQL utilizzata dal lettore di dati con l’istruzione pragma come segue:

 PRAGMA read_uncommitted = 1; SELECT Column1, Column2 FROM MyTable 

Ora è ansible aggiornare e inserire liberamente le righe mentre il lettore di dati è attivo. Qui è ansible trovare ulteriore documentazione SQLite sulla cache condivisa.

Aggiornare:

Una versione più recente del fornitore di dati SQLite Devart ora lo supporta in modo migliore. Per abilitare la cache condivisa si può effettuare la seguente chiamata:

 Devart.Data.SQLite.SQLiteConnection.EnableSharedCache(); 

È ansible configurare le letture non vincolate nella stringa di connessione, ad esempio come segue:

 Devart.Data.SQLite.SQLiteConnectionStringBuilder builder = new SQLiteConnectionStringBuilder(); builder.ReadUncommitted = true; builder.DateTimeFormat = Devart.Data.SQLite.SQLiteDateFormats.Ticks; builder.DataSource = DatabaseFilePath; builder.DefaultCommandTimeout = 300; builder.MinPoolSize = 0; builder.MaxPoolSize = 100; builder.Pooling = true; builder.FailIfMissing = false; builder.LegacyFileFormat = false; builder.JournalMode = JournalMode.Default; string connectionString = builder.ToString();