CF 2.0 multithread app accessing database

[Chris]

I am developping a smart device app that uses data stored in mobile SQL server.
There is a background thread that every specified period of time
synchronizes data from desktop SQL server to mobile one (by webservice). The
thread inserts data to mobile sql server only.
The data stored in the mobile database can be olsa synchronized by user
(simply by clicking a button in app). How to handle situation when data in
the database is being synchronized by the user (by clicking the button) and
the background thread?
I have tried to use 1 mutex (Mutex.WaitOne() and Mutex.ReleaseMutex()). But
in my opininion it does not work properly. Even if the mutex has been locked
(WaitOne() method return true value), callinng WaitOne() returns true again.
Why?
It seems that synchronization by user and the thread at the same time causes
errors/crashes in the app. Maybe should I use Monitor instead of Mutex (or
lock keyword)?

Should I use Monitor/Mutex (or lock) when I am calling select/delete in the
database?

 

[Ginny Caughey MVP]

Chris,

You should be able to open a SQL Mobile/Compact databse from multiple
threads without having to use a mutex. Are you using different connections
for the different threads? If not, that's the first thing I'd try. If you
are using different connections, perhaps a small sample of code that
reproduces the problem would be helpful.

Ginny Caughey
Device Application Development MVP

 

[Chris Tacke, eMVP]

If it's not locking you must be doing something wrong. A Monitor is a
critical section and it's locking scope is to a thread within a single
process. A mutex is a much larger hammer and works system-wide. In your
case a Monitor is probably a better choice due to speed, not necessarily due
to functionality.


--

Chris Tacke, Embedded MVP
OpenNETCF Consulting
Giving back to the embedded community
http://community.OpenNETCF.com

 

[Simon Hart [MVP]]

Lock (language lock) is the right choice here or Monitor which is what lock
is doing. Show us some code and we might be able to point you in the right
direction. Of course you don't want to be doing the same thing from two
different threads, here you will get unpredictable results.

 

[Chris]

Once again: there is a pocket pc app acessing data in pocket database. Data
stored in this db must be also synchronised with a desktop db.
(Synchronization means that data is read from the desktop db and stored in
the pocket db).
My pocket app has 1 background thread that gets data (by web service) from
the desktop db and stores it in the pocket db.
On the other hand, user can read (select sql statement) or delete (delete
sql statement) data from the pocket db. The problem is (in my opinion) how to
handle situation when the background thread is storing data and the user is
going to delete it. I think access to the pocket database (its tables) should
be done with monitor/mutex/lock keyword.
There is some c# code (but I wanted to show you a general idea of the app,
not the very code):

private static Thread synchroThread;
private static object obj=new object();

private void Form_Load(object sender, EventArgs e)
{
synchroThread = new Thread(new ThreadStart(Synchronize));
synchroThread.IsBackground = true;
synchroThread.Start();
}

private void Form_Closing(object sender, EventArgs e)
{
if (synchroThread != null)
synchroThread.Abort();
}

private static void Synchronize()
{
while (true)
{
System.Threading.Thread.Sleep(1000 * this.SynchroInterval);
if (System.Threading.Monitor.TryEnter(this.obj))
{
//data is got from the webservice and stored in the pocket db
}
else
MessageBox("cannot synchronize data. sql table in use by other process");
}
}

//on the other hand the user can also synchronze data (synchronzation on
demand)
private void button_Click(object sender, EventArgs e)
{
if (System.Threading.Monitor.TryEnter(this.obj))
{
//sql: delete from table
//or
//data is got from the webservice and stored in the pocket db
}
else
MessageBox("cannot synchronize data. sql table in use by other process");
}


In the code above there is Monitor used. It works ok - I mean sometimes I am
getting info that: "cannot synchronize data. sql table is use by other
process"
When I was using Mutex - the method WaitOne always returned true (even if
the mutex was locked after I have called WaitOne. What did it happen??)

Anyway, now I am using Monitor. But still sometimes my app hangs on section
written as:
//sql: delete from table
//or
//data is got from the webservice and stored in the pocket db,

and sometimes I am getting error saying (something like that): attempting to
read or write protected memory. this usualy means the memory is corrupted.

I hope my explanation is clear now!

 

[Chris]

