Yes this would be the case if no one closes the write transactions
before reading.
Would a possible solution be to use "read uncommited" or to include a
kind of timestamp or autoInc identifier used internally by sqlite engine
? even if I am not sure it would be enough to avoid conflicts...
sqlite was not designed for this kind of access but It would be great to
have a higher level of concurrency
Luc
Le 04/08/2017 à 09:33, Eric Grange a écrit :
The main problem with multiple writers would be in conflict resolution,
locking and deadlocks.
Imagine a simple accounting transactions "update accounts set value = value
+ 1 where ..." if run at the same time from 2 threads (or close enough),
then if you do not have conflict resolution in place you may end up
increase value by only 1
1) alice starts transaction
2) bob starts transaction
3) alice reads value, sees 10
4) bob reads value, sees 10
5) alice writes 10+1
6) bob writes 10+1 ooops....
And once you have more than one query, things become even more complicated.
Even among full relational DBs, few manage these correctly. IME only
PostgreSQL and Firebird handle these correctly by default, for Oracle or
MSSQL you have to use special locking modes and transaction options with
significant performance penalties.
Eric
On Fri, Aug 4, 2017 at 8:50 AM, Luc DAVID <lucdavid....@free.fr> wrote:
Hello,
I was thinking about a possible solution for sqlite "only single writer is
allowed at the same time" and database lock.
sqlite has WAL mode for better concurrency and this could maybe be used to
extend the number of writters:
Do you think it would be possible to create a
MyDb.WAL001...MyDb.WAL.002...MyDb.WAL.nnn when a write operation is
currently running in order to allow more writers?
The sqlite engine would then take care of dealing with all the WAL files
when reading data, backup...etc
The maximum of allowed writers could be set by a pragma or another mean
(when opening the db)
It seems a simply way to boost sqlite concurrency.
Am I wrong on this point ?
Best Regards
Luc
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
