I'm using SQL Compact 3.5 SP2. My application is multi-threaded, but it does not share connections across threads. Instead, I use a custom object pool to ensure that each thread gets its own connection. That said, it's possible that a connection might be
re-used on different threads at different times... in other words, I'm assuming that the connections don't have thread affinity. Also, not sure if it matters, but I'm using Entity Framework in .NET 3.5 SP1.
Anyway, when I've got high load situations (8+ threads), I'm getting lock timeout exceptions (regardless of the length of the timeout setting), and the exception always says the lock was on the __SysObjects table.
I'm not doing any DDL, so I don't understand why I would get locking timeouts on that table. Ideas?
The exact error is:
An error occurred while executing the command definition. See the inner exception for details. ---> 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 = 11,Thread id = 7224,Process id = 8592,Table name = __SysObjects,Conflict type = x lock (s blocks),Resource = RID: 1030:29 ]
View Complete Post