Concurrent database access in SQL 2005 Mobile

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

sardo007 - 23 Jul 2008 19:14 GMT

Hello all... I'm having a very annoying issue that's about to make me crazy.

I have a database application developed with VS2005 and OLEDB access to a
SQL 2005 Mobile database. While the application is running the same database
can be accessed by another notifying application that shoud add, modify or
delete rows in some tables. The problem is as follows:

1- If the main application creates, uses and then destroys the OLEDB
connections, everything works just fine: changes made by the notifying app.
are seen by the application. This just works too slowly for the customer in
some devices.

2- One workaroud for the previous problem I found in these forums is to
create a "dummy" connection and keep it open. That way the database file is
maintained in memory and every subsequent connection is created and opened
faster. This works fine except that any changes made by the notifying
application is not seen by the main application until the user exits and then
enters again.

3- I have recently discovered another problem: if the notifying program
deletes one row in a table, and the user tries to modify the contents of that
row (since he stills sees it, because the changes are invisible to him) the
database gets corrupted and it can't be accessed anymore from neither
application. Reads to the "nonexistent" data are OK, but they just should not
be there.

I've tried to change everything from transaction isolation method to rowset
properties regarding to the data visibility and the like... nothing works
except the "true" reset of the database file (closing all connections and
opening them again when you need them)

Is there a known issue, patch or anything I have to do explicitly to solve
this problem?

Any ideas will be welcome.

Thanx

Reply to this Message

Ginny Caughey MVP - 23 Jul 2008 19:26 GMT

This doesn't sound quite right to me, so let me ask you some questions and
see if we can figure this out.

1. What version of SQL CE are you using? I'm assuming 3.1. If so, there is
also 3.5 that is available, and 3.5 sp1 which is in beta and due to be
released very soon.
2. Are you accessing the database from an app written in C++? The OleDb
implementation for SQL CE is not a complete implementation and you'd get
more functionality using the managed code provider if you are writing in a
managed code language.
3. Are you accessing the database from multiple threads in any of the apps?
4. Are all the apps accessing the database on the same physical machine?
5. How is the main app "looking" to see changes? Is it issuing a new query
for example? It wouldn't seem to me that you'd need a new connection to
issue a new query, but I don't use OleDb against SQL CE data so I might have
missed something.

Thanks,

Signature

Ginny Caughey
Device Application Development MVP

> Hello all... I'm having a very annoying issue that's about to make me
> crazy.
[quoted text clipped - 43 lines]
>
> Thanx

Reply to this Message

sardo007 - 23 Jul 2008 20:06 GMT

Hi Ginny thanks for the reply... I answer each question individually.

> 1. What version of SQL CE are you using? I'm assuming 3.1. If so, there is
> also 3.5 that is available, and 3.5 sp1 which is in beta and due to be
> released very soon.

I'm using SQL CE 3.0 (2005 Mobile edition) I could have tried using newer
versions but we have more than one thousand users splitting more than one
country, with difficulties to connect (the application allows them to work
online and offline depending on the connection availability) and it would not
be practical to change the database version. In case there's no other way
we'd have to assume the costs, but that would be the last resort. They use
more than 10 types of terminals with older and newer versions of Windows
Mobile 2005 and the siwtch would be very painful.

> 2. Are you accessing the database from an app written in C++? The OleDb
> implementation for SQL CE is not a complete implementation and you'd get
> more functionality using the managed code provider if you are writing in a
> managed code language.

Yes, the application is written in C++ Managed code is just not practical
for the application... It's been working since PocketPC 2003, we tried to
migrate it to CFW for the 2005 version and its just too slow in some of the
devices we have in our customer's users. So it has to be just native code. I
know Microsoft is encouraging everyone to move to .NET and we have done son
in our web and PC applications, but mobile devices are still too slow running
complex applications with .NET Wrapping the managed code driver into a COM
that I can use in the C++ application would eat up all the memory of the
device. I also posted an issue here (still not solved) about SQL CE eating
device's memory slowly through the application cycle, but since it seems
there's no solution we have worked around the biggest issues. I know the
OLEDB driver for SQL CE does not implement every function, but one of the
things it claims to solve is the multiuser access, so I hope this is
addresses correctly. I don't know it it's a bug or I'm just missing something.

