I am new to this forum, so please be adjusting of me if i ask some noob level questions.

Why cant sqlite have multiple writers?

My theoretical understanding is if you open a file in append only mode by 2 process, each process can append data without overwriting each other, now i further understand the support for append is weak in other systems apart from linux or may even differ, but then whats stopping to write a module with shared memory or file or something equivalent to maintain a counter and allocate exclusive space for different writers such that they dont overwrite each other in same file a little like MVCC but instead of version of same data its writing different data from different writers..

I understand there will be more issues internal to sqlite to manage other ds but i feel they can managed with some clever solution, the real limitation should be file cannot accept multiple writer, but they do in an append only mode..

Does this click some thought?

Not even big RDBM client/server systems can do multiple writers that affect the same data. They may have sophisticated locking capabilities down to record (or even field) level locks; this gives the illusion of supporting multiple writers, but only as long as there is no contention for locks. The moment that two "independant" writers want to update the same entity, this illusion breaks down and at least one of the writers is forced top roll back their transaction.

SQLite as an embedded library is designed around file level locks which are relatively fast and easy to handle, but restrict you to one writer per database file at a time.

I may be wrong but as a DB system it should be able to provide multiple writers to a same sqlite file is my recommendation here, if to writers wants to update same at same time fine we should be able to give them that provision with complete data integrity, what meaning that data makes to them is app driven logic..

I am talking here about lockless system like MVCC where updates to same row can be made by just appending to the file at the same time without any locks. So i think there is no illusion here its a well known practice in DB world... i feel

Let's take foo and bah case for the same field

Just simplifying with files, assume W1 comes appends f1=foo, W2 comes appends f1=bah to the same fil, when reading, you read the file bottom up since on the last line you found f1 when you read the second line f1 is ignored..

Similarly when 2 writers comes at the same time they append without waiting for any locks.. reader eliminates the duplicates while reading so at writing time there is no locks involved

If I look at what resources would be common then it would be the file size counter incrementing for each append which is an atomic operation on CPU level..

Thus you can enable n writers to come at the same point in time and writer their values..

Depending on your app logic you can either read only last one and ignore all other or give application a chance to select which value it wants to read since theoretically all happened at the same time..

To avoid reader crashing into a writer when it's active you can have an EOP SOP markers to mark valid data ready to be read..

Hope this helps

This is an excellent demonstration of data corruption. You are absolutely losing any semblance of ACID in your transaction.

Assume the set of related changes (aka transaction T1) applied by W1 includes updating f1 from 'old' to 'foo'.
Assume the set of related changes (aka transaction T2) applied by W2 includes updating f1 from 'old' to 'bah'.

Assume that both transactions run independantly and sequentially would have resulted in f1 being 'new'.

There is no way to sort out this mess. T2 has lost Atomicity - it is only partly applied, one of it's changes is lost. The database has lost Consistency - the correct value of f1 is never attained.

Not sure why you thought of performance is what i am after, i am not my request was just like multiple process can read the same DB file, i wanted to understand what is the challenge to provide multiple writer? specially when systems like append-only data structures exists..

Good starting point. 
https://www.sqlite.org/lockingv3.html

I never said I understand sqlite, infact I am noob like i said in the original first post.. but am a big fan of the embedded database strategy..

Transactions in append only file formats are durable and also have order the append order..

I am not asking for a coordinator process but all we need is to do is coordinate of the underlying file size and keep appending ie adding to it as a shared counter..

I completely understand file format compatibility is number 1 priority, but we can always achieve the said by having extra file just like Wal which will be home for multiple writers and possible move data to sqlite format on periodic time..

Again my interest is in suggesting this concept of achieving multiple writer but the community can tell me if this a feasible idea..

That's called snapshot, Each transaction starts with a snapshot[simply say file size here] so when final commit happens transaction just compares the snapshot[file size] if no data is mutated it remains same else it's changes and we can throw serialization error for the transaction..

Again all this is common practice in PG and other ACID compliant RDBMS and solved problems.. you may want to read up on MVCC and Snapshotting

So wondering what's stopping sqlite from providing multiple writers? 

Till now I could gather file backward compatibility as P1 kind of issue for providing multiple writers..

A queue or multiple temp files can be created (doesn't have to be in SQLite)
If using a server this is not needed.  SQLite only takes milliseconds between writers.

Create Thread Writer(UserNum)to hthread  //create threads
// WaitForSingleUser hThread             //wait for thread(s) to finish
// Process temp files

THREAD FUNCTION Writer (UserNum AS LONG) AS LONG
 OPEN "temp" + UserNum                  // create database
 create table if not exists t(c text)   // create table
 INSERT into t(c) values(?),binddata    // append
END FUNCTION

If it can be done in sqlite then it's the best option as this gives everyone out of the box ability to have multiple writers, I can always create a wrapper that's not the point..

Also having central server is muted point, I would move to ready solutions like PG etc..

The best part of SQLite is it's embedded nature which simplifies a lot of things the only point in my opinion that is holding it down is multiple writers in my view

PG being Postgres.
SQLite may be 50-times faster using transactions (read a recent comparision.)   
Are writers other processes that require TCP or some other way to connect?

https://postgrespro.com/blog/pgsql/5967899

SQLite CAN have multiple writers (they wait for others to finish)

https://www.sqlite.org/rescode.html#busy
https://www.sqlite.org/c3ref/busy_timeout.html

PRAGMA busy_timeout; 
Pragma busy_timeout=10000     default in milliseconds.  Retry on error error 5, 6