1748025 Members
4231 Online
108757 Solutions
New Discussion юеВ

Re: SQLite3 and locking

 
SOLVED
Go to solution
Ben Armstrong
Regular Advisor

SQLite3 and locking

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
23 REPLIES 23
Willem Grooters
Honored Contributor

Re: SQLite3 and locking

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.
Willem Grooters
OpenVMS Developer & System Manager
Jean-Fran├зois Pi├йronne
Trusted Contributor

Re: SQLite3 and locking

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
Regular Advisor

Re: SQLite3 and locking

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
Trusted Contributor
Solution

Re: SQLite3 and locking

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
Honored Contributor

Re: SQLite3 and locking

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
Regular Advisor

Re: SQLite3 and locking

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
Trusted Contributor

Re: SQLite3 and locking

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
Regular Advisor

Re: SQLite3 and locking

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
Trusted Contributor

Re: SQLite3 and locking

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