title: How to lock table entries in a SQL Server Compact Database?
body: <p>I have the following problem:</p>
  
  <p>I´m using a SQL Server CE 4.0 DB to store data. The database file can be located on any computer in a private network and should be accessible by multiple client computers from the same network. If one user "loads" an object from the database to modify it, this object should be locked against access by other clients until the "owner" saves and thereby releases an object. </p>
  
  <p>So my (simplified) DB looks like this:</p>
  
  <p>Table <code>projects</code></p>
  
  <pre><code>int Project-ID      |     string SomeValue      |      bool IsLocked
  --------------------------------------------------------------------
        01            |        Value1             |          false
        02            |        Value2             |           true
  ...
  ...
  </code></pre>
  
  <p>and so on</p>
  
  <p>It works fine so far, but if a client computer crashes while editing an object, this object will be locked forever. To avoid this I could think of several approaches:</p>
  
  <ul>
  <li>If a user wants to open a locked object, he´s given the chance to unlock it, so he can unlock an object which he knows is not to be locked.</li>
  <li>Switch from SQL Server CE to a "proper" DB and manage connections and locks by server programming or use of triggers. This would afford to install a DB Server which I want to avoid. Only the application should be installed on cutsomer´s systems. The place of the DB shall be chosen with a simple file-choser.</li>
  <li>My favorite: a client modifying an object has to actively contact the DB in a fixed timespan to maintain the lock. To implement this, I added a column to the DB in which a client who locks an object writes a timestamp every five minutes. Then, if another client wants to edit a locked object which timestamp is older then five minutes, he is allowed to do so, for the lock is invalid. Problem is: All clients have to use the same time.</li>
  </ul>
  
  <p>My questions:</p>
  
  <ul>
  <li>Is there a way to let the SQL Server CE database determine a reference time? Like the time on the machine it´s running on?</li>
  <li>Does anyone know another way of locking objects/table rows? Maybe there is same build-in functionality in SQL Server CE or in the EF6 which I use?</li>
  </ul>