I'm writing a library in C and I need to lock the database file in case other instances of my library are run, so incorrect info doesn't make it's way into it.

I was wondering what the best way to lock the file is, and if the SQLite C library has any functions that would be relevant for doing that? I know I can use system calls such as open() and flock() to do that, but if there is a better way, I'd prefer to do that instead. And if there is, would someone be able to give a simple and clear example of that?

Please be very clear in any examples given so I can understand them.

Thank you.

You should never ever lock the DB file yourself.

SQLite does its own locking as necessary from the SQL you use, notably with your explicitly started transactions.

And the locking differs depending on the journal mode, how you compile SQLite, etc... See https://www.sqlite.org/wal.html for example, and what it links to.

This sounds like an XY problem in the making.

Why do you want to avoid your application writing to the database?

Why would that answer be an issue for you - has it cause problems or are you just guessing?

What would a bad outcome look like? What would a good outcome look like?

I suspect you are actually after transactions, but it is possible you are looking for access control.

You may even be looking for a client server model that sqlite is unsuited for.

Either way, it is unlikely that you are actually looking for database file locking.

Didn't know that, thank you.

Sure, I was thinking that let's say I run a program that uses my future library and then run another instance of the same program. Well, I thinking that's going to be two instances of the same library trying to write to the same database, and I'm therefore going to get two different sets of records being written to the database at the same time, which is going to result in the wrong records being written where the database gets "hammered" by two different instances both writing to it at the same time.

That was the reason I was asking about locking either the db file itself or locking the database itself so only one instance can write to it at a time. And for lack of better words, the second instance of my library can't write to it until the first instance has finished writing to it (and has closed it).

Hopefully I've been more clear this time.

Have you read the documentation on “Transactions”? If your library starts with a “BEGIN IMMEDIATE”, and then ends with a “COMMIT”, the second copy can’t write records in between those two statements.

No, I haven't.

Is that in the link above?

Is that in the link above?

https://sqlite.org/lang_transaction.html

"Hopefully I've been more clear this time."

Yep. You are looking for "Transactions".

You do not need to lock the database file.

I would direct you to review this post. It requires polling rather than responding to an event, but it appears to do what you desire.

With ROLLBACK journal mode, polling is indeed the only way (but see fsnotify below).

But with WAL mode, and shared-memory, I think SQLite could offer it, no?

Better for SQLite to solve it once and for all, better that most of us could do on our own.
Maybe that implies a small addition to the VFS to support some IPC, but it would IMHO be a welcome addition for many people.

Detecting changes to the filesystem is already possible with fsnotify and co (although portably is more difficult),
but only SQLite could offer it in a way that's consistent and on transaction commit, and thus becoming the standard.

Cross-connection notification (commit hook) would be a super addition to SQLite IMHO. --DD

Thank you punkish, I'll have a look at that.

Thank you Spindrift, I'll once again have a read of it.

So everyone is in agreement that "Transactions" is the solution?

No problem - I claim no expertise in databases either ;-)

What will be my future library doesn't take any user input, nor does it provide any UI of any kind. It gets the information from system calls from the operating system, and then writes that info to an SQLite database.

Also, since it will be a library rather than a normal application, then you won't be able to run it directly, you would have to write an application that calls the relevant function from it (in my case all but one of it's functions will likely be static functions that won't be callable "from the outside" anyway).

It's therefore likely it's only going to have one main-like function that's callable "from the outside", as most of it's other functions would fail if you tried calling them without and before say it's init() function is called for example (but not limited to). Because in that case, heap memory for one thing would not have been allocated for the struct that it uses to store variables/struct members in - as I didn't what to put anything on the stack that could blow the stack - so most of the variables it uses for various things are allocated on the heap with malloc() rather than the stack.

Just out of curiosity, can SQLite initiate communication with a connection or is it limited to responding to in-coming requests? (Kind of like HTTP versus a web socket.)

If SQLite can initiate communication then why would it not be able to inform any connection of a change in the database regardless of which connection asked for it and regardless of the journal mode?

If SQLite can only respond to requests and not initiate communication, then it would not be able to inform any connections, other than the one asking for the change, to run the code registered in a hook.

Please understand that these are just questions from a novice and I am not suggesting that SQLite should or should not be able to do anything; just wondering about how it works a bit in comparison to the few things I know a little more about. Thank you.

