SQLite3 and locking

[Ben Armstrong]

The other thing we'll need to worry about with SQLite3, now that we've shown that the code builds and runs properly, thanks to JFP's patch, is locking.

Our first simple test on OpenVMS V8.3 is to just open two sqlite3 sessions on the same database file. The second one fails to open the file, presumably because by default an open in CRTL opens for exclusive access, unlike on Windows or Linux.

Our second test was on OpenVMS V7.3-2 with the DECC patched up to ACRTL V3.0. When we tried to open a single sqlite3 session on a database file it failed, issuing this error:

$ sqlite3 foo.db
SQLite version 3.5.7
Enter ".help" for instructions
sqlite> create table bar(a,b);
SQL error: database is locked

While we can accept for the moment that we may not be able to get this to work at all on VMS V7.3-2 (though we would really like it to work there,) it is crucial that we solve the file locking problem before we can proceed. Can anyone help with either problem?

Thanks,
Ben

[Willem Grooters]

Ben,

One of the things I was told that should be checked (and altered) was just the flck functionality - that seems to work differently on Unix. I understood that flck uses posix-based locking. IIRC, Posix locks are user-mode locks on byte ranges. To have this working properly on OpenVMS, you may have to redesign the relevant functions in os_vms.c to behave like expected by SQLite.

It may well be that these locks are the different behaviour between CRTL on OpenVMS 7.3-2 and 8.3. It should be pointed out in the CRTL documentation.

[Jean-François Piéronne]

Ben,

I have already fix this. But I have found another problem I'm trying to solve.
If a program open twice the database the second connexion failed with a disk I/O error.

JF

[Ben Armstrong]

That's great news. So I guess we'll wait while you work on the locking stuff and I'll turn my attention for now to SWIG and Ruby bindings, as I don't think we'd add much, if anything, to helping to get locking working. I'd be happy to test any new code when it is ready.

Thanks,
Ben

[Jean-François Piéronne · Accepted solution]

I have commit the code in the repository.

http://hg.vmspython.dyndns.org/vmspython/rev/cda4c8dae858

Let us know is this work (no database corruption when 2 sessions update simultaneous a database).

JF

[Craig A Berry]

JF's solution is one way to do it, and a good one, but I think

$ define DECC$FILE_SHARING ENABLE

would do the same thing. It's more robust to have the change actually in the code and not depend on the environment, but then you have to maintain your changes.

When a porting issue can be taken care of entirely by a feature setting, the intersection of most robust and most maintainable is probably to call decc$feature_set_value from within a LIB$INITIALIZE psect that you link into your application. This isn't always feasible with a library that may be linked in by many applications.

[Ben Armstrong]

OK, the latest version from your repo does work on V8.3. We tested locking a bit and it appears to work as documented. Thanks!

Now as a subgoal of this project we want to reach back as far as we can in our supported client base. The current floor version we support is V7.1. We found when we went back that far (building on a V7.3-2 system against V7.1 libraries) we had compile errors relating to big file support. And when we tried on V7.3-2, we had the runtime problem mentioned earlier in this thread, i.e. "Database is locked" (single process only).

We don't really need big file support, as the databases are going to be small. I'm wondering if we could make sqlite3.exe in two flavours, a small file and a big file version. How much work would that take?

Are there any other "show stoppers" that would prevent us from supporting all of the way back to V7.1? Would we be able to get some kind of locking working that far back (not necessarily Posix)?

[Jean-François Piéronne]

Craig,

I agree that the use of DECC$FILE_SHARING ENABLE is simpler but this is a global setting and as sqlite is an embedded library this will affect other library. In this case the Ruby interpreter.

Python use the LIB$INITIALIZE solution and the behavior can be change using python_xxx logical, for example PYTHON_FILE_SHARING, so this will not interact with others programs.



Ben,

sqlite work on OpenVMS 7.3-2 using the latest acrtl patch, I have include it in Python and it pass all tests except those using multiple connexions.

