LinqConnect Professional Version 4.2.229 (18-Apr-2013) - How insert into SQLite from two process?

[AKRRKA]

Hello,

I have any new problem.
I need insert data into SQLite from several process (two and more application).
And i get error:

Code: Select all

Devart.Data.SQLite.SQLiteException (0x00000005): The database file is locked
database is locked
   в Devart.Data.SQLite.ad.h()
   в Devart.Data.SQLite.ad.b()
   в Devart.Common.f.c(DbConnectionBase A_0)
   в Devart.Common.DbConnectionClosed.Open(DbConnectionBase outerConnection)
   в Devart.Common.DbConnectionBase.Open()
   в Devart.Data.SQLite.SQLiteConnection.Open()
   в SQLiteStressTest.fMain.LoggingInsert(DateTime logDateTime, String logMessage, String logSource, String logData, String logType, String logUserName) в d:\Repository\test\SQLiteStressTest\SQLiteStressTest\FormMain.cs:строка 136
   в SQLiteStressTest.fMain.GenerateData(Int32 iCount, ProgressBar progressBar, Control label) в d:\Repository\test\SQLiteStressTest\SQLiteStressTest\FormMain.cs:строка 194
   в SQLiteStressTest.fMain.bGenerateTwo_Click(Object sender, EventArgs e) в d:\Repository\test\SQLiteStressTest\SQLiteStressTest\FormMain.cs:строка 61
   в System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
   в System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
   в System.Windows.Forms.Control.WndProc(Message& m)
   в System.Windows.Forms.ButtonBase.WndProc(Message& m)
   в System.Windows.Forms.Button.WndProc(Message& m)
   в System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

In the search for solutions, I found the following opportunity.Use modes Serialized (must be specified flag SQLITE_OPEN_FULLMUTEX) or Multi-thread (SQLITE_OPEN_NOMUTEX).
In the documentation of the Devart found this:

Code: Select all

Devart.Data.SQLite Namespace> SQLiteConnectionOpenFlags Class: SQLITE_OPEN_FULLMUTEX Field
...
[C #]public static int SQLITE_OPEN_FULLMUTEX
...

Link 1 Link 2 Link 3

But how to use it?

For example the problem that my test project: SQLiteStressTest.rar (833.2 КБ)
1) Run two instances of the application.
2) In the first instance click "Generate" as long as there is a process, click "Generate" and in the second instance.
3) We get an error about the blocking.

PS: I have license for dotConnect Universal Professional and LinqConnect Professional (both installed).
My developer license number is CRUni-00787 and CRSqlED-00467.

[MariiaI]

Only one process can make changes to an SQLite database at a time, due to the fact that the sqlite3.dll engine has this limitation.
For more information, please refer to http://www.sqlite.org/faq.html#q5
If you want to use several applications that can modify data in the same database, you should make sure they don't modify data simultaneously. Reading data by several processes or threads at the same time from a single database is an acceptable operation.

JIC: SQLite supports multithreading, not the 'several processes (two and more applications)'. However, the SQLiteConnection instance is not guaranteed to be thread safe. You should avoid using the same SQLiteConnection in several threads at the same time. It is recommended to open a new connection per thread and to close it when the work is done. Please refr to http://www.sqlite.org/threadsafe.html

[AKRRKA]

OK.

But when I changed the Devart.Data.SQLite to System.Data.SQLite error is gone.

Here, you can see from my example.
SQLiteOriginalStressTest.rar (1.1 МБ)

Can you explain why?

[MariiaI]

Sorry for the delay. We have investigated this behaviour and made some changes. From the next build of LinqConnect it will be possible to insert data into one SQLite database file from several processes.
We will inform you when the new build of LinqConnect is available for download.

[AKRRKA]

Thank you. It is waiting.

[MariiaI]

New build of LinqConnect 4.2.272 is available for download now!
It can be downloaded from http://www.devart.com/linqconnect/download.html (trial version) or from Registered Users' Area (for users with active subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?f=31&t=27387

[AKRRKA]

Really work.
Thanks.