It just dawned on me how stupid this question of mine is. In novice terminology, in the context of one machine, two separate connections are two instances of SQLite running in separate processes with no way to communicate. The database is a file and a file isn't going to "talk" to a program. (Perhaps fsnotify makes that possible).

One instance of SQLite would have to determine whether or not another instance was running and somehow open communication with it and determine if they both had a connection to the same database file. I was thinking as if SQLite was running in the database fie itself and responding to requests on multiple connections, as if SQLite was a server. I apologize for my stupidity in that post and this one.

I reply as a moderator here.

Normally, a post containing abusive language would be rejected for it. I make an exception in this case because it appears to be self-abuse, although I cannot be sure due to "anonymous" not being a unique handle.

Notice to other readers: Please do not take this post as representing tolerance of abuse between participants.

I don't mean to speak out of turn or anything, but from my reading, it looks very much like "self-abuse" to me rather than being directed at anyone else here. And while I can't speak for others, and I'm not a mod myself, I didn't take any offense to it personally - I've heard and seen far worse (not saying that makes it ok to abuse others though).

That's fine. As long as people stick to the topic at hand, I don't take issue with it.

Yes, you got the gist of it.

Yes, fsnotify allows a file to talk to a process, kinda.

The process registers interest in knowing when a particular file changes,
via OS specific APIs / syscalls; And the OS calls that process,
on the callback the process registered with the OS (and its FS),
whenever that file changes.

