SQLite: The Big Concern

by Unknown user

One statement, above all others, in SQLite's documentation has drawn quite a bit of attention. And quite a bit of concern. That statement is SQLite “must lock the entire database file” during file access.

The concern is that when SQLite is used in a multi-user or multi-threaded environment, data access will become slow, noticeably slow. There is a concern that data access will either be blocked or, possibly, stall because the database is locked. Under these circumstances, an app could even hang.

SoftVelocity adds to this concern by writing that SQLite is “not inherently multi-user.” SQLite's docs further confuse matters by saying things like:

• Only one process can make changes at any given time
• Reader/Writer locks are used
• Files must be locked for the duration of the update
• Mutexes are used (but only) if SQLite used by more than one thread – and Mutexes are expensive

The full text of the parts of the documentation about database locking are:

Multiple processes can have the same database open at the same time. Multiple processes can be doing a SELECT at the same time. But only one process can be making changes to the database at any moment in time, however.

SQLite uses reader/writer locks to control access to the database.... [A reader/writer lock allows concurrent access to multiple threads for reading but restricts write access to a single thread. Ed.]

SQLite allows multiple processes to have the database file open at once, and for multiple processes to read the database at once. When any process wants to write, it must lock the entire database file for the duration of its update. But that normally only takes a few milliseconds. Other processes just wait on the writer to finish then continue about their business.

and

For safe operation in multi-threaded environments, SQLite requires the use of mutexes.... Mutexes are only required if SQLite is used by more than one thread at a time.

and

SQLite assumes that the operating system will buffer writes and that a write request will return before data has actually been stored in the mass storage device. SQLite further assumes that write operations will be reordered by the operating system. For this reason, SQLite does a "flush" or "fsync" operation at key points.

SoftVelocity's tip:

ADD() is very fast with SQLite when you use it within a transaction. If you need to add a lot of data it is recommended to issue a LOGOUT statement followed by the ADD calls and when completed issue a COMMIT.

does not, I think, help. This comment implies that there is something peculiar in batch updating SQLite files, something different from other file systems. And, whatever this difference is, it can be overcome by using a LOGOUT.

[Since SQLite does a flush on I/O, it’s reasonable to assume that if you do a large number of Adds you’re going to get a lot of individual disk writes. This will be far slower than if you do a single disk write (or just a few writes) for all of the Adds. So the comment does in fact help. Ed.]

In fact, virtually any batch updating (ADDi-ng, DELETE-ing or UPDATE-ing) that does not use a SQL statement benefits from LOGOUT, at least as far as speed is concerned. Or, so it seems to me.

[Yes, most likely for the same reason. Ed.]

What Does This Mean?

What this means, I think, is that the developers of SQLite tried very hard to protect the integrity of the write-to-disk process. I think it means that, when it came time to describe what they had done, they did so fairly badly.

I think the SQLite documentation raises red flags and causes worries where only an “oh, by the way, just so you know ...” is all that is appropriate.

The phrase “must lock the entire database file” is doubly unfortunate.

In the first place, while the documentation talks about the database being in a single physical file, there is no actual requirement that it be in one file. Or, at least, it isn't enforced in any way. There is nothing whatever that stops me from naming multiple target files in the dictionary's Owner prompt. (You can try this for yourself in the example app, based on school.app, provided by SoftVelocity.)

In the second place, “lock” is an awfully strong word for something all of us find desirable in many other circumstances. And that is that write ops – not read ops (i.e., most browses and reports are not affected) – are serialized. SQLite uses a Mutex, a serialization object that queues up write requests and allows only one to execute at a time. A bit paranoid, perhaps, but nothing all unusual, not unlike a Transaction frame, though it might have been helpful if SQLite documented how long the Mutex WAITs, whether it WAITs forever, whether (and what) message might be returned on failing to get the Mutex, etc.

Given that we are, after all, talking about the integrity of data, Clint Eastwood's line in In The Line of Fire comes to mind: Sure I'm paranoid; but am I paranoid enough?

