Problems with multithreading. Need advice. Sqlite.

8 posts, 1 answers
  1. Jose Mejia
    Jose  Mejia avatar
    107 posts
    Member since:
    May 2009

    Posted 29 Mar 2012 Link to this post

    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.
  2. Jose Mejia
    Jose  Mejia avatar
    107 posts
    Member since:
    May 2009

    Posted 02 Apr 2012 Link to this post

    up,up,up
  3. DevCraft banner
  4. Ady
    Admin
    Ady avatar
    588 posts

    Posted 03 Apr 2012 Link to this post

    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
    Telerik OpenAccess ORM Q1 2012 release is here! Check out what's new or download a free trial >>
  5. Jose Mejia
    Jose  Mejia avatar
    107 posts
    Member since:
    May 2009

    Posted 04 Apr 2012 Link to this post

    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.




  6. Ady
    Admin
    Ady avatar
    588 posts

    Posted 05 Apr 2012 Link to this post

    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
    Telerik OpenAccess ORM Q1 2012 release is here! Check out what's new or download a free trial >>
  7. Jose Mejia
    Jose  Mejia avatar
    107 posts
    Member since:
    May 2009

    Posted 05 Apr 2012 Link to this post

    Hello.

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

  8. Jose Mejia
    Jose  Mejia avatar
    107 posts
    Member since:
    May 2009

    Posted 09 Apr 2012 Link to this post

    up
  9. Answer
    Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 10 Apr 2012 Link to this post

    Hi Jose,

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

    Kind regards,
    Thomas
    the Telerik team
    Telerik OpenAccess ORM Q1 2012 release is here! Check out what's new or download a free trial >>
Back to Top
DevCraft banner