2

I have discovered that SQL CE is not thread safe:

SQL CE objects are not thread-safe and are not thread affinitive either. If an instance of SqlCeConnection or SqlCeTransaction is shared across threads without ensuring thread safety, then that may lead to Access Violation exception.)

I do need to share connection since there are lot of client instances, each of them reading/writing to DB at the same time.

Also in order to maintain the speed I do not want to use locking, I guess I would need to use standard SQL server, is it right? Thanks

  • The objects aren't thread safe but the database is, if you use different object (especially a different connection object) for each thread. – ChrisW Jun 18 '17 at 10:19

3 Answers3

3

No database product, no matter how high you move up the chain, will let your share connections and transactions between threads concurently. You will always have to serialize access to a transaction/connection. You are asking for a non-sense requirement. One thread is going to commit what the other just rolled back? There are many products that support thousands and thousands of clients reading and writing the database at the same time. None, ever, 'shared' connections between threads.

To achieve high scalability you should do the same thing everyone else does:

  • use connection pooling to amortize the cost of login handshake
  • associate a connection with a request for the duration of servicing a request
  • use short transactions
  • optimize access path to avoid reading unnecessary data (eliminate all scans)
  • use proper locking granularity

If the load is read intensive then you can consider a versioned isolation model, like read committed snapshot in SQL Server. If the load is update intensive then you must follow OLTP principles and use short, granular locks to do any update, while avoiding work on correlated items from different transactions to reduce deadlock probability.

Remus Rusanu
  • 8,283
  • 1
  • 21
  • 23
  • SQL CE doesn't implement connection pooling, by the way. I think it's meant to be on the same machine as the application[s] using it, so "connecting" isn't an especially high overhead. – ChrisW Jun 18 '17 at 10:20
0

If SQL CE has the features you need you should look at sql express instead of buying standard, however neither product was optimized for multiuser access like sql standard and above.

Jim B
  • 24,081
  • 4
  • 36
  • 60
0

You are correct, SQL CE does not support having multiple connections coming into a single databsae. You need to move up the chain to something a bit bigger.

SQL Express would be the next step up, and definitely works under multi-user scenarios. And best of all, it is free. So as long as the limits placed on it won't stop you from getting the job done, you will be much better off using it.

Also, it is practically an in-place upgrade to move from Express to Standard when the time comes.

Goyuix
  • 3,214
  • 5
  • 29
  • 37