In fact, what concerns me, at least a little bit, is why they chose to use a Mutex instead of a Critical Section. (1) Mutexes are slower than Critical Sections (ignoring the additional time the documented FLUSH takes, though they can be tested and allow for programmer action … again, knowing how long would be helpful) and (2) had the documentation referred to Critical Sections, there would not have been, I think, the level of concern over “must lock the entire database file.”

Perhaps they chose a mutex because a mutex works across applications (multiple processes), while a critical section only works across threads within one application (one process). However, while SQLite uses mutexes that alone would not be sufficient because mutexes are per-machine not per-network.

What Does This Mean in the Real World?

Time to test and see if there really is anything to all this....

My old checkbook app - “old” as in “originally written before I owned Clarion and re-wrote in CPD, then CfD” – still uses Clarion .DAT files. One of the files is archived checks and has 22,458 records in it. The file has … had … one key (on Date + Number) and one Static Index (on category, used only in reports). For the purpose of converting it to SQLite, I added an auto-number SysID type key.

Taking a cue from the example app SoftVelocity provides, I created a Process template procedure, with the old .DAT file as the primary file and the new file in Other Files. (Actually, I also did a .DAT to .TPS version and it is the .DAT to .TPS version that I'll use to compare to .DAT to SQLite.)

This is a Clarion template chain app, so the essential embed is “Activity for each record.” Originally, the code read:

OLD:Record = tOLD:Record
Seed += 1
Add(OldLeger)

(I later found that I needed to change the order of the fields - more on this next time).

Seed is a local variable to prime the new SysID field. It defaults to zero and I increment and assign it for each record read.

Standard stuff. Nothing fancy.

Because this a Process template procedure, I normally LOGOUT / COMMIT. This gives me the opportunity to test SoftVelocity's assertion:

ADD() is very fast with SQLite when you use it within a transaction. If you need to add a lot of data it is recommended to issue a LOGOUT statement followed by the ADD calls and when completed issue a COMMIT.

The results of converting 22,458 .DAT records using LOGOUT are:

Records per second comes out as 1636.88 and 1636.68, respectively. Not a significant difference, I think. So, SQLite is only comparable to TPS for batch processing with LOGOUT.

A little more interesting is what happens when I remove the LOGOUT / COMMIT:

Now here there is a significant difference. A 30.7% difference to be precise. (100.56 records per second for .DAT to SQLite versus 130.49 to .TPS.)

My immediate reaction is “Wow! That's a difference. SQLite really is slower.” Then I try to think of a situation in which there are enough data entry operators who work fast enough to be pumping in that many records a second.

If an app is taking in that many records, regularly and consistently, neither .TPS nor SQLite is an appropriate back end. Industrial duty applications, taking in that many records per second, not in batch-mode, should be in a client-server database. But, if the app is taking in that many records, periodically, in batch-mode, the first test, using LOGOUT, shows that the throughput is acceptable.

Conclusions

Does SQLite “lock,” serialize, write operations? Yes, it does.

Is there a performance penalty for this? Yes, there is.

Try taking the code above and wrapping each assignment / ADD block in a LOGOUT / COMMIT. When I did, I got a 167% performance hit. That's not quite three times as much time with individual LOGOUT / COMMITs as with no LOGOUT at all.

In fact, the reason non-LOGOUT SQLite is 30+% slower has more to do with the overhead of getting the Mutex and releasing it than it has to do with anything else. Because all of the overhead, in my tests, is on a single thread, the statistical impact appears to be great. In the more normal course of events, multiple threads or processes, trying to update a single database “simulateously” will see little if any impact; the overhead of a single (non-cumulative) get and release of a Mutex is a few hundredth of a second (on my machine, this test, approximate .34 seconds seem to be added to each ADD by the get/release cycle). Compared to get/release/get/release... thousands of times, as in my test, the typical data entry overhead is entirely discountable.

Is anyone, in a real world application likely to notice? That, I seriously doubt.