SQL Compact and threads

Do you remember the days of Pocket PC 2002 and 2003? Life was pretty simple with no need for certificates, a single screen size and SQL CE only ran on devices. Then Microsoft released SQL Compact 3.1 and suddenly the small database was also on the desktop. By this time I already had a small set of tools that remotely accessed the engine via a streamed RAPI interface and was confronted with the need to port the code to the desktop.

In its classical form, the code runs in two different processors with an asynchronous link. Porting the code to the desktop, where both the client and the server ran on the same process, meant either a major rewrite or a smart adaptation. I chose the latter and implemented a DMA data stream that is implemented by the "server" DLL on the desktop. The client merely uses a virtual stream that can be attached to a variety of server endpoints, like a RAPI server, a TCP/IP server or even a local server running in the same process (but on different threads).

This has worked quite successfully until last week when I got a report from a customer complaining about a mysterious error. After exporting a very large (and I mean very large) database from SQL Server to SQL Compact 3.5 on the desktop, the database file would end up with over 350 MB of size but zero tables. After repairing the database it would shrink down to the 20 KB empty databases have.

I first suspected of the transaction mechanism because the whole process (table creation, data transfer and final script execution) is executed under a single transaction. After confirming with Microsoft that there was no known transaction size limitation, I got back to the drawing board in bewilderment. To make matters worse, no error was being reported!

The product that caused this issue (Data Port Wizard) has a very simple usage pattern: you set the source and target databases, set some options and run the data transfer process. At the end the application reports success or error and then quits (after user confirmation). As it turned out, the problem happened when the application exited.

When transferring data to a desktop SDF file, everything works under the same desktop process. The SQL Compact data provider runs in its own thread (not on the UI thread) due to the reason I pointed out at the beginning: I wanted to reuse the remote provider code that runs on a different CPU, so I implemented it as running on a different thread with a DMA communication to the client code. When the client application closes, it signals the SQL Compact data provider to shut down and waits a little while for it to reply. If no reply arrives on time, the desktop client proceeds to terminate the application.

Apparently with very large databases, the SQL Compact engine takes a bit longer to shut down. This is in fact something to be expected because there are larger buffers to flush to storage and there is also the auto-shrink feature that runs when the connection closes. When this whole thing is running on a remote device, your desktop client can afford not to wait for the remote provider to shut down because it is running in a different CPU. When running under the same process, you don't have that luxury. If you don't wait for the SQL Compact engine to shut down in its own thread and you shut down the application, you will be preventing the database engine from correctly shutting down altogether. You can imagine the mayhem this causes to your database...

Lesson: if you run SQL Compact on a different thread, make sure you wait until the connection closes before you shut down your application. You have been warned!