title: TransactionScope and Optimistic concurrency
body: <p>I would like to use optimistic concurrency with <code>TransactionScope</code>. Here's the code I come up so far:</p>
  
  <pre><code>var options = new TransactionOptions {IsolationLevel = IsolationLevel.ReadCommitted};
  using (var scope = new TransactionScope(TransactionScopeOption.Required, options))
  {
      using (var connection = new SqlConnection(_connectionString))
      {
          // ... execute some sql code here
  
          // bump up version
          var version = connection.ExecuteScalar&lt;DateTime&gt;(@"
              DECLARE @version datetime2 = SYSUTCDATETIME();
              UPDATE [Something].[Test]
                  SET [Version] = @version
                  WHERE Id = @Id
              SELECT @version
          ", new {Id = id});
  
          // ... execute more sql code here
  
          // check if version has not changed since bump up
          // NOTE: version is global for the whole application, not per row basis
          var newVersion = connection.ExecuteScalar&lt;DateTime&gt;("SELECT MAX([Version]) FROM [Something].[Test]");
          if (newVersion == version) scope.Complete(); // looks fine, mark as completed
      }
  } // what about changes between scope.Complete() and this line?
  </code></pre>
  
  <p>Unfortunately this code has one serious issue. Between version check and transaction commit there might be some change in the database. It's a standard <a href="https://en.wikipedia.org/wiki/Time_of_check_to_time_of_use" rel="nofollow noreferrer">time of check to time of use</a> bug. The only way I can see to resolve it is to execute version check and transaction commit as a single command.</p>
  
  <p>Is it possible to execute some SQL code along with transaction commit using <code>TransactionScope</code>? If no then what other solution could be used?</p>
  
  <p>EDIT1:
  Version needs to be per application, not per row.</p>
  
  <p>EDIT2:
  I could use serializable isolation level, but it is not an option due to performance issues this would cause.</p>