Prevent index page locks with SQLServer Compact

Question

We are using SQLServer Compact 4.0 with an application where multiple threads process and insert data concurrently. Each thread has its own database connection.

We are seeing lots of deadlocks when running the application, apparently due to SQL Server Compact using PAGE locks for the index by default. The page locks - while having some advantages - can apparently cause two transactions to conflict, even if they are not working on the same row. Whether two rows share the same index page or not, is practically not known from an application's perspective. 

Here is a scenario: 

• Thread 1: updates/inserts Row 1 in Table A, then Row 2 in table B
• Thread 2: updates/inserts Row 3 in Table B, then Row 4 in table A

This gives a good chance of a deadlock, even through both threads work on distinct rows. In understand, that if they were working on the same rows, then this would need to fail, but they don't.

We need a solution to the problem, but the following attempts have failed so far or constraints prevent the solution

• Thread 1 and 2 really need to operate this way. There is no way they could allocate the resources in the same order. 
• we do not seem to be able to append WITH ROWLOCK to each SQL command (basically we use NHibernate and haven't found a way to do this)
• The full SQL Server has an option ALLOW_PAGE_LOCKS when creating indexes. This does not seem to be supported by SQL Server Compact.
• We have not found any option to disable index page locks for the entire database or connection

Is there any other way to prevent index page locks other than using the WITH ROWLOCK option on each statement?

Any help would be highly appreciated

J.-

Answer 1

You need to rethink your design, so you can access resources in the same order from all threads

---

Please mark as answer, if this was it. Visit my SQL Server Compact blog