SQLite semi-concurrent access

[willichan]

I have found several references for SQLite, and confirmed for myself, that state that concurrent reads are supported, but that it will return a database locked error for concurrent write attempts.

I have also found a few posts stating that SQLite has a mode that will retry writes for a specified timeout before returning a database locked error. Is this "retry mode" supported under the current SQLite UDF?

edit:

One such reference is from Wikipedia

Several computer processes or threads may access the same database without problems. Several read accesses can be satisfied in parallel. A write access can only be satisfied if no other accesses are currently being serviced, otherwise the write access fails with an error code (or can automatically be retried until a configurable timeout expires).

http://en.wikipedia.org/wiki/SQLite Edited June 28, 2010 by willichan

[PsaltyDS]

More info at SQLite: File Locking And Concurrency In SQLite Version 3, but I think those are internal details not really intended for DLL uses like the AutoIt UDF.

You might just implement a wrapper function of your own that watches for SQLITE_BUSY status and retries until SQLITE_OK or a timeout occurs.

[jchd]

The short answer to your question is "Yes".

Now it begs some explanations to be really useful to you.

Case #1: several processes are using one or several database(s) hosted on the same computer at the same time... easy.

Case #2: several processes need to share one or more database over a network... not easy

Case #1: be sure to read and understand the locking mecanism inside SQLite (see the SQLite docs). Now for each connection, use _SQLite_Timeout to specify a large enough timeout (more about this later). Then wrap _every_ Read-Modify-Write (RMW) transaction inside

_SQLite_Exec($hDb, "begin immediate;")

and

_SQLite_Exec($hDb, "commit;")

Immediate keyword is crucial in that it informs SQLite to place a reserved lock on the DB file, which will block subsequent attempt to obtain a write lock, until _you_ commit. In the meantime, read (select) operations by concurrent processes can run without being blocked. Also read locks can't be promoted to write locks in the meantime, as it would result in a deadlock situation, which your mission is to avoid like the plague.

The timeout value needs to be long enough to allow for _any_ sequence ot RMW or W transactions to complete. SQLite does no serialization of the requests, so your applications must be ready to accept being delayed for possibly longer than the duration of only one RMW transaction by another process. In this framework, you never have to worry about receiving "Database is busy" error. In a low concurrency context like mine, I may have up to 7 processes using the DB, but I setup 15 minutes of timeout! (One of the process is a weekly Vacuum, another is a hourly live backup, which are both slow operations). If your context needs to be more responsive, then decrease the timeout down to a value you can cope with, but always test and deal for SQLITE_BUSY condition inside after any SQLite operation inside a transaction or not (even for reads!).

Case #2 is much harder. SQLite uses the underlying filesystem to insure proper locking, but it turns out than almost every implementation of NFS, SMB, ... has enough bugs to sometimes cause DB corruption. Exposing how and why things are this way is a bit too much for this answer. Let's say that, without using a intermediate client/server layer, SQLite over a network isn't safe. Don't give up if you need it anyway, there are a number of open-source implementations available that allow such operation with excellent success.

Hope this clears some mud. There are much information about all this in the SQLite web documentation.

[willichan]

PsaltyDS & jchd:

Thank you for your responses.

jchd: That does clear up the picture, somewhat. I'll admit that I didn't absorb all of it, but it did clear up for me that the "retry mode" is not as simple as was implied elsewhere. I will need to re-read to make sure I get it in full.

For this particular implementation, machines periodically update a database on a network share with their status (software deployment completion/failure, update completion/failure, etc...) with a single write. Reads are really only done when I need to generate a report. For the most part, there are no conflicts, but once in a while, two machines will attempt up write status at the same time, and one will error out. For this case, I will probably just put my own wrapper around the writes to watch for SQLITE_BUSY as suggested.

[jchd]

It would be even simpler as a "begin immediate" .. "commit" transaction around RMW transaction and a decent timeout setup on every (read or write) connection would be enough to insure you're never getting BUSY ever.

The only catch in your framework is that SQLite is _not_ safe over a network, at least not out of the box. Again it's not SQLite fault, as it relies on underlying network file system to handle locking correctly, which almost none does well (Windows, Linux, Mac are in the same bag).

What is the frequency of writes? Would it be acceptable for writers to have to wait more time (how much?). I ask as you could be able to circumvent those limitations with simple, essentially robust, code.

[PsaltyDS]

After reading jchd, I was thinking along the lines of writing some kind of transaction file to the folder, and let a single script on the file server handle posting the data to the DB. Only one process ever writes, and it is local with the DB.

[willichan]

What is the frequency of writes? Would it be acceptable for writers to have to wait more time (how much?). I ask as you could be able to circumvent those limitations with simple, essentially robust, code.

Each machine would write an update at least once per day (usually at logon) with inventory information on monitored applications. Other writes would only be as deployments or updates are performed. With about 800 machines, mornings would probably be hit heaviest when people are logging in. Currently, I only get maybe one call per morning (I have it set to give them a msgbox with my extension to call if it happens). Since the inventory and updates happen in the background, without the user seeing anything, the delay is really no problem.

