Is it just me, or... is SQL Server CE supposed to be a 'single user' database?

It is supposedly safe for multi threading...

You might be able to use something like the method I used just recently for a normal SqlConnection, to specify a timeout:

http://improve.dk/blog/2008/03/10/controlling-sqlconnection-timeouts

The docs for SqlCeConnection look at little disappointing:

"Any public static (Shared in Visual Basic) members of this type are thread safe. Any instance members are not guaranteed to be thread safe."

-- http://msdn2.microsoft.com/en-us/library/system.data.sqlserverce.sqlceconnection(VS.80).aspx

I haven't tried it because I don't use CE, but doesn't specifying a timeout value in the connect string work as per SQL Server?

SqlCeConnection conn = new SqlCeConnection();

conn.ConnectionString = "Persist Security Info=False; Data Source = Northwind.sdf;" +

"Password = <password>; server=mySQLServer;Connect Timeout=30";

You cannot share a SqlCeConnection between threads, but you can use a single DB from multiple threads

Mr_Simple,

No, this is not supported in SQL CE.

CE is a horrible database. No scalar queries in selects/where etc., a nasty bug in parameter binding in anonymous parametered queries (so you have to change code for CE 3.x separately), and less features than MS Access.

I'd abandone it a.s.a.p. if you have the chance and go for a .mdb or firebird embedded approach.

Looks like setting up 'ssce: default lock timeout' property in connection string can help you.

see http://msdn2.microsoft.com/en-us/library/ms173385.aspx

This is for a transaction. And the default is valid and good for me.

My issue is with opening the connection

See if this helps any.

http://msdn2.microsoft.com/en-us/library/aa326335(VS.71).aspx

Connect Timeout is not supported by Sql CE

I got this:

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 = 2,Thread id = 4700,Process id = 4296,Table name = Test,Conflict type = s lock (x blocks),Resource = TAB ]

So, timeouts are supported.

Alex,

the situation above is showing a situation of them waiting for ~6 HOURS.

That is with the defaults, so it should have timed out after 4 seconds, it didn't

Well... It did for me (how else could I get this message). Can you build 10 LOC test bed demonstrating this deadlock?

http://msdn2.microsoft.com/en-us/library/aa326335(VS.71).aspx

So basically you are saying the Microsoft docs are wrong - correct?

Just trying to save the rest of us a little time here so we don't have to run down the same issue.

Those docs are for 1.1, I am using the 2.0 version.

And yes, the observed behavior doesn't match the docs.

One last question, again for the benefit of us all, but mostly me because I need things simple.

Your comment "the observed behavior" has intrigued me because I work with databases most of the time, and like most folks here have had the docs not match our personal observations from time to time.

Are you stating you have found official Microsoft documentation that the connection timeout property is not supported anymore, or is this something that your are just fighting on your box at the moment.

There is a very important distinction between Microsoft dropping support for this critical feature and you fighting the API on your box.

Just wondering.

I mean that doing new SqlCeConnection("Database=test.sdf;Connect Timeout=30").Open() throws an exception, Connect Timeout not understood.

Connection Timeout property is read-only and is equal to 0.

http://msdn2.microsoft.com/en-us/library/system.data.sqlserverce.sqlceconnection.connectiontimeout.aspx

Ah. Now the explanation of the failure is simple, but most of all clear.

Mr_Simple says thanks for sharing your pain on this issue.