[Info-vax] VMS databases

[Jake Hamby (Solid State Jake)]

On Saturday, November 18, 2023 at 3:44:00 AM UTC-8, Neil Rieck wrote:
> On Friday, November 17, 2023 at 8:10:25 PM UTC-5, Arne Vajhøj wrote: 
> > I just did another little article. 
> > 
> > https://www.vajhoej.dk/arne/articles/vmsdbx.html 
> > 
> > providing some examples for the databases available on VMS (Rdb, MySQL, 
> > SQLite, Mimer, Derby, H2, HSQLDB) in various programming languages 
> > (Cobol, C, Pascal, Java, Jython, Python, PHP). 
> > 
> > Nothing surprising and a lot of overlap with previous 
> > articles, but a different perspective. 
> > 
> > Arne
> This is a very impressive publication. On behalf of the OpenVMS community I say "thanks!" 
> 
> Two additional points. 
> 
> 1) I've done a bit of hacking with SQLite (on both OpenVMS + Linux ) and can inform that it should only be used in single user applications. When any process issues an "update table" command, all other processes are locked out.

I’ve also long considered SQLite to be basically only useful for single-process use. SQLite 3 has file locking support using various OS-specific APIs but I’m not sure how efficient or concurrent it is in practice.

I see Arne’s blog post doesn’t appear to mention the native VMS port of SQLite by David L. Jones. I downloaded it and looked at it very briefly and it has a custom locking implementation using a “lock nexus file” (default sys$login:sqlite3_lock_nexus.nxs). Code comment: “The intent is the actual files are unlocked and shared access is coordinated through locking of records in the nexus files (which allows waits) as a proxy.”

There’s clearly a lot of effort that has gone into the native port but I’m not sure if it matters much when multi-process access doesn’t seem to be a common use case for SQLite. It’s also an unusual choice to create a separate file for lock management and put it in the user’s home directory. What if you want to make the database file accessible to multiple users?