SQL Server Compact timed out waiting for a lock

Question

Hi all, I'm having an application in that i use Sql Compact 3.5 with VS2008. I'm running multiple threads in my application which contacts the compact database and accesses the row. It selects and deletes those rows in a fashion i.e selecting and giving to the application 5 rows and deleting those rows from the table. It works great with a single thread but if i use multiple threads i.e if 3 or more threads are running I get very often the TimeOut Error!!! I have increased the Time out property in the connection string but it didn't give me expected result. The error log is as follow:

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 = 4204,Process id = 4808,Table name = XXX,Conflict type = x lock (s blocks),Resource = TAB ]

The Query that I use to retrieve is as follows:

" select Top(5) * from TableName order by id; delete from TableName where id in(select top(5) id from TableName order by id); "

Is there any way by which we can avoid this Time Out exception???????

The above query I un as a transaction in VS2008 one using SQLCECommand and the other using SqlCEDataAdapter.

Any Idea!!!!!! Reply

Answer 1

There are some connection string settings you can try, like "default lock timeout", and so on.

See SqlCeConnection.ConnectionString Property at http://msdn.microsoft.com/en-us/library/system.data.sqlserverce.sqlceconnection.connectionstring%28VS.80%29.aspx.

(I had nothing but trouble chaining queries with the semi-colon.)

Answer 2

I'm using Default Lock Timeout property but doesn't seems to react or what. It still gives me the same error I have kept it's value as 10000. The query waits till that time and it finds till the table busy and hence it gives the exception. Is there any way so that we lock only hte row instead of table??????