Behavior:

The OperationTimeoutException depends on the amount of records.

Entities:



[Serializable]
[HierarchyRoot]
public class TestA : Entity
{
  public TestA( Session session ) : base( session ) { }

  [Field, Key]
  public int Id { get; private set; }

  [Field]
  public int Val { get; set; }
}

[Serializable]
[HierarchyRoot]
public class TestB : Entity
{
  public TestB( Session session ) : base( session ) { }

  [Field, Key]
  public int Id { get; private set; }

  [Field]
  public int Val { get; set; }
}

Init:


using( var session = domain.OpenSession( new SessionConfiguration( SessionOptions.ServerProfile ) ) )
{
  using( var transaction = session.OpenTransaction() )
  {
    for( int I=0; I < 0x01FF; I++ )
      new Model.TestA( session );
    // To much records???
    for( int I=0; I < 0x01FFF; I++ )
      new Model.TestB( session );
    transaction.Complete();
  }
 }

Exception:


using( var sessionA = domain.OpenSession( new SessionConfiguration( SessionOptions.ServerProfile ) ) )
{
  using( sessionA.OpenTransaction() )
  {
    sessionA.Query.All<model.testa>().Count();
    sessionA.Query.All<model.testb>().Count();
    using( var sessionB = App.Domain.OpenSession( new SessionConfiguration( SessionOptions.ServerProfile ) ) )
    {
      using( sessionB.OpenTransaction() )
      {
        sessionB.Query.All<model.testa>().Count();
        // Exception
        sessionB.Query.All<model.testb>().Count();
      }
    }
  }
}

Exception:


using( var sessionA = domain.OpenSession( new SessionConfiguration( SessionOptions.ClientProfile ) ) )
{
  sessionA.Query.All<model.testa>().Count();
  sessionA.Query.All<model.testb>().Count();
  using( var sessionB = App.Domain.OpenSession( new SessionConfiguration( SessionOptions.ClientProfile ) ) )
  {
    sessionB.Query.All<model.testa>().Count();
    // Exception
    sessionB.Query.All<model.testb>().Count();
  }
}

No Exception!!!


using( var sessionA = domain.OpenSession( new SessionConfiguration( SessionOptions.ServerProfile | SessionOptions.AutoTransactionOpenMode ) ) )
{
  sessionA.Query.All<model.testa>().Count();
  sessionA.Query.All<model.testb>().Count();
  using( var sessionB = App.Domain.OpenSession( new SessionConfiguration( SessionOptions.ServerProfile | SessionOptions.AutoTransactionOpenMode ) ) )
  {
    sessionB.Query.All<model.testa>().Count();
    sessionB.Query.All<model.testb>().Count();
  }
}

Exception Message:

  
Xtensive.Orm.OperationTimeoutException was unhandled by user code
  Message=SQL error occured.
