This was a standard feature of flat file databases in the early 90s. There were many products. They often had an ODBC driver, which provided a SQL front end. dBase .dbf files were often used for storage. The arcane file locking in Windows is intended for exactly this kind of application.

Apart from quality (!), SQLite's main advantage over these products is broad platform support. And continued existence.

We still have a multi user desktop application that uses the Borland database engine and dbf files over a windows file server. The BDE is no longer supported but it still works

Unix like operating systems also allow locking files for on disk databases (like mbox files) it’s just not what every application does by default.

Used to love dbm files. 1980s serverless NoSQL. They're still totally usable although we have LevelDB nowadays too.

In the old days of DOS I could do that with Clipper 5.2. Two or more PCs using Netbios shared directories could work on the same database, provided that they wouldn't write to the same record. That wasn't a problem because the environment (can't recall for sure if it was Clipper or an external library) allowed single record locking, so I enclosed the lock attempt into a timed spinlock-like block which attempted once per second for 10 or 15 seconds before obtaining the lock or failing with a record busy error. No SQL involved however, and indexes had to be rebuilt by hand like twice a day to be safe. But those were the days when a 486 could crunch a 100.000 records db with multiple indexes very easily.

This is the first time I've heard Clipper mentioned in a looong time. I was just talking the other day how useful it was in those days that there were tools that included the database and UI all in one. Everything was a bit easier because the language assumed a database, rather than just using a library to interface with one.

Pretty much all dBase derivatives had both file-based and record-based locking, that was specifically designed to work in a networked environment with file shares (although NetWare was much more common than NetBIOS back in the day). RLOCK() was the function you had to use for records.

MS Access “allowed” it but depending on the version it could be quite problematic. We had a use case where Tableau connected to an Access file read only (but which another program used as a data store and wrote to it often) on an Windows file share, and once in a while the lock files would get screwy and we would have to manually delete the lock files to get things working again. Deleting lock files could be a huge chore because you have to figure out stuck processes and kill them. Task Manager wasn’t up to the task, so had to use SysInternal tools to help with that.

Access is really meant for single-user scenarios I feel. Maybe the locking mechanism has gotten better but for multiuser access I tell people to use a real SQL database.

It was ok pre-windows 2000. I had access 97 it on 9 workstations on NT with never a problem! Moment office 2000 and windows 2000 came out it went to hell. Moved to SQL Server then. I am not sure what changed.

It was quite frankly the most productive custom business software package I have ever used. Literally would take 10 people a week to do something custom you could do in an afternoon with 1 person in Access. I suspect the same is true now.

We had a much heavier multiuser setup on Access 97, and it worked fine. We had the same breakdown as you did, and had to do a registry edit on all users' machines to keep it limping along until we could move to SQL Server, which was the right thing to do anyway.

Interesting. Nice to know it wasn't just me. There was no one to talk to about it back then so you had to think on your toes.

SQL Server was a crap load easier to back up reliably

> Access is really meant for single-user scenarios

In a lot of cases, I've found it to be the best tool for a temporary or one-off (preferably smaller scale) data mining/massaging project. The query-building interface was the way I originally learned the basics of relational databases, and it also helped me get a better grasp of SQL-- the ability to flip back & forth from the GUI query builder to the SQL it generates is nice.

On the multiuser side, however, I have found a couple workarounds in the past. If you have everyone operate locally and space out their central database connections to intermittent, automated burst queries, you can get more concurrent users than you might expect. It helps to have fewer users per table, as well, and of course it really helps if they don't need to see the most recent adds/changes in real time.

My recollection was that the automated wizard loved to nest statements and it was a huge chore to manually massage stuff after the fact; almost incomprehensible to parse. but I agree that it was a real godsend for people trying to branch out from the limitations of excel.

Yeah, Access' Jet Engine and many of its issues are very well known.

[https://en.wikipedia.org/wiki/Microsoft_Jet_Database_Engine]

And all xBase based programming languages, Clipper, FoxPro, Visual Assist, and their competition Paradox.

I loved the Clipper 5 OOP capabilities, sadly Visual Objects tried to be too much like Visual Basic and some of the easiness was lost.

Since Access 97 or so it could actually use MS SQL as a backend which largely removed those issues; the Access "database" was then merely a VBS GUI for SQL Server. This works pretty well.

I am not sure about MS Access. From what little I know 2 people opening from network drive would mostly result in database being corrupt.

MS Access "allowed" it. SQLite actually works.

Access is actually designed to work in a multiuser situation over a LAN for concurrent read and write. SQLite isn't afaik. As long as the LAN was cabled I never saw any issues. The only reason it was necessary to move to a server type database was because people were insisting on wifi networking.

It might have been designed for it. It was poorly designed for it. It was a clustfuck of corruption when actually utilized

You basically had to engineer around the corruption risks for anything actually in production. Data redundancy, old-school 'Save' buttons (your work isn't properly saved until you click it, because it sends copies of that work to multiple backends) and isolating users as much as possible to their own 'shards' was part of how I saw it kludged through in the real world.

There was still a lot of weird behavior, though, and while you could reduce data loss you couldn't eliminate it.

Penny wise pound foolish. I had a president waste two to three hours a day instead of using a 40 dollar service to take care of the incredibly simple and repetitive task. Eventually when I was planning to leave anyways I asked him what his time was with per hour. I guess it was less than minimum wage at the time. (5.25 an hour)

I'm probably going to have nightmares filled with screens of the access database corruption dialogs

What's anyone's time worth? Might have kept him from doing something foolish.

Valid point. He did defraud the federal government for 600k. If he had more time on his calendar he could have fucked over the American taxpayers ever more.

It depended. It suited a small cabled office with 10 to 15 computers fine. I saw such setups work fine for 12 years without corruption. But when wifi came along people started connecting that way, sometimes unintentionally, and corruption became an issue. So then we bit the bullet and moved the backend to a server database. Not a single database corruption since.

But this is the difference between supporting concurrent access or not. The fact that the feature relied so much on network reliability means that race conditions still existed.

I'm not arguing with you but the fact remains that unlike SQLite, Access doesn't lock the database file so that only one user can write at a particular moment, it's more granular than that. It's also the fact that this worked very well for databases on small cabled LANs where no more than a dozen-ish computers might be interacting with the database at any one time. It was never designed for use over the internet (having said which I have maintained a forum running an Access mdb file as its backend since forever without a hitch the forum software process does the database edits so it's like a database server process in a way I suppose).

I'm not sure I understand what the physical transport has to do with this. Can you please expand a bit?

Maybe disconnect and reconnect cause windows network share to lose locks or something like that.

Yes, for Access (and sqlite?) it is the client computers that do the edits directly to the database file, rather than via a mediating server side process, so they are particularly vulnerable to network interruptions while in the middle of an edit. Cable is far more reliable than wifi and so far fewer interruptions. I think that's it.

Ok but you cant really say you support network access if you rely on it to be flawless. Working fast, maybe but not reliably working if your network is not super reliable is not really support.

It was a different time.

Back in the day, which was a Wednesday

Race conditions not triggering when the transport is fast enough?

I understand the distinction now :), thank you. Funny.

Good point.

Berkeley DB also supports multiple processes accessing a database concurrently, as far as I know.

I was wondering if the authors were referring to SQL-like databases, but MS Access seems to be one?

Yes, MS Access supports both SQL and non-SQL API, and not mentioning it isn't very professional from the author.

Is there anyone actually choosing MS Access for new projects in 2020?

They were indeed referring to SQL servers. The paragraph starts with

> Most SQL database engines are client/server based.

I skipped it for brevity.

It did allow it but getting it running reliably was basically getting punched in the face on a daily basis. Act! Was similar except they included getting kicked in the balls two to three times a week.

The reason access was popular was it made any middle manager that could wield an Excel spreadsheet think they could build a database.

I have a sales guy buddy with a 50K contact database in Act! that runs a Win95 VM to maintain access to this data. I've tried to figure out how to migrate it, but it is more than a few hours and who has that kind of time?

Didn't prevent it is closer to my recollection. My first job was on a system that used this idea pretty heavily - what a nightmare! We only got that code stabilized once we removed any sharing (and later removed Access completely).

Not open source, but Raima supports multiple processes on a single db file as well. https://raima.com/

FoxPro/Foxbase as well. In the Foxbase days predating FoxPro, there was even a “multi-user” version that, unsurprisingly, cost more than the standard single user.

And plenty of other file-based DBs such as Borland’s Paradox and the db engine, dBase; pretty sure FileMaker, too.

HSQL does too I believe.

JET database are what MS Access used underneath, and attempting multiple concurrent usage on JET databases is a good way to corrupt them.

Waaaaait wouldn’t that mean the file system is the server, with some binary API and responsible for handling concurrent access and locks for the entire file? LOL.

Serverless in this situation means that you don't really have to provision or setup an actual server to handle the database, the client itself just need the ability to read and write an SQLite file.

Because many of the locking things are outsourced to the file system which acts as server for concurrent threads.

A server is something that listens for commands from various clients and executes them.

That just circle jerk. There is an agreed upon def of serverless.

If we reduce things to the absurd we stop being able to reason about things.

https://en.m.wikipedia.org/wiki/Serverless_computing

>There is an agreed upon def of serverless.

Sorta but not really. The fact people have worked backwards from marketing names to try and constructively define inherently self-contradictory branding (rather than create a descriptive category into which we place questionable names and ignore them) is an embarrassment for everyone except the marketing departments.

I honestly too dislike all this naming fad and feel the internet’s been taken over by the management and marketting folk .. but still, i try not to hyperbole about it - too bad, but its sorta ok, and it ultimately irrelevant for the job, dbConnection is simply remote.

It's called a buzzword, nothing more nothing less, it's like "cloud computing", eventually it becomes meaningless.

We now have kubernetes tho, and openshift. And i actually believe in docker swarms and compose battalions.

Ill give u that its hard to discern when a thing is a real change and when it isnt, as the titans of industry try to peacock around.

Id just examine what they actually mean, for good measure. Theyre trying to sell things they have no idea about - but thats why the hierarchy of commerce is.

Thank you for saying this. People seem to argue semantics to appear clever and it really pollutes communication and reasoning.

That is not the definition of serverless in question.

If you examine it closely, youll see that they have alot incommon, and that the author defined the term neo-serverless to attempt to adress this - both definitions share the fact that multiple applications (in clientless form) can access the database. He even gives amazon S3 as an example. For neo serverless.

I agree with you that it take a bit to marry both, but the stretch isnt far.

Im also avoiding criticising the Author for not sticking to the main def, since id then be red-herringing the post.

> both definitions share the fact that multiple applications (in clientless form) can access the database

That is like the least relevant thing on that entire page, to be frank. "SQLite is Serverless" is specifically referring to SQLite being an embeddable library that runs in the same process (and same thread, even) as your application vs. the client-server architecture (database in another process, with communication via a port) that DBMSes like MySQL and co have.

> Im also avoiding criticising the Author for not sticking to the main def

The "main definition" (i.e. the web dev buzzword) came into being years after this post was written.

You are right

Well you effectively report some of the workload normally handled by the server to the OS's filesystem layer, that's true. In particular you rely heavily on the FS locking working correctly. Calling the FS a "server" is a bit of a stretch though.