SQL Server Compact timed out waiting for a lock

[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!!!!!!

[ck9663]

Once the selected statement is returned, do you display that on your GUI first then delete it after a click of a button or something? You might want to consider modifying your design. You can gather the ID that was displayed and delete only those ID on your table. Not use a query. You might encounter a problem, specially if there's a NULL on your ID.

You may also give more detail on your application. Let's see if we may be able to suggest some other way.

Happy Coding!

--- CK

[jankhana]

Hi,
Thanks for the reply.

Ya i use those values in my application for further processing. one command inserts the records in the table and so soon as the records are inserted it is used by the application. To make the process fast I've multiple threads that contact the table and uses the records. Each thread takes five-five records does the job. The records are used in the application for further processing and it's not for displaying in GUI but at the backend for processing and hence I don't want duplicate records also and so I'm using delete query to delete the record. It works properly if I've just one thread but if I have more threads say nearly five or ten than I get those TIMEOUTEXCEPTION and my process gets slow down.

So is there any way by which these exceptions can be avoided????????

The table has one Primary Key called ID using which I retrieve or delete the records!!!.
Any Idea!!!!!!!!!!!!!

[ck9663]

Why not do an update of record if it's existing. Your delete could be locking everyone so no one can access it. You might also have a big table that the delete is taking a while, hence the timeout.

Good luck!!!

--- CK

[jankhana]

Hi,
Thanks for the reply...

I don't need those records again to be used and hence i'm deleting it. Ya table is big. It may contain hundred records or one lakh records depending on user's choice. Mostly ten thousand records will be there and once i get the record i don't want to use them again and hence i need to delete them!!!!!!!!
hmmm........ So update will not work in my scenario......