Problems with multithreading. Need advice. Sqlite.

[Jose Mejia]

Hello.

I'm using OA ver.  2012.1.301.2 in my WCF application. As a database I'm using sqlite.
I have service with InstanceContextMode.PerSession which owns OA model (OA context). I also have percall service (lets call it service2) which save some data in db. Service2 uses model provided by  service1. I'm also using spatialite extension to store some geodata in sqlite db so in my case Save changes looks like:

public partial class ArchivariusModel
 {
  internal void SaveGeoInfo()
   {
    using (DbCommand cmd = Connection.StoreConnection.CreateCommand())
     {
        cmd.CommandText = @"SELECT load_extension('libspatialite-2.dll');";
        cmd.ExecuteNonQuery();
        SaveChanges();
     }
  }
}

When I'm trying batch insert lots of data it throws exceptions like NRE, "Telerik.OpenAccess.Exceptions.InvalidOperationException: Not allowed because Commit / Rollback is in progress" and etc.
So, I read this blog post and decided to use lock;

public void SaveGeoData<T>(T data) where T : class

   {


      if (data == null) throw new ArgumentNullException("data");

      lock (_savelocker)

      {

         _model.Add(data);

          _model.SaveGeoInfo();

      }

   }


But when I have more than one sessions which own  models I'm steel getting errors, exceptions like above.
I decided to use static lock for all models not for it's instance which ofcourse decreases performance and I got Telerik.OpenAccess.RT.sql.SQLException: The database file is locked.

All these problems I'm expecting when I'n trying to do batch insert, so SaveGeoData calls one thread in percall wcf service.

I tried every possible settings in Isolation level and transaction mode -- nothing helps. Maybe you can advice preferable settings
for working with sqlite with highload, some useful pragmas and etc. Maybe I should recompile sqlite engine with specific options?

Maybe there are some usefull patterns for this case (highload,sqlite)? Also can you explain for what purposes Multithread context option is if it doesn't help? I can provide you simple multithreaded application with settings that duplicate my main app.

Thank you very,very much in advance.

PS: I'm using 1.0.76.0 client of Sqlite for .net.

[Jose Mejia]

up,up,up

[Ady]

Hi Jose,

 I see that you are accessing the 'OAConnection.StoreCommand' property in the method - SaveGeoInfo. You should avoid accessing the underlying DbConnection instance because once the underlying DbConnection has been accessed by user code, OpenAccess disowns the connection i.e it is the user's responsiblity to close the connection to the database.  You can instead use something like the following :

01.using(OAConnection con  = this.Connection)
02.{
03.     using (DbCommand cmd = con.CreateCommand())
04.     {
05.        cmd.CommandText = @"SELECT load_extension('libspatialite-2.dll');";
06.        cmd.ExecuteNonQuery();
07.        SaveChanges();
08.     }
09.}

Note that it is very important to dispose the OAConnection instance obtained from the context. Please try the above approach and let me know if that fixes your problem.
If that does not help can you send me a sample application where I can reproduce the exceptions?

Kind regards,
Ady
the Telerik team

[Jose Mejia]

Hello, Ady!

Thank you for reply.
I changed code as you adviced and now I'm getting the following exception:

Telerik.OpenAccess.Exceptions.LockNotGrantedException: Telerik.OpenAccess.RT.sql.SQLException: The database file is locked
database is locked
   at Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.executeUpdate()
   at OpenAccessRuntime.Relational.conn.PooledPreparedStatement.executeUpdate()
   at OpenAccessRuntime.Relational.RelationalStorageManager.lock_Renamed(RelationalOID oid, RelationalClass relationalClass)
   at OpenAccessRuntime.ExceptionWrapper.Throw()
   at OpenAccessRuntime.DataObjects.PCStateMan.handleException(Exception x)
   at OpenAccessRuntime.DataObjects.PCStateMan.getObjectFieldImp(PersistenceCapable pc, FieldMetaData fmd, Object currentValue)
   at OpenAccessRuntime.DataObjects.SynchronizedStateManagerProxy.GetObjectField(PersistenceCapable pc, Int32 field, Object currentValue)

Backend config is:

<BackendConfigurationSettings>
     <BackendConfiguration>
       <Backend>sqlite</Backend>
       <ProviderName>System.Data.SQLite</ProviderName>
       <Runtime>
         <CacheReferencesType>Weak</CacheReferencesType>
         <Concurrency>PESSIMISTIC_WRITE_LOCK_WHEN_FETCHED</Concurrency>
         <MultithreadedContext>True</MultithreadedContext>
       </Runtime>
       <ConnectionPool>
         <ConnectionTimeout>2000</ConnectionTimeout>
         <IsolationLevel>ReadUncommitted</IsolationLevel>
         <Pool>ADOManaged</Pool>
       </ConnectionPool>
       <Logging>
         <LogLevel>All</LogLevel>
         <LogFileName>d:\\DalTrace</LogFileName>
         <MaxFileSize>10000</MaxFileSize>
         <EventText>True</EventText>
         <MetricStoreSnapshotInterval>0</MetricStoreSnapshotInterval>
       </Logging>
     </BackendConfiguration>
   </BackendConfigurationSettings>

I'm using WCF and I've got two kinds of services: -- per session and per call.
Per session WCF service owns OA context(model) stores in global dictionary mapping from session to OA context.
I need to save a lot of files so I'm using streams(wcf messages) in  and per call wcf service instances. These instances getting OA context through global dictionary:

Archivarius archivarius = ArchSessionManager.Instance[sessionId];
var dal = archivarius.Dal;

Dal wraps OA context  -- have methods like SaveGeoInfo and etc...
So when there are  more then one active users  (2 or 3)  and they are going to save lots of files, and store data in sqlite db I'm getting provided errors.

So, maybe there are patterns to use OA with WCF with these scenarios?
Maybe I'm doing smth wrong?

Thanks in advance.

[Ady]

Hello Jose,

 You get the 'LockNotGranted' exception because you are using Pessismistic concurrency control where a lock is requested for objects that are fetched. Do you really need pessimistic concurrency or can you Optimistic concurrency control where no locks are obtained but the concurrency checks are done during the commit of a transaction? You can find more information on the various options, here.

Kind regards,
Ady
the Telerik team

[Jose Mejia]

Hello.

I'm getting the same error. I'm increased Lock timeout to 10000msec. Nothing helps.

[Jose Mejia]

up

[Thomas]

Hi Jose,

SQLite supports only concurrent reads, not concurrent writes. This is why the database file is locked.

Kind regards,
Thomas
the Telerik team