I'm looking for a cause of a really strange case of SQL Server Compact db corruption. The case is that in a .NET Compact Framework app that we are running every once in a while we get into a situation where the following statement returns no rows
select * from order_item where id = '043740d1-c6d0-4fd4-ae84-fdb9218f8b81'
select * from order_item where id LIKE '043740d1-c6d0-4fd4-ae84-fdb9218f8b81'
returns one. Actually, when you select all rows from the table you can see that this row indeed exists in the db. The id column is uniqueidentifier, so it's not related to casing etc.
In any case, when I run the sdf file through Repair in SSMS, the first query starts returning the correct row.
I can only guess that the problem is related to some sort of index corruption - maybe it gets out of sync with the actual data store. Neither the app nor SSMS gives any sign of DB corruption - no exceptions, no messageboxes whatsoever.
Anyway, I can't really get to the root cause of this. I think it might have to do with modifying the db from different threads (at some points we may have 2 threads working on the db), but I thought this shouldn't be an issue.
Any idea what might cause this sort of issue?
UPDATE: As @Catcall mentioned dropping the index fixed the issue, so it seems it's related to an out-of synch index. I think it's likely happening when the row is deleted - the index gets updated but the table row isn't for some reason.