> 3. Are you accessing the database from multiple threads in any of the apps?

The main application has more than one thread accessing the database, but
even disabling the second thread and leaving one thread per application the
problem persists. The notifying application has only one thread accessing
data.

> 4. Are all the apps accessing the database on the same physical machine?

Yes, both applications are located in the same mobile device. The main
application is a form-based user-entry application. The notifying application
receives data via remote connections, writes it to the database and makes any
changes requested by the host application (deletions and updates)

> 5. How is the main app "looking" to see changes? Is it issuing a new query
> for example?

Yes. I uses a plain windows timer, since receiving notifications is not a
realtime issue. The user just has to know it has available data. When the
timer message is processed the application runs a query again the
notification table and in case it has new data, it notifies de user. The user
then opens his work forms (they are normaly listviews) and it's in those
forms in which he still sees deleted data or does not see newly inserted
records. The notifying loop is also failing to detect the presence of new
notifications, but we had worked around it with a named event and a blocking
thread.

> It wouldn't seem to me that you'd need a new connection to
> issue a new query, but I don't use OleDb against SQL CE data so I might have
> missed something.

The pplication was written in the SQCE 2003 style: only one connection and
everything done on it. So the connection was open at the beginning and kept
that way. Both applications exchanged messages to close and open the
connection since they could not open it at the same time. Since SQLCE 2005
does not have that problem we changed the model and now they both use their
own connections. One of the well-behaving things we learned was: open the
connection, make your tasks and then close it again. The problem with that
issue (it is the only one working well) is that's too slow (just on some
devices) while keeping one connection open all the time for everything or
just using a dummy connection and opening/closing the others triggers the
data visibility problem.

We can now reproduce it in a simpler way:

1- Open the main application
2- Delete some rows in a table using isql
3- Open the data form in main application... changes are invisible (deleted
rows still there)

I had neveer seen this behaviour before...

Thank you very much

Reply to this Message

Ginny Caughey MVP - 23 Jul 2008 20:31 GMT

Thanks for your detailed answers. Perhaps somebody with more OleDb
experience will spot something. I'm afraid that I don't. Your approach of
opening one connection for the lifetime of the app and keeping it open seems
to me that it should work to improve performance, and unless you aren't
committing your changes made on the other connection(s) I don't understand
why the changes aren't visible when you execute a new query. I don't recall
if this was a problem that has been fixed in a newer version of SQL CE, but
I do understand the issue of testing and rolling out that kind of change to
many different types of devices.

Signature

Ginny Caughey
Device Application Development MVP

> Hi Ginny thanks for the reply... I answer each question individually.
>>
[quoted text clipped - 103 lines]
>
> Thank you very much

Reply to this Message

sardo007 - 23 Jul 2008 20:40 GMT

Thank you very much... I'll see if somebody can help... in the meantime I'll
try with the newer versions of SQLCE and in case they work fine we'll have to
discuss it with the commercial staff and with the customer... In the meantime
if there's anyone else who has faced a similar issue, I would appreciate any
help.

Thanks again for everything Ginny

> Thanks for your detailed answers. Perhaps somebody with more OleDb
> experience will spot something. I'm afraid that I don't. Your approach of
[quoted text clipped - 113 lines]
> >
> > Thank you very much

Reply to this Message

Laxmi Narsimha Rao Oruganti [MSFT] - 24 Jul 2008 06:21 GMT

1) Performance
Please stick to having an "dummy" connection in Open State.

2) Data Visibility
By default, SQL CE/Mobile has a background thread that flushes the changes
to disk. So it is a kind of asynchronous commit which happens every 10
seconds. If you want to commit the changes immediately/synchronously,
please use DBPROPVAL_SSCE_TCM_FLUSH and see if that helps your data
visibility problem. If you want to go with asynchronous commit, but wanted
to change the interval of asynchronous flush, please use
DBPROP_SSCE_FLUSH_INTERVAL.