SQL error details 'Type: OperationTimeout;'
Query 'SELECT CAST(COUNT([a].[Id])  AS bigint) AS [c01umn] FROM [TestB] [a];'
Original message 'SQL Server Compact timed out waiting for a lock. The default lock time is 2000ms for devices and 5000ms for desktops. The default lock timeout can be increased in the connection string using the ssce: default lock timeout property. [ Session id = 5,Thread id = 3436,Process id = 2952,Table name = __SysObjects,Conflict type = s lock (x blocks),Resource = RID: 1658:13 ]'
  Source=Xtensive.Orm
  StackTrace:
       at Xtensive.Orm.Providers.Sql.StorageDriver.ExecuteCommand[TResult](Session session, DbCommand command, Func`2 action)
       at Xtensive.Orm.Providers.Sql.StorageDriver.ExecuteReader(Session session, DbCommand command)
       at Xtensive.Orm.Providers.Sql.Command.ExecuteReader()
       at Xtensive.Orm.Providers.Sql.SimpleCommandProcessor.ExecuteTasksWithReader(QueryRequest lastRequest)
       at Xtensive.Orm.Providers.Sql.SessionHandler.<xtensive.orm.providers.sql.iqueryexecutor.executetuplereader>d__0.MoveNext()
       at Xtensive.Orm.Providers.Sql.SqlProvider.<onenumerate>d__9.MoveNext()
       at Xtensive.Orm.Rse.Providers.ExecutableProvider.<getenumerator>d__0.MoveNext()
       at Xtensive.Core.EnumerableExtensions.<batch>d__20`1.MoveNext()
       at Xtensive.Core.EnumerableExtensions.<applybeforeandafter>d__28`1.MoveNext()
       at Xtensive.Orm.Rse.RecordSet.<getbatchedenumerator>d__c.MoveNext()
       at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
       at Xtensive.Core.EnumerableExtensions.<batch>d__20`1.MoveNext()
       at Xtensive.Core.EnumerableExtensions.<applybeforeandafter>d__28`1.MoveNext()
       at System.Linq.Enumerable.<selectmanyiterator>d__14`2.MoveNext()
       at System.Linq.Enumerable.First[TSource](IEnumerable`1 source)
       at lambda_method(Closure , Object[] , IEnumerable`1 , Session , Dictionary`2 , ParameterContext )
       at Xtensive.Core.DelegateBindExtensions.<>c__DisplayClassd`6.<bind>b__c(T2 arg2, T3 arg3, T4 arg4, T5 arg5)
       at Xtensive.Orm.Linq.TranslatedQuery`1.Execute(Session session, ParameterContext parameterContext)
       at Xtensive.Orm.Linq.QueryProvider.Execute[TResult](Expression expression)
       at System.Linq.Queryable.Count[TSource](IQueryable`1 source)
  InnerException: System.Data.SqlServerCe.SqlCeLockTimeoutException
       Message=SQL Server Compact timed out waiting for a lock. The default lock time is 2000ms for devices and 5000ms for desktops. The default lock timeout can be increased in the connection string using the ssce: default lock timeout property. [ Session id = 5,Thread id = 3436,Process id = 2952,Table name = __SysObjects,Conflict type = s lock (x blocks),Resource = RID: 1658:13 ]
       Source=SQL Server Compact ADO.NET Data Provider
       HResult=-2147467259
       NativeError=25090
       StackTrace:
            at System.Data.SqlServerCe.SqlCeCommand.CompileQueryPlan()
            at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand(CommandBehavior behavior, String method, ResultSetOptions options)
            at System.Data.SqlServerCe.SqlCeCommand.ExecuteReader(CommandBehavior behavior)
            at System.Data.SqlServerCe.SqlCeCommand.ExecuteDbDataReader(CommandBehavior behavior)
            at System.Data.Common.DbCommand.ExecuteReader()
            at Xtensive.Orm.Providers.Sql.StorageDriver.<executereader>b__4(DbCommand c)
            at Xtensive.Orm.Providers.Sql.StorageDriver.ExecuteCommand[TResult](Session session, DbCommand command, Func`2 action)

asked Jul 26 '12 at 10:01

TeaMan's gravatar image

TeaMan
140141418

edited Jul 26 '12 at 10:12


3 Answers:

Hello Teaman,

During the investigation it was revealed that the exception is thrown only when there are nested sessions (and transactions) within one thread. Scenario with each session in a separate thread showed no problems. Seems that SQL Server CE provider has some thread affinity.

However, we've found a bug in SessionOptions.ClientProfile mode. After it is fixed, Example #2 started working without throwing the exception. The updated binaries (4.5.4 RC 2) will be published shortly.

Thanks!

answered Aug 03 '12 at 08:18

Dmitri%20Maximov's gravatar image

Dmitri Maximov
22111211

Hello Teaman,

I've found an interesting article concerning that case, please, check it out. The author suspects that due to large database fragmentation locktimeout exception is thrown and investigates the scenario in details.

Hope that helps.

answered Jul 26 '12 at 10:22

Dmitri%20Maximov's gravatar image

Dmitri Maximov
22111211

Hallo Dmitri,

thanks for the quick reply. Very interesting article!

But this case does not apply. Please see the query example with no exception (ServerProfile + AutoTransactionOpenMode).

It is very important for us that we can offer a trial version with the smallest possible installer. The retail version will then work with various 'real' SQL Servers - depends on requirement.

(Jul 26 '12 at 11:15) TeaMan TeaMan's gravatar image

Sorry Dmitri,

under 'Behavior' I forgot to mention that all the examples use concurrent read access in separate sessions.

By the way what can I do that the examples look better? The use of <pre><code> </code></pre> does not really help.

(Jul 27 '12 at 02:58) TeaMan TeaMan's gravatar image

Hello TeaMan,

I might be wrong, but the main idea from the article was that any subsequent command after the lockexception is thrown will lead to error.

However, if your database file is fragmented or large its possible that your select query may not run with default time out of 5 seconds in desktop so it will keep on throwing locktimeout exception every 5 seconds for every select query.

I think this can be easily validated and the third example proves the idea: just try running any single query in the first example in a separate transaction.

Alternatively, you may try SQLite

(Jul 27 '12 at 06:41) Dmitri Maximov Dmitri%20Maximov's gravatar image

As for the code formatting, you left 4 spaces before each line of code and the site will format that part as a code.

(Jul 27 '12 at 06:43) Dmitri Maximov Dmitri%20Maximov's gravatar image

Hallo Dmitri,

it has nothing to do with fragmented data or long running queries!

1.) There is a small test database with only 511 records in entity TestA und 8191 records in entity TestB.
2.) A deadlock must not occur on only read access between 2 sessions with 1 transaction in it.
3.) Point 2 is true with entity TestA and false with entity TestB.

(Jul 27 '12 at 08:53) TeaMan TeaMan's gravatar image

Hallo Dmitri,

according to some studies I've now discovered why the error is not noticed before.

Starting with DataObjects.Net version 4.5.X, there were significant changes in ClientProfile. Now the new function BeginDisconnectedTransaction() in session constructor leads to a permanently open transaction. The flag AutoTransactionOpenMode in ClientProfile is no longer used. Because of that example 2 fails.

Prior to version 4.5.X auto transactions were used. Why are these changes? According to the manual DisconnectedState don't want to keep transaction open.

(Jul 29 '12 at 13:01) TeaMan TeaMan's gravatar image

Hello Dmitri,

many thanks for the patch!

It seems there is a weird workaround to suppress the wrong exception. After entity inserts, a simple read access to the table in auto-commit mode avoids the unwanted locking errors permanently until next inserts.

For those interested here is an example:


using( var connection = new SqlCeConnection( String.Format( "DataSource='{0}'", 
       domain.Configuration.ConnectionInfo.ConnectionUrl.Resource ) ) )
{
  connection.Open();
  var Command = connection.CreateCommand();
  const string CommandText = "SELECT COUNT(*) FROM [{0}]";
  foreach( var entity in domain.Model.Types.Entities )
  {
    Command.CommandText = String.Format( CommandText, entity.MappingName );
    Command.ExecuteScalar();
  }
}

answered Aug 05 '12 at 13:30

TeaMan's gravatar image

TeaMan
140141418

edited Aug 05 '12 at 13:36

Your answer
Please start posting your answer anonymously - your answer will be saved within the current session and published after you log in or create a new account. Please try to give a substantial answer, for discussions, please use comments and please do remember to vote (after you log in)!
toggle preview

powered by OSQA