title: Postgres trigger and row locking
body: <p>Please help with my understanding of how triggers and locks can interact</p>
  
  <p>I bulk load records to a table with statements something like this…..</p>
  
  <pre><code>BEGIN;
  INSERT INTO table_a VALUES (record1) , (record2), (record3)………;
  INSERT INTO table_a VALUES (record91) , (record92), (record93)………;
  …..
  ….
  COMMIT;
  </code></pre>
  
  <p>There can be several hundred records in a single insert, and there can be several dozen INSERT statements between COMMITs</p>
  
  <p>Table_a has a trigger on it defined as…. </p>
  
  <pre><code>AFTER INSERT ON table_a FOR EACH ROW EXECUTE PROCEDURE foo();
  </code></pre>
  
  <p>The procedure <em>foo()</em> parses each new row as it’s added, and will (amongst other stuff) update a record in a summary <em>table_b</em> (uniquely identified by primary key).  So, for every record inserted into <em>table_a</em> a corresponding record will be updated in <em>table_b</em></p>
  
  <p>I have a 2nd process that also attempts to (occasionally) update records in <em>table_b</em>. On very rare occasions it may attempt to update the same row in <em>table_b</em> that the bulk process is updating</p>
  
  <p><strong>Questions</strong> – should anything in the bulk insert statements affect my 2nd process being able to update records in <em>table_b</em>? I understand that the bulk insert process will obtain a row lock each time it updates a row in <em>table_b</em>, but when will that row lock be released? – when the individual record (record1, record2, record3 etc etc) has been inserted? Or when the entire INSERT statement has completed? Or when the COMMIT is reached?</p>
  
  <p><strong>Some more info</strong> - my overall purpose for this question is to try to understand why my 2nd process occasionally pauses for a minute or more when trying to update a row in table_b that is also being updated by the bulk-load process. What <em>appears</em> to be happening is that the lock on the target record in <em>table_b</em> isn't actually being released until the COMMIT has been reached - which is contrary to what I think ought to be happening. (I think a row-lock should be released as soon as the UPDATE on that row is done)</p>
  
  <hr>
  
  <p>UPDATE after answer(s) - yes of course you're both right. In my mind I had somehow convinced myself that the individual updates performed within the trigger were somehow separate from the overall BEGIN and COMMIT of the whole transaction. Silly me.</p>
  
  <p>The practice of adding multiple records with one INSERT, and multiple INSERTs between COMMITs was introduced to improve the bulk load speed (which it does) I had forgotten about the side-effect of increasing the time before locks would be released.</p>