SQL Server Compact timed out waiting for a lock
- by jankhana
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
