SQLite Thread Safety
-
The SQLite documentation says that SQLite is thread safe for reading but not for writing. Specifically, they say you can have multiple threads reading from the DB, but only one thread should write at one time. Do you think this means that I should also avoid two threads accessing the DB at one time if one of the threads is reading and the other is writing? IOW, does reading need to be serialized as well?
The difficult we do right away... ...the impossible takes slightly longer.
-
The SQLite documentation says that SQLite is thread safe for reading but not for writing. Specifically, they say you can have multiple threads reading from the DB, but only one thread should write at one time. Do you think this means that I should also avoid two threads accessing the DB at one time if one of the threads is reading and the other is writing? IOW, does reading need to be serialized as well?
The difficult we do right away... ...the impossible takes slightly longer.
It's "rereads" that are (usually) the problem. If one is writing, and another reading, one for one makes little difference; if the record aren't related. On the other hand, if you're not writing "complete" records at one time, then they have to be "locked". For "rereads" you have to check for "dirty" (e.g. timestamp changes). "Screen data has been updated". With related records, you may need need to lock a table or the entire DB (for a very short time). All calls should be async to avoid UI waits.
"Before entering on an understanding, I have meditated for a long time, and have foreseen what might happen. It is not genius which reveals to me suddenly, secretly, what I have to say or to do in a circumstance unexpected by other people; it is reflection, it is meditation." - Napoleon I
-
It's "rereads" that are (usually) the problem. If one is writing, and another reading, one for one makes little difference; if the record aren't related. On the other hand, if you're not writing "complete" records at one time, then they have to be "locked". For "rereads" you have to check for "dirty" (e.g. timestamp changes). "Screen data has been updated". With related records, you may need need to lock a table or the entire DB (for a very short time). All calls should be async to avoid UI waits.
"Before entering on an understanding, I have meditated for a long time, and have foreseen what might happen. It is not genius which reveals to me suddenly, secretly, what I have to say or to do in a circumstance unexpected by other people; it is reflection, it is meditation." - Napoleon I
Well that seems related to the issue that I'm encountering. Occasionally I'll receive a "Access denied - Database locked" error message, and I can't understand it because all of my writes are synchronized around a single mutex. So I thought maybe I can't read either when a thread is writing to the DB. It's an INSERT statement that's receiving the Database locked message, so the only other thing I can think of is that there's something wrong with my threading code and it's actually attempting two writes at once.
The difficult we do right away... ...the impossible takes slightly longer.
-
The SQLite documentation says that SQLite is thread safe for reading but not for writing. Specifically, they say you can have multiple threads reading from the DB, but only one thread should write at one time. Do you think this means that I should also avoid two threads accessing the DB at one time if one of the threads is reading and the other is writing? IOW, does reading need to be serialized as well?
The difficult we do right away... ...the impossible takes slightly longer.
Richard Andrew x64 wrote:
Do you think this means that I should also avoid two threads accessing the DB at one time if one of the threads is reading and the other is writing?
Yes. SQLite CVSTrac[^]
Wrong is evil and must be defeated. - Jeff Ello
-
Well that seems related to the issue that I'm encountering. Occasionally I'll receive a "Access denied - Database locked" error message, and I can't understand it because all of my writes are synchronized around a single mutex. So I thought maybe I can't read either when a thread is writing to the DB. It's an INSERT statement that's receiving the Database locked message, so the only other thing I can think of is that there's something wrong with my threading code and it's actually attempting two writes at once.
The difficult we do right away... ...the impossible takes slightly longer.
An insert implies an index update; or a "split" somewhere. A simple append might be different. In effect, the "internal" state becomes dirty. Because it is "lite".
"Before entering on an understanding, I have meditated for a long time, and have foreseen what might happen. It is not genius which reveals to me suddenly, secretly, what I have to say or to do in a circumstance unexpected by other people; it is reflection, it is meditation." - Napoleon I