Oh, sorry, I forgot to exit the monitor (in the code shown you only). I am
exiting monitor in my app:

private static Thread synchroThread;
private static object obj=new object();

private void Form_Load(object sender, EventArgs e)
{
synchroThread = new Thread(new ThreadStart(Synchronize));
synchroThread.IsBackground = true;
synchroThread.Start();
}

private void Form_Closing(object sender, EventArgs e)
{
if (synchroThread != null)
synchroThread.Abort();
}

private static void Synchronize()
{
while (true)
{
System.Threading.Thread.Sleep(1000 * this.SynchroInterval);
if (System.Threading.Monitor.TryEnter(this.obj))
{
//data is got from the webservice and stored in the pocket db
System.Threading.Monitor.Exit(this.obj);
}
else
MessageBox("cannot synchronize data. sql table in use by other process");
}
}

//on the other hand the user can also synchronze data (synchronzation on
demand)
private void button_Click(object sender, EventArgs e)
{
if (System.Threading.Monitor.TryEnter(this.obj))
{
//sql: delete from table
//or
//data is got from the webservice and stored in the pocket db
System.Threading.Monitor.Exit(this.obj);
}
else
MessageBox("cannot synchronize data. sql table in use by other process");
}

 

[Simon Hart [MVP]]

Firstly your code looks a little odd. I'm not sure if you quickly hacked this
together to show us a quick sample or what but a couple of things spring to
mind.

1. Are you openig and closing Form1?
2. You know that calling thread.Abort() will generate an exception?
3. Why don't you use lock(obj) instead of Monitor.TryEnter?
4. Why is the Thread object static?
5. Why this not placed in a business layer.

Sorry for all the questions but it seems like a strange bit of code.

 

[Simon Hart [MVP]]

Forget about point 3 as you are calling this code from a UI thread
--
Simon Hart
Visual Developer - Device Application Development MVP
http://simonrhart.blogspot.com

 

[Chris]

1. Are you openig and closing Form1?
No. That happens when the form is opened and user clicks button to
synchronize on demand

2. You know that calling thread.Abort() will generate an exception?
I see it. It throws exception in mscorelib.dll but the exception isnt thrown
to user.
How to change that piece of code (to stop the thread)?

3. Why don't you use lock(obj) instead of Monitor.TryEnter?
Why in UI thread cant I use lock(obj)?

4. Why is the Thread object static?
Why not? Should I change it? I need 1 synchronizing thread only. Not more.

5. Why this not placed in a business layer.
Thats my fault - subject to change

thx for all the help!

I think, the problem has been solved. I used a global sqlconnection object,
that was opened all the time and used when needed. Now, I create/destroy a
local connection when needed. The problem happend when one connection object
was used more than once at the same time.

 

[Ginny Caughey MVP]

Chris,

This makes sense. You need a separate connection for separate threads.
Accessing a single SqlCeConnection from multiple threads, even with
locks/monitors/critical sections isn't supported.

Ginny Caughey
Device Application Development MVP

 

[Doug]

Just want to get clarification on this statement. It doesn't seem
right.

"Accessing a single SqlCeConnection from multiple threads, even with
locks/monitors/critical sections isn't supported"

If I keep a "pool" of connections and the connections are used from
many different threads, but never used by more than a single thread at
a given time what problem does this cause and why is it not supported?

As for the Chris' statement:

"It works ok - I mean sometimes I am
getting info that: "cannot synchronize data. sql table is use by
other
process"

This seems normal to me. You just happen to hit a point when the user
has initiated a transaction at the same time as your sync thread. I
would say your code is working.

Lastly, some questions.

Providing Chris isn't using the same sqlce connection from both
threads, why does he need to use a lock at all? Is sqlce not capable
of handling more than 1 transaction at a time?

Thanks,

Doug

 

[Ginny Caughey MVP]

Doug,

It sounds like you understand things the way I do. Using different
connections on different threads, he shouldn't need a lock at all for the
connections. Your pool approach should work fine. What is not supported is
using a single connection on multiple threads at the same time.

Ginny

 

[Simon Hart [MVP]]

Why in UI thread cant I use lock(obj)?

You can, but the UI thread will block until the object becomes available so
your user will be sitting there waiting for your application to become
responsive again. Users don't tend to like this much