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

+2


source to share


4 answers


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!

+2


source


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).

+1


source


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 ??????

0


source


I had the same problem just now ...

The problem was that I made a table ALTER

in a transaction and then tried to read it from that table later in the same transaction. oops. Dropped the table ALTER

from the transaction and everything is fine again.

0


source







All Articles