3) Long running application leads to memory leak
I am not sure of this claim as we have many applications built on SQL CE and
they run for long hours and we have not seen memory leaks so far. However,
I see that it is just a perception. SQL CE has a shared memory to host
preallocated buffer pool, ...etc. This will be there as occupied/used for
the lifetime of SQL CE usage. This is not a memory leak but just the
preallocated buffer held by SQL CE. If you want to change the parameters
that affect the shared memory, please look at Books Online. Note that,
decresing buffer pool preallocation size affects performance.

Thanks,
Laxmi

> Thank you very much... I'll see if somebody can help... in the meantime
> I'll
[quoted text clipped - 168 lines]
>> >
>> > Thank you very much

Reply to this Message

Laxmi Narsimha Rao Oruganti [MSFT] - 24 Jul 2008 07:57 GMT

Hmm..correcting my reply now!

Data Visibility should not be a problem as long as it is committed, it need
not have been flushed to disk. Raja , the OLEDB Expert in my team, says
that you might be using scrollable-*insensitive* cursor which has a cached
result set. If you do not want to cache, you might want to open
scrollable-sensitive cursor which directly reads from the table. If this is
not possible, you need to execute the query again.

Thanks,
Laxmi

> 1) Performance
> Please stick to having an "dummy" connection in Open State.
[quoted text clipped - 198 lines]
>>> >
>>> > Thank you very much

Reply to this Message

sardo007 - 24 Jul 2008 09:51 GMT

Thank you very much for your answer...

> Data Visibility should not be a problem as long as it is committed, it need
> not have been flushed to disk. Raja , the OLEDB Expert in my team, says
> that you might be using scrollable-*insensitive* cursor which has a cached
> result set. If you do not want to cache, you might want to open
> scrollable-sensitive cursor which directly reads from the table. If this is
> not possible, you need to execute the query again.

