title: Have "select for update" block on nonrexisting rows
body: <p>we have some persistent data in an application, that is queried from a server and then stored in a database so we can keep track of additional information. Because we do not want to query when an object is used in the memory we do an <code>select for update</code> so that other threads that want to get the same data will be blocked.</p>
  
  <p>I am not sure how <code>select for update</code> handles non-existing rows. If the row does not exist and another thread tries to do another <code>select for update</code> on the same row, will this thread be blocked until the other transaction finishes or will it also get an empty result set? If it does only get an empty result set is there any way to make it block as well, for example by inserting the missing row immediately?</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>Because there was a remark, that we might lock too much, here some more details on the concrete usage in our case. In reduced pseudocode our programm flow looks like this:</p>
  
  <pre><code>d = queue.fetch();
  r = SELECT * FROM table WHERE key = d.key() FOR UPDATE;
  if r.empty() then
    r = get_data_from_somewhere_else();
  
  new_r = process_stuff( r );
  
  
  if Data was present then
     update row to new_r
  else
     insert new_r
  </code></pre>
  
  <p>This code is run in multiple thread and the data that is fetched from the queue might be concerning the same row in the database (hence the lock). However if multiple threads are using data that needs the same row, then these threads need to be sequentialized (order does not matter). However this sequentialization fails, if the row is not present, because we do not get a lock.</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>For now I have the following solution, which seems like an ugly hack to me.</p>
  
  <pre><code>select the data for update
  if zero rows match then
    insert some dummy data   // this will block if multiple transactions try to insert
    if insertion failed then
      // somebody beat us at the race
      select the data for update
  
  do processing
  
  if data was changed then
     update the old or dummy data
  else
     rollback the whole transaction
  </code></pre>
  
  <p>I am neither 100% sure however that this actually solves the problem, nor does this solution seem good style. So if anybody has to offer something more usable this would be great.</p>