SQL Server Compact pending lock
I have an application where I am using Sql Compact 3.5 with VS2008. I am running multiple threads in my application that communicates with a compact database and accesses a string. It selects and removes those rows in such a way that you select and present 5 rows to the application and deletes those rows from the table. It works fine with one thread, but if I use multiple threads then if 3 or more threads are running I often get TimeOut error !!! I increased the Time out property on the connection string, but that didn't give the expected result. The error log looks like this:
A SQL Server Compact lock awaiting a lock. The default lock time is 2000ms for devices and 5000ms for desktops. The default blocking timeout can be increased in the connection string using the ssce: default blocking timeout property. [Session ID = 5, Thread ID = 4204, Process ID = 4808, Table Name = XXX, Conflict Type = Lock x (s blocks), Resource = TAB]
The request I'm using to fetch looks like this:
"select" Top "(5) * from TableName order by id; delete from TableName, where id (select top (5) id from TableName order by id);"
Is there any way we can avoid this Time Out exception ???????
The above query I un as a transaction in VS2008 using SQLCECommand and the other using SqlCEDataAdapter.
Any Idea !!!!!! To answer
I had exactly the same problem. I know this is an old question, but for anyone on Google for an answer, I fixed it by calling Reader.Close () on my dataReader when I was done with it.
The app was single threaded, but I got it all the time if I made too many requests too quickly.
I hope this helps!
source to share
There are some connection string options you can try, like "default lock timeout", etc.
See SqlCeConnection.ConnectionString Property at http://msdn.microsoft.com/en-us/library/system.data.sqlserverce.sqlceconnection.connectionstring(VS.80).aspx .
(I had nothing but problems with chaining requests using the colon).
source to share
I am using the Time Lock Timeout property, but it doesn't seem to respond to it. It still gives me the same error that I saved, this value is 10000. The query waits until that time and it finds until the table is busy and hence it throws an exception. Is there a way that we only block the hte row instead of the table ??????