title: Lock row, release later
body: <p>I'm trying to understand how to lock a row, and only release that lock later.</p>
  
  <p>I have a table like this :</p>
  
  <pre><code>create table testTable (Name varchar(100));
  </code></pre>
  
  <p>Some test data</p>
  
  <pre><code>insert into testTable (name) select 'Bob';
  insert into testTable (name) select 'John';
  insert into testTable (name) select 'Steve';
  </code></pre>
  
  <p>Now, I want to select one of those rows, and prevent other other queries from seeing this row. I achieve that like this :</p>
  
  <pre><code>begin transaction;
  select * from testTable where name = 'Bob' for update;
  </code></pre>
  
  <p>In another window, I do this :</p>
  
  <pre><code>select * from testTable for update skip locked;
  </code></pre>
  
  <p>Great, I don't see 'Bob' in that result set. Now, I want to do something with the primary retrieved row (Bob), and after I did my work, I want to release that row again. Simple answer would be to do :
      commit transaction</p>
  
  <p>However, I am running multiple transactions on the same connection, so I can't just begin and commit transactions all over the show. Ideally I would like to have a "named" transaction, something like :</p>
  
  <pre><code>begin transaction 'myTransaction';
  select * from testTable where name = 'Bob' for update;
  //do stuff with the data, outside sql then later call ...
  commit transaction 'myTransaction';
  </code></pre>
  
  <p>But postgres doesn't support that. I have found "prepare transaction", but that seems to be a pear-shaped path I don't want to go down, especially as these transaction seem to persist through restarts even.</p>
  
  <p>Is there anyway I can have a reference to commit/rollback for a specific transaction?</p>