On Fri, Aug 04, 2017 at 09:33:31AM +0200, Eric Grange wrote: > The main problem with multiple writers would be in conflict resolution, > locking and deadlocks.
There is plenty in the literature about this. You have to code more defensively, you may need things like "FOR UPDATE", etc. > 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 I write that like so: SELECT value FROM accounts WHERE ...; -- store this in a variable UPDATE accounts SET value = value + 1 WHERE ... AND value = :value_read_earlier; This is how one is supposed to get atomicity in LDAP too, incidentally. So, if you were creating a SEQUENCE kind of thing (see the "sequencer" thread this week too) then I'd return 1 + value_read_earlier. This approach works well and is portable! But it does require that you think about concurrency :( > 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.... Yeah, "don't do that"; see above. > And once you have more than one query, things become even more complicated. Things are already this complicated if you use PostgreSQL, SQL Server, Oracle, ... There's nothing new here, except that plenty of code wirtten with SQLite3 in mind may need to be modified to be safe with higher write concurrency enabled (so it probably shouldn't be enabled by default). > 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. Yes, you have to be aware of varying synchronization/locking features (e.g., "FOR UPDATE") and write concurrency semantics. Nico -- _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
