I would like to add: If you have multiple readers sharing a connection and they share the same prepared statement, none of them can be expected to receive the full result set, nor can it be guaranteed that the fields values retrieved will belong to the same row of the result set. A prepared statement has but one notion of the "current row", so if thread A retrieves N fields (N > 1) of the result row while other threads are busily calling sqlite3_step(), the retrieved values may be from up to N different rows of the result set.
Even if readers take care to prepare their own statements, an implicit transaction "created" by the serially first statement will not complete until all statements have "completed" (see SQLite documentation for the exact definition of "creating" and "completing" transactions). Attempts by one thread to complete an explicit transaction (created by itself or a different thread) will possibly affect all threads sharing the connection. Separate connections allow isolation of transactions (the I in ACID); if you choose to share a connection, you must take care to achieve the necessary isolation yourself. -----Ursprüngliche Nachricht----- Von: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Igor Tandetnik Gesendet: Freitag, 01. Juli 2016 01:53 An: sqlite-users@mailinglists.sqlite.org Betreff: Re: [sqlite] Readers Sharing Connection On 6/30/2016 12:39 PM, Jarred Ford wrote: > How are multiple concurrent readers that share the same connection handled? A connection has a mutex. Every API call locks it on entry and releases on exit. > Do I need to create a separate connection for each reader in order to keep > these from running serially? Not necessarily. Multiple readers on the same connection can interleave (if you have a SELECT statement, fetching each row and each value in a row is a separate API call, so multiple readers can take turns getting their rows). This may or may not be sufficient for your application. If you have a difficult query that takes a long time to produce a row, then all readers would be locked out while it runs. If you just scan tables with simple fast queries, then interleaving may be good enough. > Will writers on separate connections block the readers from running queries? Yes in journal mode, no in WAL mode. > I'm using PRAGMA read_uncommitted = true This is only meaningful when using shared cache mode ( https://www.sqlite.org/sharedcache.html ). It does nothing otherwise. -- Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
