A SQLite DB Question

[XinLu]

Hi all,

I would like to know when two processes are writing into the SQLite database at the same time, how does SQLite handle it?

I looked up on SQLite official website, they say SQLite is capable to lock the database while one is writing. However, my question here is:

Do I need to call _SQLite_ErrMsg to check the status of the database (Locked, Pending, etc), or SQLite.dll will handle the writing behaviors itself (such as to pause the 2nd writing behavior, and to wait for finishing current writing behavior).

I hope you guys understand what I am trying to say. I will appreciate your answers.

[PsaltyDS]

Hi all,

I would like to know when two processes are writing into the SQLite database at the same time, how does SQLite handle it?

I looked up on SQLite official website, they say SQLite is capable to lock the database while one is writing. However, my question here is:

Do I need to call _SQLite_ErrMsg to check the status of the database (Locked, Pending, etc), or SQLite.dll will handle the writing behaviors itself (such as to pause the 2nd writing behavior, and to wait for finishing current writing behavior).

I hope you guys understand what I am trying to say. I will appreciate your answers.

I don't think you need to do anything special. If another process is writing, then your process will be delayed a few extra milliseconds before returning status. But I haven't tested it to be sure. From the SQLite FAQs:

(5) Can multiple applications or multiple instances of the same application access a single database file at the same time?

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. (Under Win95/98/ME which lacks support for reader/writer locks, a probabilistic simulation is used instead.) But use caution: this locking mechanism might not work correctly if the database file is kept on an NFS filesystem. This is because fcntl() file locking is broken on many NFS implementations. You should avoid putting SQLite database files on NFS if multiple processes might try to access the file at the same time. On Windows, Microsoft's documentation says that locking may not work under FAT filesystems if you are not running the Share.exe daemon. People who have a lot of experience with Windows tell me that file locking of network files is very buggy and is not dependable. If what they say is true, sharing an SQLite database between two or more Windows machines might cause unexpected problems.

We are aware of no other embedded SQL database engine that supports as much concurrancy as SQLite. 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. Other embedded SQL database engines typically only allow a single process to connect to the database at once.

However, client/server database engines (such as PostgreSQL, MySQL, or Oracle) usually support a higher level of concurrency and allow multiple processes to be writing to the same database at the same time. This is possible in a client/server database because there is always a single well-controlled server process available to coordinate access. If your application has a need for a lot of concurrency, then you should consider using a client/server database. But experience suggests that most applications need much less concurrency than their designers imagine.

When SQLite tries to access a file that is locked by another process, the default behavior is to return SQLITE_BUSY. You can adjust this behavior from C code using the sqlite3_busy_handler() or sqlite3_busy_timeout() API functions.

[XinLu]

I don't think you need to do anything special. If another process is writing, then your process will be delayed a few extra milliseconds before returning status. But I haven't tested it to be sure. From the SQLite FAQs:

Yes, this is what I don't understand. In my program, do I have to check to see if the database is being written (locked), or I don't have to care about this problem at all since SQLite will handle file locking automatically. The FAQ does not clear this part.

I would assume I will just execute CREATE/SET SQL command as normal, and SQLite handles the rest, is this true?

Edited March 27, 2008 by XinLu

[PsaltyDS]

Yes, this is what I don't understand. In my program, do I have to check to see if the database is being written (locked), or I don't have to care about this problem at all since SQLite will handle file locking automatically. The FAQ does not clear this part.

I would assume I will just execute CREATE/SET SQL command as normal, and SQLite handles the rest, is this true?

I believe so.

It is unclear with these two parts of what quoted:

... 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...

... When SQLite tries to access a file that is locked by another process, the default behavior is to return SQLITE_BUSY...

So... what does SQLite do, wait/write/return as normal, or return SQLITE_BUSY and expect the app to detect that and try again? On my first read through of that, I thought that meant if you execute a query on an already open database, it will wait for the lock to clear and finish normally. But if you try to SQLite_Open a file that is locked by something else, then it will return SQLITE_BUSY. Reading it over again is only making me less sure.

Testing is called for. If/When I get a chance, I might try a hand at testing those conditions.

[XinLu]

I believe so.

It is unclear with these two parts of what quoted:

So... what does SQLite do, wait/write/return as normal, or return SQLITE_BUSY and expect the app to detect that and try again? On my first read through of that, I thought that meant if you execute a query on an already open database, it will wait for the lock to clear and finish normally. But if you try to SQLite_Open a file that is locked by something else, then it will return SQLITE_BUSY. Reading it over again is only making me less sure.

Testing is called for. If/When I get a chance, I might try a hand at testing those conditions.

Thank you. But the writing time takes only about a few of milliseconds, so I am afraid that it is hard to test those conditions.

[PsaltyDS]

Thank you. But the writing time takes only about a few of milliseconds, so I am afraid that it is hard to test those conditions.

What was that, a Bobby Flay Throw-down challenge...? I'm not digg'n your negative vibes, man.