I am currently re-reading the SQLite file locking docs (PsaltyDS's link above) along with your response.

(edited for grammar errors)

Edited June 30, 2010 by willichan

[jchd]

I need a (very primitive) client/server layer as well for the need of our business. Like many users of such feature, I don't need blazingly fast answer and I'd just love have the whole thing done in AutoIt. The only problem is that the server side needs to be multi-threaded. That's why I've postponed writing that myself until now. What I' thinking is to clone the SQLite UDF to make it able to open either a local file, in which case the current functions will be used, or, if the DB is remote, use a TCP connection with a simple protocol (forward SQLite command strings verbatim and receive back CSV-style data + status). This way, the application code would remain identical (for invokation or for the handling of data) that the DB be local or beyond Saturn.

Having as many server-side executable is very inefficient but simple, but that would be a last resort solution. I just need to take the time to crack a server side host in C, I'm afraid.

[willichan]

We do have a couple of SQL servers I could port this over to use, I just hate going through the red-tape to create a new database for this, when reports are only run once or twice a year. This setup is to save me from having to track information manually, and to ensure that my information is up-to-date if someone else does a deployment, but forgets to inform me.

Even though I have more technically sound solutions available for many thing I want to do, I often find that AutoIt fits the bill for "unofficial" projects, where I want to cut through red-tape and just make it work. The fun part is that AutoIt usually allows me to do this without violating policies, so I get praised for it far more often than reprimanded. (The fact that my deployments usually complete in 1/10th the time of others, and with fewer mistakes doesn't hurt either. )

[jchd]

@willichan,

OK I see the use framework. Anyway, 800 potential writers isn't nothing and you're better rely on a solid basis to avoid inconsistencies. Also, be aware that readers need to lock the DB as well (that's inevitable to keep the ACID properties satisfied) so reads need to be reasonably fast (e.g. perform the read with _SQLite_GetTable2d and then do what you need to do with the data).

I read from your most recent reply that you won't read that much. I understand why you'd better use lightweight tools (AutoIt and SQLite) to do this sort of job.

Since you have time to write safely at the client stations and the application isn't critical, you could use one of the following poor man's solutions:

1) use a "see'n shoot" semaphore protocol to guarantee serialization. Create a "Semaphore" directory on the server (only to keep things separate). It's obvious every workstation has a unique ID. At logon, have each workstation detele files whose name contain this station ID. Then have the client create a 0-byte file named _DataCalc & $StationID and remember the filename. As soon as the file actually exists (FileExists) loop until the file disappears (so below) or timeout (means problem with the server). That means you're "clear to send" so _SQLite_Exec($hDB, "insert into ...). On the server side, regularly watch files appearing in the semaphore directory. When there is one, wait one second (should be more than enough) and delete it! That gives the signal to that station that the server is OK to receive the sqlite command. In case of power failure or if the server goes to maintenance, you may have old files (see timestamp) there. delete them the day after.

2) have every station maintain its own DB and query them when needed. This is more involved as it needs more setup at the stations and you won't have fresh information until you poll them all.

I'd go with something along 1) as it's very simple to do, essentially foolproof in the absence of adversaries and even if slow that's not a problem.

EDIT: sorry, I mean rename it (use some extension) on the client side at the end of DB update, else the server won't know when give the green to another station.

Edited June 30, 2010 by jchd

[willichan]

@jchd

Thank you. I will give that a try.

[jchd]

Sorry, I wrote a little too fast (I've one "core" only and I was doing too many tasks at the same time). But I'm sure you get the idea to create a unique file per station and when required, and use some characteristic (simple contents or more simple rename by changing extension) to make it a vehicle for semaphore. That is a bit ugly and slow but will work and make sure that the DB won't risk corruption problem due to for instance SMB malfunctionning in some rare circonstances. Also you'll only need little and simple code to do that.

Of course if I'm wrong at assuming that missing an update in the case of some machine being stopped or reset (station or server) will not result in a disaster, then you need something more serious. But if I'm correct at understanding that it's only to keep track of installed software/configuration on clients, then the odds of utterly wrong behavior is probably low enough.

[willichan]

Sorry, I wrote a little too fast (I've one "core" only and I was doing too many tasks at the same time).

If I could upgrade to dual or quad core for myself, I would jump at it ... but then again, with all the swelling that takes place in there, my head is crowded enough already. (Why is this microphone making cricket noises?)

In this particular case, if an update is missed, it isn't the end of the world. I do, however, like to use less critical situations like this to develop more stable solutions/techniques for when the more critical ones hit.

[willichan]

I have been writing some semaphore functions similar to what jchd describes, with one difference. To avoid the need for processes running on the server (a big benefit of SQLite over a client-server setup) I am using cooperative semaphores. The concept is basically the same, but does not require anything running on the server side. The methodology is outlined pretty well by Sean M. Burke in his articles:

"Resource Locking with Semaphore Files" and "Resource Locking Over Networks"

I decided to take the time to write generalized functions from the start, rather than writing them for my own use, then trying to generalize later. I hope to have a UDF tested and ready for upload in about a week (assuming nothing major plops in my lap in the mean time). It is a short testing cycle, I know, but given the 1-2 conflicts a day I have been getting (at least the ones I know of), it should give me sufficient conflicts to find any problems. The issue of abandoned/dead locks will also be handled cooperatively, rather than running a cleanup process on the server.

Edit: If anyone would like to take a look at the UDF as it stands, download it here.

Edited July 8, 2010 by willichan

[willichan]

Testing went well. I am going to move this off to a new topic in the "Example Scripts" forum.

http://www.autoitscript.com/forum/index.php?showtopic=117033