3

SqlCE has a parameter set on the Connect String called Flush Interval. It is defined as:

The interval time (in seconds) before all committed transactions are flushed to disk. If not specified, the default value is 10.

I thought that a committed transaction, by definition, is a transaction that has been flushed to disk, specifically the database file. If a transaction is only stored in RAM then cannot the transaction be easily lost?

I thought that transactions were first written to a log file and then applied to the database file itself, so perhaps this parameter could mean the time to wait until the transaction log is applied to the database file?

I would have thought that this parameter should be 0.

UPDATE-------

Let me put my database internals hat on. As I understand it, when an application starts a transaction a start-of-transaction record is written to a database LOG, then each added, changed, or deleted record, is written to the LOG then an end-of-transaction record. A separate thread detected the end-of-transaction and moved the records from the LOG to the DATABASE. When this was complete a transaction ID was incremented to indicate that the transaction was complete. If the process crashed anywhere, when the database started it would would check the LOG to ascertain the state of the database and either finish or roll back open transactions. All of this implies that work is written to disk at all steps of the process.

If Flush Interval was the time to write from the LOG to the DATABASE then everything makes sense, but if the transaction is held in RAM not a LOG then the database cannot be ACID compliant.

NormD
  • 549
  • 4
  • 9
  • 19

2 Answers2

1

With newer versions, the Commit operation is overloaded. If you call Commit with a CommitMode.Immediate parameter, the Flush-Interval setting is ignored, and changes are persisted to the file immediately. The default option is CommitMode.Deferred (in the parameter-less call) which is based on the Flush-Interval value.

Refer to my post on SQLCE Corruptions: resolving corruption in SQL Server Compact Edition database files

Community
  • 1
  • 1
R D
  • 521
  • 5
  • 12
0

With older versions there were indeed corruption problems. Worst case scenario with the more recent versions is missing data.

You cannot set the parameter to zero, though. 1 is the minimum.

Kenneth
  • 1,364
  • 1
  • 8
  • 11