Hi,
we are using UniDAC 6.3.12 to access SQLite3 databases on Android with Delphi 10.1 Berlin. After a while, we added an Android Service which also writes data into the same database, also with UniDAC / Delphi, in a separate process.
Since then we encountered problems when the two processes tried to write at the same time. Access violations until app restart, corrupted database files. After we switched to WAL mode it is a whole lot better, but not gone completely. We have added the behavior that if an exception is thrown during commit, it tries to commit for two more times, but that didn't help.
As I can see in the SQLite FAQ, SQLite is capable of concurrent write access through file locking. The second process just waits until the first one unlocks the file, if the lock is not released in a specific time, SQLITE_BUSY is returned.
So my question is, is UniDAC able to handle concurrent write access? Are there any properties to set to make the component more concurrent access proof? Do you have any suggestions?
We are not using direct mode.
Thanks
Dominik
Concurrent access
Re: Concurrent access
Yes, indeed, the 'database is locked' error may occur in the situation you are describing. Its occurrence is due to the specificity of locking implementation in SQLite. According to the link provided by you, SQLite developers do not recommend to use a lot of competitive requests to the database in applications. This error handling must be implemented
at the application level, because a similar error can occur in other scenarios, which differ from yours, and the logic of its handling depends on the business logic of the particular application. For example, you can handle an error by wrapping a piece of code in the try ... except statement:
You can get more detailed information about error codes in SQL official documentation: https://www.sqlite.org/rescode.html .
at the application level, because a similar error can occur in other scenarios, which differ from yours, and the logic of its handling depends on the business logic of the particular application. For example, you can handle an error by wrapping a piece of code in the try ... except statement:
Code: Select all
uses LiteError;
...
try
...
except
on E: ESQLiteError do
if (ESQLiteError(E).ErrorCode = 5 {SQLITE_BUSY}) or (ESQLiteError(E).ErrorCode = 517 {SQLITE_BUSY_SNAPSHOT}) then
begin
...
end
else
raise;
end;
Re: Concurrent access
Hi Maxim,
thank you for your answer. We have already tried to catch the exception and redo the commit, in fact we catch it two times. The problem is that we do not get an ESQLiteError, but an AccessViolation. Once we get this exception, all other Database access result in this AV until the App is restarted.
So I think we'll test with a newer version of UniDAC to see if we still get the AV. Do you recommend using direct mode?
Thanks
Dominik
thank you for your answer. We have already tried to catch the exception and redo the commit, in fact we catch it two times. The problem is that we do not get an ESQLiteError, but an AccessViolation. Once we get this exception, all other Database access result in this AV until the App is restarted.
So I think we'll test with a newer version of UniDAC to see if we still get the AV. Do you recommend using direct mode?
Thanks
Dominik
Re: Concurrent access
Of course, you can use the Direct Mode in your project. To reproduce the described Access Violation error in our environment, please compose and send us a small separate project, in which the issue occurs. In addition, we will need a file with the SQLite test database used in this project. To do this, use the e-support form (https://www.devart.com/company/contactform.html)