I'll look around to find how it's done (I'll send you the properties and
values I'm currently using) Anyway, I run the query every time, that's what
annoys me. I'm creating a new command, setting the command text and executing
it every time, so I don't think it would be the problem... I'll check for the
sensitive cursor mode and try to see if it improves the issue.

> Please stick to having an "dummy" connection in Open State.

I'm trying to do it by all means... it improves the performance
exponentially... and I tnik it's normal... it's avoiding a lot of overhead.
There are some devices though that run very fast even without the dummy
connection, so our application allows the user to configure the presence of
the dummy connection.

> By default, SQL CE/Mobile has a background thread that flushes the changes
> to disk. So it is a kind of asynchronous commit which happens every 10
> seconds.

I've tried the TCM_FLUSH property in the session... it does not affect the
performance at all whe I keep the dummy connection open (I know there should
be some performance penalty but it's imperceptible for my application)
Whereas you use this flag or not, the data visibility issue persists. It
makes sense, because of what you say in your second message: transactions
have to be committed, not neccesarily flushed. Anyway, even when they are
flushed inmediately the problem persists.

> 3) Long running application leads to memory leak
> I am not sure of this claim as we have many applications built on SQL CE
> and they run for long hours and we have not seen memory leaks so far.

I'm not sure whether there's a problem with SQL CE or our application.
That's why we've try to work around the problem. Memory leaks are very
difficult to trace in the mobile environment, so we're more concerned with
the data visibility issue which is out of our reach. In fact, when a mobile
user has an application error he's more prone to reset the device and start
over again than a desktop or web user is. It's hard to say, but that's the
way it is. Restarting a mobile application in a timely fashion is something a
user is able to understand... they're even prone to open the application,
execute the selected tasks and then close it.

> However, I see that it is just a perception. SQL CE has a shared memory
> to host preallocated buffer pool, ...etc. This will be there as
> occupied/used for the lifetime of SQL CE usage. This is not a memory leak
> but just the preallocated buffer held by SQL CE. If you want to change
> the parameters that affect the shared memory, please look at Books Online.
> Note that, decresing buffer pool preallocation size affects performance.

Yes, I knew that... and that's why I don't touch such parameters, since I
know that may be worse than the illness :-P Current devices have enough
storage space to deal with the configuration SQL CE needs. The biggest
problem I still see in Windows Mobile is the process memory limit. We've had
to split some applications into two to avoid this problem. It seems to be
present in WM6 too, so we'll have to wait for a definitive solution.

These are the OLEDB parameters and values I set in the command:

DBPROP_IRowsetChange = VARIANT_FALSE
DBPROP_BOOKMARKS = VARIANT_FALSE
DBPROP_OWNUPDATEDELETE = VARIANT_TRUE
DBPROP_OWNINSERT =VARIANT_TRUE
DBPROP_OTHERUPDATEDELETE =VARIANT_TRUE
DBPROP_OTHERINSERT =VARIANT_TRUE
DBPROP_QUICKRESTART =VARIANT_FALSE
DBPROP_CANFETCHBACKWARDS =VARIANT_TRUE
DBPROP_CANSCROLLBACKWARDS=VARIANT_TRUE
DBPROP_IRowsetUpdate =VARIANT_FALSE
DBPROP_UPDATABILITY =0

I know some of these properties are not honored by the driver, but in those
cases, setting them has just no effect.

Thank you very much in advance...

Reply to this Message

sardo007 - 24 Jul 2008 18:23 GMT

Well.. good news and bad news...

I've been trying the SQL CE Compact 3.5 and the good news is the corruption
problem seems to be gone, at least it's not showing everytime, just with
specific patterns. The bad news is the problem persists. I'm using my
application and isql35 to access the same database. Neither application can
see the changes made by the other till it is closed and restarted. Anyway the
following scenario always causes a database corruption:

1- Application open. New row created. Say it's row 1.
2- Isql open. Row 1 is visible.
3- New row creation. Say it's row 2.
4- Row 2 is not visible in Isql.
5- Row 1 is modified inside Isql. Change is not visible in application but
it is visible within Isql.
6- Application is closed.
7- Row 2 is still invisible in Isql. Row 1 is reflecting the change Isql
made before. Changes of the application are lost.
8- Application open.
9- Application sees Row 1 with the changes made by Isql in the previous
step. Row 2 is gone.
10- Application tries to modify Row 1. SQL CE fails with E_UNEXPECTED.
Database is corrupted.
11- Isql continues to work until it's closed. Once it's started again it
cannot access database anymore.

This pattern can be repeated everytime. It always behaves as described.

Any ideas?

Thanx in advance.

Reply to this Message

sardo007 - 25 Jul 2008 20:24 GMT

Well... sorry for the invonveniences... I've finally found the cause. It's
nothing to do with OLEDB, Windows Mobile nor SQL Server.

We had managed to write another small application taht could write to the
database and "be seen" by Isql and the notifying program. The main
application still was unable to see changes made by the new application so wh
thought it was the main application which was missbehaving. Everything
changed when we deployed some beta versions to our test team working with a
DIFFERENT device. Everything was working fine!! So we took it over from the
start again ataking the device as our main subject. We ran the application
pair against six different device models and all except the first one ran
perfectly. It seems that device model has some filesystem cache issue that's
causing some kind of paging trouble... It was failing in 4 different sets,
all running the same version of OS ROM. We're working with the manufacturer
to find the cause, though installing a previous revision of the OS ROM solved
the problem for those devices. As I said, all the other models we've tried
today worked fine.

We're planning an extensive lab test with more device models to spot any
other missbehaving but knowing already the application is running fine, and
there is no problem with the tools used. It seems we got obsessed with this
issue and we didn' address it in the best possible way: isolating the causes
and trying to simulate the problem in other environments (we have an emulator
with the same ROM image and it also missbehaves) I'm both very sorry for
taking up your time and very grateful for answering to my messages.

Reply to this Message

Ginny Caughey MVP - 25 Jul 2008 22:13 GMT

Thanks for the update!

Signature

Ginny Caughey
Device Application Development MVP

> Well... sorry for the invonveniences... I've finally found the cause. It's
> nothing to do with OLEDB, Windows Mobile nor SQL Server.
[quoted text clipped - 31 lines]
> with the same ROM image and it also missbehaves) I'm both very sorry for
> taking up your time and very grateful for answering to my messages.

Reply to this Message