to remove the support of large file just compile without the _LARGEFILE define.



JF

[Ben Armstrong]

I thought I had checked that we were up to date with ACRTL 3.0, and yet it still doesn't work for us, giving us this "database is locked" error. Am I wrong about what the "latest" is for ACRTL on V7.3-2?

[Jean-François Piéronne]

Oops, sorry I have done the test on the wrong system.

You're correct it work on VMS 8.3 which is the minimum version supprorting byte range locking using fcntl anf F_SETLK and it don't work on 7.3-2 which doesn't have this feature.
Sorry my mistake...

JF

[Willem Grooters]

....VMS 8.3 which is the minimum version supprorting byte range locking using fcntl anf F_SETLK and it don't work on 7.3-2 which doesn't have this feature.

Like I suspected. The only way to get around it is to redesign and recode locking and do it the VMS way, to make it compatible with older VMS versions.
For larger databases, I wouldn't use SQLite (as the name suggest (and the roiginal coders admit) it is a light-weight SQL engine - not a database. Big stuf needs big databases (like MySQL, if you want/need to use Opensource)

[Craig A Berry]

Note also that the byte ranging locking features don't look like they'd work on large files because they only lock ranges that can be stored in 32-bit integers. The v8.3 New Features manual covers it here:

http://h71000.www7.hp.com/doc/83FINAL/6679/6679pro_008.html#byte_range_locking_sec

[Ben Armstrong]

So these are arguments against big files (or at least making the small file version be the default and a separate sqlite3_bigfiles.exe be the exception).

As for the byte-range locking, it seems like a bit of a frill to us for our application. For the small databases with low transaction rates we want to use this for, I expect we can get away with global file locks. It would be nice if a single binary provided both: byte-range locking when available and degrade to a file lock when it isn't. In fact, isn't that what SQLITE_ENABLE_LOCKING_STYLE is supposed to accomplish? But our first attempts to use this failed. I can furnish exact build/run-time errors if this seems like the right direction to go ...

Ben

[Ben Armstrong]

Ah. It is simply this:

#include
.^
%CC-F-NOINCLFILEF, Cannot find file specified in #include directive.
at line number 18098 in file DSA0:[BH.SQLITE_A]SQLITE3.C;3

And if I put that inside an #ifndef __VMS and also sys/mount.h, then I get a whole slew of errors. I'd have to do some digging through doc to solve these. Am I on the right track, though?

#pragma mark AFP Support
........^
%CC-I-UNKNOWNPRAGMA, The pragma "mark" is unrecognized.
at line number 19653 in file DSA0:[BH.SQLITE_A]SQLITE3.C;5

#pragma mark flock() style locking
........^
%CC-I-UNKNOWNPRAGMA, The pragma "mark" is unrecognized.
at line number 19968 in file DSA0:[BH.SQLITE_A]SQLITE3.C;5

#pragma mark Old-School .lock file based locking
........^
%CC-I-UNKNOWNPRAGMA, The pragma "mark" is unrecognized.
at line number 20061 in file DSA0:[BH.SQLITE_A]SQLITE3.C;5

#pragma mark No locking
........^
%CC-I-UNKNOWNPRAGMA, The pragma "mark" is unrecognized.
at line number 20174 in file DSA0:[BH.SQLITE_A]SQLITE3.C;5

struct statfs fsInfo;
................^
%CC-E-INCOMPNOLINK, In this declaration, "fsInfo" has no linkage and is of an incomplete type.
at line number 18775 in file DSA0:[BH.SQLITE_A]SQLITE3.C;5