The problem with that approach is that the file changing could be in the middle of a write transaction, not yet fully committed for example.
So if you use SQLite APIs correctly to read the DB (from a different connection, in another process or not, doesn't matter),
you'd either block (JOURNAL mode), or not-block but not see the new data yet (WAL mode).

Plus in WAL mode, the DB file changes on checkpoints only, it's the WAL file that changes instead, when writing new data in a transaction.
So which files to monitor depends on the journal mode too, which complicates things. (especially since there's also a WAL2 branch, with two WAL files!)

I'm sure Richard or Dan could allow cross-connections notifications, technically.
Kinda like PostgreSQL's LISTEN/NOTIFY, perhaps limited to connections on the same host only.
That would probably require a VFS change, for fsnotify or some IPC, but unless / until
they have a paying client that needs this, it will never happen. (and even then perhaps, I dunno).

Running another instance of my code does in fact cause sqlite to complain that the db is locked, and my code does handle that correctly so I think that's an acceptable outcome and good enough for me.

So while I may still have a look at "Transactions" (even just for any future projects that involve using sqlite); I'm not sure I really need to go to the trouble in this particular case since my code keeps the db open until it's finished writing to it anyway. Therefore since sqlite locks the db until it's closed, well that seems acceptable to me.

Thanks for all the help and all the info all the same.

since sqlite locks the db until it's closed

I'm afraid you are mistaken. Not sure how you got that impression.

Having a DB open (in one connection) does NOT prevent other connections (in other processes or the same one) to read or write that DB.

Without explicit transactions, each statement is its own mini transaction.
And in between transactions, anyone can read or write the DB.

You can lock a DB by opening an EXCLUSIVE transaction.

Regular (non-EXCLUSIVE) transactions are lazy, they lock on the first real access, i.e. the first statement executed inside that transaction.

Locks are released on COMMIT or ROLLBACK, for explicit transactions.

So while I may still have a look at "Transactions" (even just for any future projects that involve using sqlite); I'm not sure I really need to go to the trouble in this particular case since my code keeps the db open until it's finished writing to it anyway. Therefore since sqlite locks the db until it's closed, well that seems acceptable to me.

Simply opening a db does not lock it. The library does not lock the file unless a transaction is active. i.e. you'll need a transaction in order to lock the db.

Edit: x'ed by ddevienne.

Ok, it seems I've misunderstood.

To be clear:

1. If I want to make sure no other instance of my code can also write to it while the first/original instance is writing to it, I need to use "Transactions" ? Is this correct or incorrect? Please be crystal clear.
2. If the above statement is correct, could someone give a clear and simple example of any SQL statements I need to add to my existing SQL statements.

Thank you.

1. Yes
2. See example SQL below

BEGIN EXCLUSIVE TRANSACTION;
INSERT INTO SomeTable (c2,c3) VALUES ('val2','val3');
:
... more of your SQL statements here ...
:
COMMIT TRANSACTION;

or if something went wrong

ROLLBACK TRANSACTION;

Notes:

BEGIN EXCLUSIVE locks the database for other processes until COMMIT or ROLLBACK. Only one process can successfully start an `EXCLUSIVE transaction at a time.

The additions / deletions / updates to the database are only visible for transactions in other processes after this process has COMMITted them.

Read the docs for the fine print about BEGIN.

SQLite is a database. It provides ACID (atomicity, consistency, isolation, durability) guarantees, and every (correct) use of the SQLite library will involve transactions, whether explicit or implicit, which allow multiple different processes to access the same database without fear of corrupting it.

Your whole thread seems to revolve around manually protecting your database from two processes altering it simultaneously and hence corrupting it, in something akin to a race condition.

While this is a concern in various contexts, especially sharing one database connection over multiple forked processes, it doesn't matter for most simple use cases. It is already prevented by the very architecture of the database.

I am increasingly convinced that you are trying to solve an already solved problem and this is the source of all the confusion.

Just run your programme. I think the problem you are ruminating over is just a figment of your imagination.

Thank you once again for your help Kees. I'll give it a go.

Thanks again.

Yes, I know SQLite is a database, that's why I'm using it.

As I've already clearly stated before, I'm not talking about corruption, not sure how I can be more clear about that. I'm talking about the wrong information being written to it. I cannot control what people do on their systems that are not my system, anymore than I can control how they run my code on their system(s). It's not you that will get the blame if the wrong records are being written to the db, that will be me that gets the blame, as it's my code and not your code. I do not want nor need that.

There is also absolutely no need whatsoever to get personal, as this helps nobody. Not to mention that I could not care less what one person thinks about me, or if they think I'm paranoid or not. Also, I'm writing a library that does not have a main() function, not a normal application program with a main() function - which I've also already stated at least a couple of times now.

There is also no need for you to even have responded, nor to continue to respond when Kees has already clearly answered my question and given what I believe is (or at least should be) a clear example. In any case, I don't have the time to play silly buggers with anyone, so I'll be taking Kees's answer and I'll be done with it. Therefore, unless I have issues with the example Kees has kindly provided, this will be my last response to this thread.

Good day.

You're most welcome for the time and thought that has gone into any replies and reading your posts. It seems an interesting problem, and I look forward to hearing how it develops and any other questions which crop up!

I'm talking about the wrong information being written to it. I cannot control what people do on their systems that are not my system, anymore than I can control how they run my code on their system(s). It's not you that will get the blame if the wrong records are being written to the db, that will be me that gets the blame

Depending on how people cause "wrong records" to be written to the DB, either your code deserves the blame or those people deserve the blame. Obviously, you cannot control how people might access the DB outside of the actions of your program, and if they blame you for it then that problem is outside the scope of issues that are topical in this forum. (It's a customer relations or selection issue then.)

The answers you have gotten for the possible issues in your code related to DB access have been relevant and correct. As far as I can see, those answers should suffice to solve the problem as you have stated it.

There is also absolutely no need whatsoever to get personal, as this helps nobody. Not to mention that I could not care less what one person thinks about me, or if they think I'm paranoid or not.

Nobody has responded in a malicious or needlessly personal manner. When it appears that somebody is imagining difficulties which are inconsistent with the relevant referenced documentation, and appears to continue doing so after such inconsistency is pointed out, then to say "the problem you are ruminating over is just a figment of your imagination" is entirely appropriate. And for many people, that observation could be helpful.

Nobody (other than you) has suggested that to imagine a non-existent problem is to be paranoid. The fact is that programmers often become confused, even the most proficient among them. There is nothing insulting or unduly personal about recognizing mental errors.

I have noticed that the most proficient and competent creators of human-designed artifacts are those most prepared to recognize their own error and not take indications of their own error as cause for emotional defense.¹ That is such an important personal attribute that I look for it specifically when conducting job interviews. I recommend cultivating that attribute to all people who are serious about their profession.

1. ^{^} I well remember an incident involving somebody who inspired me in several ways. After a vigorous (but logical) argument, he suddenly paused for several seconds after his opponent had made a point, clearly thinking hard about it. Then he smiled and said "You're right. Thank you." That point had exposed the error in his thinking, and he was fully prepared to recognize it.

Excellent way to put it Gunter, FWIW.

ensure that multiple instances take turns

Well, it's of course more complicated.

Depends on the busy timeout in practice,
and also the journal mode (as previously mentioned).