sql server compact deadlock caused by page-lock on index

Question

I am working on a c# desktop application using NHibernate as data access layer and SQL Server compact. The application uses multiple threads to perform selects and updates of data.

In general it is working fine but sometimes deadlock situations arise.

15:28:16,750 55 WARN : System.Data.SqlServerCe.SqlCeLockTimeoutException (0x80004005): Timeout ..... [ Session id = 28,Thread id = 14576,Process id = 12960,Table name = PatientOrder,Conflict type = x lock (x blocks),Resource = PAG (idx): 1035 ]

I can understand deadlock problems if two transactions try to update the same rows. However in this case both transactions are working on different rows. The deadlock seems to be caused by the page lock on an index (Resource = PAG (idx): 1035).

So my question is: What can be done to prevent these deadlocks?

I already looked into the following options:

• Disable page locks for the index. This is possible for "full" SQL server but does not seem to be supported by the compact edition

• Configure hibernate to generate with(rowlock) for the sql statements that would (hopefully) prevent the problem

• Trying to use session.lock(...) to enforce resource access in the same order for all transactions. However this does not seem to help as the deadlocking transactions are working on different rows anyway

• Serialize all database transactions so that only one is active at a time. This does work but has a heavy performance impact.

Answer 1

@Wolfgang How you are managing your ISession? ISession is not threadsafe, if you are using a single instance and sharing between threads this should be your problem. If you are using a IoC, verify the scope of ISession, change that for one per thread.