if( statfs(filePath, &fsInfo) == -1 ){
......^
%CC-I-IMPLICITFUNC, In this statement, the identifier "statfs" is implicitly declared as a function.
at line number 18777 in file DSA0:[BH.SQLITE_A]SQLITE3.C;5

if( fsInfo.f_flags & MNT_RDONLY ){
.......................^
%CC-E-UNDECLARED, In this statement, "MNT_RDONLY" is not declared.
at line number 18780 in file DSA0:[BH.SQLITE_A]SQLITE3.C;5

err = fsctl(path, afpfsByteRangeLock2FSCTL, &pb, 0);
........^
%CC-I-IMPLICITFUNC, In this statement, the identifier "fsctl" is implicitly declared as a function.
at line number 19699 in file DSA0:[BH.SQLITE_A]SQLITE3.C;5

int rc = flock(pFile->h, LOCK_EX | LOCK_NB);
.............^
%CC-I-IMPLICITFUNC, In the initializer for rc, the identifier "flock" is implicitly declared as a function.
at line number 19982 in file DSA0:[BH.SQLITE_A]SQLITE3.C;5

int rc = flock(pFile->h, LOCK_EX | LOCK_NB);
.............................^
%CC-E-UNDECLARED, In the initializer for rc, "LOCK_EX" is not declared.
at line number 19982 in file DSA0:[BH.SQLITE_A]SQLITE3.C;5

int rc = flock(pFile->h, LOCK_EX | LOCK_NB);
.......................................^
%CC-E-UNDECLARED, In the initializer for rc, "LOCK_NB" is not declared.
at line number 19982 in file DSA0:[BH.SQLITE_A]SQLITE3.C;5

flock(pFile->h, LOCK_UN);
......................^
%CC-E-UNDECLARED, In this statement, "LOCK_UN" is not declared.
at line number 19985 in file DSA0:[BH.SQLITE_A]SQLITE3.C;5

int rc = flock(pFile->h, LOCK_EX | LOCK_NB);
...........................^
%CC-E-UNDECLARED, In the initializer for rc, "LOCK_EX" is not declared.
at line number 20003 in file DSA0:[BH.SQLITE_A]SQLITE3.C;5

int rc = flock(pFile->h, LOCK_EX | LOCK_NB);
.....................................^
%CC-E-UNDECLARED, In the initializer for rc, "LOCK_NB" is not declared.
at line number 20003 in file DSA0:[BH.SQLITE_A]SQLITE3.C;5

int rc = flock(pFile->h, LOCK_UN);
...........................^
%CC-E-UNDECLARED, In the initializer for rc, "LOCK_UN" is not declared.
at line number 20031 in file DSA0:[BH.SQLITE_A]SQLITE3.C;5

srandomdev();
......^
%CC-I-IMPLICITFUNC, In this statement, the identifier "srandomdev" is implicitly declared as a function.
at line number 20416 in file DSA0:[BH.SQLITE_A]SQLITE3.C;5

if( (strlen(zDir) - sizeof(SQLITE_TEMP_FILE_PREFIX) - 17) <=0 ){
......^
%CC-I-QUESTCOMPARE, In this statement, the unsigned expression "(strlen(...)-sizeof("etilqs_")-17)" is being compared with a relational operator to a constant
whose value is not greater than zero. This might not be what you intended.
at line number 20725 in file DSA0:[BH.SQLITE_A]SQLITE3.C;5

[Jean-François Piéronne]

SQLITE_ENABLE_LOCKING_STYLE seem to be very unix specific.

I have found a small thread on this:
http://archives.devshed.com/forums/databases-124/sqlite-enable-locking-style-2138671.html

And extract from the final message:
"""
Defining the SQLITE_ENABLE_LCKING_STYLE macro (doesn't matter if it's
set to 0 or 1, just whether or not it's defined) activates code to
handle some tricky filesystem locking semantics (NFS etc.) on
Mac SX.
"""

So probably not very portable and as Willem suggest may need some work on VMS :-(

JF

[Willem Grooters]

... some work ...

probably an understatement. I didn't get into the SQLite code that deep, and not knowing the exact functions of flck, my feeling it'll be at least a bit more than "some work". Personal imprsssion only.

If update rate is low, and you can live with an occasional exclusive lock, it might be the way to go. I would suggest to do that in your application code, if possible, for the simple reason that it's easier to control and handle at that level.

[Hoff]

sys/param.h is largely a legacy file; that's basically old code, and there are now (more) portable APIs for that stuff. A number of packages I've encountered can reference it, but pull nothing from it. If you're porting code, I'd conditionalize any references to it -- check for other ports that have done the same, too. (Often an empty file is good enough -- that avoids needing conditionalizations.) See next.

An empty params.h file is often created as part of porting this stuff. See: http://www.openvms.compaq.com/openvms/products/ips/porting_relnotes.html

The rest of the stuff looks like standard porting stuff.

#pragma mark doesn't exist; it's non-portable. (The compiler messages can be suppressed, either via compile switch or via the /FIRST_INCLUDE mechanism.)

Avoid conditionalizations if you're looking to push fixes back out, if you can avoid them. It just messes up the code. The /FIRST_INCLUDE can be handy.

The locking will be the interesting bit. Most (all of?) the file locking will either involve an upgrade to current releases of OpenVMS (as you've found), or rolling your own locking code, or finding and porting some locking code from elsewhere.

Based on your V8.3 experience, what you're encountering is a decade-old platform with decade-old run-time libraries; you're working before these calls and these features were added into OpenVMS.

There's a list of this sort of stuff in the back of the C RTL manual; tables of when various C routines were implemented in OpenVMS.

All of which means you either upgrade, or you roll your own support. Which then means you get to make a crucial business decision: upgrades, roll your own code and support, drop support for older releases, or other such. Or possibly look to alternatives, such as porting PostgreSQL or another such database, or at a commercial database.

Locking code is not something you should walk into implementing and supporting lightly, either. It's usually easy to get it mostly-working, and rather tougher to get it reliably working.

[mohcine]

Dear Ben Armstrong,

i understand that you have working to use the SQLite3 under OpenVMS.
I would like to use it too.

What is the current status?
Do you have an up to date version that is working? If yes could send me this version (sources)?

Thanks for all.
Mohcine

[labadie_1]

Mohcine

nothing has changed about the status of Sqlite3, it works with one user, there is still the locking problem with several users.


If you want to use it, get the 2 LD devices for Python for VMS at
http://www.vmspython.org/DownloadAndInstallationPython

launch Python for your process only (see on that page
Process Installation) or for everybody
(see on that page
System Wide installation)
and you will have sqlite on VMS.

[mohcine]

Hi Labadie,

thanks for your answer.

You means that we can access to a database through only one thread?

How can I acces to the last available sources files?

Thanks
Mohcine

[Hoff]

There are other discussions around the ITRC forums of folks that have been working with SQLite.

There are VMS-related comments in the SQLite source pool, and there are existing discussions of folks that have built SQLite on VMS available via Google. It appears that various fixes have been fed back into the SQLite source pool. (Dr Hipp is pretty good about this stuff, FWIW.)

Download the SQLite source pool. See if it builds.

If you're looking for a pre-built and pre-packaged download here, MySQL (which has those available) might be a better choice. (I don't know of a pre-built SQLite download.)

And please start your own thread.

[labadie_1]

Mohcine

See
http://hg.vmspython.org/vmspython/file/ae11909ae66e/python/vms/sqlite/sqlite3.c

[Ben Armstrong]

We never got it to work well enough to deploy it. The locking issues indicated above were show-stoppers.

[Ben Armstrong]

Hm, re-reading the whole thread now, I seem to have reversed my earlier position.

I should have said ... we *could* have used it in some limited applications with file locking instead of record locking, as I indicated earlier, but record locking does remain a desirable feature. When we ran up against the record locking issues and learned that the VMS python project already took sqlite3 into their repository, we left off trying to solve it on our own. We've learned from past experience that we can't sustain this kind of project, so we had hoped the python project's code would continue to move along so that by the time we were ready to look into it again, we would just use their code.

I do not know the status of that code, however, as we haven't looked at any updates in many months.